Mastering SQL for Data Science: A Comprehensive Guide Day-10
Mastering SQL for Data Science: A Comprehensive Guide Day-10
Cursors
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/SQL implicitly 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=‘Anshuman’ 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=‘Anshuman’ 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;
/