Packages

---

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; / 

---