| < Day Day Up > |
|
There are three package procedures in the ORA_EMP database:
CREATE OR REPLACE PACKAGE AccountPackage AS -- Add a new Employee into the specified Account. PROCEDURE AddEmployee(p_EmployeeID IN Employees.emp_id%TYPE, p_dept_code IN accounts.dept_code%TYPE, p_acct_id IN accounts.acct_id%TYPE); -- Removes the specified Employee from the specified Account. PROCEDURE RemoveEmployee(p_EmployeeID IN Employees.emp_id%TYPE, p_dept_code IN accounts.dept_code%TYPE, p_acct_id IN accounts.acct_id%TYPE); PROCEDURE AccountList(p_dept_code IN accounts.dept_code%TYPE, p_acct_id IN accounts.acct_id%TYPE, p_NumEmployees OUT NUMBER); END AccountPackage; CREATE OR REPLACE PACKAGE BODY AccountPackage AS -- Add a new Employee for the specified account. PROCEDURE AddEmployee(p_EmployeeID IN Employees.emp_id%TYPE, p_dept_code IN accounts.dept_code%TYPE, p_acct_id IN accounts.acct_id%TYPE) IS BEGIN INSERT INTO Employees (Emp_id, dept_code, acct_id) VALUES (p_EmployeeID, p_dept_code, p_acct_id); COMMIT; END AddEmployee; -- Removes the specified Employee from the specified account. PROCEDURE RemoveEmployee(p_EmployeeID IN Employees.emp_id%TYPE, p_dept_code IN accounts.dept_code%TYPE, p_acct_id IN accounts.acct_id%TYPE) IS e_EmployeeNotRegistered EXCEPTION; BEGIN DELETE FROM Employees WHERE Emp_id = p_EmployeeID AND dept_code = p_dept_code AND acct_id = p_acct_id; -- Check to see if the DELETE operation was successful. If -- it didn't match any rows, raise an error. IF SQL%NOTFOUND THEN RAISE e_EmployeeNotRegistered; END IF; COMMIT; END RemoveEmployee; PROCEDURE AccountList(p_dept_code IN accounts.dept_code%TYPE, p_acct_id IN accounts.acct_id%TYPE, p_NumEmployees OUT NUMBER) IS v_EmployeeID Employees.Emp_id%TYPE; -- Local cursor to fetch the registered Employees. CURSOR c_RegisteredEmployees IS SELECT Emp_id FROM Employees WHERE dept_code = p_dept_code AND acct_id = p_acct_id; BEGIN /* p_NumEmployees will be the table index. It will start at 0, and be incremented each time through the fetch loop. At the end of the loop, it will have the number of rows fetched, and therefore the number of rows returned in p_IDs. */ p_NumEmployees := 0; OPEN c_RegisteredEmployees; LOOP FETCH c_RegisteredEmployees INTO v_EmployeeID; EXIT WHEN c_RegisteredEmployees%NOTFOUND; p_NumEmployees := p_NumEmployees + 1; END LOOP; END AccountList; END AccountPackage; CREATE OR REPLACE PACKAGE "REFPKG" AS TYPE RCT1 IS REF CURSOR; END REFPKG; /
| < Day Day Up > |
|