What?
- PL/SQL implicitly declares a cursor for all SQL data manipulation statements
- Feature of PL/SQL that allows SQL syntax directly in a PL/SQL statement
- SQL Statement here is defined during compile time.
- PL/SQL static SQL statements:
SELECT, INSERT, UPDATE, DELETE,andMERGECOMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTIONLOCK TABLE
- A PL/SQL static SQL statement can have a PL/SQL identifier wherever its SQL counterpart can have a placeholder for a bind argument.
- PseudoColumns: behave like a table column, but it is not stored in the table
- CURRVAL and NEXTVAL
- LEVEL
- OBJECT_VALUE
- ROWID and ROWNUM
Cursors
- Pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
- Session cursors live in session memory until the session ends
- A Session cursor can be implicit or explicit.
V$OPEN_CURSORlists session cursors that each user session has opened and parsed- A PL/SQL cursor variable lets you retrieve multiple rows from a stored subprogram.
- You can pass cursor variables as parameters in your 3GL application.
- With cursors, an SQL stmt can be reexecuted without including the parse stage.
- Repeated execution of the same SQL statements: can begin at the describe, define, bind, or run step, saving the repeated cost of opening cursors and parsing.
| Check the performance characteristics of a cursor | |
|---|---|
V$SQL_PLAN | Execution plan information for each child cursor loaded in the library cache |
V$SQL_PLAN_STATISTICS | Execution plan information for each child cursor loaded in the library cache |
V$SQL_PLAN_STATISTICS_ALL | Memory usage statistics for row sources that use SQL memory (sort or hash-join). This view concatenates information in V$SQL_PLAN with execution statistics from V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA |
How many cursors can a session maintain?
- Depends on: The amount of memory available to the session
- Depends on: The value of the initialization parameter
OPEN_CURSORS
 | Implicit cursors |
