SQL>
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL> CREATE OR REPLACE PACKAGE empinfo
2 IS
3 bysal CONSTANT INTEGER := 1;
4 bysaldesc CONSTANT INTEGER := 2;
5 bydept CONSTANT INTEGER := 3;
6 byname CONSTANT INTEGER := 4;
7 TYPE two_pieces_t IS RECORD (str VARCHAR2(100), num NUMBER);
8 TYPE emp_cvt IS REF CURSOR RETURN two_pieces_t;
9 FUNCTION open (query_number IN INTEGER) RETURN emp_cvt;
10 PROCEDURE show (query_number IN INTEGER);
11 END;
12 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY empinfo
2 IS
3 FUNCTION open (query_number IN INTEGER) RETURN emp_cvt
4 IS
5 retval emp_cvt;
6 BEGIN
7 IF query_number = bysal
8 THEN
9 OPEN retval FOR
10 SELECT last_name, salary FROM employee ORDER BY salary;
11 ELSIF query_number = bysaldesc
12 THEN
13 OPEN retval FOR
14 SELECT last_name, salary FROM employee ORDER BY salary DESC;
15 ELSIF query_number = bydept
16 THEN
17 OPEN retval FOR
18 SELECT last_name, department_id FROM employee ORDER BY department_id;
19 ELSIF query_number = byname
20 THEN
21 OPEN retval FOR
22 SELECT first_name || ' ' || last_name, salary
23 FROM employee ORDER BY last_name;
24 END IF;
25 RETURN retval;
26 END;
27
28 PROCEDURE show (query_number IN INTEGER)
29 IS
30 cv emp_cvt;
31 rec cv%ROWTYPE;
32 BEGIN
33 cv := open (query_number);
34 LOOP
35 FETCH cv INTO rec;
36 EXIT WHEN cv%NOTFOUND;
37 IF cv%ROWCOUNT = 1
38 THEN
39 DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-'));
40 DBMS_OUTPUT.PUT_LINE ('Contents of Query ' || query_number);
41 DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-'));
42 END IF;
43 DBMS_OUTPUT.PUT_LINE (RPAD (rec.str, 30) || rec.num);
44 END LOOP;
45 CLOSE cv;
46 END;
47
48 END;
49 /
Package body created.
SQL> drop table employee;
Table dropped.