Mega Code Archive
A package to manage a list of employees
<![CDATA[
SQL>
SQL> CREATE TABLE employee(
2 emp_id INTEGER,
3 emp_name VARCHAR2(32),
4 supervised_by INTEGER,
5 pay_rate NUMBER(9,2),
6 pay_type CHAR);
Table created.
SQL>
SQL> CREATE TABLE department
2 (dept_id INTEGER,
3 dept_name VARCHAR2(32));
Table created.
SQL>
SQL> CREATE TABLE emp_dept (emp_id INTEGER, dept_id INTEGER);
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE package emp_dept_procs AS
2 PROCEDURE init_list;
3 PROCEDURE add_to_list (emp_id IN emp_dept.emp_id%TYPE,dept_id IN emp_dept.dept_id%TYPE);
4 FUNCTION get_count RETURN NUMBER;
5 PROCEDURE get_from_list (to_get IN BINARY_INTEGER,emp_id OUT emp_dept.emp_id%TYPE,dept_id OUT emp_dept.dept_id%TYPE);
6 END emp_dept_procs;
7 /
Package created.
SQL>
SQL> CREATE OR REPLACE package body emp_dept_procs AS
2 listx BINARY_INTEGER;
3 TYPE emp_dept_pk IS RECORD (emp_id emp_dept.emp_id%TYPE,dept_id emp_dept.dept_id%TYPE);
4 TYPE emp_dept_list_type IS TABLE OF emp_dept_pk INDEX BY BINARY_INTEGER;
5
6 emp_dept_list emp_dept_list_type;
7
8 PROCEDURE init_list is
9 BEGIN
10 listx := 0;
11 END;
12
13 PROCEDURE add_to_list (emp_id IN emp_dept.emp_id%TYPE ,dept_id IN emp_dept.dept_id%TYPE) IS
14 BEGIN
15 listx := listx + 1;
16 emp_dept_list(listx).emp_id := emp_id;
17 emp_dept_list(listx).dept_id := dept_id;
18 END;
19
20 FUNCTION get_count RETURN NUMBER IS
21 BEGIN
22 RETURN listx;
23 END;
24
25 PROCEDURE get_from_list (to_get IN BINARY_INTEGER ,emp_id OUT emp_dept.emp_id%TYPE ,dept_id OUT emp_dept.dept_id%TYPE) IS
26 BEGIN
27 emp_id := emp_dept_list(to_get).emp_id;
28 dept_id := emp_dept_list(to_get).dept_id;
29 END;
30
31 END emp_dept_procs;
32 /
Package body created.
SQL>
SQL> drop table department;
Table dropped.
SQL> drop table employee;
Table dropped.
SQL> --
]]>