Implicit cursors - Implicit Cursor Example - Implicit Cursors in PL SQL - Where Current of
Implicit cursors in PL SQL - SELECT, INSERT, UPDATE, DELETE operations. WHERE CURRENT OF clause. 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
Video Index :
What is implicit cursor - 01:12
Which statements are used - 03:01
Special Exceptions - 03 : 52
Example program - 08:26
Where current of example - 14:21
Program Code used in Implicit Cursor examples :
==========================================
Example :Update salary of employees of given department
with given amount of increment
declare
vdno emp.deptno%type;
incr number;
burd 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
burd := incr * sql%rowcount;
dbms_output.put_line('Total Burden '||burd);
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;
WHERE CURRENT OF example
=======================================
Problem : In the above problem if it is required to limit
the increment to only such employees who do not cross a
certain specified limit. For those who are not found eligible
for this criteria then they have to be skipped.
declare
vdno emp.deptno%type;
incr number;
lim number;
burd number;
rupd number := 0;
rnupd number := 0;
lck exception;
pragma exception_init(lck , -00054);
cursor c1(pdno emp.job%type) is select * from emp where deptno = pdno
for update nowait;
begin
vdno := '&deptno';
incr := &increment;
lim := &limit;
for var in c1(vdno) loop
rupd := c1%rowcount;
if var.sal + incr [less than] lim then
update emp set sal = sal + incr where current of c1;
else
dbms_output.put_line ('employee '||var.ename|| ' not updated');
rnupd := rnupd + 1;
end if;
end loop;
rupd := rupd - rnupd;
burd := rupd * incr;
dbms_output.put_line ('burden = '||burd);
dbms_output.put_line ('updated = '|| rupd);
dbms_output.put_line ('not updated = '|| rnupd);
exception
when lck then
dbms_output.put_line('rows are locked - try later');
end;