CURSORS

C U R S O R S

Whenever a SQL statement is in process, Oracle allocates a special memory area for that SQL statement. This memory area is called as context area. The rows retrieved by the given select statement are called as ACTIVE SET. Cursor is a pointer to the active set for accessing the rows of active set from PL/SQL.

TYPES OF CURSORS : Cursors are of two types, Explicit cursors and Implicit cursors. A cursor that is created automatically by Oracle when a select statement is executed is called as an IMPLICIT cursors. A cursor that is created and processed by the user is called as an EXPLICIT cursor.
EXPLICIT CURSOR : A cursor i.e. declared by the user is called as an explicit cursor. Explicit cursors are declared by user. The code associated to those cursors must be written by the user. When using an explicit cursor, user has to follow four steps.
1. Cursor Declaration.
2. Opening cursor.
3. Fetching Cursor.
4. Closing Cursor.

1. CURSOR DECLARATION : Cursor declaration assigns a name to the cursor and associates that cursor with a select statement that retrieves the rows, which the cursor has to point.

Syntax : Cursor CursorName is select statement;

2. OPENING CURSOR : A cursor must be opened before accessing the rows pointed by the cursor. During open statement Oracle executes the select statement associated with the cursor and then points to the first row retrieved by the select statement.

Syntax : Open CursorName;

3. FETCHING DATA : During this stage cursor retrieves the values of current record to local variables and then moves the pointer to the immediate next row.

Syntax : Fetch CursorName Into Local variable list;

4. CLOSING CURSOR : A cursor must be closed after completing work with that cursor. During close stage Oracle closes the cursor and releases all memory resources used by the cursor.

Syntax : Close CursorName;

CURSOR ATTRIBUTES :
The keywords of PL/SQL that determine the status of a cursor or the number of rows retrieved by a fetch statement till the time etc. are called as cursor attributes. Cursor attributes in PL/SQL are as follows.
1.%FOUND
2.%NOTFOUND
3.%ISOPEN
4.%ROWCOUNT
1. %FOUND : This attribute returns true if the previously written fetch statement retrieves a row. Otherwise it returns false.

2. %NOTFOUND : This attribute returns true when the previous fetch statement doesn’t return any row. Otherwise it returns false.

3. %ISOPEN : This attribute returns true if the cursor is already opened. Otherwise it returns false.

4. %ROWCOUNT : This attribute returns the number of rows fetched until now.



Write a PL/SQL block to access all rows of EMP table using a cursor and update salary based on following criteria.
SAL >= 5000 300
SAL >= 3000 500
ELSE 750
DECLARE
S EMP.SAL%TYPE;
CURSOR MYCUR IS SELECT EMPNO, SAL FROM EMP;
ENO EMP.EMPNO%TYPE;
BEGIN
OPEN MYCUR;
FETCH MYCUR INTO ENO, S;
WHILE MYCUR %FOUND
LOOP
IF S>=5000 THEN S:=S+300;
ELSIF S>=300 THEN S:=S+500;
ELSE S:=S + 750;
END IF;

UPDATE EMP SET SAL = S WHERE EMPNO = ENO;
FETCH MYCUR INTO ENO, S;
END LOOP;
CLOSE MYCUR;
END;

The same using LOOP

DECLARE
S EMP.SAL%TYPE;
CURSOR MYCUR IS SELECT EMPNO, SAL FROM EMP;
ENO EMP.EMPNO%TYPE;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO ENO, S;
EXIT WHEN MYCUR%NOTFOUND;
IF S >= 5000 THEN S := S + 300;
ELSIF S >= 300 THEN S := S + 500;
ELSE S := S + 750;
END IF;
UPDATE EMP SET SAL = S WHERE EMPNO = ENO;
END LOOP;
CLOSE MYCUR;
END;

CURSOR FOR LOOP

DECLARE
EMPROW EMP%ROWTYPE;
CURSOR MYCUR IS SELECT * FROM EMP;
BEGIN
FOR MYCUR IN EMPROW

