Showing posts with label Cursors. Show all posts
Showing posts with label Cursors. Show all posts

(8) Cursors and Static SQL


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, and MERGE
    • COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION
    • LOCK 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_CURSOR lists 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
  • %ROWCOUNT attribute: 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?)

  • Always returns FALSE. (Since implicit cursors always close after the statement runs.)
SQL%FOUND and SQL%NOTFOUND
(Were any rows affected?)
  • NULL: If no SELECT OR DML was executed.
  • TRUE: If SELECT or DML returned/affected +0 rows.
  • FALSE: If no 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
  • If SELECT INTO returns no rows: predefined exception NO_DATA_FOUND is raised immediately (before SQL%NOTFOUND can be checked).
  • SELECT INTO that invokes a SQL aggregate function (i.e. avg, sum, count, etc) always returns a value (possibly NULL). Here SQL%NOTFOUND is always FALSE.
SQL%ROWCOUNT
(how many rows affected?)
  • NULL: If no SELECT OR DML was executed.
  • N: N is the number of rows returned (select) or affected (dml).
  • FALSE: If no 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
  • Is like an associative array whose ith element is the number of rows affected by the ith DML statement in the most recently completed FORALL statement..
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
  • SQL%BULK_EXCEPTIONS is like an associative array of information about the DML statements that failed during the most recently run FORALL statement.
  • To allow a FORALL statement to continue even if some of its DML statements fail, include the SAVE EXCEPTIONS clause.
  • After the FORALL statement completes, PL/SQL raises a single exception for the FORALL statement (ORA-24381). In the exception handler for ORA-24381, you can get information about each individual DML statement failure from the implicit cursor attribute SQL%BULK_EXCEPTIONS.

  • SQL%BULK_EXCEPTIONS.COUNT:# of DML stmts that failed.
  • SQL%BULK_EXCEPTIONS(i).ERROR_INDEX: # of the ith failed DML stmt.
  • SQL%BULK_EXCEPTIONS(i).ERROR_CODE: error code for the ith failure.
  • SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE)): returns the associated error message
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:
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?)



  • Useful for: Checking if an explicit cursor is already open before trying to open it.
  • If you try to open an explicit cursor that is already open, PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN. You must close an explicit cursor before you can reopen it.
  • Checking that an explicit cursor is open before you try to close it.
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?)
  • NULL: after the explicit cursor is opened but before the first fetch
  • TRUE: if the most recent fetch from the explicit cursor returned a row
  • FALSE: otherwise
  • %FOUND use to check if there is a fetched row to process.
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?)

  • Zero after the explicit cursor is opened but before the first fetch
  • Otherwise, the number of rows fetched
Cursor variables

Cursor variable is a pointer: its value is the address of an item, not the item itself. - Similar to an an explicit cursor, except that:
  • 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_REFCURSOR or define a REF CURSOR type 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
(1) Package specification: Define public (a) cursor variable; (b) procedures to open and manipulate cursor. (2) Package body: Procedures that open the cursor for read and write (IN OUT). And list cursor contents (IN). (3) Anonymous block: declares cursor variable of the same type defined in package specification. (4) Anonymous block: Calls procedures to open and iteract through the cursor.
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