PL SQL Tutorial


PL SQL CURSORS

     In PL/SQL Cursors are Name Assigned to Memory Area.

  • Exlicit Cursors
  • Implicit Cursors

Implicit Cursor

     Implicit Cursors, Opening a cursor,Fetching records, and Closing a cursor, these operations done by PL/SQL Engine. User has no knowledge how it is done, internally PL/SQL performs These operations on behalf of User. Anyway Programmer can check cursor status using following Cursor Attributes.

Implicit Cursor Attributes

  • SQL%FOUND
  • SQL%NOTFOUND
  • SQL%ROWCOUNT
  • SQL%ISOPEN
Example:
	
	declare
begin for idx in (select * from emp) loop
   dbms_output.put_line(idx.empno|| ' ' || idx.ename);
end loop;
end;

	
Example2:
	declare
 cursor c is select * from emp;
 emp_rec emp%ROWTYPE;
begin
/*for idx in c
 loop
   dbms_output.put_line(idx.empno|| ' ' || idx.ename);
end loop;
*/

	

Explicit Cursor

    



Steps required to use Explicite Cursor

  1. Declare Cursor variable
  2. OPEN cursor
  3. FETCH
  4. Check Cursor Attribute
  5. CLOSE CURSOR

Explicit Cursor Attributes

  • cursor_name%ISOPEN
  •     returns TRUE when cursor is opened.

  • cursor_name%FOUND
  •    returns TRUE when record fetched successfully

  • cursor_name%NOTFOUND
  •     returns TRUE when record was not fetched successfully

  • cursor_name%ROWCOUNT
  •     returns number of records fetched from the cursor.

Example:
declare
 cursor c is select * from emp;
 emp_rec emp%ROWTYPE;
begin
	
open c;
loop
fetch c into emp_rec;
exit when c%NOTFOUND;
        dbms_output.put_line(emp_rec.empno||' '||emp_rec.ename);
end loop;
close c;
end;
	
	
	

ADS