Oracle PL/SQL/Cursor/sys refcursor

Материал из SQL эксперт
Перейти к: навигация, поиск

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>
   
  


return sys_refcursor from a function

   <source lang="sql">

SQL> CREATE TABLE EMP(

 2      EMPNO NUMBER(4) NOT NULL,
 3      ENAME VARCHAR2(10),
 4      JOB VARCHAR2(9),
 5      MGR NUMBER(4),
 6      HIREDATE DATE,
 7      SAL NUMBER(7, 2),
 8      COMM NUMBER(7, 2),
 9      DEPTNO NUMBER(2)
10  );

Table created. SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> SQL> create or replace function emp_list return sys_refcursor is

 2   rc sys_refcursor;
 3   begin
 4   open rc for select * from emp;
 5   return rc;
 6   end;
 7  /

Function created. SQL> SQL> create or replace procedure list_emps is

 2   e sys_refcursor;
 3   r emp%rowtype;
 4  begin
 5   e := emp_list;
 6   loop
 7       fetch e into r;
 8       exit when e%notfound;
 9       dbms_output.put_line(r.empno||","||r.hiredate);
10   end loop;
11   close e;
12   end;
13  /

Procedure created. SQL> SQL> drop table emp; Table dropped.

 </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>
   
  


sys_refcursor type variable

   <source lang="sql">

SQL> CREATE TABLE EMP(

 2      EMPNO NUMBER(4) NOT NULL,
 3      ENAME VARCHAR2(10),
 4      JOB VARCHAR2(9),
 5      MGR NUMBER(4),
 6      HIREDATE DATE,
 7      SAL NUMBER(7, 2),
 8      COMM NUMBER(7, 2),
 9      DEPTNO NUMBER(2)
10  );

Table created. SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> SQL> create or replace function emp_list return sys_refcursor is

 2   rc sys_refcursor;
 3   begin
 4   open rc for select * from emp;
 5   return rc;
 6   end;
 7  /

Function created. SQL> SQL> create or replace procedure list_emps is

 2   e sys_refcursor;
 3   r emp%rowtype;
 4  begin
 5   e := emp_list;
 6   loop
 7       fetch e into r;
 8       exit when e%notfound;
 9       dbms_output.put_line(r.empno||","||r.hiredate);
10   end loop;
11   close e;
12   end;
13  /

Procedure created. SQL> SQL> drop table emp; Table dropped.

 </source>