Oracle PL/SQL/Cursor/ref cursor
Содержание
Cursor Variables Assignment
SQL> --
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 TYPE rc is REF CURSOR;
3 v_rc1 rc;
4 v_rc2 rc;
5 hrc_rec emp%ROWTYPE;
6 BEGIN
7 OPEN v_rc1 FOR SELECT * from emp;
8 v_rc2 := v_rc1;
9 LOOP
10 FETCH v_rc2 INTO hrc_rec;
11 EXIT WHEN v_rc2%NOTFOUND;
12 dbms_output.put_line(hrc_rec.empno ||" "||hrc_rec.ename);
13 END LOOP;
14 CLOSE v_rc2;
15 END;
16 /
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL> --
declare ref cursors
SQL>
SQL>
SQL> CREATE TABLE employees
2 ( employee_id number(10) not null,
3 last_name varchar2(50) not null,
4 email varchar2(30),
5 hire_date date,
6 job_id varchar2(30),
7 department_id number(10),
8 salary number(6),
9 manager_id number(6)
10 );
Table created.
SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created.
SQL>
SQL> select * from employees;
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID
----------- -------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
1001 Lawson lawson@g.ru 01-JAN-02 MGR 1 30000 1004
1002 Wells wells@g.ru 01-JAN-02 DBA 2 20000 1005
1003 Bliss bliss@g.ru 01-JAN-02 PROG 3 24000 1004
1004 Kyte tkyte@a.ru 14-JUN-98 MGR 4 25000 1005
1005 Viper sdillon@a .ru 11-JUN-08 PROG 1 20000 1006
1006 Beck clbeck@g.ru 11-JUN-08 PROG 2 20000
1007 Java java01@g.ru 11-JUN-08 PROG 3 20000 1006
1008 Oracle oracle1@g.ru 11-JUN-08 DBA 4 20000 1006
8 rows selected.
SQL>
SQL> declare
2 type refcur_t is ref cursor;
3
4 type emp_refcur_t is ref cursor
5 return employees%rowtype;
6 begin
7 null;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
strong ref cursor
SQL>
SQL> CREATE TABLE employees
2 ( employee_id number(10) not null,
3 last_name varchar2(50) not null,
4 email varchar2(30),
5 hire_date date,
6 job_id varchar2(30),
7 department_id number(10),
8 salary number(6),
9 manager_id number(6)
10 );
Table created.
SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created.
SQL>
SQL> CREATE TABLE JOBS (
2 JOB_ID VARCHAR2 (10) NOT NULL,
3 JOB_TITLE VARCHAR2 (35) CONSTRAINT JOB_TITLE_NN NOT NULL,
4 MIN_SALARY NUMBER (6),
5 MAX_SALARY NUMBER (6),
6 CONSTRAINT JOB_ID_PK PRIMARY KEY ( JOB_ID ) ) ;
Table created.
SQL>
SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( "DBA", "President", 20000, 40000);
1 row created.
SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( "PROG", "Administration Vice President", 15000, 30000);
1 row created.
SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( "MGR", "Administration Assistant", 3000, 6000);
1 row created.
SQL>
SQL>
SQL>
SQL> declare
2 type emp_job_rec is record(
3 employee_id number,
4 employee_name varchar2(56),
5 job_title varchar2(35)
6 );
7
8 type emp_job_refcur_type is ref cursor
9 return emp_job_rec;
10
11 emp_refcur emp_job_refcur_type;
12
13 emp_job emp_job_rec;
14 begin
15 open emp_refcur for
16 select e.employee_id,
17 e.last_name "employee_name",
18 j.job_title
19 from employees e, jobs j
20 where e.job_id = j.job_id
21 and rownum < 11
22 order by 1;
23
24 fetch emp_refcur into emp_job;
25 while emp_refcur%FOUND loop
26 dbms_output.put(emp_job.employee_name || """s job is ");
27 dbms_output.put_line(emp_job.job_title);
28 fetch emp_refcur into emp_job;
29 end loop;
30 end;
31 /
Lawson"s job is Administration Assistant
Wells"s job is President
Bliss"s job is Administration Vice President
Kyte"s job is Administration Assistant
Viper"s job is Administration Vice President
Beck"s job is Administration Vice President
Java"s job is Administration Vice President
Oracle"s job is President
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employees;
Table dropped.
SQL> drop table jobs;
Table dropped.
Use native dynamic SQL to process queries.
SQL>
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE NativeDynamic AS
2 TYPE t_RefCur IS REF CURSOR;
3
4 FUNCTION lecturerQuery(p_WhereClause IN VARCHAR2)
5 RETURN t_RefCur;
6
7 FUNCTION lecturerQuery2(p_Major IN VARCHAR2)
8 RETURN t_RefCur;
9 END NativeDynamic;
10 /
Package created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY NativeDynamic AS
2 FUNCTION lecturerQuery(p_WhereClause IN VARCHAR2)
3 RETURN t_RefCur IS
4 v_ReturnCursor t_RefCur;
5 v_SQLStatement VARCHAR2(500);
6 BEGIN
7 v_SQLStatement := "SELECT * FROM lecturer " || p_WhereClause;
8
9 OPEN v_ReturnCursor FOR v_SQLStatement;
10 RETURN v_ReturnCursor;
11 END lecturerQuery;
12
13 FUNCTION lecturerQuery2(p_Major IN VARCHAR2)
14 RETURN t_RefCur IS
15 v_ReturnCursor t_RefCur;
16 v_SQLStatement VARCHAR2(500);
17 BEGIN
18 v_SQLStatement := "SELECT * FROM lecturer WHERE major = :m";
19
20 OPEN v_ReturnCursor FOR v_SQLStatement USING p_Major;
21 RETURN v_ReturnCursor;
22 END lecturerQuery2;
23 END NativeDynamic;
24 /
Package body created.
SQL> show errors
No errors.
SQL>
SQL> set serveroutput on format wrapped
SQL>
SQL> DECLARE
2 myLecturer lecturer%ROWTYPE;
3 myLecturerCur NativeDynamic.t_RefCur;
4 BEGIN
5 myLecturerCur :=
6 NativeDynamic.lecturerQuery("WHERE MOD(id, 2) = 0");
7
8 DBMS_OUTPUT.PUT_LINE("The following lecturer have even IDs:");
9 LOOP
10 FETCH myLecturerCur INTO myLecturer;
11 EXIT WHEN myLecturerCur%NOTFOUND;
12 DBMS_OUTPUT.PUT_LINE(" " || myLecturer.id || ": " ||
13 myLecturer.first_name || " " ||
14 myLecturer.last_name);
15 END LOOP;
16 CLOSE myLecturerCur;
17
18 myLecturerCur :=
19 NativeDynamic.lecturerQuery2("Music");
20
21 DBMS_OUTPUT.PUT_LINE(
22 "The following lecturer are music majors:");
23 LOOP
24 FETCH myLecturerCur INTO myLecturer;
25 EXIT WHEN myLecturerCur%NOTFOUND;
26 DBMS_OUTPUT.PUT_LINE(" " || myLecturer.id || ": " ||
27 myLecturer.first_name || " " ||
28 myLecturer.last_name);
29 END LOOP;
30 CLOSE myLecturerCur;
31 END;
32 /
The following lecturer have even IDs:
10002: Mar Wells
10004: Man Kyte
10006: Tim Viper
10008: David Large
10010: Rose Bond
10012: Sharon Clear
The following lecturer are music majors:
10008: David Large
10010: Rose Bond
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>