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
<source lang="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> 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.</source>
Cursor expressions as arguments to functions called from SQL
<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 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></source>
Define refcursor variable
<source lang="sql">
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.</source>
Open SYS_REFCURSOR for select ... from
<source lang="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, "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.</source>
Output value in a refcursor
<source lang="sql">
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.</source>
Row type Reference cursor
<source lang="sql">
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></source>
Subprograms returning resultsets by using SYS_REFCURSOR
<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> 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></source>
SYS_REFCURSOR type parameter
<source lang="sql">
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></source>
The use of REF CURSOR
<source lang="sql">
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></source>