Mastering SQL for Data Science: A Comprehensive Guide
Day-10
Cursors
v
In a SQL when you submit a query, it returns
number of rows depend on query. It may be zero or may be hundreds. While in
PL/SQL if your select statement returns multiple rows then oracle must return
too_many_rows, error message.
A
cursor is used to refer to a program to fetch and process the rows returned by
the SQL statement, one at a time. There are two types of cursors;
1) Explicit Cursor
2) Implicit Cursor
PL/SQLimplicitly declares a cursor of every SQL statement. Implicit cursor is
declared by oracle for each update, delete or insert SQL command. Any select
statement can return zero, one or many rows of data. When a PL/SQL cursor query
returns multiple rows of data, the resulting groups of rows is called the active
set.
1) Explicit Cursor:
User
define cursor are known as explicit cursor. Explicit cursor is one in which the
cursor explicitly assigned to the select statement processing of explicit
cursor involves four steps;
1) Declare the cursor
2) Open the cursor
3) Fetch data from the cursor
4) Close the cursor
These
steps explain in detail;
1) Declare the cursor:
The
first step is to declare cursor in order for PL/SQL to reference the returned
data. This must be done in the declaration portion of your PL/SQL block.
ü The name of the cursor
ü It associates a query with a cursor
Syntax: cursor <cursor_name> is <select statement>;
E.g: cursor c1 is select *
from emp where name like ‘a%’;
2) Open the cursor:
Opening the cursor activates the query and identifies the active set.
When the open command executed, the cursor identifies only the rows that
satisfy the query used with cursor definitions. After a cursor is opened, until
the moment user close it, all the fetched data in the active set will remain
static.
Syntax: open
<cursor_name>;
E.g: open c1;
3) Fetching data from the cursor:
Getting data from the active set is accomplished with the fetch
statement. The fetch command retrieves the rows from the active set one row at
a time. The fetch command is usually used in conjunction with some type of
iterative process.
Syntax: fetch <cursor_name> into
<variable_name|record_list>;
E.g: fetch c1 into no,nm,c,s;
4) Close the cursor:
The close statement closed or deactivates the previously opened cursor
and makes the active set undefined. Oracle will implicitly close a cursor when
the user’s program or session is terminated.
Syntax: close <cursor_name>;
E.g: close c1;
E.g:declare
cursor c1 is select emp_no, ename, city, sal from emp;
no emp.emp_no%type;
nm emp.ename%type;
c emp.city%type;
s emp.sal%type;
begin
open c1;
fetch c1 into no,nm,c,s;
dbms_output.put_line(no||’ ‘||nm||’ ‘||c||’ ‘||s);
close c1;
end;
/
Above cursor will store all the records of emp table into
active set but display only first employee details. Without use of attributes
of cursor, user cannot get every record from the table.
Explicit cursor attributes:
Each cursor, whenever it is explicitly or implicitly defined, carriers
with it attributes that provide useful data of the cursor. The four cursor
attributes are;
%isopen, %found, %notfound and
%rowcount.
1) %isopen attribute:
The %isopen attribute indicates whether the cursor is open. If the cursor
is open, then this attribute equates to true otherwise it will be false.
E.g:
declare
Cursor cur1 is select * from emp;
begin
Open cur1;
If cur1%isopen then
dbms_output.put_line(‘Cursor is already open’);
else
open cur1;
end if;
close cur1;
end;
/
2)%found attribute:
The %found attribute equates to true if the fetch statement returns row.
Therefore, the %found attribute is a logical opposite of the %notfound
attribute. The %found attribute equates to false when no rows are fetched.
3)%notfound attribute:
The %notfound attribute is useful in telling you whether a cursor has any
rows left in it to be fetched. The %notfound attribute equates to true when
last fetch statements returns no row, while it equates to false if last fetch
statement returns row.
E.g: %found and %notfound
declare
cursor c1 is select * from student where no>5;
n student.no%type;
nm student.name%type;
begin
open c1;
loop
fetch c1 into n,nm;
exit when c1%notfound;
if c1%found then
dbms_output.put_line(n||' ' ||nm);
end if;
end loop;
close c1;
end;
/
4)%rowcount attribute:
The %rowcount attribute returns the number of rows that fetched so fat
for the cursor. Prior to the first fetch, %rowcount is zero.
E.g:
declare
cursor cur1 is select * from student where no<7;
n student.no%type;
nm student.name%type;
begin
open cur1;
loop
fetch cur1 into n,nm;
exit when cur1%rowcount>5;
dbms_output.put_line(n||' '||nm);
end loop;
close cur1;
end;
/
· Cursor using for loop:
In many programming situations, there is more than one
way to code your logic. This is also implies/applies to PL/SQL cursor, there
are opportunities to streamline or simplify the coding and usage of them. An
alternate way to open, fetch or close the cursor oracle furnishes another
approach to place the cursor within for loop.
Syntax:
For <record_list|
variable>
In <cursor_name>
Loop
Statements;
End loop;
E.g:
declare
cursor c1
is select * from emp where deptno in (10,20);
erec
emp%rowtype;
begin
open c1;
for erec in c1;
loop
Dbms_output.put_line(erec.empno||’
‘||erec.ename||’ ‘||erec.sal);
end loop;
end;
/
2) Implicit cursor:
Oracle crates and opens
a cursor for every SQL statement that is not part of an explicitly declared
cursor. PL/SQL implicitly declares cursors for all SQL data manipulation
statements, including queries that return one row.
1)%isopen attribute:
After the
execution of the SQL statement, the associated SQL cursor is always closed
automatically by oracle. Hence, the %isopen attribute always evaluates to
false.
2) %found attribute:
This attribute will
equate to true if an insert, update or delete affected one or more rows or
select into returns one or more rows.
E.g:
begin
update student
set name='tanmay'
where no=3;
if SQL%found then
commit;
else
dbms_output.put_line('Record is not available');
end if;
end;
/
3) %notfound attribute:
The %notfound attribute
evaluates to true if the most recent SQL statement does not affect any rows.
E.g:
begin
update student
set name='tanmay'
where no=3;
if SQL%notfound then
dbms_output.put_line('Record is not available');
else
commit;
end if;
end;
/
4)%rowcount attribute:
This attribute
equates to the total number of rows affected by the most recent SQL statement.
E.g:
begin
delete from student
where no=4;
dbms_output.put_line(SQL%rowcount||' rows deleted');
end;
/
0 Comments