PLSQL Cursor

Cursor:- Cursor is a memory area,that hold the data of query which is assigned to cursor

Purpose of Cursor:- We can not use query in PLSQL block which returns more than 1 row, to handle that query we use cursor 

Types of Cursor :- There are 2 types of cursor
1) Implicit Cursor
2) Explicit Cursor

Implicit Cursor :- Implicit cursor is managed by the Oracle internally for query
Explicit Cursor :-  Explicit cursor managed by user.
Single row returning query in PLSQL:- Single value or record can be handled by variable and no need to define any cursor for that


Example1:-
declare
vsal emp.sal%type;
begin
select sal into vsal from emp where empno=7934 ;
dbms_output.put_line(vsal);
end;
/

 








More Examples for single value/row returning query:-
 
Example2:-
declare
vsal emp.sal%type;
vename emp.ename%type;
begin
select ename,sal into vename,vsal from emp where empno=7934 ;
dbms_output.put_line(vename||'  '||vsal);
end;
/

Example3:-  declare
v emp%rowtype;
begin
select * into v from emp where empno=7934 ;
dbms_output.put_line(v.ename||'  '||v.sal||'  '||v.empno);
end;
/

Multiple row returning query in PLSQL:-When we are using multiple row returning query  in   plsql ,Oracle returns Error  "ORA-01422: exact fetch returns more than requested number of rows"

Example1:-
set serveroutput on;
declare
vsal emp.sal%type;
begin
select sal into vsal from emp ;
dbms_output.put_line(vsal);
end;
/

 








To handle this error we can use cursor :-

Steps for cursor:- There are following steps need to follow sequentially for cursor

1) Declaration
cursor is ;
2) Open
open ;
3) Fetch
fetch into ;
4) close
close ;

Example:-
declare
cursor c1 is select sal  from emp;
vsal emp.sal%type;
begin
open c1;
fetch c1 into vsal;
dbms_output.put_line(vsal);
fetch c1 into vsal;
dbms_output.put_line(vsal);
fetch c1 into vsal;
dbms_output.put_line(vsal);
fetch c1 into vsal;
dbms_output.put_line(vsal);
close c1;
end;
/