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
6. 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;