Oracle PL/SQL/Cursor/Cursor parameters — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 09:54, 26 мая 2010
Содержание
- 1 cursor parameters are used to specify the classid for which lecturer is listed at runtime
- 2 Cursor without parameters (simplest)
- 3 Cursor with parameter
- 4 Declaration of a parameterized cursor which has two parameters
- 5 Explicit cursor with parameter
- 6 For loop with parameterized cursor
- 7 Number Parameterized Cursors
- 8 Open cursor by index
cursor parameters are used to specify the classid for which lecturer is listed at runtime
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> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL>
SQL> set serverout on;
SQL> -- cursor parameters are used to specify the classid for which lecturer is listed at runtime:
SQL>
SQL> DECLARE
2
3 CURSOR deptList IS
4 SELECT deptno
5 FROM dept
6 WHERE loc = "NEW YORK";
7
8 CURSOR employee_enrolled (p_deptno NUMBER) IS
9 SELECT *
10 FROM emp
11 WHERE deptno = p_deptno;
12
13 BEGIN
14
15 FOR depts IN deptList LOOP
16 FOR employee_record IN
17 employee_enrolled(depts.deptno) LOOP
18
19 DBMS_OUTPUT.PUT_LINE(employee_record.empno);
20 END LOOP;
21 END LOOP;
22 END;
23 /
7782
7839
7934
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> drop table dept;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL>
Cursor without parameters (simplest)
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name 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 Employee(ID, First_Name, Last_Name, 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 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID Employee"s Fi LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 $1,234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 $6,661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 $6,544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 $2,344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 $2,334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 $4,322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 $7,897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 $1,232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> -- Cursor without parameters (simplest)
SQL> CREATE OR REPLACE Function FindEmployeeSalary ( name_in IN varchar2 ) RETURN number
2 IS
3 mySalary number;
4
5 CURSOR c1
6 IS
7 SELECT salary
8 from employee
9 where first_name = name_in;
10
11 BEGIN
12
13 open c1;
14 fetch c1 into mySalary;
15
16 if c1%notfound then
17 mySalary := 0;
18 end if;
19
20 close c1;
21
22 RETURN mySalary;
23
24 END;
25 /
Function created.
SQL>
SQL> select FindEmployeeSalary(first_name) from employee;
FINDEMPLOYEESALARY(FIRST_NAME)
------------------------------
1234.56
6661.78
6544.78
2344.78
2334.78
4322.78
7897.78
6544.78
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Cursor with parameter
SQL>
SQL>
SQL> set serveroutput on
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>
SQL> declare
2 cursor cursorValue (p_deptid in number)
3 is select *
4 from employees
5 where department_id = p_deptid;
6
7 l_emp employees%rowtype;
8 begin
9 open cursorValue(30);
10 loop
11 fetch cursorValue into l_emp;
12 exit when cursorValue%notfound;
13 dbms_output.put("Employee id " || l_emp.employee_id || " is ");
14 dbms_output.put_line(l_emp.last_name);
15 end loop;
16 close cursorValue;
17
18 dbms_output.put_line("Getting employees for department 90");
19 open cursorValue(90);
20 loop
21 fetch cursorValue into l_emp;
22 exit when cursorValue%notfound;
23 dbms_output.put("Employee id " || l_emp.employee_id || " is ");
24 dbms_output.put_line(l_emp.last_name);
25 end loop;
26 close cursorValue;
27 end;
28 /
Getting employees for department 90
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employees;
Table dropped.
SQL> --
Declaration of a parameterized cursor which has two parameters
SQL>
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL> DECLARE
2 CURSOR c_session(p_Department session.department%TYPE,
3 p_Course session.course%TYPE) IS
4 SELECT *
5 FROM session
6 WHERE department = p_Department
7 AND course = p_Course;
8 BEGIN
9 OPEN c_session("HIS", 101);
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL>
Explicit cursor with parameter
SQL>
SQL>
SQL> CREATE TABLE departments
2 (department_id number(10) not null,
3 department_name varchar2(50) not null,
4 CONSTRAINT departments_pk PRIMARY KEY (department_id)
5 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 1, "Data Group" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 2, "Purchasing" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 3, "Call Center" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 4, "Communication" );
1 row created.
SQL>
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 CONSTRAINT employees_pk PRIMARY KEY (employee_id),
11 CONSTRAINT fk_departments FOREIGN KEY (department_id) REFERENCES departments(department_id)
12 );
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> select * from departments;
DEPARTMENT_ID Department Names
------------- ------------------------------
1 Data Group
2 Purchasing
3 Call Center
4 Communication
SQL>
SQL>
SQL> set serverout on
SQL>
SQL> declare
2 cursor cursorValue (p_deptid in number)
3 is select *
4 from employees
5 where department_id = p_deptid;
6
7 l_emp employees%rowtype;
8 begin
9 dbms_output.put_line("Getting employees for department 30");
10 open cursorValue(30);
11 loop
12 fetch cursorValue into l_emp;
13 exit when cursorValue%notfound;
14 dbms_output.put("Employee id " || l_emp.employee_id || " is ");
15 dbms_output.put_line(l_emp.last_name);
16 end loop;
17 close cursorValue;
18
19 dbms_output.put_line("Getting employees for department 90");
20 open cursorValue(90);
21 loop
22 fetch cursorValue into l_emp;
23 exit when cursorValue%notfound;
24 dbms_output.put("Employee id " || l_emp.employee_id || " is ");
25 dbms_output.put_line(l_emp.last_name);
26 end loop;
27 close cursorValue;
28 end;
29 /
Getting employees for department 30
Getting employees for department 90
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employees cascade constraints;
Table dropped.
SQL> drop table departments cascade constraints;
Table dropped.
For loop with parameterized cursor
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> DECLARE
2 CURSOR csr_org(p_hrc_code NUMBER) IS
3 SELECT empno, ename FROM emp;
4 v_org_rec csr_org%ROWTYPE;
5 BEGIN
6 FOR idx in csr_org(1) LOOP
7 dbms_output.put_line(v_org_rec.empno||" "||v_org_rec.ename);
8 END LOOP;
9 FOR idx in csr_org(2) LOOP
10 dbms_output.put_line(v_org_rec.empno||" "||v_org_rec.ename);
11 END LOOP;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL> --
Number Parameterized Cursors
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> DECLARE
2 CURSOR csr_org(p_hrc_code NUMBER) IS
3 SELECT empno, ename FROM emp;
4 v_org_rec csr_org%ROWTYPE;
5 BEGIN
6 OPEN csr_org(1);
7 LOOP
8 FETCH csr_org INTO v_org_rec;
9 EXIT WHEN csr_org%NOTFOUND;
10 dbms_output.put_line(v_org_rec.empno||" "||v_org_rec.ename);
11 END LOOP;
12 CLOSE csr_org;
13 OPEN csr_org(2);
14 LOOP
15 FETCH csr_org INTO v_org_rec;
16 EXIT WHEN csr_org%NOTFOUND;
17 dbms_output.put_line(v_org_rec.empno||" "||v_org_rec.ename);
18 END LOOP;
19 CLOSE csr_org;
20 END;
21 /
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
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> --
Open cursor by index
SQL>
SQL> create table t ( object_id primary key, object_name )
2 organization index
3 as
4 select object_id, object_name from all_objects;
Table created.
SQL>
SQL> create or replace procedure explicit
2 as
3 l_object_name t.object_name%type;
4 l_dummy t.object_name%type;
5
6 cursor c( l_object_id in number)is
7 select object_name
8 from t
9 where object_id = l_object_id;
10 begin
11 for i in 1 .. 30000
12 loop
13 open c(i);
14 fetch c into l_object_name;
15 close c;
16 end loop;
17 end;
18 /
Procedure created.
SQL>
SQL>
SQL>
SQL> drop table t;
Table dropped.