In this tutorial, we will introduce you to PL/SQL cursor. You will learn step by step how to use a cursor to loop through a set of rows and process each row individually.
Introducing to PL/SQL Cursor
When you work with Oracle database, you work with a complete set of rows returned from a select statement. However the application in some cases cannot work effectively with the entire result set, therefore, the database server needs to provide a mechanism for the application to work with one row or a subset of the result set at a time. As the result, Oracle created PL/SQL cursor to provide these extensions.
Cursor:- Cursor is a memory area, that holds the data of query which is assigned to the cursor
Purpose of Cursor:- We can not use the query in PLSQL block which returns more than 1 row, to handle that query we use the cursor
Types of Cursor:- There are 2 types of cursor
1) Implicit Cursor
2) Explicit Cursor
Implicit Cursor:- Implicit cursor is managed by the Oracle internally for query
Explicit Cursor:- Explicit cursor managed by the user.
Single row-returning query in PLSQL:- Single value or record can be handled by variable and no need to define any cursor for that
Example1:-
declare
vsal emp.sal%type;
begin
select sal into vsal from emp where empno=7934 ;
dbms_output.put_line(vsal);
end;
/
More Examples for single value/row-returning query:-
Example2:-
declare
vsal emp.sal%type;
vename emp.ename%type;
begin
select ename,sal into vename,vsal from emp where empno=7934 ;
dbms_output.put_line(vename||' '||vsal);
end;
/
Example3:- declare
v emp%rowtype;
begin
select * into v from emp where empno=7934 ;
dbms_output.put_line(v.ename||' '||v.sal||' '||v.empno);
end;
/
Multiple row-returning queries in PLSQL:-When we are using multiple row-returning query in plsql , Oracle returns Error "ORA-01422: exact fetch returns more than requested number of rows"
Example1:-
set serveroutput on;
declare
vsal emp.sal%type;
begin
select sal into vsal from emp ;
dbms_output.put_line(vsal);
end;
/
To handle this error we can use cursor:-
Steps for the cursor:- There are following steps need to follow sequentially for cursor
1) Declaration
cursoris ;
2) Open
open;
3) Fetch
fetchinto ;
4) close
close;
Example:-
declare
cursor c1 is select sal from emp;
vsal emp.sal%type;
begin
open c1;
fetch c1 into vsal;
dbms_output.put_line(vsal);
fetch c1 into vsal;
dbms_output.put_line(vsal);
fetch c1 into vsal;
dbms_output.put_line(vsal);
fetch c1 into vsal;
dbms_output.put_line(vsal);
close c1;
end;
/
Purpose of Cursor:- We can not use the query in PLSQL block which returns more than 1 row, to handle that query we use the cursor
Types of Cursor:- There are 2 types of cursor
1) Implicit Cursor
2) Explicit Cursor
Implicit Cursor:- Implicit cursor is managed by the Oracle internally for query
Explicit Cursor:- Explicit cursor managed by the user.
Single row-returning query in PLSQL:- Single value or record can be handled by variable and no need to define any cursor for that
Example1:-
declare
vsal emp.sal%type;
begin
select sal into vsal from emp where empno=7934 ;
dbms_output.put_line(vsal);
end;
/
More Examples for single value/row-returning query:-
Example2:-
declare
vsal emp.sal%type;
vename emp.ename%type;
begin
select ename,sal into vename,vsal from emp where empno=7934 ;
dbms_output.put_line(vename||' '||vsal);
end;
/
Example3:- declare
v emp%rowtype;
begin
select * into v from emp where empno=7934 ;
dbms_output.put_line(v.ename||' '||v.sal||' '||v.empno);
end;
/
Multiple row-returning queries in PLSQL:-When we are using multiple row-returning query in plsql , Oracle returns Error "ORA-01422: exact fetch returns more than requested number of rows"
Example1:-
set serveroutput on;
declare
vsal emp.sal%type;
begin
select sal into vsal from emp ;
dbms_output.put_line(vsal);
end;
/
To handle this error we can use cursor:-
Steps for the cursor:- There are following steps need to follow sequentially for cursor
1) Declaration
cursor
2) Open
open
3) Fetch
fetch
4) close
close
Example:-
declare
cursor c1 is select sal from emp;
vsal emp.sal%type;
begin
open c1;
fetch c1 into vsal;
dbms_output.put_line(vsal);
fetch c1 into vsal;
dbms_output.put_line(vsal);
fetch c1 into vsal;
dbms_output.put_line(vsal);
fetch c1 into vsal;
dbms_output.put_line(vsal);
close c1;
end;
/
0 comments:
Post a Comment