LOOP
IF EMPROW.SAL >= 5000 THEN
EMPROW.SAL := EMPROW.SAL + 300;
ELSIF EMPROW.SAL >= 3000 THEN NOT WORKING
EMPROW.SAL := EMPROW.SAL + 500;
ELSE
EMPROW.SAL := EMPROW.SAL + 750;
END IF;
UPDATE EMP SET SAL = EMPROW.SAL WHERE EMPNO = EMPROW.EMPNO;
END LOOP;
END;


Write a PL/SQL block to create a cursor on student table and print all student names who have no dues.

DECLARE
F STUDENT.FEE%TYPE;
CURSOR MYCUR IS SELECT SNO, FEE FROM STUDENT;
SN STUDENT.SNO%TYPE;
BEGIN
OPEN MYCUR;
FETCH MYCUR INTO SN, F;
WHILE MYCUR%FOUND
LOOP
IF F = 0 THEN
DBMS_OUTPUT.PUT_LINE(SN); DON’T KNOW WORKING OR NOT
END IF;
FETCH MYCUR INTO SN, F;
END LOOP;
CLOSE MYCUR;
END;

The same with LOOP

DECLARE
F STUDENT.FEE%TYPE;
CURSOR MYCUR IS SELECT SNAME, FEE FROM STUDENT;
SNA STUDENT.SNAME%TYPE;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO SNA, F;
EXIT WHEN MYCUR%NOTFOUND
IF F = 0 THEN
DBMS_OUTPUT.PUT_LINE(SNA);
END IF;
END LOOP;
CLOSE MYCUR;
END;

DECLARE
EMPROW EMP%ROWTYPE;
CURSOR MYCUR IS SELECT * FROM EMP;
BEGIN
FOR MYCUR IN EMPROW
LOOP
IF EMPROW.SAL >= 5000 THEN
EMPROW.SAL := EMPROW.SAL + 300;
ELSIF EMPROW.SAL >= 3000 THEN
EMPROW.SAL := EMPROW.SAL + 500;
ELSE
EMPROW.SAL := EMPROW.SAL + 750;
END IF;
UPDATE EMP SET SAL = EMPROW.SAL WHERE EMPNO = EMPROW.EMPNO;
END LOOP;
END;

PARAMETERISD CURSORS
We can also pass parameters to a cursor and select data based on that parameters. To declare a cursor as parameterized cursor syntax is as follows…
Syntax: CURSOR CURNAME(PARAM1 DATATYPE, PARAM2 DATATYPE,… PARAM N DATATYPE ) IS SELECT STATEMENT;

Write a PL/SQL block that accepts a course from user and displays the names of all students who has due in that course.

DECLARE
C STUDENT.COURSE%TYPE;
SN STUDENT.SNAME%TYPE;
CURSOR MYCUR(PC STUDENT.COURSE%TYPE) IS SELECT SNAME, FEE FROM STUDENT WHERE COURSE = PC;
F STUDENT.FEE%TYPE;
BEGIN
C := ‘COURSE’;
OPEN MYCUR(C);
FETCH MYCUR INTO SN, F;
WHILE MYCUR%FOUND
LOOP
IF F > 0 THEN
DBMS_OUTPUT.PUT_LINE(SN);
END IF;
FETCH MYCUR INTO SN, F;
END LOOP;
CLOSE MYCUR;
END;

FOR UPDATE WITH CURSORS : Whenever we are opening a cursor, a snapshot of the corresponding table is created as active set. Any changes made to the table that are committed before opening the cursors will reflect in the active set. If any changes are made to the table after opening the cursor will not reflect in the active set even they are committed.
To avoid this problem we can use for update clause at the end of the select statement written for the cursor. Because of this the rows retrieved by the select statement associated with cursor will be locked and will not allow any changes in the table by other users. This lock will be released when cursor is closed.
If any other user has locked the table or same rows retrieved by a cursor prior to opening the cursor with for update clause will hang the system and it will wait for the user to release the locks. To avoid system hanging you can use NOWAIT clause along with for update.
Example :
C STUDENT.COURSE%TYPE;
SN STUDENT.SNAME%TYPE;
CURSOR MYCUR(PC STUDENT.COURSE%TYPE) IS
SELECT SNAME, FEE FROM STUDENT WHERE COURSE = ‘PC’ FOR UPDATE NOWAIT;
F STUDENT.FEE%TYPE;
- - - - - -
- - - - - -
END;