- Also called SQL Cursor
- PL/SQL opens an implicit cursor every time you run a SELECT or DML statement
%ROWCOUNTattribute: stores the number of rows affected by a DML.
- You cannot control an implicit cursor, but you can get information from its attributes
- SQLattribute always refers to the most recently run SELECT or DML statement. If no such statement has run, the value of SQLattribute is NULL.
- Cursor attribute values remain available until another SELECT or DML statement runs.
| Implicit Cursor Attributes | |
|---|---|
| SQL%ISOPEN (Is the Cursor Open?) |
|
| SQL%FOUND and SQL%NOTFOUND (Were any rows affected?) |
create table dept_temp as select * from departments;
create or replace procedure p ( dept_no number)
authid definer as
begin
delete from dept_temp
where department_id = dept_no;
If sql%found then
dbms_output.put_line('delete succeeded. dept_no: '
|| dept_no);
else
dbms_output.put_line('No Dept_no: '|| dept_no);
end if;
end;
/
SQL> set serveroutput on
SQL> exec p(400);
anonymous block completed
No Dept_no: 400
|
| SQL%ROWCOUNT (how many rows affected?) |
SQL> set serveroutput on
SQL> declare
mgr_no number(6) := 122;
begin
delete from emp_temp where manager_id = mgr_no;
dbms_output.put_line
('number of employees deleted: ' || to_char(sql%rowcount));
end;
/
anonymous block completed
number of employees deleted: 8
set serveroutput on
declare
n number;
begin
dbms_output.put_line('Select.. ---------');
select count(*) into n from employees;
dbms_output.put_line('SQL%ROWCOUNT: [' || SQL%ROWCOUNT
||'] rows returned');
dbms_output.put_line('Update.. ---------');
update employees
set salary = salary*1.10
where job_id = 'SA_REP';
dbms_output.put_line('SQL%ROWCOUNT: [' || SQL%ROWCOUNT
||'] rows updated');
end;
anonymous block completed
Select.. ---------
SQL%ROWCOUNT: [1] rows returned
Update.. ---------
SQL%ROWCOUNT: [30] rows updated
|
| SQL%BULK_ROWCOUNT |
set serveroutput on
declare
type NumList is table of number;
depts numlist := NumList(30, 50, 60);
begin
forall j in depts.first..depts.last
delete from emp_temp where department_id = depts(j);
for i in depts.first..depts.last loop
dbms_output.put_line(
'Statement #' || i || ' deleted ' ||
sql%bulk_rowcount(i) || 'rows.');
end loop;
dbms_output.put_line('Total rows deleted: '
|| sql%rowcount);
end;
/
anonymous block completed
Statement #1 deleted 0rows.
Statement #2 deleted 45rows.
Statement #3 deleted 5rows.
Total rows deleted: 50
|
| SQL%BULK_EXCEPTIONS |
create or replace procedure p
authid definer as
type deplist is table of number;
depts deplist := deplist(40, 90, 100);
type emptab is table of employees.employee_id%type;
type deptab is table of employees.department_id%type;
enums emptab;
dnums deptab;
errm varchar2(100);
bad_stmt_no pls_integer;
bad_deptno emp_temp.department_id%type;
bad_empno emp_temp.employee_id%type;
bad_email emp_temp.email%type;
dml_errors exception;
pragma exception_init(dml_errors, -24381);
begin
select employee_id, department_id
bulk collect into enums, dnums
from emp_temp
where department_id in (depts(1), depts(2), depts(3));
forall j in enums.first..enums.last save exceptions
update emp_temp
set email = email || '@dep' || dnums(j)
|| '.company.com'
where employee_id = enums(j);
exception
when dml_errors then
for i in 1..sql%bulk_exceptions.count loop
errm := sqlerrm(-(sql%bulk_exceptions(i).error_code));
dbms_output.put_line(error_message);
bad_stmt_no := sql%bulk_exceptions(i).error_index;
dbms_output.put_line('Bad stmt #: '|| bad_stmt_no);
bad_empno := enums(bad_stmt_no);
dbms_output.put_line('Bad empno #: '|| bad_empno);
bad_deptno := dnums(bad_stmt_no);
select email into bad_email
from emp_temp
where employee_id = bad_empno;
dbms_output.put_line('Bad email #' || i
|| ' : '|| bad_email);
end loop;
-- commit;
when others then
dbms_output.put_line('other error');
raise;
end;
set serveroutput on;
exec p;
anonymous block completed
ORA-12899: value too large for column (actual: , maximum: )
Bad stmt #: 2
Bad empno #: 101
Bad email #1 : NKOCHHAR
(...)
ORA-12899: value too large for column (actual: , maximum: )
Bad stmt #: 5
Bad empno #: 109
Bad email #3 : DFAVIET
(...)
|
Using SELECT INTO: Implicit cursor
- May return a single row - (here select directly into a scalar or record)
- you may also use WHERE ROWNUM = n to select a specific row.
- Or return multiple rows: here you need to use Select xxx BULK COLLECT INTO collection;
Returning a single row:
DECLARE
bonus NUMBER(8,2);
BEGIN
SELECT salary * 0.10 INTO bonus
FROM employees
WHERE employee_id = 100;
END;
DBMS_OUTPUT.PUT_LINE('bonus = ' || TO_CHAR(bonus));
returning into a record SELECT select_list INTO record_variable_name FROM table_or_view_name;
DECLARE
TYPE RecordTyp IS RECORD (
last employees.last_name%TYPE
id employees.employee_id%TYPE
);
rec1 RecordTyp;
BEGIN
SELECT last_name, employee_id INTO rec1
FROM employees
WHERE job_id = 'AD_PRES';
DBMS_OUTPUT.PUT_LINE ('Employee #' || rec1.id || ' = ' || rec1.last);
END;
/
Returning multiple rows: BULK COLLECT clause DECLARE
TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;
enums NumTab;
names NameTab;
PROCEDURE print_first_n (n POSITIVE) IS
BEGIN
IF enums.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE ('Collections are empty.');
ELSE
DBMS_OUTPUT.PUT_LINE ('First ' || n || ' employees:');
FOR i IN 1 .. n LOOP
DBMS_OUTPUT.PUT_LINE (
' Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END IF;
END;
BEGIN
SELECT employee_id, last_name
BULK COLLECT INTO enums, names
FROM employees
ORDER BY employee_id;
print_first_n(3);
print_first_n(6);
END;
/
Using RETURNING INTO clause on a DML
SQL> create table emp as
select employee_id, last_name, salary
from employees;
set serveroutput on
declare
type emprec is record
(empid emp.employee_id%type,
lname emp.last_name%type,
sal emp.salary%type);
vemprec emprec;
begin
delete from emp
where employee_id = 188
returning employee_id, last_name, salary
INTO vemprec;
dbms_output.put_line('the record deleted was: ');
dbms_output.put_line(vemprec.lname || 'id: '|| vemprec.empid
||' sal: '|| vemprec.sal);
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
anonymous block completed
the record deleted was:
Chungid: 188 sal: 3800
Cursor FOR LOOP
- Use with either implicit or explicit cursor.
- The cursor FOR LOOP statement implicitly declares its loop index as a %ROWTYPE record variable of the type that its cursor returns.
- Implicit cursor FOR LOOP stmt
- SELECT stmt inside the FOR LOOP
set serveroutput on
begin
for item in
( select last_name, job_id
from employees
where job_id like '%CLERK%'
and manager_id > 100
order by last_name)
loop
dbms_output.put_line
('Name = '|| item.last_name || ', Job = ' || item.job_id);
end loop;
end;
anonymous block completed
Name = Atkinson, Job = ST_CLERK
Name = Baida, Job = PU_CLERK
Name = Bell, Job = SH_CLERK
(...)
- explicit cursor FOR LOOP
- Move the select statement to the declaration section and explicitly delcare a cursor.
set serveroutput on
declare
cursor c1 is
select last_name, job_id
from employees
where job_id like '%CLERK%'
and manager_id > 100
order by last_name;
begin
for item in c1 loop
dbms_output.put_line
('Name = '|| item.last_name || ', Job = ' || item.job_id);
end loop;
end;
anonymous block completed
Name = Atkinson, Job = ST_CLERK
Name = Baida, Job = PU_CLERK
Name = Bell, Job = SH_CLERK
(...)
- Cursor FOR LOOP with parameters
set serveroutput on
declare
cursor c1 (job employees.job_id%type, max_wage number) is
select * from employees
where job_id = job
and salary > max_wage;
begin
for person in c1 ('ST_CLERK', 3000) loop
dbms_output.put_line(
'Name = '|| person.last_name || ', salary = ' ||
person.salary ||', Job Id = '|| person.job_id);
end loop;
end;
anonymous block completed
Name = Nayer, salary = 3200, Job Id = ST_CLERK
Name = Bissot, salary = 3300, Job Id = ST_CLERK
(...)
Cursors with subqueries
set serveroutput on
declare
cursor c1 is
select t1.department_id, department_name, staff
from departments t1,
( select department_id, count(*) as staff
from employees
group by department_id
) t2
where (t1.department_id = t2.department_id)
and staff >= 5
order by staff;
begin
for dept in c1 loop
dbms_output.put_line ('Department = '
|| dept.department_name || ', staff = ' || dept.staff);
end loop;
end;
anonymous block completed
Department = IT, staff = 5
Department = Finance, staff = 6
Department = Purchasing, staff = 6
Department = Administration, staff = 7
Department = Sales, staff = 34
Department = Shipping, staff = 45
| Explicit cursors |
- Also called a named cursor, since unlike implicit ones, an explicit cursor can be referenced by its name
- is a named pointer to a private SQL area that stores information for processing a specific query or DML statement
- Before using an explicit cursor, you must declare (cursor specification) and define it (cursor body).
- You cannot assign a value to an explicit cursor, use it in an expression, or use it as a formal subprogram parameter or host variable.
- You can do those things with a cursor variable.
Explicit cursor declaration, which only declares a cursor:
An explicit cursor definition has this syntax:
CURSOR cursor_name [ parameter_list ] RETURN return_type;An explicit cursor definition has this syntax:
CURSOR cursor_name [ parameter_list ]
[ RETURN return_type ] IS select_statement; DECLARE
CURSOR c1 RETURN departments%ROWTYPE; -- Declare c1
CURSOR c2 IS -- Declare and define c2
SELECT employee_id, job_id, salary FROM employees
WHERE salary > 2000;
CURSOR c1 RETURN departments%ROWTYPE IS -- Define c1,
SELECT * FROM departments -- repeating return type
WHERE department_id = 110;
BEGIN
NULL;
END;
OPEN, FETCH, and CLOSE
- Process multiple result sets in parallel, using multiple cursors.
- Process multiple rows in a single loop iteration, skip rows, or split the processing into multiple loops.
- Specify the query in one PL/SQL unit but retrieve the rows in another.
| Managing Explicit Cursors | |
|---|---|
| OPEN | - Allocates database resources - Process the query (identify result set and lock rows (if FOR UPDATE clause is there) - Position cursor before the first row of the result set. |
| FETCH | FETCH cursor_name INTO into_clause - retrieves the current row of the result set, stores the column values of that row into the variables or record, and advances the cursor to the next row. - PL/SQL does not raise an exception when a FETCH statement returns no rows. To detect this, use the attribute %NOTFOUND. |
| CLOSE | - After closing a cursor, you cannot fetch records from its result set or reference its attributes. If you try, PL/SQL raises the predefined exception INVALID_CURSOR. - You can reopen a closed cursor. You must close an explicit cursor before you try to reopen it. Otherwise, PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN. |
set serveroutput on
declare
cursor c1 is
select last_name, job_id from employees
where regexp_like(job_id, 'S[HT]_CLERK')
order by last_name;
v_lastname employees.last_name%type;
v_jobid employees.job_id%type;
cursor c2 is
select * from employees
where regexp_like(job_id, '[ACADFIMKSA]_M[ANGR]')
order by job_id;
v_employees employees%rowtype;
begin
open c1;
loop
fetch c1 into v_lastname, v_jobid;
exit when c1%notfound;
dbms_output.put_line(RPAD(v_lastname, 25, ' ') || v_jobid);
end loop;
close c1;
dbms_output.put_line( '-----------------------------');
open c2;
loop
fetch c2 into v_employees;
exit when c2%notfound;
dbms_output.put_line(RPAD(v_employees.last_name, 25, ' ') ||
v_employees.job_id);
end loop;
close c2;
end;
anonymous block completed
Atkinson ST_CLERK
Bell SH_CLERK
...
Walsh SH_CLERK
-----------------------------
Higgins AC_MGR
Glenn AC_MGR
...
Zlotkey SA_MAN
Explicit cursors with parameter
set serveroutput on
declare
cursor c (job varchar2, max_sal number) IS
select last_name, first_name, (salary-max_sal) overpayment
from employees
where job_id = job
and salary > max_sal
order by salary;
procedure print_overpaid is
ln_ employees.last_name%type;
fn_ employees.first_name%type;
overpayment_ employees.salary%type;
begin
loop
fetch c into ln_, fn_, overpayment_;
exit when c%notfound;
dbms_output.put_line(ln_ || ', ' || fn_ ||
' (by ' || overpayment_ || ')');
end loop;
end print_overpaid;
begin
dbms_output.put_line('---------------------');
dbms_output.put_line('Overpaid Clerks:');
dbms_output.put_line('---------------------');
Open c('ST_CLERK', 5000);
print_overpaid;
close c;
dbms_output.put_line('---------------------');
dbms_output.put_line('Overpaid Sales Rep:');
dbms_output.put_line('---------------------');
Open c('SA_REP', 10000);
print_overpaid;
close c;
end;
| Explicit Cursor Attributes Syntax: cursor_name%attribute | |
|---|---|
| %ISOPEN (Is the Cursor Open?) |
declare
cursor c1 is
select last_name, salary from employees
where rownum < 11;
v1 employees.last_name%type;
v2 employees.salary%type;
begin
if not c1%isopen then
open c1;
end if;
fetch c1 into v1, v2;
if c1%isopen then
close c1;
end if;
end;
|
| %FOUND and %NOTFOUND (Has a row been fetched?) |
set serveroutput on
DECLARE
CURSOR c1 IS
SELECT last_name, salary FROM employees
WHERE ROWNUM < 11
ORDER BY last_name;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%FOUND THEN -- fetch succeeded
DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ',
salary = ' || my_salary);
ELSE -- fetch failed
DBMS_OUTPUT.PUT_LINE('---------------------');
DBMS_OUTPUT.PUT_LINE('C''est fini');
EXIT;
END IF;
END LOOP;
END;
/
|
| %ROWCOUNT (How may rows fetched?) |
|
| Cursor variables |  |
- It is 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.
- It cannot accept parameters. But you can pass whole queries to it.
- To create a cursor variable, either declare a variable of the predefined type
SYS_REFCURSORor define aREF CURSORtype and then declare a variable of that type.
Cursor variable declarations: Syntax:
TYPE type_name IS REF CURSOR [ RETURN return_type ] DECLARE DECLARE -- strong type TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; -- weak type TYPE genericcurtyp IS REF CURSOR; cursor1 empcurtyp; -- strong cursor variable cursor2 genericcurtyp; -- weak cursor variable my_cursor SYS_REFCURSOR; -- weak cursor variable BEGIN NULL; END;
Cursor Variable - Examples
 | Cursor Variable |
(1) Opens a cursor variable for a query that references the variable factor.
(2) Notice that the variable is evaluated only once: when the cursor is opened.
set serveroutput on
declare
sal employees.salary%type;
sal_multiple employees.salary%type;
factor integer := 2;
-- declare cursor variable predefined type
cv sys_refcursor;
begin
-- open cursor defining SQL stmt.
-- once closed later, the cursor can be opened
-- again with a different stmt
Open cv for
select salary, salary*factor
from employees
where job_id like 'AD_%';
loop
--load the next row of data into variables
-- if no rows left, exit loop
fetch cv into sal, sal_multiple;
exit when cv%notfound;
dbms_output.put_line('factor = '|| factor);
dbms_output.put_line('sal = '|| sal);
dbms_output.put_line('sal_multiple = '|| sal_multiple);
-- note that the variable factor is evaluated only one
-- when the cursor is opened. After this, new variable
-- values are not seen by the cursor unless it is
-- closed and opened again.
factor := factor + 1;
end loop;
close cv;
end;
anonymous block completed
factor = 2
sal = 4400
sal_multiple = 8800
factor = 3
sal = 24000
sal_multiple = 48000
factor = 4
sal = 17000
sal_multiple = 34000
factor = 5
sal = 17000
sal_multiple = 34000
| Passing cursor variables as parameter |
set serveroutput on
-- Package specification.
create or replace package emp_data as
-- Defines public cursor variable type (strongly enforced)
type empcurtype is ref cursor return employees%rowtype;
-- define public procedures
procedure open_emp_cv (emp_cv in out empcurtype);
procedure list_emp_cv (emp_cv in empcurtype);
procedure close_emp_cv (emp_cv in empcurtype);
end emp_data;
-- package body
create or replace package body emp_data as
-- procedure receives cursor var as parameter
-- and writes on it.
-- NOTICE: that the SQL is fixed. You can't change
-- parameters without recompiling the package body.
procedure open_emp_cv(emp_cv in out empcurtype) is
begin
open emp_cv for select *
from employees
where rownum <=5
order by hire_date;
end open_emp_cv;
-- procedure receives cursor var as parameter.
-- read only.
procedure list_emp_cv (emp_cv in empcurtype) is
v_emp employees%rowtype;
begin
-- loop through all cursor records.
-- exit loop when last row reached.
-- each FETCH increments %ROWCOUNT attribute.
loop
fetch emp_cv into v_emp;
exit when emp_cv%notfound;
dbms_output.put_line('Oldest #' || emp_cv%rowcount ||': '
|| v_emp.first_name
||' '|| v_emp.last_name ||
'. Hire date: '|| v_emp.hire_date);
end loop;
end list_emp_cv;
procedure close_emp_cv (emp_cv in empcurtype) is
begin
if emp_cv%isopen then
dbms_output.put_line('Closing cursor');
close emp_cv;
end if;
end close_emp_cv;
end emp_data;
-- anonymous block
set serveroutput on
declare
-- declare cursor variable of the same type
-- specified in the package spec.
v_empcv emp_data.empcurtype;
begin
emp_data.open_emp_cv(v_empcv);
emp_data.list_emp_cv(v_empcv);
emp_data.close_emp_cv(v_empcv);
end;
anonymous block completed
Oldest #1: Steven King. Hire date: 17-JUN-87
Oldest #2: Neena Kochhar. Hire date: 21-SEP-89
Oldest #3: Alexander Hunold. Hire date: 03-JAN-90
Oldest #4: Bruce Ernst. Hire date: 21-MAY-91
Oldest #5: Lex De Haan. Hire date: 13-JAN-93
Closing cursor
| Using Cursor expression |
set serveroutput on
declare
type emp_cur_type is ref cursor;
emp_cur emp_cur_type;
dept_name departments.department_name%type;
emp_name employees.last_name%type;
cursor c1 is
select department_name,
cursor ( select e.last_name
from employees e
where e.department_id = d.department_id
order by e.last_name
) employees
from departments d
where department_name like 'A%'
order by department_name;
begin
open c1;
loop
fetch c1 into dept_name, emp_cur;
exit when c1%notfound;
dbms_output.put_line('Dept: ' || dept_name);
loop
fetch emp_cur into emp_name;
exit when emp_cur%notfound;
dbms_output.put_line('-- Employee: '|| emp_name);
end loop;
end loop;
close c1;
end;
anonymous block completed
Dept: Accounting
-- Employee: Gietz
-- Employee: Higgins
Dept: Administration
-- Employee: Fansom
-- Employee: Fay
-- Employee: Fergunson
-- Employee: Glenn
-- Employee: Hartstein
-- Employee: Mavris
-- Employee: Whalen
 | Using SELECT FOR UPDATE and FOR UPDATE Cursors |
