Binaryworld - Passing data between Functions and Procedures in Oracle. ... [ ORACLE -> PL-SQL ]

In this code I've tried to show how data might be passed between functions and procedures in Oracle using cursor variables and collections.

I have made use of the example schema HR which is provided by Oracle.

The objective was to create a function that accepted a set of records passed to it as a cursor variable (REF CURSOR) and this function returns data as a NESTED TABLE.

I've created a table that stores the quarterly remuneration paid to the top executives of the company. It stores the employee details, the quarter in consideration (table stores this as the first day of the quarter) and the salary paid for that quarter.

Click here to copy the following block

CREATE TABLE quarterly_remun
(employee_id NUMBER(6),
remun_quarter DATE,
first_name VARCHAR2(20),
last_name VARCHAR2(25),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
manager_id NUMBER(6),
department_id NUMBER(4),
country_id CHAR(2),
CONSTRAINT pk_quarterly_remun PRIMARY KEY(employee_id, remun_quarter) );

Then I create an OBJECT type that can be used to store a record of the above table:

Click here to copy the following block

CREATE or REPLACE TYPE t_ctc_obj AS object
(
employee_id NUMBER(6),
remun_year DATE,
first_name VARCHAR2(20),
last_name VARCHAR2(25),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
manager_id NUMBER(6),
department_id NUMBER(4),
country_id CHAR(2));

--I then create a NESTED TABLE of the type of the object created as above:

CREATE OR REPLACE TYPE t_ctc_rec AS TABLE OF t_ctc_obj;

-- A package, to define a REF CURSOR (to pass data)

CREATE OR REPLACE PACKAGE t_ctc_pkg IS
TYPE refcur_t IS REF CURSOR RETURN employees%ROWTYPE;
END t_ctc_pkg;

Finally the FUNCTION itself, which takes a set of records as a REF CURSOR and RETURNs a NESTED TABLE:

Click here to copy the following block

CREATE OR REPLACE FUNCTION gen_ctc(p_c t_ctc_pkg.refcur_t, d date) RETURN t_ctc_rec
PIPELINED IS
out_rec t_ctc_obj:=t_ctc_obj(NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
in_rec p_c%rowtype;
BEGIN
LOOP
  FETCH p_c INTO in_rec;
  EXIT WHEN p_c%NOTFOUND;
  -- first row
  out_rec.employee_id := in_rec.employee_id;
  out_rec.remun_year := trunc(d,'Q');
  out_rec.first_name := in_rec.first_name;
  out_rec.last_name := in_rec.last_name;
  out_rec.hire_date := in_rec.hire_date;
  out_rec.job_id := in_rec.job_id;
  out_rec.salary := in_rec.salary*(1+Nvl(in_rec.commission_pct,0))*4;
  out_rec.manager_id := in_rec.manager_id;
  out_rec.department_id := in_rec.department_id;
  SELECT country_id
  into out_rec.country_id
  FROM departments d, locations l
  WHERE d.department_id = in_rec.department_id
  AND l.location_id = d.location_id;

  PIPE ROW(out_rec);

  END LOOP;
CLOSE p_c;
RETURN;
END;

I'll be adding some stuff about PIPELINED functions soon. So come back for more!

Here's how you can use the function elsewhere:

Click here to copy the following block

INSERT INTO quarterly_remun
  SELECT * from TABLE(gen_ctc(
     CURSOR(SELECT * FROM employees
     WHERE job_id in ('AD_PRES','AD_VP')
     ORDER BY salary desc),SYSDATE)
  );

SELECT * FROM quarterly_remun;
--EMP_ID RENUM_QTR F_NAM L_NAME HIRE_DATE JOB_ID SALARY MGR DEPT CTR
--100 01-JAN-05 Steven King  17-JUN-87 AD_PRES 96000     90 US
--101 01-JAN-05 Neena Kochhar 21-SEP-89 AD_VP  68000   100 90 US
--102 01-JAN-05 Lex  De Haan 13-JAN-93 AD_VP  68000   100 90 US
--100 01-JUL-05 Steven King  17-JUN-87 AD_PRES 96000     90 US
--101 01-JUL-05 Neena Kochhar 21-SEP-89 AD_VP  68000   100 90 US
--102 01-JUL-05 Lex  De Haan 13-JAN-93 AD_VP  68000   100 90 US