A package to manage a list of employees

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

Related examples in the same category

1.	Create a function to convert string type variable to date type variable
2.	Fetch a bulk into a table structure
3.	Define a nested table type for each column
4.	Extend once, outside the loop for better performance
5.	Fill table of custom type and use it in for loop to insert
6.	Table of custome type indexed by BINARY_INTEGER
7.	Reference type attribute through index
8.	Use for loop to fill a table collection
9.	Select bulk collect into
10.	Use for loop to insert value to table collection and then use table collection in another insert statement
11.	The EXISTS Table Attribute
12.	FIRST & LAST Table Attributes
13.	NEXT & PRIOR Table Attributes
14.	Uses the COUNT method to display the number of rows contained in a table collection
15.	How to do a bulk collect into a nested table.
16.	How to do a bulk collect into an associative array
17.	Table collection indexed by column type

A package to manage a list of employees : Table of Type « PL SQL « Oracle PL / SQL

Related examples in the same category