EXPLICIT CURSORTypes of cursor
Created and Managed internally by the oracle server to process SQL statements.
Attributes Description
---------------- ----------------------------------------------------------------------------
sql%notfound Boolean attributes that evaluates to TRUE. If the most recent SQL statement
did not return even one row.
sql%found Boolean attribute that evaluates to TRUE if the most recent SQL statement
returned at least on row.
sql%rowcount An integer value that represents the number of row affected by the most recent
SQL statement.
Implicit cursor :
declare
v_rows number(5);
begin
update emp set sal=sal+100 where comm is null;
if sql%notfound then
dbms_output.put_line('NOT UPDATE');
elsif sql%found then
v_rows := sql%rowcount;
dbms_output.put_line('salary for '||v_rows||' people increased');
end if;
end;
/
salary for 10 people increased
PL/SQL procedure successfully completed.
Explicit cursor :Explicit declared the cursor name by the programmer.
Attributes Description
---------------- ---------------------------------------------------------------------
CUR%ISOPEN Evaluates to TRUE if the cursor is open.
CUR%NOTFOUND Evaluates to TRUE if the most recent fetch does not return a row.
CUR%FOUND Evaluates to TRUE if the most recent fetch returns a row:
complement of %NOTFOUND
CUR%ROWCOUNT Evaluates to the total number of rows returned so far.
Explicit cursor :Explicit declared the cursor name by the programmer. Syntax :
CURSOR cursor_name [ (
[ parameter_1 [, parameter_2 ...] ) ]
[ RETURN return_specification ]
IS sql_select_statements
[FOR UPDATE [OF [column_list]];
Begin
Open cursor_name:
statement;
Close cursor_name;
End;
Action Description
---------- -------------------------------------------------------------
Declare cursor is creating a memory area.
Open Identify the active set from a SQL query
Fetch Load the current information into variables
Empty Its check whether row exists, if exists fetch the record.
Close Release the active set.
Explicit cursor - static :
SQL> declare
tesdb_dept dept%rowtype;
cursor tesdb_cur is select * from dept;
begin
open tesdb_cur;
fetch tesdb_cur into tesdb_dept;
dbms_output.put_line(tesdb_dept.deptno||' '||
tesdb_dept.dname||' '||tesdb_dept.loc);
close tesdb_cur;
end;
/
10 ACCOUNTING NEW YORK
PL/SQL procedure successfully completed.
Explicit cursor - Dynamic :
SQL> declare
tesdb_dept dept%rowtype;
cursor tesdb_cur is select * from dept;
begin
open tesdb_cur;
loop
fetch tesdb_cur into tesdb_dept;
exit when tesdb_cur%notfound;
dbms_output.put_line (tesdb_dept.deptno||' '||
tesdb_dept.dname||' '||tesdb_dept.loc);
end loop;
close tesdb_cur;
end;
/
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALESTEAM CHICAGO
40 OPERATIONS BOSTON
90 SALES MUMBAI
PL/SQL procedure successfully completed.
Explicit cursor -%isopen
SQL >declare
tesdb_dept dept%rowtype;
cursor tesdb_cur is select * from dept;
begin
if not tesdb_cur%isopen then
open tesdb_cur;
end if;
loop
fetch tesdb_cur into tesdb_dept;
exit when tesdb_cur%notfound;
dbms_output.put_line (tesdb_dept.deptno
||' '||tesdb_dept.dname||' '||tesdb_dept.loc);
end loop;
close tesdb_cur;
end;
/
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALESTEAM CHICAGO
40 OPERATIONS BOSTON
90 SALES MUMBAI
PL/SQL procedure successfully completed.
For loop with cursor :
SQL> declare
cursor tesdb_cur is select * from salgrade;
tesdb_salgrade salgrade%rowtype;
begin
for tesdb_salgrade in tesdb_cur
loop
dbms_output.put_line(tesdb_salgrade.grade||' '||
tesdb_salgrade.losal||' '||tesdb_salgrade.hisal);
end loop;
end;
/
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
PL/SQL procedure successfully completed.
While loop with cursor :
SQL> declare
cursor tesdb_cur is select * from dept;
tesdb_dept tesdb_cur%rowtype;
begin
open tesdb_cur;
fetch tesdb_cur into tesdb_dept;
while tesdb_cur%found
loop
dbms_output.put_line(tesdb_dept.deptno||' '||
tesdb_dept.dname||' '||tesdb_dept.loc);
fetch tesdb_cur into tesdb_dept;
end loop;
end;
/
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALESTEAM CHICAGO
40 OPERATIONS BOSTON
90 SALES MUMBAI
PL/SQL procedure successfully completed.
Reference cursor : An acronym of reference to a cursor.A Ref cursor is plsql data type is the memory address of work area. Ref cursor is a pointer to handle result set on the database.A Ref cursor refers to a memory address on the database.
declare
type tesdb_ty is ref cursor;
cur_tesdb tesdb_ty;
db_free number;
db_used number;
db_total number;
be --free space
open cur_tesdb for
select sum(bytes/1024/1024) from dba_free_space;
loop
fetch cur_tesdb into db_free;
exit when cur_tesdb%notfound;
dbms_output.put_line
('database free space is '||db_free);
end loop;
close cur_tesdb;
--used space
open cur_tesdb for
select sum(bytes/1024/1024) from dba_segments;
loop
fetch cur_tesdb into db_used;
exit when cur_tesdb%notfound;
dbms_output.put_line
('database used space is '||db_used);
end loop;
close cur_tesdb;
--total space
open cur_tesdb for
select sum(bytes/1024/1024) from dba_data_files;
loop
fetch cur_tesdb into db_total;
exit when cur_tesdb%notfound;
dbms_output.put_line
('database total available space is '||db_total);
end loop;
close cur_tesdb;
end;
/
database free space is 1443.125
database used space is 674.875
database total available space is 2130
PL/SQL procedure successfully completed.
Parameterized cursor :Parameterized cursors are static cursors that can accept passed-in parameter values when they are opened.
declare
a number;
cursor c1(b number) is select empno from emp where deptno=10;
begin
open c1(10);
loop
fetch c1 into a;
dbms_output.put_line(a);
exit when c1%notfound;
end loop;
end;
7782
7839
7934
7934
Non-declared cursor :
declare
a number
begin
for i in (select * from emp)
loop
dbms_output.put_line(i.empno);
end loop;
end;
/
PL/SQL procedure successfully completed.
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
PL/SQL procedure successfully completed.
« Previous Next Topic » (PL/SQL - Exceptions) |