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.
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
Below is a function that demonstrates how to use the CLOSE statement
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>
Close a cursor after looping
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.
Close a cursor and open it again with another query
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>
Close cursor after while loop
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.
Close cursor in excpetion handler
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.
Closing the Cursor Variable
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> --