Oracle PL/SQL/Cursor/Reference Cursor

Содержание

1 Define and use reference cursor
2 Defines and declares a reference cursor before explicitly opening it
3 REF CURSOR custom type
4 refcursor variable
5 Reference cursor demo
6 Reference value in a cursor by cursor variable
7 Set the REFCURSOR variable to the results of a SELECT statement, and print out the REFCURSOR variable

Define and use reference cursor

   <source lang="sql">

SQL> -- Define and use reference cursor SQL> SQL> create table myTable as select * from all_users; Table created. SQL> SQL> variable myReferenceCursor refcursor; SQL> SQL> begin

 2     open :myReferenceCursor for select * from myTable;
 3  end;
 4  /

PL/SQL procedure successfully completed. SQL> SQL> delete from myTable; 14 rows deleted. SQL> SQL> commit; Commit complete. SQL> SQL> print myReferenceCursor; USERNAME USER_ID CREATED

---------- ---------

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 TSMSYS 20 07-FEB-06 DIP 18 07-FEB-06 USERNAME USER_ID CREATED

---------- ---------

OUTLN 11 07-FEB-06 SYSTEM 5 07-FEB-06 SYS 0 07-FEB-06 14 rows selected. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL>

 </source>

Defines and declares a reference cursor before explicitly opening it

   <source lang="sql">

SQL> SQL> DECLARE

 2  TYPE weakly_typed IS REF CURSOR;
 3  quick WEAKLY_TYPED;
 4  BEGIN
 5    OPEN quick FOR
 6      SELECT   item_title
 7      ,        COUNT(*)
 8      FROM     item
 9      HAVING  (COUNT(*) > 2)
10      GROUP BY item_title;
11    :refcur := quick;
12  END;
13  /

SP2-0552: Bind variable "REFCUR" not declared. SQL>

 </source>

REF CURSOR custom type

   <source lang="sql">

SQL> CREATE TABLE book (

 2    isbn      CHAR(10) PRIMARY KEY,
 3    category  VARCHAR2(20),
 4    title     VARCHAR2(100),
 5    num_pages NUMBER,
 6    price     NUMBER,
 7    copyright NUMBER(4),
 8    emp1   NUMBER,
 9    emp2   NUMBER,
10    emp3   NUMBER
11  );

Table created. SQL> SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)

 2             VALUES ("1", "Database", "Oracle", 563, 39.99, 2009, 1, 2, 3);

1 row created. SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)

 2             VALUES ("2", "Database", "MySQL", 765, 44.99, 2009, 4, 5);

1 row created. SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)

 2             VALUES ("3", "Database", "SQL Server", 404, 39.99, 2001, 6, 7, 8);

1 row created. SQL> SQL> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> DECLARE

 2
 3     TYPE book_typ IS REF CURSOR RETURN book%ROWTYPE;
 4     cv_book book_typ;
 5     v_book book%ROWTYPE;
 6
 7  BEGIN
 8
 9     DBMS_OUTPUT.ENABLE(1000000);
10
11     OPEN cv_book FOR SELECT * FROM book WHERE isbn = "1";
12
13     FETCH cv_book INTO v_book;
14
15     DBMS_OUTPUT.PUT_LINE(v_book.title||" is "||v_book.price);
16
17     CLOSE cv_book;
18  END;
19  /

Oracle is 39.99 PL/SQL procedure successfully completed. SQL> SQL> drop table book; Table dropped.

 </source>

refcursor variable

   <source lang="sql">

SQL> SQL> create table t as 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; 27 rows deleted. SQL> SQL> commit; Commit complete. SQL> SQL> print x USERNAME USER_ID CREATED

---------- ---------

SYS 0 07-FEB-06 SYSTEM 5 07-FEB-06 OUTLN 11 07-FEB-06 DIP 18 07-FEB-06 TSMSYS 20 07-FEB-06 INV15 42 17-OCT-09 DBSNMP 23 07-FEB-06 INV10 37 17-OCT-09 CTXSYS 25 07-FEB-06 XDB 27 07-FEB-06 ANONYMOUS 28 07-FEB-06 MDSYS 32 07-FEB-06 HR 33 07-FEB-06 FLOWS_FILES 34 07-FEB-06 FLOWS_020100 35 07-FEB-06 sqle 36 15-OCT-09 INV11 38 17-OCT-09 INV12 39 17-OCT-09 INV13 40 17-OCT-09 INV14 41 17-OCT-09 PLSQL 57 19-OCT-09 INV16 43 17-OCT-09 INV17 44 17-OCT-09 INV18 45 17-OCT-09 INV19 46 17-OCT-09 INV20 47 17-OCT-09 DEFINER 48 17-OCT-09 27 rows selected. SQL> SQL> drop table t; Table dropped.

 </source>

Reference cursor demo

   <source lang="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, "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> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> CREATE OR REPLACE PROCEDURE emp_sel (cv_results IN OUT SYS_REFCURSOR)

 2  IS
 3  BEGIN
 4     OPEN cv_results FOR SELECT id, fname, lname FROM emp;
 5  END;
 6  /

Procedure created. SQL> SQL> VARIABLE x REFCURSOR SQL> SQL> EXEC emp_sel(:x) PL/SQL procedure successfully completed. SQL> 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> drop table emp; Table dropped. SQL>

 </source>

Reference value in a cursor by cursor variable

   <source lang="sql">

SQL> SQL> -- create demo table SQL> create table emp(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    fname         VARCHAR2(10 BYTE),
 4    lname          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 emp(ID, fname, lname, 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  /