Oracle PL/SQL/Cursor/sys refcursor
Содержание
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>