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>