Oracle PL/SQL Tutorial/Cursor/Cursor function

A PL/SQL function that uses a cursor expression

SQL>
SQL>
SQL> create table product(
  2     product_id number(4)     not null,
  3     product_description varchar2(20) not null
  4  );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL>
SQL> create table company(
  2     product_id        number(4)    not null,
  3     company_id          NUMBER(8)    not null,
  4     company_short_name  varchar2(30) not null,
  5     company_long_name   varchar2(60)
  6  );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL> create or replace function f_cursor_exp return NUMBER
  2  is
  3    TYPE rc is REF CURSOR;
  4
  5    CURSOR myCursor IS
  6      SELECT h.product_description,
  7        CURSOR(SELECT o.rupany_long_name
  8               FROM company o
  9               WHERE o.product_id =h.product_id)long_name
 10      FROM product h;
 11
 12    productRecord rc;
 13    v_product_description VARCHAR2(20);
 14    v_company_long_name VARCHAR2(60);
 15  BEGIN
 16
 17    OPEN myCursor;
 18    LOOP
 19      FETCH myCursor INTO v_product_description,productRecord;
 20      EXIT WHEN myCursor%notfound;
 21
 22      LOOP
 23        FETCH productRecord INTO v_company_long_name;
 24        EXIT WHEN productRecord%notfound;
 25        DBMS_OUTPUT.PUT_LINE(v_product_description ||""||v_company_long_name);
 26      END LOOP;
 27    END LOOP;
 28
 29    close myCursor;
 30    RETURN (0);
 31  EXCEPTION WHEN OTHERS THEN
 32    RETURN (SQLCODE);
 33  END;
 34  /
Function created.
SQL>
SQL>
SQL>
SQL> drop table company;
Table dropped.
SQL>
SQL> drop table product;
Table dropped.
SQL>

Cursor expressions using multiple levels of nested cursors

SQL>
SQL>
SQL> create table product(
  2     product_id number(4)     not null,
  3     product_description varchar2(20) not null
  4  );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL>
SQL> create table company(
  2     product_id        number(4)    not null,
  3     company_id          NUMBER(8)    not null,
  4     company_short_name  varchar2(30) not null,
  5     company_long_name   varchar2(60)
  6  );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL>
SQL> create table company_site(
  2     site_no number(4)       not null,
  3     site_descr varchar2(20) not null
  4  );
Table created.
SQL> insert into company_site values (1,"New York");
1 row created.
SQL> insert into company_site values (2,"Washington");
1 row created.
SQL> insert into company_site values (3,"Chicago");
1 row created.
SQL> insert into company_site values (4,"Dallas");
1 row created.
SQL> insert into company_site values (5,"San Francisco");
1 row created.
SQL>
SQL>
SQL> create table org_company_site(
  2     company_id number(8) not null,
  3     site_no number(4) not null
  4  );
Table created.
SQL> insert into org_company_site values (1001,1);
1 row created.
SQL> insert into org_company_site values (1002,2);
1 row created.
SQL> insert into org_company_site values (1003,3);
1 row created.
SQL> insert into org_company_site values (1004,1);
1 row created.
SQL> insert into org_company_site values (1004,2);
1 row created.
SQL> insert into org_company_site values (1004,3);
1 row created.
SQL> insert into org_company_site values (1005,1);
1 row created.
SQL> insert into org_company_site values (1005,4);
1 row created.
SQL> insert into org_company_site values (1005,5);
1 row created.
SQL> insert into org_company_site values (1006,1);
1 row created.
SQL>
SQL> create or replace function f_cursor_exp_complex return NUMBER
  2  is
  3    TYPE rc is REF CURSOR;
  4
  5    CURSOR myCursor IS
  6      SELECT h.product_description,
  7        CURSOR(SELECT o.rupany_long_name,
  8          CURSOR (SELECT s.site_descr
  9                  FROM org_company_site os,company_site s
 10                  WHERE os.site_no =s.site_no
 11                  AND os.rupany_id =o.rupany_id)as site_name
 12               FROM company o
 13               WHERE o.product_id =h.product_id)long_name
 14      FROM product h;
 15
 16    myRecord rc;
 17    company_rec rc;
 18    v_product_description VARCHAR2(20);
 19    v_company_long_name VARCHAR2(60);
 20    v_site_name VARCHAR2(20);
 21  BEGIN
 22
 23    OPEN myCursor;
 24    LOOP
 25
 26      FETCH myCursor INTO v_product_description,myRecord;
 27      EXIT WHEN myCursor%notfound;
 28      LOOP
 29
 30        FETCH myRecord INTO v_company_long_name,company_rec;
 31        EXIT WHEN myRecord%notfound;
 32        LOOP
 33          FETCH company_rec INTO v_site_name;
 34          EXIT WHEN company_rec%notfound;
 35          DBMS_OUTPUT.PUT_LINE(v_product_description ||""||v_company_long_name||""||v_site_name);
 36        END LOOP;
 37      END LOOP;
 38    END LOOP;
 39
 40    close myCursor;
 41    RETURN (0);
 42  EXCEPTION WHEN OTHERS THEN
 43    RETURN (SQLCODE);
 44  END;
 45  /
