Closing a Cursor:
CLOSE statement releases the data within the cursor and closes it. A cursor can be reopened to refresh its data.
The subsequent examples describe the concept of using Static cursors.
Example
This instance is used to select all the employee names and their jobs from the table.
DECLARE
CURSOR c1 IS SELECT ename,job FROM emp;
Mname VARCHAR2(20);
Mjob VARCHAR2(20);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO mname,mjob;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(mname||' is a ' ||mjob);
END LOOP;
CLOSE c1;
END;
The output of the program is:
KING is a PRESIDENT
BLAKE is a MANAGER
CLARK is a MANAGER
JONES is a MANAGER
MARTIN is a SALESMAN
ALLEN is a SALESMAN
TURNER is a SALESMAN
JAMES is a CLERK
WARD is a SALESMAN
FORD is a ANALYST
SMITH is a CLERK
SCOTT is a ANALYST
ADAMS is a CLERK
MILLER is a CLERK
All the employee names and their respective jobs are displays from the employee table. Here, further there is an EXIT WHEN statement that was described in chapter 7. The yield differs depending on where the FETCH statement is placed there. After the DBMS_OUTPUT.PUT_LINE statement if the EXIT statement is placed and the last record is displayed twice because the variables holds the last fetched record. Let Consider the following example is:
DECLARE
CURSOR c1 IS SELECT ename,job FROM emp;
Mname VARCHAR2(20);
Mjob VARCHAR2(20);
BEGIN
OPEN c1; LOOP
FETCH c1 INTO mname,mjob;
DBMS_OUTPUT.PUT_LINE(mname||' is a ' ||mjob);
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;
The output will look like:
KING is a PRESIDENT
BLAKE is a MANAGER
CLARK is a MANAGER
JONES is a MANAGER
MARTIN is a SALESMAN
ALLEN is a SALESMAN
TURNER is a SALESMAN
JAMES is a CLERK
WARD is a SALESMAN
FORD is a ANALYST
SMITH is a CLERK
SCOTT is a ANALYST
ADAMS is a CLERK
MILLER is a CLERK
MILLER is a CLERK