Table of Contents
Working With Cursors In Oracle
CURSORS:
- Cursor is a pointer to the chunk of memory area where the sql statements are processed and results are stored.
- Each and every sql statement in a plsql block is a cursor.
- A cursor structure allows the processing of a select statement and accesses the result returned by that query.
- For a dedicated database the chunk of memory is part of User Global Area (UGA). And in for shared server connections, the cursor context area is allocated in System Global Area (SGA).
TYPES OF CURSOR:
- IMPLICIT CURSOR
- EXPLICIT CURSOR
IMPLICIT CURSOR:
Every sql statement in the executable or exception section of a plsql block is an example of implicit cursor.
- SELECT..INTO
- SELECT..BULK COLLECT INTO
- SELECT IN CURSOR FOR LOOP
- INSERT, UPDATE, DELETE, MERGE
EXPLICIT CURSOR:
A cursor defined by the user or developer in the declaration section of a plsql block is an example of explicit cursor.
CURSOR EXECUTION CYCLE:
A cursor is a handler to execute an sql statement and exists within the life of a session. Once the session terminates the cursor no longer exists.
Cursor goes through the following stages of execution whether its created explicitly or implicitly:
OPEN:
As soon as the cursor gets created, oracle allocates a private area in the session’s UGA. This private area is used for sql statement processing. The value of the initialization parameter OPEN_CURSOR decides the maximum no of open cursors in a session.
PARSE:
Oracle checks whether the statement is syntactically correct, its semantics and privileges.
BIND:
If the sql statement needs additional input values for processing, the respective placeholders are replaced by the actual values.
EXECUTE:
The sql statement is executed following the conventional execution process. Oracle generates the hash value for the sql statements and places it in the shared pool. Oracle also performs library cache lookup to search for any past executions of the same sql. A successful lookup in the library cache avoids hard parsing of sql statement. If a hash is not found then, a new execution plan is generated and the sql is processed. Once the sql query is executed, the result set is placed in UGA.
FETCH:
Fetch the record from the result set corresponding the current position of the record pointer. The record pointer is moved forward by one after every successful fetch.
CLOSE:
The cursor is closed and the private memory area is flushed.
You can check the v$open_cursor to view the list of open cursor in the current session:
Conn sys as sysdba
grant select on v_$open_cursor to hr;
conn hr/hr
create or replace synonym v$open_cursor for sys.v_$open_cursor;
declare
v_cnt number;
begin
select count(1)
into v_cnt
from employees;
end;
/
select sql_id,
sql_text,
cursor_type
from v$open_cursor
where user_name = 'HR'
and cursor_type <> 'DICTIONARY LOOKUP CURSOR CACHED';
CURSOR ATTRIBUTES:
Cursor attributes provides the information about last active cursor. Its not persisted in the database but are aligned with the result set in the session memory.
%FOUND
Returns true if the cursor pointer points to valid record. Once the last record is fetched its return false.
%NOTFOUND
Its just the opposite of %FOUND
%ISOPEN
It returns true if the cursor is currently open, otherwise it returns false.
%ROWCOUNT
Returns the no of records processed by the sql statement. It must be referred within the cursor execution cycle, otherwise it raises INVALID CURSOR exception.
Additional two cursor attributes are used in bulk processing with FORALL statement.
- %BULK ROWCOUNT
- %BULK EXCEPTIONS
Example of Simple Cursors:
--Working With Cursors In Oracle : Example: Simple Cursor
declare
v_empid number;
begin
select employee_id
into v_empid
from employees
where employee_id = 1000;
 if sql%found then
  dbms_output.put_line('Count = '||sql%rowcount);
end if;
exception
when others then
  dbms_output.put_line('Record fetched - '||sql%rowcount);
