Cursor For Loop in PL/SQL - Explicit Cursor - Parameterized Cursors - Explicit Cursor Orcle
Cursor for loop for Oracle explicit cursor. Parameterized cursors. Full courses : SQL - https://bit.ly/38c91ih | Python - https://bit.ly/3ihca4L | MongoDB - https://bit.ly/38bRJ4K | PL/SQL - https://bit.ly/2Zl4OVw
#Crazy4DB #OraclePLSQL #LearnPLSQL #Crazy4DB #MunshiSir #LearnOracle
What is explicit cursor : 2.00
Why use explicit cursor : 3:39
Step of Explicit Cursor : 7:43
Explicit Cursor attributes : 14:06
Explicit cursor example : 19:40
Cursor For loop : 27:00
Parameterized cursor : 34:52
Cursor for locking for update nowait : 38:49
Program code :
Use of explicit cursor with simple loop
====================================
declare
n number;
s number;
e emp.ename%type;
cursor c1 is select distinct sal from emp
where sal is not null
order by sal desc;
cursor c2 is select ename from emp
where sal = s;
begin
n := '&EnterRank';
open c1;
fetch c1 into s;
while c1%found loop
if c1%rowcount = n then
open c2;
loop
fetch c2 into e;
if c2%found then
dbms_output.put_line(e||' '||s);
else
exit;
end if;
end loop;
exit;
end if;
fetch c1 into s;
end loop;
dbms_output.put_line('no more emp');
exception
when value_error then
dbms_output.put_line('value must be numeric');
end;
Using Cursor for loop
==================================
declare
n number;
s number;
cursor c1 is select distinct sal from emp
where sal is not null
order by sal desc;
cursor c2 is select ename from emp
where sal = s;
begin
n := '&EnterRank';
for vsal in c1 loop
if c1%rowcount = n then
s := vsal.sal;
for vname in c2 loop
dbms_output.put_line(vname.ename||' '||s);
end loop;
end if;
end loop;
dbms_output.put_line('no more emp');
exception
when value_error then
dbms_output.put_line('value must be numeric');
end;
Parameterized cursor example
====================================
declare
n number;
cursor c1 is select distinct sal from emp
where sal is not null
order by sal desc;
cursor c2 (s emp.sal%type) is select ename from emp
where sal = s;
begin
n := '&EnterRank';
for vsal in c1 loop
if c1%rowcount = n then
for vname in c2(vsal.sal) loop
dbms_output.put_line(vname.ename||' '||vsal.sal);
end loop;
end if;
end loop;
dbms_output.put_line('no more emp');
exception
when value_error then
dbms_output.put_line('value must be numeric');
end;
Locking for Transaction by explicit cursor
======================================
declare
vdno emp.deptno%type;
incr number;
lck exception;
pragma exception_init(lck, -00054);
cursor c1 (pdno emp.deptno%type) is select * from emp
where deptno = pdno
for update nowait;
begin
vdno := '&DeptNo';
incr := '&Increment';
open c1(vdno);
update emp set sal = sal + incr
where deptno = vdno;
if sql%rowcount [greater than] 0 then
dbms_output.put_line(sql%rowcount||' rows updated');
else
dbms_output.put_line('department does not exist');
end if;
close c1;
exception
when value_error then
dbms_output.put_line('check input values');
when lck then
dbms_output.put_line('rows locked - try later');
end;