SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York', 'Manager')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
3 /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> CREATE OR REPLACE PACKAGE EmployeePackage AS
2 PROCEDURE AddEmployee(p_EmployeeID IN Employee.id%TYPE,
3 p_first_name IN Employee.first_name%TYPE,
4 p_last_name IN Employee.last_name%TYPE);
5
6 -- Removes the specified student from the specified class.
7 PROCEDURE RemoveEmployee(p_EmployeeID IN Employee.id%TYPE,
8 p_first_name IN Employee.first_name%TYPE,
9 p_last_name IN Employee.last_name%TYPE);
10
11 -- Exception raised by RemoveEmployee.
12 e_EmployeeNotRegistered EXCEPTION;
13
14 -- Table type used to hold student info.
15 TYPE t_EmployeeIDTable IS TABLE OF Employee.id%TYPE INDEX BY BINARY_INTEGER;
16
17 -- Returns a PL/SQL table containing the Employee currently
18 -- in the specified class.
19 PROCEDURE EmployeeList(p_first_name IN Employee.first_name%TYPE,
20 p_last_name IN Employee.last_name%TYPE,
21 p_IDs OUT t_EmployeeIDTable,
22 p_NumEmployees IN OUT BINARY_INTEGER);
23 END EmployeePackage;
24 /
SP2-0808: Package created with compilation warnings
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY EmployeePackage AS
2 PROCEDURE AddEmployee(p_EmployeeID IN Employee.id%TYPE,
3 p_first_name IN Employee.first_name%TYPE,
4 p_last_name IN Employee.last_name%TYPE) IS
5 BEGIN
6 INSERT INTO Employee (id, first_name, last_name) VALUES (p_EmployeeID, p_first_name, p_last_name);
7 COMMIT;
8 END AddEmployee;
9
10 PROCEDURE RemoveEmployee(p_EmployeeID IN Employee.id%TYPE,
11 p_first_name IN Employee.first_name%TYPE,
12 p_last_name IN Employee.last_name%TYPE) IS
13 BEGIN
14 DELETE FROM Employee
15 WHERE id = p_EmployeeID
16 AND first_name = p_first_name
17 AND last_name = p_last_name;
18
19 -- Check to see if the DELETE operation was successful. If
20 -- it didn't match any rows, raise an error.
21 IF SQL%NOTFOUND THEN
22 RAISE e_EmployeeNotRegistered;
23 END IF;
24
25 COMMIT;
26 END RemoveEmployee;
27
28
29 -- Returns a PL/SQL table containing the Employee currently
30 -- in the specified class.
31 PROCEDURE EmployeeList(p_first_name IN Employee.first_name%TYPE,
32 p_last_name IN Employee.last_name%TYPE,
33 p_IDs OUT t_EmployeeIDTable,
34 p_NumEmployees IN OUT BINARY_INTEGER) IS
35
36 v_EmployeeID Employee.id%TYPE;
37
38 -- Local cursor to fetch the registered Employee.
39 CURSOR c_RegisteredEmployees IS
40 SELECT id
41 FROM Employee
42 WHERE first_name = p_first_name
43 AND last_name = p_last_name;
44 BEGIN
45 /* p_NumEmployees will be the table index. It will start at
46 0, and be incremented each time through the fetch loop.
47 At the end of the loop, it will have the number of rows
48 fetched, and therefore the number of rows returned in
49 p_IDs. */
50 p_NumEmployees := 0;
51
52 OPEN c_RegisteredEmployees;
53 LOOP
54 FETCH c_RegisteredEmployees INTO v_EmployeeID;
55 EXIT WHEN c_RegisteredEmployees%NOTFOUND;
56
57 p_NumEmployees := p_NumEmployees + 1;
58 p_IDs(p_NumEmployees) := v_EmployeeID;
59 END LOOP;
60 END EmployeeList;
61 END EmployeePackage;
62 /
SP2-0810: Package Body created with compilation warnings
SQL>
SQL> --Calling a Packaged Procedure
SQL> DECLARE
2 v_HistoryEmployees EmployeePackage.t_EmployeeIDTable;
3 v_NumEmployees BINARY_INTEGER := 20;
4 BEGIN
5 EmployeePackage.EmployeeList('James', 'Cat', v_HistoryEmployees,v_NumEmployees);
6
7 -- Insert these Employee into temp_table.
8 FOR v_LoopCounter IN 1..v_NumEmployees LOOP
9 DBMS_OUTPUT.put_line(v_HistoryEmployees(v_LoopCounter));
10 END LOOP;
11 END;
12 /
08
PL/SQL procedure successfully completed.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>