ORACLE CURSORS
Cursors are the work place user by the Oracle engine for its internal processing to save the data returned by the SQL statements. The type of a cursor depends upon the nature of its working. For Example if Oracle Engine open a cursor for its internal processing then it is known as Implicit Cursor and is a cursor defined by the user as a user defined cursor then it is called as Explicit Cursor.
Implicit Cursors
Implicit Cursors are automatically used by the Oracle Engine to store some information regarding the internal processing of the server. Implicit Cursors are automatically created, fetches records and closed by the oracle database server.
Basically Internal Cursors are used to table the information about last insert, update, delete or single row select statement.
Implicit Cursor Attributes
1) SQL%ISOPEN – If the cursor is open then this attribute returns TRUE otherwise returns FALSE. In the case of implicit cursors this attribute always return FALSE because Implicit Cursors are automatically open and the closed by the Oracle Engine.
2) SQL%FOUND – If cursor affects one or more then one row by the SQL statement then this attribute will return TRUE otherwise it will return FALSE.
3) SQL%NOTFOUND – This is opposite to the SQL%FOUND attribute. If the statement will not return any of the value then this attribute will be TRUE otherwise it will by FALSE.
4) SQL%ROWCOUNT – You can count number of affected rows by the last SQL statement by using this attribute.
Consider the following block of code-
Take the table used in the tutorial Native Dynamic SQL (MyEmployeeMaster).
CREATE OR REPLACE PROCEDURE demo_imp IS
row_deleted VARCHAR2(30);
BEGIN
update MYEMPLOYEEMASTER set EMPLOYEENAME=‘Ashish’ where upper(EMPLOYEENAME) like ‘%V%’;
IF SQL%ISOPEN THEN
dbms_output.put_line(‘SQL%FOUND – True’);
else
dbms_output.put_line(‘SQL%FOUND – False’);
END IF;
IF SQL%FOUND THEN
dbms_output.put_line(‘SQL%FOUND – True’);
else
dbms_output.put_line(‘SQL%FOUND – False’);
END IF;
IF SQL%NOTFOUND THEN
dbms_output.put_line(‘SQL%NOTFOUND – True’);
else
dbms_output.put_line(‘SQL%NOTFOUND – False’);
END IF;
dbms_output.put_line(‘SQL%ROWCOUNT – ‘ || SQL%ROWCOUNT || ‘ NO OF ROWS AFFACTED’);
END demo_imp;
Resule of above procedure –
SQL> set serverout on;
SQL> exec demo_imp;
SQL%FOUND – False
SQL%FOUND – True
SQL%NOTFOUND – False
SQL%ROWCOUNT – 3 NO OF ROWS AFFACTED
Explicit Cursors –
Explicit Cursors are explicitly used by the user for the data processing. Explicit Cursors are not automatically opens and close by like an Implicit Cursor. It is declare, open and close by the user in the PL/SQL block. Following are the steps to take by the user for using the Explicit Cursors in their PL/SQL block.
1) Declare the cursor in the declaration section with CURSOR keyword.
2) Open the declared Cursor by using the OPEN keyword.
3) Fetch the Record from the opened cursor by the FETCH … INTO keywords.
4) Process the data in the cursor by using the loop by using the LOOP…. END LOOP block.
5) Fetch the next record and point out the pointer to the next record by using the FETCH…. INTO.
6)
Explicit Cursor Attributes-
1) SQL%ISOPEN – If the cursor is open then this attribute returns TRUE otherwise returns FALSE.
2) SQL%FOUND – If cursor affects one or more then one row by the SQL statement then this attribute will return TRUE otherwise it will return FALSE.
3) SQL%NOTFOUND – This is opposite to the SQL%FOUND attribute. If the statement will not return any of the value then this attribute will be TRUE otherwise it will by FALSE.
4) SQL%ROWCOUNT – You can count number of affected rows by the last SQL statement by using this attribute.
Consider the following block of code
CREATE OR REPLACE PROCEDURE demo_exp IS
cur sys_refcursor;
employeeidx number;
eployeenamex varchar2(20);
BEGIN
open cur for select employeeid, employeename from myemployeemaster;
fetch cur into employeeidx,eployeenamex ;
while cur%found
loop
dbms_output.put_line(‘employeeid: ‘ || employeeidx || ‘—-’ || ‘eployeename: ‘ || eployeenamex );
fetch cur into employeeidx,eployeenamex ;
commit;
end loop;
END demo_imp;
<p class=”MsoNormal” style=”text-align:justify;margin:0;”