Function created.
SQL>
SQL> drop table company;
Table dropped.
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL> drop table company_site;
Table dropped.
SQL>
SQL> drop table org_company_site;
Table dropped.
SQL>

Cursor function

SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> create table dept_fy_budget
  2  ( deptno   number(2) ,
  3    fy       date,
  4    amount   number,
  5    constraint dept_fy_budget_pk primary key(deptno,fy)
  6  )
  7  /
Table created.
SQL> insert into dept_fy_budget values( 10, to_date( "01-jan-1999" ), 500 );
1 row created.
SQL> insert into dept_fy_budget values( 10, to_date( "01-jan-2000" ), 750 );
1 row created.
SQL> insert into dept_fy_budget values( 10, to_date( "01-jan-2001" ), 1000 );
1 row created.
SQL>
SQL> select
  2      dept.deptno, dept.dname,
  3      cursor(select empno from emp where deptno = dept.deptno),
  4      cursor(select fy, amount from dept_fy_budget where deptno = dept.deptno)
  5  from dept
  6  where deptno = 10
  7  /

    DEPTNO DNAME          CURSOR(SELECTEMPNOFR CURSOR(SELECTFY,AMOU
---------- -------------- -------------------- --------------------
        10 ACCOUNTING     CURSOR STATEMENT : 3 CURSOR STATEMENT : 4
CURSOR STATEMENT : 3
drop table emp;
     EMPNO
----------
      7782
      7839
      7934

CURSOR STATEMENT : 4
FY            AMOUNT
--------- ----------
01-JAN-99        500
01-JAN-00        750
01-JAN-01       1000

SQL> SP2-0042: unknown command "table emp" - rest of line ignored.
SQL>
SQL> drop table dept;
Table dropped.
SQL>
SQL> drop table dept_fy_budget;
Table dropped.
SQL>

Passing data from one table function to another in a pipelined fashion

SQL>
SQL> CREATE OR REPLACE PACKAGE pkg_table_func
  2  IS
  3    TYPE address_rec IS RECORD
  4    (LINE1 VARCHAR2(20),
  5     LINE2 VARCHAR2(20),
  6     CITY VARCHAR2(20),
  7     STATE_CODE VARCHAR2(2),
  8     ZIP VARCHAR2(13),
  9     COUNTRY_CODE VARCHAR2(4));
 10
 11     TYPE temp_adds IS TABLE OF address_rec;
 12  END;
 13  /
Package created.
SQL>
SQL> CREATE OR REPLACE FUNCTION myProc
  2  RETURN pkg_table_func.temp_adds
  3  PIPELINED
  4  IS
  5    addressValue pkg_table_func.address_rec;
  6  BEGIN
  7    FOR i IN 1..3 LOOP
  8      IF (i=1) THEN
  9        addressValue.line1 :="20 Spring St.";
 10        addressValue.line2 :=null;
 11        addressValue.city :="New York";
 12        addressValue.state_code :="NY";
 13        addressValue.zip :="10020";
 14        addressValue.country_code :="USA";
 15      ELSIF (i=2) THEN
 16        addressValue.line1 :="Suite 206";
 17        addressValue.line2 :="P Blvd";
 18        addressValue.city :="B";
 19        addressValue.state_code :="IL";
 20        addressValue.zip :="60000";
 21        addressValue.country_code :="USA";
 22      ELSIF (i=3) THEN
 23        addressValue.line1 :="1  Dr.";
 24        addressValue.line2 :=null;
 25        addressValue.city :="Vancouver";
 26        addressValue.state_code :="NJ";
 27        addressValue.zip :="22222";
 28        addressValue.country_code :="USA";
 29      END IF;
 30      PIPE ROW(addressValue);
 31    END LOOP;
 32    RETURN;
 33  END;
 34  /
Function created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION f_table_plsql2_pipelined(p_ref_cursor SYS_REFCURSOR)
  2  RETURN pkg_table_func.temp_adds PIPELINED
  3  IS
  4    addressValue1 pkg_table_func.address_rec;
  5    addressValue2 pkg_table_func.address_rec;
  6  BEGIN
  7    LOOP
  8      FETCH p_ref_cursor INTO addressValue1;
  9      EXIT WHEN p_ref_cursor%NOTFOUND;
 10      IF (addressValue1.city="New York") THEN
 11        addressValue2.line1 :="P.O.Box 2215";
 12        addressValue2.line2 :=null;
 13        addressValue2.city :="New York";
 14        addressValue2.state_code :="NY";
 15        addressValue2.zip :="10020-2215";
 16        addressValue2.country_code :="USA";
 17      ELSIF (addressValue1.city="Bloomington") THEN
 18        addressValue2.line1 :="P.O.Box 6615";
 19        addressValue2.line2 :=null;
 20        addressValue2.city :="Bloomington";
 21        addressValue2.state_code :="IL";
 22        addressValue2.zip :="60610-6615";
 23        addressValue2.country_code :="USA";
 24      ELSIF (addressValue1.city="Vancouver") THEN
 25        addressValue2.line1 :="P.O.Box 0001";
 26        addressValue2.line2 :=null;
 27        addressValue2.city :="Vancouver";
 28        addressValue2.state_code :="NJ";
 29        addressValue2.zip :="08540";
 30        addressValue2.country_code :="USA";
 31      END IF;
 32      PIPE ROW(addressValue2);
 33    END LOOP;
 34    close p_ref_cursor;
 35    RETURN;
 36  END;
 37  /
Function created.
SQL>
SQL> SELECT * FROM TABLE(f_table_plsql2_pipelined(
  2                     CURSOR(SELECT * FROM TABLE(myProc()))));
LINE1                LINE2                CITY                 ST
-------------------- -------------------- -------------------- --
ZIP           COUN
------------- ----
P.O.Box 2215         null                 New York             NY
10020-2215    USA
P.O.Box 2215         null                 New York             NY
10020-2215    USA
P.O.Box 0001         null                 Vancouver            NJ
08540         USA

3 rows selected.
SQL>

SYS_REFCURSOR as parameter

SQL>
SQL> create table product(
  2     product_id number(4)     not null,
  3     product_description varchar2(20) not null
  4  );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL>
SQL> create table company(
  2     product_id        number(4)    not null,
  3     company_id          NUMBER(8)    not null,
  4     company_short_name  varchar2(30) not null,
  5     company_long_name   varchar2(60)
  6  );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL> create table org_company_site(
  2     company_id number(8) not null,
  3     site_no number(4) not null
  4  );
Table created.
SQL> insert into org_company_site values (1001,1);
1 row created.
SQL> insert into org_company_site values (1002,2);
1 row created.
SQL> insert into org_company_site values (1003,3);
1 row created.
SQL> insert into org_company_site values (1004,1);
1 row created.
SQL> insert into org_company_site values (1004,2);
1 row created.
SQL> insert into org_company_site values (1004,3);
1 row created.
SQL> insert into org_company_site values (1005,1);
1 row created.
SQL> insert into org_company_site values (1005,4);
1 row created.
SQL> insert into org_company_site values (1005,5);
1 row created.
SQL> insert into org_company_site values (1006,1);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION f_cursor(p_cursor SYS_REFCURSOR)
  2  RETURN NUMBER
  3  IS
  4    v_company_short_name VARCHAR2(30);
  5    v_cnt NUMBER :=0;
  6    v_ret_code NUMBER;
  7  BEGIN
  8    BEGIN
  9      LOOP
 10        FETCH p_cursor INTO v_company_short_name;
 11        EXIT WHEN p_cursor%NOTFOUND;
 12        v_cnt :=v_cnt +1;
 13      END LOOP;
 14      IF (v_cnt >0)THEN
 15        v_ret_code :=1;
 16      ELSE
 17        v_ret_code :=0;
 18      END IF;
 19    EXCEPTION WHEN OTHERS THEN
 20      v_ret_code :=SQLCODE;
 21    END;
 22    RETURN (v_ret_code);
 23  END;
 24  /
Function created.
SQL>
SQL>
SQL>
SQL> SELECT rpad(h.product_description,20," ") "Hierarchy",
  2         rpad(o.rupany_short_name,30," ") "Organization"
  3  FROM product h,company o
  4  WHERE h.product_id =o.product_id
  5  AND f_cursor(
  6        CURSOR(SELECT o1.rupany_short_name
  7               FROM company o1
  8               WHERE o1.rupany_id =o.rupany_id
  9               AND 1 < (SELECT count(os.site_no)
 10                        FROM org_company_site os
 11                        WHERE os.rupany_id =o1.rupany_id)
 12              )
 13  )=1;
Hierarchy            Organization
-------------------- ------------------------------
Oracle               D Inc.
Oracle               E Inc.
2 rows selected.
SQL>
SQL>
SQL> drop table company;
Table dropped.
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL> drop table org_company_site;
Table dropped.
SQL>