end;
/
Record fetched – 0
--Working With Cursors In Oracle : Example: Simple Cursor
declare
v_empid number;
begin
select employee_id
into v_empid
from employees
where employee_id = 105;
if sql%found then
dbms_output.put_line('Count = '||sql%rowcount);
end if;
exception
when others then
dbms_output.put_line('Record fetched - '||sql%rowcount);
end;
/
Count = 1
--Working With Cursors In Oracle : Example: Simple Cursor
begin
update employees
set salary = salary + 10
where employee_id = 105;
if sql%found then
dbms_output.put_line('Rows updated = '||sql%rowcount);
else
dbms_output.put_line('Rows updated = '||sql%rowcount);
end if;
rollback;
exception
when others then
dbms_output.put_line('Rows updated = '||sql%rowcount);
end;
/
Rows updated = 1
--Working With Cursors In Oracle : Example: Simple Cursor
begin
update employees
set salary = salary + 10
where employee_id = 1000;
if sql%found then
dbms_output.put_line('Rows updated = '||sql%rowcount);
else
dbms_output.put_line('Rows updated = '||sql%rowcount);
end if;
rollback;
exception
when others then
dbms_output.put_line('Rows updated = '||sql%rowcount);
end;
/
Rows updated = 0
--Working With Cursors In Oracle : Example: Simple Cursor
declare
cursor c1
is
select * from employees
where department_id = 20;
v_type c1%rowtype;
begin
open c1;
loop
fetch c1 into v_type;
exit when c1%notfound;
dbms_output.put_line('Employee_id = '||v_type.employee_id||' Name = '||v_type.last_name);
end loop;
exception
when others then
dbms_output.put_line('Error - '||sqlerrm);
end;
/
Employee_id = 201 Name = Hartstein
Employee_id = 202 Name = Fay
Oracle accepts parameter for explicit cursors:
--Working With Cursors In Oracle : Example: Oracle accepts parameter for explicit cursors
declare
cursor c1(p_deptno number)
is
select * from employees
where department_id = p_deptno;
v_type c1%rowtype;
begin
open c1(20);
loop
fetch c1 into v_type;
exit when c1%notfound;
dbms_output.put_line('Employee_id = '||v_type.employee_id||' Name = '||v_type.last_name);
end loop;
exception
when others then
dbms_output.put_line('Error - '||sqlerrm);
end;
/
Employee_id = 201 Name = Hartstein
Employee_id = 202 Name = Fay
FOR UPDATE CLAUSE:
For update clause is an optional part of a select statement.
The select statement associated with the cursor does not have any locks on the rows it returns, allowing any session to perform any operation on those rows during the cursor operation.
When we want to issue a lock over the record set returned by the cursor associated select query, we can opt for the FOR UPDATE clause. Its automatically places an exclusive row-level lock on all the rows retrieved, forbidding any other session performing a DML operation on them until we perform a commit or rollback to release the lock.
When we issue a SEECT..FOR UPDATE statement, the RDBMS automatically exclusive row level locks on all the rows identified by the select statement, holding the records for your changes only as you move through the rows retrieved by the cursor. No one else will be able to change any of these records until you perform a COMMIT or ROLLBACK.
Case 1:
--Working With Cursors In Oracle : Example: FOR UPDATE Clause
Cursor emp_cur
Is
Select employee_id,
last_name,
hire_date,
job_id
salary
from employees
where department_id = 20
for update;
Case 2:
--Working With Cursors In Oracle : Example: FOR UPDATE Clause
Cursor emp_cur
Is
Select employee_id,
last_name,
hire_date,
job_id
salary
from employees
where department_id = 20
for update of salary;
In the last example the for update qualifies only for the update of salary column.
FOR UPDATE clause in a select statement can be used against multiple tables. In this case, rows in a table is locked only if the FOR UPDATE clause references a column in that table.
Case 3:
--Working With Cursors In Oracle : Example: FOR UPDATE Clause
Cursor emp_dept_cur
Is
Select a.emloyee_id,
a.last_name,
b.department_name,
a.salary
from employees a,
departments b
where a.department_id = b.department_id
for update of salary;
FOR UPDATE OF clause does not restrict you to change only columns listed. Locks are still placed on all the rows. In the above example, OF clause only mention the column from employees table.
No columns from departments tables are used in the cursor. So FOR UPDATE does not result in any locked rows in the departments table. If you simple state FOR UPDATE in a query (e.g. Case 4) and do not include any columns after the OF keyword, then database will lock all the identified row across all tables listed in the FROM clause.
Case 4:
--Working With Cursors In Oracle : Example: FOR UPDATE Clause
Cursor emp_dept_cur
Is
Select a.emloyee_id,
a.last_name,
b.department_name,
a.salary
from employees a,
departments b
where a.department_id = b.department_id
for update;
NOWAIT:
You can append the optional NO WAIT key word to the FOR UPDATE clause to tell oracle not to wait if the table has been locked by another user. In this case control will be returned immediately to your program so that you can perform other work or simply wait for a period of time before you trying again. Without NOWAIT clause, your process will be blocked until the table is available. There is no limit to the wait time unless the table is remote. For remote objects, oracle initialization parameter DISTRIBUTED_LOCK_TIMEOUT is used to set the limit.
RELEASING THE LOCK WITH A COMMIT OR ROLLBACK:
--Working With Cursors In Oracle : Example: Oracle accepts parameter for explicit cursors
declare
cursor c1
is
select * from employees
for update;
begin
for i in c1
loop
if i.employee_id = 100 then
update test_employees
set salary = salary + 100
where employee_id = i.employee_id;
commit;
end if;
end loop;
exception
when others then
dbms_output.put_line('Error - '||sqlerrm);
end;
/
Error - ORA-01002: fetch out of sequence
PL/SQL procedure successfully completed.
In the above example, as soon as a cursor with FOR UPDATE clause is opened all the rows identified in the result set of the cursor are locked until your session issues either a COMMIT/ROLLBACK statement to save/cancel the changes respectively. When either of these actions occurs, the locks on the rows are released. As a result, you cannot execute another FETCH against the FOR UPDATE cursor after you COMMIT/ROLLBACK. The program raises the following exception Error – ORA-01002: fetch out of sequence.
--Working With Cursors In Oracle : Example: Oracle accepts parameter for explicit cursors
declare
cursor c1
is
select * from test_employees
for update;
begin
for i in c1
loop
if i.employee_id = 100 then
update test_employees
set salary = salary + 100
where employee_id = i.employee_id;
commit;
exit;
end if;
end loop;
exception
when others then
dbms_output.put_line('Error - '||sqlerrm);
end;
/
PL/SQL procedure successfully completed.
WHERE CURRENT OF:
Plsql provides the WHERE CURRENT OF clause for both UPDATE and DELETE statement inside a cursor to allow you to make changes to the most recently fetched rows of data.
--Working With Cursors In Oracle : Example: WHERE CURRENT OF
declare
cursor c1
is
select * from test_employees
for update;
begin
for i in c1
loop
if i.employee_id = 100 then
update test_employees
set salary = salary + 100
where current of c1;
commit;
exit;
end if;
end loop;
exception
when others then
dbms_output.put_line('Error - '||sqlerrm);
end;
/
PL/SQL procedure successfully completed.
The most important advantage to using WHERE CURRENT OF where you need to change the row fetched last is that you don’t have to code in two or more places the criteria used to uniquely identify a row in a table. Without use of WHERE CURRENT OF, you would need to repeat the WHERE clause of your cursor in the WHERE clause of the associated UPDATE and DELETEs. As a result if the table structure changes in a way that affects the construction of the primary key, you have to make sure that each SQL statement is upgraded to support this change. On the other hand if you use WHERE CURRENT OF then you have to change the WHERE clause of the select statement.
CURSOR EXAMPLES:
create table test_emp as select employee_id, last_name, job_id, salary, department_id from employees where 1 = 2;
--Working With Cursors In Oracle : Example: CURSORS
declare
cursor emp_cur
is
select employee_id,
last_name,
job_id,
salary,
department_id
from employees
where department_id = 20;
v_emp_cur emp_cur%rowtype;
v_cnt number := 0;
begin
execute immediate 'truncate table test_emp';
open emp_cur;
v_cnt := 0;
loop
fetch emp_cur into v_emp_cur;
exit when emp_cur%notfound;
insert into test_emp(employee_id, last_name, job_id, salary, department_id)
values(v_emp_cur.employee_id, v_emp_cur.last_name, v_emp_cur.job_id, v_emp_cur.salary, v_emp_cur.department_id);
v_cnt := v_cnt + sql%rowcount;
end loop;
dbms_output.put_line('Records inserted through normal loop -> '||v_cnt);
close emp_cur;
execute immediate 'truncate table test_emp';
v_cnt := 0;
for i in emp_cur
loop
insert into test_emp(employee_id, last_name, job_id, salary, department_id)
values(i.employee_id, i.last_name, i.job_id, i.salary, i.department_id);
v_cnt := v_cnt + sql%rowcount;
end loop;
dbms_output.put_line('Records inserted through cursor for loop 1 -> '||v_cnt);
execute immediate 'truncate table test_emp';
v_cnt := 0;
for j in (select employee_id,
last_name,
job_id,
salary,
department_id
from employees
where department_id = 20)
loop
insert into test_emp(employee_id, last_name, job_id, salary, department_id)
values(j.employee_id, j.last_name, j.job_id, j.salary, j.department_id);
v_cnt := v_cnt + sql%rowcount;
end loop;
dbms_output.put_line('Records inserted through cursor for loop 2 -> '||v_cnt);
exception
when others then
dbms_output.put_line('Error occurred - >'||sqlerrm);
end;
/
Output:
Records inserted through normal loop -> 2
Records inserted through cursor for loop 1 -> 2
Records inserted through cursor for loop 2 -> 2
PL/SQL procedure successfully completed.
CURSOR VARIABLE:
A cursor variable is like an explicit cursor that is not limited to one query.
To create a cursor variable, either declare a variable of the predefined type SYS_REFCURSOR or REF CURSOR type and then declare a variable of that type.
REF CURSOR is of two types:
- WEAK REF CURSOR
- STRONG REF CURSOR
Weak Ref cursor does not have a return type. SYS_REFCURSOR is a weak type of ref cursor. Strong Ref Cursor has a return type.
FEATURES:
- It’s not limited to one query.
- You can assign a value to it.
- You can use it in an expression.
- It can be a subprogram parameter.
- It can be a host variable.
- Strong Cursor cannot accept parameters.
RESTRICTION ON CURSOR VARIABLE:
- You cannot use a cursor variable inside a cursor for loop.
- You cannot declare a cursor variable in a package specification. A package cannot have public cursor variable.
- You cannot store the value of a cursor variable in a collection or database column.
- You cannot use comparison operator to test cursor variable for equality, inequality or nullity.
- Using a cursor variable in a server to server remote procedure call causes an error.
EXAMPLE OF ALL TYPES OF CURSORS:
WEAK REF CURSOR:
--Working With Cursors In Oracle : Example: WEAK REF CURSORS
declare
type t_cur_rec is record(employee_id number,
last_name varchar2(30),
job_id varchar2(30),
salary number,
department_id number);
v_cur_rec t_cur_rec;
type t_cur is ref cursor;
v_cur t_cur;
begin
open v_cur
for
select employee_id,
last_name,
job_id,
salary,
department_id
from employees
where department_id = 20;
loop
fetch v_cur into v_cur_rec;
exit when v_cur%notfound;
dbms_output.put_line('Employee_id => '||v_cur_rec.employee_id||' Name => '||v_cur_rec.last_name);
end loop;
close v_cur;
exception
when others then
dbms_output.put_line('Error - >'||sqlerrm);
end;
/
Output:
Employee_id => 201 Name => Hartstein
Employee_id => 202 Name => Fay
PL/SQL procedure successfully completed.
SYS_REFCURSOR:
--Working With Cursors In Oracle : Example: SYS_REFCURSOR
declare
type t_cur_rec is record(employee_id number,
last_name varchar2(30),
job_id varchar2(30),
salary number,
department_id number);
v_cur_rec t_cur_rec;
v_cur sys_refcursor;
begin
open v_cur
for
select employee_id,
last_name,
job_id,
salary,
department_id
from employees
where department_id = 20;
loop
fetch v_cur into v_cur_rec;
exit when v_cur%notfound;
dbms_output.put_line('Employee_id => '||v_cur_rec.employee_id||' Name => '||v_cur_rec.last_name);
end loop;
close v_cur;
exception
when others then
dbms_output.put_line('Error - >'||sqlerrm);
end;
/
Output:
Employee_id => 201 Name => Hartstein
Employee_id => 202 Name => Fay
PL/SQL procedure successfully completed.
STRONG REF CURSOR:
--Working With Cursors In Oracle : Example: STRONG REF CURSORS
declare
type t_cur_rec is record(employee_id number,
last_name varchar2(30),
job_id varchar2(30),
salary number,
department_id number);
v_cur_rec t_cur_rec;
type t_cur is ref cursor return test_emp%rowtype;
v_cur t_cur;
v_cnt number := 0;
begin
execute immediate 'truncate table test_emp';
v_cnt := 0;
open v_cur
for
select employee_id,
last_name,
job_id,
salary,
department_id
from employees
where department_id = 20;
loop
fetch v_cur into v_cur_rec;
exit when v_cur%notfound;
insert into test_emp(employee_id, last_name, job_id, salary, department_id)
values(v_cur_rec.employee_id, v_cur_rec.last_name, v_cur_rec.job_id, v_cur_rec.salary, v_cur_rec.department_id);
v_cnt := v_cnt + sql%rowcount;
end loop;
dbms_output.put_line('Records inserted through cursor variable loop -> '||v_cnt);
close v_cur;
exception
when others then
dbms_output.put_line('Error - >'||sqlerrm);
end;
/
Output:
Records inserted through cursor variable loop -> 2
PL/SQL procedure successfully completed.
NOT LIMITED TO ONE QUERY:
--Working With Cursors In Oracle : Example: CURSOR NOT LIMITED TO ONE QUERY
declare
type t_cur_rec is record (employee_id number,
last_name varchar2(30),
job_id varchar2(30),
salary number,
department_id number);
v_cur_rec t_cur_rec;
type t_cur is ref cursor;
v_cur t_cur;
t_emp_rec employees%rowtype;
v_cnt number := 0;
begin
execute immediate 'truncate table test_emp';
v_cnt := 0;
open v_cur
for
select employee_id,
last_name,
job_id,
salary,
department_id
from employees
where department_id = 20;
loop
fetch v_cur into v_cur_rec;
exit when v_cur%notfound;
insert into test_emp(employee_id, last_name, job_id, salary, department_id)
values(v_cur_rec.employee_id, v_cur_rec.last_name, v_cur_rec.job_id, v_cur_rec.salary, v_cur_rec.department_id);
v_cnt := v_cnt + sql%rowcount;
end loop;
dbms_output.put_line('Records inserted through cursor variable loop -> '||v_cnt);
close v_cur;
open v_cur
for
select * from employees
where department_id = 30;
loop
fetch v_cur into t_emp_rec;
exit when v_cur%notfound;
dbms_output.put_line('Employee_id => '||t_emp_rec.employee_id||' Name => '||t_emp_rec.last_name||' Salary => '||t_emp_rec.salary);
end loop;
close v_cur;
exception
when others then
dbms_output.put_line('Error - >'||sqlerrm);
end;
/
Output:
Records inserted through cursor variable loop -> 2
Employee_id => 114 Name => Raphaely Salary => 11000
Employee_id => 115 Name => Khoo Salary => 3100
Employee_id => 116 Name => Baida Salary => 2900
Employee_id => 117 Name => Tobias Salary => 2800
Employee_id => 118 Name => Himuro Salary => 2600
Employee_id => 119 Name => Colmenares Salary => 2500
ASSIGN A VALUE TO IT:
--Working With Cursors In Oracle : Example: ASSIGN A VALUE TO CURSOR VARIABLE
declare
type t_cur_rec is record(employee_id number,
last_name varchar2(30),
job_id varchar2(30),
salary number,
department_id number);
v_cur_rec t_cur_rec;
type t_cur is ref cursor;
v_cur t_cur;
s_cur t_cur;
t_emp_rec employees%rowtype;
v_cnt number := 0;
begin
execute immediate 'truncate table test_emp';
v_cnt := 0;
open v_cur
for
select employee_id,
last_name,
job_id,
salary,
department_id
from employees
where department_id = 20;
s_cur := v_cur;
loop
fetch s_cur into v_cur_rec;
exit when s_cur%notfound;
insert into test_emp(employee_id, last_name, job_id, salary, department_id)
values(v_cur_rec.employee_id, v_cur_rec.last_name, v_cur_rec.job_id, v_cur_rec.salary, v_cur_rec.department_id);
v_cnt := v_cnt + sql%rowcount;
end loop;
dbms_output.put_line('Records inserted through cursor variable loop -> '||v_cnt);
close v_cur;
open v_cur
for
select * from employees
where department_id = 30;
s_cur := v_cur;
loop
fetch s_cur into t_emp_rec;
exit when s_cur%notfound;
dbms_output.put_line('Employee_id => '||t_emp_rec.employee_id||' Name => '||t_emp_rec.last_name||' Salary => '||t_emp_rec.salary);
end loop;
close v_cur;
exception
when others then
dbms_output.put_line('Error - >'||sqlerrm);
end;
/
Output:
Records inserted through cursor variable loop -> 2
Employee_id => 114 Name => Raphaely Salary => 11000
Employee_id => 115 Name => Khoo Salary => 3100
Employee_id => 116 Name => Baida Salary => 2900
Employee_id => 117 Name => Tobias Salary => 2800
Employee_id => 118 Name => Himuro Salary => 2600
Employee_id => 119 Name => Colmenares Salary => 2500
CURSOR VARIABLE IN AN EXPRESSION:
A cursor expression returns a nested cursor. Each row in the result set can contain values, as usual, and cursor produced by subqueries involving other values in the row. A single query can return a large set of related values retrieve from multiple tables. You can process the result set with nested loops.
Plsql supports queries with cursor expressions as a part of cursor declarations, ref cursor declaration and ref cursor variables. You can also use cursor expression in dynamic sql statement.
A nested cursor is implicitly opened when the containing row is fetched from the parent cursor. The nested cursor is closed only when:
- The nested cursor is explicitly closed by the user.
- The parent cursor is re-executed.
- The parent cursor is closed.
- Parent cursor is cancelled.
EXAMPLE : Working With Cursors In Oracle : Example: CURSOR VARIABLE IN AN EXPRESSION:
--Working With Cursors In Oracle : Example: CURSOR VARIABLE IN AN EXPRESSION
declare
type t_cur_record is record(employee_id number,
last_name varchar2(30),
job_id varchar2(30),
salary number,
department_id number);
v_cur_rec t_cur_record;
type t_cur is ref cursor;
v_cur t_cur;
v_department_name departments.department_name%type;
v_cnt number := 0;
cursor nest_cur
is
select d.department_name,
cursor(select e.employee_id,
e.last_name,
e.job_id,
e.salary,
e.department_id
from employees e
where e.department_id = d.department_id) emp_cur
from departments d
where d.department_id = 30;
begin
execute immediate 'truncate table test_emp';
open nest_cur;
v_cnt := 0;
loop
fetch nest_cur into v_department_name, v_cur;
exit when nest_cur%notfound;
dbms_output.put_line('Department_name = '||v_department_name);
loop
fetch v_cur into v_cur_rec;
exit when v_cur%notfound;
insert into test_emp(employee_id, last_name, job_id, salary, department_id)
values(v_cur_rec.employee_id, v_cur_rec.last_name, v_cur_rec.job_id, v_cur_rec.salary, v_cur_rec.department_id);
v_cnt := v_cnt + sql%rowcount;
end loop;
close v_cur;
dbms_output.put_line('Records inserted through cursor variable loop -> '||v_cnt);
end loop;
close nest_cur;
exception
when others then
dbms_output.put_line('Error - >'||sqlerrm);
end;
/
Output:
Department_name = Purchasing
Records inserted through cursor variable loop -> 6
PL/SQL procedure successfully completed.
CURSOR Variable As a Parameter To a Subprogram:
Passing Cursor variable as a Parameter to a Procedure:
--Working With Cursors In Oracle : Example: Passing Cursor Variable as a Parameter to a Procedure
declare
type t_cur is ref cursor;
v_cur t_cur;
v_cnt number := 0;
procedure sp_test_cur(p_cur sys_refcursor)
is
type t_cur_record is record(employee_id number,
last_name varchar2(30),
job_id varchar2(30),
salary number,
department_id number);
v_cur_rec t_cur_record;
begin
loop
fetch p_cur into v_cur_rec;
exit when p_cur%notfound;
dbms_output.put_line('Employee_id = '||v_cur_rec.employee_id||' Name = '||v_cur_rec.last_name||' Job = '||v_cur_rec.job_id||' Salary = '||v_cur_rec.salary||' Departmentid = '||v_cur_rec.department_id);
end loop;
close p_cur;
exception
when others then
dbms_output.put_line('Error - '||sqlerrm);
end;
begin
open v_cur
for
select e.employee_id,
e.last_name,
e.job_id,
e.salary,
e.department_id
from employees e
where e.department_id = 30;
sp_test_cur(v_cur);
exception
when others then
dbms_output.put_line('Error - >'||sqlerrm);
end;
/
Employee_id = 114 Name = Raphaely Job = PU_MAN Salary = 11000 Departmentid = 30
Employee_id = 115 Name = Khoo Job = PU_CLERK Salary = 3100 Departmentid = 30
Employee_id = 116 Name = Baida Job = PU_CLERK Salary = 2900 Departmentid = 30
Employee_id = 117 Name = Tobias Job = PU_CLERK Salary = 2800 Departmentid = 30
Employee_id = 118 Name = Himuro Job = PU_CLERK Salary = 2600 Departmentid = 30
Employee_id = 119 Name = Colmenares Job = PU_CLERK Salary = 2500 Departmentid = 30
Passing Cursor variable as a Parameter to a Procedure & a Function:
--Working With Cursors In Oracle : Example: Passing Cursor Variable as a Parameter to a Procedure/Function
declare
type t_cur is ref cursor;
v_cur t_cur;
v_cnt number := 0;
procedure sp_test_cur(p_cur sys_refcursor)
is
type t_cur_record is record(employee_id number,
last_name varchar2(30),
job_id varchar2(30),
salary number,
department_id number);
v_cur_rec t_cur_record;
begin
execute immediate 'truncate table test_emp';
loop
fetch p_cur into v_cur_rec;
exit when p_cur%notfound;
insert into test_emp(employee_id, last_name, job_id, salary, department_id)
values(v_cur_rec.employee_id, v_cur_rec.last_name, v_cur_rec.job_id, v_cur_rec.salary, v_cur_rec.department_id);
end loop;
exception
when others then
dbms_output.put_line('Error - '||sqlerrm);
end;
function fn_test_cur(p_cur sys_refcursor)
return number
is
v_cur_rec test_emp%rowtype;
begin
loop
fetch p_cur into v_cur_rec;
exit when p_cur%notfound;
dbms_output.put_line('Employee_id = '||v_cur_rec.employee_id||' Name = '||v_cur_rec.last_name||' Job = '||v_cur_rec.job_id||' Salary = '||v_cur_rec.salary||' Departmentid = '||v_cur_rec.department_id);
end loop;
return 0;
exception
when others then
dbms_output.put_line('Error - '||sqlerrm);
return 1;
end;
begin
open v_cur
for
select e.employee_id,
e.last_name,
e.job_id,
e.salary,
e.department_id
from employees e
where e.department_id = 30;
sp_test_cur(v_cur);
close v_cur;
open v_cur
for
select e.employee_id,
e.last_name,
e.job_id,
e.salary,
e.department_id
from test_emp e;
v_cnt := fn_test_cur(v_cur);
close v_cur;
exception
when others then
dbms_output.put_line('Error - >'||sqlerrm);
end;
/
Output:
Employee_id = 114 Name = Raphaely Job = PU_MAN Salary = 11000 Departmentid = 30
Employee_id = 115 Name = Khoo Job = PU_CLERK Salary = 3100 Departmentid = 30
Employee_id = 116 Name = Baida Job = PU_CLERK Salary = 2900 Departmentid = 30
Employee_id = 117 Name = Tobias Job = PU_CLERK Salary = 2800 Departmentid = 30
Employee_id = 118 Name = Himuro Job = PU_CLERK Salary = 2600 Departmentid = 30
Employee_id = 119 Name = Colmenares Job = PU_CLERK Salary = 2500 Departmentid = 30
RELATED TOPICS:
