Oracle PL/SQL Tutorial/Cursor/Cursor function
Содержание
A PL/SQL function that uses a cursor expression
<source lang="sql">
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></source>
Cursor expressions using multiple levels of nested cursors
<source lang="sql">
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></source>
Cursor function
<source lang="sql">
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></source>
Passing data from one table function to another in a pipelined fashion
<source lang="sql">
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></source>
SYS_REFCURSOR as parameter
<source lang="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 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></source>
