Oracle PL/SQL Tutorial/Cursor/REFCURSOR
Содержание
- 1 An example of using SYS_REFCURSOR for cursor variable processing
- 2 Cursor expressions as arguments to functions called from SQL
- 3 Define refcursor variable
- 4 Open SYS_REFCURSOR for select ... from
- 5 Output value in a refcursor
- 6 Row type Reference cursor
- 7 Subprograms returning resultsets by using SYS_REFCURSOR
- 8 SYS_REFCURSOR type parameter
- 9 The use of REF CURSOR
An example of using SYS_REFCURSOR for cursor variable processing
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> DECLARE
2 refCursorValue SYS_REFCURSOR;
3 myRecord product%ROWTYPE;
4 BEGIN
5 OPEN refCursorValue FOR SELECT *from product;
6
7 LOOP
8 FETCH refCursorValue INTO myRecord;
9 EXIT WHEN refCursorValue%NOTFOUND;
10 dbms_output.put_line(to_char(myRecord.product_id)||" "||
11 rpad(myRecord.product_description,20," "));
12 END LOOP;
13 CLOSE refCursorValue;
14 END;
15 /
1 Java
2 Oracle
3 C#
4 Javascript
5 Python
PL/SQL procedure successfully completed.
SQL>
SQL> drop table product;
Table dropped.
Cursor expressions as arguments to functions called from 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 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_report(p_cursor SYS_REFCURSOR,p_title VARCHAR2)
2 RETURN NUMBER
3 IS
4 v_product_description VARCHAR2(20);
5 v_company_short_name VARCHAR2(30);
6 v_ret_code NUMBER;
7 BEGIN
8 BEGIN
9 dbms_output.put_line(p_title);
10
11 LOOP
12 FETCH p_cursor INTO v_product_description,v_company_short_name;
13 EXIT WHEN p_cursor%NOTFOUND;
14 dbms_output.put_line(rpad(v_product_description,20," ")||" "||
15 rpad(v_company_short_name,30," "));
16 END LOOP;
17 v_ret_code :=1;
18 EXCEPTION WHEN OTHERS THEN
19 v_ret_code :=SQLCODE;
20 END;
21 RETURN (v_ret_code);
22 END;
23 /
Function created.
SQL>
SQL>
SQL> SELECT "Report Generated on "||TO_CHAR(SYSDATE,"MM/DD/YYYY ") "Report1"
2 FROM DUAL
3 WHERE f_report(
4 CURSOR(SELECT h.product_description,o.rupany_short_name
5 FROM product h,company o
6 WHERE o.product_id =h.product_id
7 AND 1 < (SELECT count(os.site_no)
8 FROM org_company_site os
9 WHERE os.rupany_id =o.rupany_id)
10 ),
11 "List of Organizations located in more than one site"
12 )=1;
Report1
-------------------------------
Report Generated on 07/24/2008
1 row selected.
List of Organizations located in more than one site
Oracle D Inc.
Oracle E Inc.
SQL>
SQL> drop table company;
Table dropped.
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL> drop table org_company_site;
Table dropped.
SQL>
Define refcursor variable
SQL> set echo on
SQL>
SQL> set timing off
SQL>
SQL> create table t
2 as
3 select * from all_users;
Table created.
SQL>
SQL> variable x refcursor
SQL>
SQL> begin
2 open :x for select * from t;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> delete from t;
17 rows deleted.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> print x
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
USER1 55 02-JUN-07
sqle 38 09-SEP-06
BOB 36 30-AUG-06
FLOWS_020100 35 07-FEB-06
FLOWS_FILES 34 07-FEB-06
HR 33 07-FEB-06
MDSYS 32 07-FEB-06
ANONYMOUS 28 07-FEB-06
XDB 27 07-FEB-06
CTXSYS 25 07-FEB-06
DBSNMP 23 07-FEB-06
ANEWUSER 44 09-SEP-06
TSMSYS 20 07-FEB-06
DIP 18 07-FEB-06
OUTLN 11 07-FEB-06
SYSTEM 5 07-FEB-06
SYS 0 07-FEB-06
17 rows selected.
SQL>
SQL> drop table t;
Table dropped.
Open SYS_REFCURSOR for select ... from
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z");
1 row created.
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> CREATE OR REPLACE PROCEDURE emp_sel(cv_results IN OUT SYS_REFCURSOR) IS
2 BEGIN
3 OPEN cv_results FOR SELECT id, fname, lname FROM emp;
4 END;
5 /
Procedure created.
SQL>
SQL> COL fname FORMAT A12
SQL> VARIABLE x REFCURSOR
SQL> EXEC emp_sel(:x)
PL/SQL procedure successfully completed.
SQL> PRINT x
ID FNAME LNAME
---------- ------------ --------------------------------------------------
1 A B
2 C D
3 Enn F
4 G H
5 G Z
5 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
Output value in a refcursor
SQL> set echo on
SQL>
SQL> set timing off
SQL>
SQL> create table t
2 as
3 select * from all_users;
Table created.
SQL>
SQL> variable x refcursor
SQL>
SQL> begin
2 open :x for select * from t;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> delete from t;
17 rows deleted.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> print x
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
USER1 55 02-JUN-07
sqle 38 09-SEP-06
BOB 36 30-AUG-06
FLOWS_020100 35 07-FEB-06
FLOWS_FILES 34 07-FEB-06
HR 33 07-FEB-06
MDSYS 32 07-FEB-06
ANONYMOUS 28 07-FEB-06
XDB 27 07-FEB-06
CTXSYS 25 07-FEB-06
DBSNMP 23 07-FEB-06
ANEWUSER 44 09-SEP-06
TSMSYS 20 07-FEB-06
DIP 18 07-FEB-06
OUTLN 11 07-FEB-06
SYSTEM 5 07-FEB-06
SYS 0 07-FEB-06
17 rows selected.
SQL>
SQL> drop table t;
Table dropped.
Row type Reference cursor
SQL>
SQL>
SQL> CREATE TABLE pizza (
2 code NUMBER(38) CONSTRAINT pk_pizza PRIMARY KEY NOT NULL
3 ,flavor VARCHAR2(30) NOT NULL
4 );
Table created.
SQL>
SQL> CREATE OR REPLACE PACKAGE pizza_pkg
2 AUTHID CURRENT_USER
3 IS
4 c_chocolate CONSTANT PLS_INTEGER := 16;
5 c_strawberry CONSTANT PLS_INTEGER := 29;
6
7 TYPE codes_nt IS TABLE OF INTEGER;
8
9 my_pizza codes_nt;
10
11 TYPE fav_info_rct IS REF CURSOR RETURN pizza%ROWTYPE;
12
13 PROCEDURE show_pizza (list_in IN codes_nt);
14
15 FUNCTION most_popular RETURN fav_info_rct;
16
17 END pizza_pkg;
18 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY pizza_pkg
2 IS
3 g_most_popular PLS_INTEGER;
4
5 PROCEDURE show_pizza (list_in IN codes_nt)
6 IS
7 BEGIN
8 FOR indx IN list_in.FIRST .. list_in.LAST
9 LOOP
10 DBMS_OUTPUT.put_line (list_in (indx));
11 END LOOP;
12 END show_pizza;
13
14 FUNCTION most_popular RETURN fav_info_rct
15 IS
16 retval fav_info_rct;
17 null_cv fav_info_rct;
18 BEGIN
19 OPEN retval FOR
20 SELECT * FROM pizza WHERE code = g_most_popular;
21
22 RETURN retval;
23 EXCEPTION
24 WHEN NO_DATA_FOUND
25 THEN
26 RETURN null_cv;
27 END most_popular;
28
29 PROCEDURE analyze_pizza (year_in IN INTEGER)
30 IS
31 BEGIN
32
33 NULL;
34 END analyze_pizza;
35
36 BEGIN
37 g_most_popular := c_chocolate;
38
39 analyze_pizza (EXTRACT (YEAR FROM SYSDATE));
40 END pizza_pkg;
41 /
SP2-0810: Package Body created with compilation warnings
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table pizza;
Table dropped.
SQL>
SQL>
Subprograms returning resultsets by using SYS_REFCURSOR
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>
SQL> CREATE OR REPLACE FUNCTION getAllHierarchies
2 RETURN SYS_REFCURSOR
3 IS
4 refCursorValue SYS_REFCURSOR;
5 BEGIN
6 OPEN refCursorValue FOR SELECT * FROM product;
7
8 RETURN (refCursorValue);
9
10 EXCEPTION WHEN OTHERS THEN
11
12 RAISE_APPLICATION_ERROR(-20002,SQLERRM);
13
14 END;
15 /
Function created.
SQL>
SQL> DECLARE
2 refCursorValue SYS_REFCURSOR;
3 myRecord product%ROWTYPE;
4 BEGIN
5 refCursorValue :=getAllHierarchies;
6
7 LOOP
8
9 FETCH refCursorValue INTO myRecord;
10
11 EXIT WHEN refCursorValue%NOTFOUND;
12
13 dbms_output.put_line(TO_CHAR(myRecord.product_id)||" "||myRecord.product_description);
14
15 END LOOP;
16
17 EXCEPTION WHEN OTHERS THEN
18
19 dbms_output.put_line(TO_CHAR(SQLCODE)||" "||SQLERRM);
20
21 END;
22 /
1 Java
2 Oracle
3 C#
4 Javascript
5 Python
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL>
SYS_REFCURSOR type parameter
SQL>
SQL>
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "E", "F");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z");
1 row created.
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> CREATE OR REPLACE PROCEDURE emp_sel_regexp_like (cv_emp IN OUT SYS_REFCURSOR)
2 IS
3 BEGIN
4
5 OPEN cv_emp FOR
6 SELECT id, fname, lname
7 FROM emp
8 WHERE REGEXP_LIKE(lname, "^h", "i");
9
10 EXCEPTION
11 WHEN OTHERS
12 THEN
13 DBMS_OUTPUT.PUT_LINE(sqlerrm);
14 END;
15 /
Procedure created.
SQL>
SQL> COL fname FORMAT A20
SQL> COL lname FORMAT A20
SQL>
SQL> VARIABLE x REFCURSOR
SQL> EXEC emp_sel_regexp_like(:x)
PL/SQL procedure successfully completed.
SQL>
SQL> print x
no rows selected
SQL> drop table emp;
Table dropped.
SQL>
The use of REF CURSOR
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> CREATE OR REPLACE PROCEDURE authors_sel (cv_results IN OUT SYS_REFCURSOR)
2 IS
3 BEGIN
4 OPEN cv_results FOR
5 SELECT id, first_name, last_name
6 FROM employee;
7 END;
8 /
Procedure created.
SQL>
SQL> VARIABLE x REFCURSOR
SQL> EXEC authors_sel(:x)
PL/SQL procedure successfully completed.
SQL> print x
ID FIRST_NAME LAST_NAME
---- -------------------- --------------------
01 Jason Martin
02 Alison Mathews
03 James Smith
04 Celia Rice
05 Robert Black
06 Linda Green
07 David Larry
08 James Cat
8 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>