Cursor : PLSQL creates a temporary area for the sql query, and that area is called cursor
Types of Cursors
- Implicit Cursor :
- Explicit cursor :
Implicit Cursor: executing a select/dml commands in execution block is called implicit cursor.
Implicit cursor attributes
- SQL%NOTFOUND: Returns true when no records updated/deleted.
- SQL%FOUND : Returns true when records found to update/delete
- SQL%ROWCOUNT: Returns no.of records udpated/deleted.
Syntax:
select col1,col2…
into var1, var2
from table
where condition
UPdate table
set column=value
where condition
delete from table
where <condition>Note : Select statement should return only one record and it should be in begin..end
Example: Update records in emp table
declare
begin
update emp
set sal=sal+500
where job=’manager’;
if SQL%FOUND then
dbms_output.put_line ('No of records updated '||SQL%ROWCOUNT);
COMMIT;
elsif SQL%NOTFOUND then
dbms_output.put_line ('No records updated');
end if;end;
/
SQL> @ filname.sql
no of records updated 2
(or)
No records updated
.
Example 2: Use select and update
declare
ln_sal number;
lc_ename varchar2(10);
begin
begin
select ename,sal
into lc_ename, ln_sal
from emp
where empno=7933;
DBMS_OUTPUT.PUT_LINE ('ENAME '||lc_ename);
dbms_output.put_line ('Sal '||ln_sal);
exception
when others then
dbms_output.put_line ('Error '||SQLERRM);
end;
begin
update emp
set sal=sal+500
where upper(job)='MANAGER';
if SQL%FOUND then
dbms_output.put_line ('No of records updated '||SQL%ROWCOUNT);
COMMIT;
elsif SQL%NOTFOUND then
dbms_output.put_line ('No records updated');
end if;
end;end;
/
==============================================================================
Tasks : Increase the price of all items purchased from the supplier Dell in purchase table
show proper messages like no of records updated / no records updated etc.
Delete all records purchased before '01-jan-2015' purchase table
show proper messages like no of records deleted / no records delete.