Oracle PL/SQL/Cursor/Close Cursor
Содержание
- 1 After the cursor is opened, the book table is dropped prior to looping through the cursor.
- 2 Below is a function that demonstrates how to use the CLOSE statement
- 3 Close a cursor after looping
- 4 Close a cursor and open it again with another query
- 5 Close cursor after while loop
- 6 Close cursor in excpetion handler
- 7 Closing the Cursor Variable
After the cursor is opened, the book table is dropped prior to looping through the cursor.
<source lang="sql">
SQL> CREATE TABLE book (
2 isbn CHAR(10),
3 status VARCHAR2(25) CHECK (status IN ("IN STOCK", "BACKORDERED", "FUTURE")),
4 status_date DATE,
5 amount NUMBER
6 );
Table created. SQL> SQL> INSERT INTO book (isbn, status, status_date, amount)VALUES ("1", "BACKORDERED", TO_DATE("06-JUN-2004", "DD-MON-YYYY"), 1000); 1 row created. SQL> INSERT INTO book (isbn, status, status_date, amount)VALUES ("2", "IN STOCK", NULL, 5000); 1 row created. SQL> INSERT INTO book (isbn, status, status_date, amount)VALUES ("3", "IN STOCK", NULL, 1000); 1 row created. SQL> INSERT INTO book (isbn, status, status_date, amount)VALUES ("7", "IN STOCK", NULL, 1000); 1 row created. SQL> SQL> SQL> SQL> DECLARE
2
3 v_isbn book.ISBN%TYPE;
4
5 CURSOR book_cur IS SELECT isbn FROM book;
6
7 BEGIN
8 OPEN book_cur;
9
10 EXECUTE IMMEDIATE ("DROP TABLE book");
11
12 LOOP
13 FETCH book_cur INTO v_isbn;
14 EXIT WHEN book_cur%NOTFOUND;
15
16 DBMS_OUTPUT.PUT_LINE(v_isbn);
17
18 END LOOP;
19
20 CLOSE book_cur;
21
22 END;
23 /
1 2 3 7
</source>
Below is a function that demonstrates how to use the CLOSE statement
<source lang="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 ID Employee"s Fi LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
------------- ---------- --------- --------- ---------- ---------- ---------------
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> SQL> select FindEmployeeSalary(first_name) from employee; FINDEMPLOYEESALARY(FIRST_NAME)
1234.56
6661.78
6544.78
2344.78
2334.78
4322.78
7897.78
FINDEMPLOYEESALARY(FIRST_NAME)
6544.78
8 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL>
</source>
Close a cursor after looping
<source lang="sql">
SQL> SQL> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL, 3 department CHAR(3) NOT NULL, 4 course NUMBER(3) NOT NULL, 5 grade CHAR(1) 6 );
Table created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created. SQL> SQL> SQL> set serveroutput on SQL> SQL> CREATE OR REPLACE PROCEDURE RSLoop AS
2 v_RSRec myStudent%ROWTYPE;
3 CURSOR c_RSGrades IS SELECT * FROM myStudent ORDER BY grade;
4 BEGIN
5 OPEN c_RSGrades;
6 LOOP
7 FETCH c_RSGrades INTO v_RSRec;
8 EXIT WHEN c_RSGrades%NOTFOUND;
9 END LOOP;
10 CLOSE c_RSGrades;
11
12 DBMS_OUTPUT.PUT_LINE("Last row selected has ID " || v_RSRec.student_id);
13 END RSLoop;
14 /
Procedure created. SQL> show errors No errors. SQL> SQL> SQL> SQL> SQL> drop table myStudent; Table dropped.
</source>
Close a cursor and open it again with another query
<source lang="sql">
SQL> CREATE TABLE place (
2 room_id NUMBER(5) PRIMARY KEY, 3 building VARCHAR2(15), 4 room_number NUMBER(4), 5 number_seats NUMBER(4), 6 description VARCHAR2(50) 7 );
Table created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20003, "Building 6", 150, 50, "Discussion Room A");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20004, "Building 6", 160, 50, "Discussion Room B");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20005, "Building 6", 170, 50, "Discussion Room C");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20006, "Music Building", 100, 10, "Music Practice Room");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20007, "Music Building", 200, 1000, "Concert Room");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20008, "Building 7", 300, 75, "Discussion Room D");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20009, "Building 7", 310, 50, "Discussion Room E");
1 row created. SQL> SQL> set serveroutput on SQL> SQL> DECLARE
2 TYPE roomCursor IS REF CURSOR RETURN place%ROWTYPE;
3 roomCursorV roomCursor;
4 v_Rooms place%ROWTYPE;
5 BEGIN
6 OPEN roomCursorV FOR SELECT * FROM place WHERE building = "Building 7";
7 LOOP
8 FETCH roomCursorV INTO v_Rooms;
9 EXIT WHEN roomCursorV%NOTFOUND;
10 DBMS_OUTPUT.PUT_LINE("Fetched Room #" || v_Rooms.room_number ||" in Building 7 from roomCursorV");
11 END LOOP;
12 CLOSE roomCursorV;
13 OPEN roomCursorV FOR SELECT * FROM place WHERE number_seats > 100;
14 LOOP
15 FETCH roomCursorV INTO v_Rooms;
16 EXIT WHEN roomCursorV%NOTFOUND;
17 DBMS_OUTPUT.PUT_LINE("Fetched " || v_Rooms.building || "," ||" Room #" || v_Rooms.room_number || " from roomCursorV");
18 END LOOP;
19
20 CLOSE roomCursorV;
21 END;
22 /
Fetched Room #201 in Building 7 from roomCursorV Fetched Room #300 in Building 7 from roomCursorV Fetched Room #310 in Building 7 from roomCursorV Fetched Building 7, Room #201 from roomCursorV Fetched Building 6, Room #101 from roomCursorV Fetched Music Building, Room #200 from roomCursorV PL/SQL procedure successfully completed. SQL> SQL> drop table place; Table dropped. SQL> SQL>
</source>
Close cursor after while loop
<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, "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> DECLARE
2 v_emp emp%ROWTYPE; 3 4 CURSOR auth_cur IS SELECT * FROM emp; 5 BEGIN 6 OPEN auth_cur; 7 8 FETCH auth_cur INTO v_emp; 9 10 WHILE auth_cur%FOUND LOOP 11 12 DBMS_OUTPUT.PUT_LINE(v_emp.lname); 13 14 FETCH auth_cur INTO v_emp; 15 16 END LOOP; 17 18 CLOSE auth_cur; 19 END; 20 /
B D F H Z PL/SQL procedure successfully completed. SQL> drop table emp; Table dropped.
</source>
Close cursor in excpetion handler
<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> 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> create or replace procedure PROCESS_EMP is
2 cursor C is select empno, sal, comm from emp; 3 newSalary number := 10000; 4 begin 5 for i in c loop 6 newSalary := newSalary + i.rum / i.sal; 7 end loop; 8 exception 9 when zero_divide then 10 if C%ISOPEN then 11 close C; 12 end if; 13 end; 14 /
Procedure created. SQL> drop table emp; Table dropped.
</source>
Closing the Cursor Variable
<source lang="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 TYPE rc is REF CURSOR;
3 v_rc rc;
4 hrc_rec emp%ROWTYPE;
5 BEGIN
6 OPEN v_rc FOR SELECT * from emp;
7 dbms_output.put_line("Hierarchy Details");
8 dbms_output.put_line("Code"||" "||rpad("Description",20," "));
9 dbms_output.put_line(rpad("-",4,"-")||" "||rpad("-",20,"-"));
10 LOOP
11 FETCH v_rc INTO hrc_rec;
12 EXIT WHEN v_rc%NOTFOUND;
13 dbms_output.put_line("not found");
14 END LOOP;
15 CLOSE v_rc;
16 END;
17 /
Hierarchy Details Code Description
--------------------
not found not found not found not found not found not found not found not found not found not found not found not found not found not found PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL> SQL> --
</source>
