Mega Code Archive
Package level cursor variable
<![CDATA[
SQL>
SQL> CREATE TABLE employee (
2 employee_id NUMBER(38,0)
3 ,deptno NUMBER(3,0) NOT NULL
4 ,first_name VARCHAR2(95) NOT NULL
5 ,last_name VARCHAR2(95) NOT NULL
6 ,salary NUMBER(11,2)
7 );
Table created.
SQL>
SQL> CREATE OR REPLACE PACKAGE onecur
2 IS
3 CURSOR onerow (employee_id_in IN employee.employee_id%TYPE) IS
4 SELECT * FROM employee WHERE employee_id = employee_id_in;
5
6 PROCEDURE open_onerow(employee_id_in IN employee.employee_id%TYPE,close_if_open IN BOOLEAN := TRUE);
7 PROCEDURE close_onerow;
8 END onecur;
9 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY onecur IS
2 PROCEDURE open_onerow (employee_id_in IN employee.employee_id%TYPE,close_if_open IN BOOLEAN := TRUE)
3 IS
4 v_close BOOLEAN := NVL (close_if_open, TRUE);
5 v_open BOOLEAN := TRUE;
6 BEGIN
7 IF onerow%ISOPEN AND v_close
8 THEN
9 CLOSE onerow;
10 ELSIF onerow%ISOPEN AND NOT v_close
11 THEN
12 v_open := FALSE;
13 END IF;
14 IF v_open THEN
15 OPEN onerow (employee_id_in);
16 END IF;
17 END;
18
19 PROCEDURE close_onerow IS
20 BEGIN
21 IF onerow%ISOPEN
22 THEN
23 CLOSE onerow;
24 END IF;
25 END;
26 END onecur;
27 /
Package body created.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
]]>