Oracle PL/SQL/Cursor/sys refcursor
Содержание
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.
return sys_refcursor from a function
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.
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>
sys_refcursor type variable
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.