Saturday, 10 June 2017

Cursor in Oracle 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.

Cursor Parameter in Oracle
SQL>show parameter cursor
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     50





 




Change the Open Cursor limit :-

SQL> alter system set open_cursors=300 scope=both;
System altered.






 




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 <cursor_name> is <query>;
2) Open
open <cursor_name>;
3) Fetch
fetch <cursor_name> into <variable_name>;
4) close
close <cursor_name>;

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;
/



Cursor Attribute 


Explicit Cursor attribute :-
1) c1%isopen
2) c1%found
3) c1%notfound
4) c1%rowcount 

Implicit Cursor attribute :-
1) sql%isopen
2) sql%found
3) sql%notfound
4) sql%rowcount


More Examples of cursors with cursor attribute:-

declare
cursor c1 is select sal  from emp;
vsal emp.sal%type;
begin
open c1;
loop
fetch c1 into vsal;
exit when c1%notfound;
dbms_output.put_line(vsal);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/




declare
cursor c1 is select ename  from emp where deptno=&dno;
v emp.ename%type;
begin
open c1;
loop
fetch c1 into v;
exit when c1%notfound;
dbms_output.put_line(v);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/



Parameterized Cursor:- This cursor is helpful when we need to execute the same query for different value of parameter 

Example:- 
declare
cursor c1(dno number) is select ename  from emp where deptno=dno;
v emp.ename%type;
begin
open c1(10);
loop
fetch c1 into v;
exit when c1%notfound;
dbms_output.put_line(v);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/




declare
cursor c1(dno number) is select ename  from emp where deptno=dno;
v emp.ename%type;
begin
open c1(&dpt);
loop
fetch c1 into v;
exit when c1%notfound;
dbms_output.put_line(v);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/

declare
cursor c1(dno emp.deptno%type,vjob emp.job%type) is select ename  from emp
where deptno=dno and job=vjob;
v emp.ename%type;
begin
open c1(&dpt,&ajob);
loop
fetch c1 into v;
exit when c1%notfound;
dbms_output.put_line(v);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/

declare
cursor c1(dno emp.deptno%type,vjob emp.job%type) is select *  from emp
where deptno=dno and job=vjob;
v emp%rowtype;
begin
open c1(&dpt,&ajob);
loop
fetch c1 into v;
exit when c1%notfound;
dbms_output.put_line(v.ename||','||v.empno||','||v.deptno);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/

declare
cursor c1(dno emp.deptno%type,vjob emp.job%type) is
select empno,ename,job  from emp
where deptno=dno and job=vjob;
v emp%rowtype;
begin
open c1(&dpt,&ajob);
loop
fetch c1 into v.empno,v.ename,v.job;
exit when c1%notfound;
dbms_output.put_line(v.ename||','||v.empno||','||v.job);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/

For Loop Cursor:- This is the best cursor ever. It is most frequently used cursor. For this cursor no need to follow cursor steps means no need to declare,open fetch and close for this cursor

declare
cursor c1 is select empno,ename,job  from emp
where deptno=&dno;
begin
for i in c1 loop
dbms_output.put_line(i.ename||','||i.empno||','||i.job);
end loop;
end;
/


More Examples for For Loop Cursor:-

begin
for i in (select empno,ename,job  from emp where deptno=&dno) loop
dbms_output.put_line(i.ename||','||i.empno||','||i.job);
end loop;
end;
/
drop table emp_cur;
create table emp_cur as select * from emp where 1=2;
select * from emp_cur;

begin
for i in (select * from emp) loop
insert into emp_cur values (i.empno,i.ename,i.job,i.mgr,i.hiredate,i.sal,i.comm,i.deptno);
end loop;
end;
/

begin
for i in (select * from emp) loop
insert into emp_cur values (i.empno,i.ename,i.job,i.mgr,i.hiredate,i.sal,i.comm,i.deptno);
end loop;
commit;
end;
/


begin
for i in (select * from emp where deptno=&dno) loop
delete from emp_cur where empno=i.empno;
commit;
end loop;
end;
/

begin
for i in (select * from emp) loop
update emp_cur set sal=nvl(i.sal,0)+1000 where empno=i.empno;
commit;
end loop;
end;
/

begin
for i in (select * from emp_cur) loop
if i.deptno=10 then
update emp_cur set sal=nvl(i.sal,0)+1000 where empno=i.empno;
end if;
commit;
end loop;
end;
/

begin
for i in (select * from emp_cur) loop
if i.deptno=10 then
update emp_cur set sal=nvl(i.sal,0)+1000 where empno=i.empno;
elsif i.deptno=20 then
update emp_cur set sal=nvl(i.sal,0)+5000 where empno=i.empno;
end if;
commit;
end loop;
end;
/
begin
execute immediate 'create table xy (a number)';
end;
/
select * from emp_cur ;


declare
cursor c1 is select empno,ename,job  from emp
where deptno=&dno;
begin
for i in c1 loop
dbms_output.put_line(i.ename||','||i.empno||','||i.job);
end loop;
end;
/


begin
for i in (select empno,ename,job  from emp where deptno=&dno) loop
dbms_output.put_line(i.ename||','||i.empno||','||i.job);
end loop;
end;
/

create table emp_cur as select * from emp where 1=2;
select * from emp_cur;

begin
for i in (select * from emp) loop
insert into emp_cur values (i.empno,i.ename,i.job,i.mgr,i.hiredate,i.sal,i.comm,i.deptno);
end loop;
end;
/

begin
for i in (select * from emp) loop
insert into emp_cur values (i.empno,i.ename,i.job,i.mgr,i.hiredate,i.sal,i.comm,i.deptno);
end loop;
commit;
end;
/


begin
for i in (select * from emp) loop
delete from emp_cur where empno=i.empno;
commit;
end loop;
end;
/

begin
for i in (select * from emp) loop
update emp_cur set sal=nvl(i.sal,0)+1000 where empno=i.empno;
commit;
end loop;
end;
/

begin
for i in (select * from emp_cur) loop
if i.deptno=10 then
update emp_cur set sal=nvl(i.sal,0)+1000 where empno=i.empno;
end if;
commit;
end loop;
end;
/

begin
for i in (select * from emp_cur) loop
if i.deptno=10 then
update emp_cur set sal=nvl(i.sal,0)+1000 where empno=i.empno;
elsif i.deptno=20 then
update emp_cur set sal=nvl(i.sal,0)+5000 where empno=i.empno;
end if;
commit;
end loop;
end;
/


declare
cursor c1 is select sal  from emp;
vsal emp.sal%type;
begin
open c1;
loop
fetch c1 into vsal;
exit when c1%rowcount=5;
dbms_output.put_line(vsal);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/

Cursor Exception :- Below are predefined exceptions for cursor

1) CURSOR_ALREADY_OPEN
2) INVALID_CURSOR

 

1 comment:

  1. Thanks for the given information you are providing content very usefull to users.
    Learn How to Work With Ref Cursor in ORACLE 12C For Beginner Actual cost of this course is 9,999/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.


    Hurry-Up Enroll Now and get Free Access to the Entire Course


    ReplyDelete

Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...