PL/SQL Static SQL
Static SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement.
7.1 Description of Static SQL
7.1.1 Statements
SELECT
(this statement is also called a query)Data manipulation language (DML) statements:
INSERT
UPDATE
DELETE
Transaction control language (TCL) statements:
COMMIT
ROLLBACK
SAVEPOINT
SET
TRANSACTION
LOCK
TABLE
Example 7-1 Static SQL Statements
SQL> show user
USER is "SYS"
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=PDB1;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL>
SQL> conn hr/hr@PDB1;
Connected.
SQL> show user;
USER is "HR"
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL> set pagesize 200 linesize 200
SQL>
SQL> DROP TABLE employees_temp;
DROP TABLE employees_temp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE employees_temp AS
2 SELECT employee_id, first_name, last_name
3 FROM employees;
Table created.
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 emp_id employees_temp.employee_id%TYPE := 299;
3 emp_first_name employees_temp.first_name%TYPE := 'Bob';
4 emp_last_name employees_temp.last_name%TYPE := 'Henry';
5 BEGIN
6 INSERT INTO employees_temp (employee_id, first_name, last_name)
7 VALUES (emp_id, emp_first_name, emp_last_name);
8
9 UPDATE employees_temp
10 SET first_name = 'Robert'
11 WHERE employee_id = emp_id;
12
13 DELETE FROM employees_temp
14 WHERE employee_id = emp_id
15 RETURNING first_name, last_name
16 INTO emp_first_name, emp_last_name;
17
18 COMMIT;
19 DBMS_OUTPUT.PUT_LINE (emp_first_name || ' ' || emp_last_name);
20 END;
21 /
Robert Henry
PL/SQL procedure successfully completed.
SQL>
7.1.2 Pseudocolumns
A pseudocolumn behaves like a table column, but it is not stored in the table.
Static SQL includes these SQL pseudocolumns:
CURRVAL
andNEXTVAL
,
LEVEL
OBJECT_VALUE
7.1.2.1 CURRVAL and NEXTVAL in PL/SQL
You can use
sequence_name
.CURRVAL
andsequence_name
.NEXTVAL
in a PL/SQL expression wherever you can use aNUMBER
expression. However:
Using
sequence_name
.CURRVAL
orsequence_name
.NEXTVAL
to provide a default value for an ADT method parameter causes a compilation error.PL/SQL evaluates every occurrence of
sequence_name
.CURRVAL
andsequence_name
.NEXTVAL
(unlike SQL, which evaluates a sequence expression for every row in which it appears).
Example 7-2 CURRVAL and NEXTVAL Pseudocolumns
This example generates a sequence number for the sequence
HR.EMPLOYEES_SEQ
and refers to that number in multiple statements.
SQL>
SQL> DROP TABLE employees_temp;
Table dropped.
SQL> CREATE TABLE employees_temp AS
2 SELECT employee_id, first_name, last_name
3 FROM employees;
Table created.
SQL> DROP TABLE employees_temp2;
DROP TABLE employees_temp2
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE employees_temp2 AS
2 SELECT employee_id, first_name, last_name
3 FROM employees;
Table created.
SQL>
SQL> DECLARE
2 seq_value NUMBER;
3 BEGIN
4 -- Generate initial sequence number
5
6 seq_value := employees_seq.NEXTVAL;
7
8 -- Print initial sequence number:
9
10 DBMS_OUTPUT.PUT_LINE (
11 'Initial sequence value: ' || TO_CHAR(seq_value)
12 );
13
14 -- Use NEXTVAL to create unique number when inserting data:
15
16 INSERT INTO employees_temp (employee_id, first_name, last_name)
17 VALUES (employees_seq.NEXTVAL, 'Lynette', 'Smith');
18
19 -- Use CURRVAL to store same value somewhere else:
20
21 INSERT INTO employees_temp2 VALUES (employees_seq.CURRVAL,
22 'Morgan', 'Smith');
23
24 /* Because NEXTVAL values might be referenced
25 by different users and applications,
26 and some NEXTVAL values might not be stored in database,
27 there might be gaps in sequence. */
28
29 -- Use CURRVAL to specify record to delete:
30
31 seq_value := employees_seq.CURRVAL;
32
33 DELETE FROM employees_temp2
34 WHERE employee_id = seq_value;
35
36 -- Update employee_id with NEXTVAL for specified record:
37
38 UPDATE employees_temp
39 SET employee_id = employees_seq.NEXTVAL
40 WHERE first_name = 'Lynette'
41 AND last_name = 'Smith';
42
43 -- Display final value of CURRVAL:
44
45 seq_value := employees_seq.CURRVAL;
46
47 DBMS_OUTPUT.PUT_LINE (
48 'Ending sequence value: ' || TO_CHAR(seq_value)
49 );
50 END;
51 /
Initial sequence value: 207
Ending sequence value: 209
PL/SQL procedure successfully completed.
SQL>
7.2 Cursors Overview
7.2.1 Implicit Cursors
A cursor is a pointer to a private SQL area that stores information about processing a specific
SELECT
or DML statement.
A cursor that is constructed and managed by PL/SQL is an implicit cursor. A cursor that you construct and manage is an explicit cursor.
You can get information about any session cursor from its attributes (which you can reference in procedural statements, but not in SQL statements).
To list the session cursors that each user session currently has opened and parsed, query the dynamic performance view
V$OPEN_CURSOR
.The number of cursors that a session can have open simultaneously is determined by:
The amount of memory available to the session
The value of the initialization parameter
OPEN_CURSORS
7.2.1.1 SQL%ISOPEN Attribute: Is the Cursor Open?
SQL%ISOPEN
always returnsFALSE
, because an implicit cursor always closes after its associated statement runs.
7.2.1.2 SQL%FOUND Attribute: Were Any Rows Affected?
SQL%FOUND
returns:
NULL
if noSELECT
or DML statement has run
TRUE
if aSELECT
statement returned one or more rows or a DML statement affected one or more rows
FALSE
otherwise
Example 7-3 SQL%FOUND Implicit Cursor Attribute
SQL>
SQL> DROP TABLE dept_temp;
DROP TABLE dept_temp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE dept_temp AS
2 SELECT * FROM departments;
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE p (
2 dept_no NUMBER
3 ) AUTHID CURRENT_USER AS
4 BEGIN
5 DELETE FROM dept_temp
6 WHERE department_id = dept_no;
7
8 IF SQL%FOUND THEN
9 DBMS_OUTPUT.PUT_LINE (
10 'Delete succeeded for department number ' || dept_no
11 );
12 ELSE
13 DBMS_OUTPUT.PUT_LINE ('No department number ' || dept_no);
14 END IF;
15 END;
16 /
Procedure created.
SQL>
SQL> BEGIN
2 p(270);
3 p(400);
4 END;
5 /
Delete succeeded for department number 270
No department number 400
PL/SQL procedure successfully completed.
SQL>
7.2.1.3 SQL%NOTFOUND Attribute: Were No Rows Affected?
SQL%NOTFOUND
(the logical opposite ofSQL%FOUND
) returns:
NULL
if noSELECT
or DML statement has run
FALSE
if aSELECT
statement returned one or more rows or a DML statement affected one or more rows
TRUE
otherwiseThe
SQL%NOTFOUND
attribute is not useful with the PL/SQLSELECT
INTO
statement, because:
If the
SELECT
INTO
statement returns no rows, PL/SQL raises the predefined exceptionNO_DATA_FOUND
immediately, before you can checkSQL%NOTFOUND
.A
SELECT
INTO
statement that invokes a SQL aggregate function always returns a value (possiblyNULL
). After such a statement, theSQL%NOTFOUND
attribute is alwaysFALSE
, so checking it is unnecessary.
7.2.1.4 SQL%ROWCOUNT Attribute: How Many Rows Were Affected?
SQL%ROWCOUNT
returns:
NULL
if noSELECT
or DML statement has runOtherwise, the number of rows returned by a
SELECT
statement or affected by a DML statement (anINTEGER
)
Example 7-4 SQL%ROWCOUNT Implicit Cursor Attribute
SQL>
SQL> DROP TABLE employees_temp;
Table dropped.
SQL> CREATE TABLE employees_temp AS
2 SELECT * FROM employees;
Table created.
SQL> DECLARE
2 mgr_no NUMBER(6) := 122;
3 BEGIN
4 DELETE FROM employees_temp WHERE manager_id = mgr_no;
5 DBMS_OUTPUT.PUT_LINE
6 ('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
7 END;
8 /
Number of employees deleted: 8
PL/SQL procedure successfully completed.
SQL>
7.2.2 Explicit Cursors
An explicit cursor is a session cursor that you construct and manage. You must declare and define an explicit cursor, giving it a name and associating it with a query (typically, the query returns multiple rows). Then you can process the query result set in either of these ways:
Open the explicit cursor (with the
OPEN
statement), fetch rows from the result set (with theFETCH
statement), and close the explicit cursor (with theCLOSE
statement).Use the explicit cursor in a cursor
FOR
LOOP
statement
7.2.2.1 Declaring and Defining Explicit Cursors
You can either declare an explicit cursor first and then define it later in the same block, subprogram, or package, or declare and define it at the same time.
An explicit cursor declaration, which only declares a cursor, 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;
Example 7-5 Explicit Cursor Declaration and Definition
SQL>
SQL> DECLARE
2 CURSOR c1 RETURN departments%ROWTYPE; -- Declare c1
3
4 CURSOR c2 IS -- Declare and define c2
5 SELECT employee_id, job_id, salary FROM employees
6 WHERE salary > 2000;
7
8 CURSOR c1 RETURN departments%ROWTYPE IS -- Define c1,
9 SELECT * FROM departments -- repeating return type
10 WHERE department_id = 110;
11
12 CURSOR c3 RETURN locations%ROWTYPE; -- Declare c3
13
14 CURSOR c3 IS -- Define c3,
15 SELECT * FROM locations -- omitting return type
16 WHERE country_id = 'JP';
17 BEGIN
18 NULL;
19 END;
20 /
PL/SQL procedure successfully completed.
SQL> show user
USER is "HR"
SQL>
7.2.2.2 Opening and Closing Explicit Cursors
After declaring and defining an explicit cursor, you can open it with the
OPEN
statement, which does the following:
Allocates database resources to process the query
Processes the query; that is:
Identifies the result set
If the query has a
FOR
UPDATE
clause, locks the rows of the result setPositions the cursor before the first row of the result set
7.2.2.3 Fetching Data with Explicit Cursors
FETCH cursor_name INTO into_clause
Example 7-6 FETCH Statements Inside LOOP Statements
SQL>
SQL> show user
USER is "HR"
SQL>
SQL> DECLARE
2 CURSOR c1 IS
3 SELECT last_name, job_id FROM employees
4 WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
5 ORDER BY last_name;
6
7 v_lastname employees.last_name%TYPE; -- variable for last_name
8 v_jobid employees.job_id%TYPE; -- variable for job_id
9
10 CURSOR c2 IS
11 SELECT * FROM employees
12 WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]')
13 ORDER BY job_id;
14
15 v_employees employees%ROWTYPE; -- record variable for row of table
16
17 BEGIN
18 OPEN c1;
19 LOOP -- Fetches 2 columns into variables
20 FETCH c1 INTO v_lastname, v_jobid;
21 EXIT WHEN c1%NOTFOUND;
22 DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
23 END LOOP;
24 CLOSE c1;
25 DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
26
27 OPEN c2;
28 LOOP -- Fetches entire row into the v_employees record
29 FETCH c2 INTO v_employees;
30 EXIT WHEN c2%NOTFOUND;
31 DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||
32 v_employees.job_id );
33 END LOOP;
34 CLOSE c2;
35 END;
36 /
Atkinson ST_CLERK
Bell SH_CLERK
Bissot ST_CLERK
Bull SH_CLERK
Cabrio SH_CLERK
Chung SH_CLERK
Davies ST_CLERK
Dellinger SH_CLERK
Dilly SH_CLERK
Everett SH_CLERK
Feeney SH_CLERK
Fleaur SH_CLERK
Gates SH_CLERK
Gee ST_CLERK
Geoni SH_CLERK
Grant SH_CLERK
Jones SH_CLERK
Ladwig ST_CLERK
Landry ST_CLERK
Mallin ST_CLERK
Markle ST_CLERK
Marlow ST_CLERK
Matos ST_CLERK
McCain SH_CLERK
Mikkilineni ST_CLERK
Nayer ST_CLERK
OConnell SH_CLERK
Olson ST_CLERK
Patel ST_CLERK
Perkins SH_CLERK
Philtanker ST_CLERK
Rajs ST_CLERK
Rogers ST_CLERK
Sarchand SH_CLERK
Seo ST_CLERK
Stiles ST_CLERK
Sullivan SH_CLERK
Taylor SH_CLERK
Vargas ST_CLERK
Walsh SH_CLERK
-------------------------------------
Higgins AC_MGR
Greenberg FI_MGR
Hartstein MK_MAN
Russell SA_MAN
Partners SA_MAN
Errazuriz SA_MAN
Cambrault SA_MAN
Zlotkey SA_MAN
PL/SQL procedure successfully completed.
SQL>
Example 7-7 Fetching Same Explicit Cursor into Different Variables
SQL>
SQL> show user;
USER is "HR"
SQL>
SQL> DECLARE
2 CURSOR c IS
3 SELECT e.job_id, j.job_title
4 FROM employees e, jobs j
5 WHERE e.job_id = j.job_id AND e.manager_id = 100
6 ORDER BY last_name;
7
8 -- Record variables for rows of cursor result set:
9
10 job1 c%ROWTYPE;
11 job2 c%ROWTYPE;
12 job3 c%ROWTYPE;
13 job4 c%ROWTYPE;
14 job5 c%ROWTYPE;
15
16 BEGIN
17 OPEN c;
18 FETCH c INTO job1; -- fetches first row
19 FETCH c INTO job2; -- fetches second row
20 FETCH c INTO job3; -- fetches third row
21 FETCH c INTO job4; -- fetches fourth row
22 FETCH c INTO job5; -- fetches fifth row
23 CLOSE c;
24
25 DBMS_OUTPUT.PUT_LINE(job1.job_title || ' (' || job1.job_id || ')');
26 DBMS_OUTPUT.PUT_LINE(job2.job_title || ' (' || job2.job_id || ')');
27 DBMS_OUTPUT.PUT_LINE(job3.job_title || ' (' || job3.job_id || ')');
28 DBMS_OUTPUT.PUT_LINE(job4.job_title || ' (' || job4.job_id || ')');
29 DBMS_OUTPUT.PUT_LINE(job5.job_title || ' (' || job5.job_id || ')');
30 END;
31 /
Sales Manager (SA_MAN)
Administration Vice President (AD_VP)
Sales Manager (SA_MAN)
Stock Manager (ST_MAN)
Marketing Manager (MK_MAN)
PL/SQL procedure successfully completed.
SQL>
7.2.2.4 Variables in Explicit Cursor Queries
An explicit cursor query can reference any variable in its scope. When you open an explicit cursor, PL/SQL evaluates any variables in the query and uses those values when identifying the result set. Changing the values of the variables later does not change the result set.
Example 7-8 Variable in Explicit Cursor Query—No Result Set Change
SQL>
SQL> show user
USER is "HR"
SQL> DECLARE
2 sal employees.salary%TYPE;
3 sal_multiple employees.salary%TYPE;
4 factor INTEGER := 2;
5
6 CURSOR c1 IS
7 SELECT salary, salary*factor FROM employees
8 WHERE job_id LIKE 'AD_%';
9
10 BEGIN
11 OPEN c1; -- PL/SQL evaluates factor
12
13 LOOP
14 FETCH c1 INTO sal, sal_multiple;
15 EXIT WHEN c1%NOTFOUND;
16 DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
17 DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
18 DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
19 factor := factor + 1; -- Does not affect sal_multiple
20 END LOOP;
21
22 CLOSE c1;
23 END;
24 /
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
PL/SQL procedure successfully completed.
SQL>
Example 7-9 Variable in Explicit Cursor Query—Result Set Change
SQL> DECLARE
2 sal employees.salary%TYPE;
3 sal_multiple employees.salary%TYPE;
4 factor INTEGER := 2;
5
6 CURSOR c1 IS
7 SELECT salary, salary*factor FROM employees
8 WHERE job_id LIKE 'AD_%';
9
10 BEGIN
11 DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
12 OPEN c1; -- PL/SQL evaluates factor
13 LOOP
14 FETCH c1 INTO sal, sal_multiple;
15 EXIT WHEN c1%NOTFOUND;
16 DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
17 DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
18 END LOOP;
19 CLOSE c1;
20
21 factor := factor + 1;
22
23 DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
24 OPEN c1; -- PL/SQL evaluates factor
25 LOOP
26 FETCH c1 INTO sal, sal_multiple;
27 EXIT WHEN c1%NOTFOUND;
28 DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
29 DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
30 END LOOP;
31 CLOSE c1;
32 END;
33 /
factor = 2
sal = 4400
sal_multiple = 8800
sal = 24000
sal_multiple = 48000
sal = 17000
sal_multiple = 34000
sal = 17000
sal_multiple = 34000
factor = 3
sal = 4400
sal_multiple = 13200
sal = 24000
sal_multiple = 72000
sal = 17000
sal_multiple = 51000
sal = 17000
sal_multiple = 51000
PL/SQL procedure successfully completed.
SQL>
7.2.2.5 When Explicit Cursor Queries Need Column Aliases
When an explicit cursor query includes a virtual column (an expression), that column must have an alias if either of the following is true:
You use the cursor to fetch into a record that was declared with
%ROWTYPE
.You want to reference the virtual column in your program.
Example 7-10 Explicit Cursor with Virtual Column that Needs Alias
SQL>
SQL> DECLARE
2 CURSOR c1 IS
3 SELECT employee_id,
4 (salary * .05) raise
5 FROM employees
6 WHERE job_id LIKE '%_MAN'
7 ORDER BY employee_id;
8 emp_rec c1%ROWTYPE;
9 BEGIN
10 OPEN c1;
11 LOOP
12 FETCH c1 INTO emp_rec;
13 EXIT WHEN c1%NOTFOUND;
14 DBMS_OUTPUT.PUT_LINE (
15 'Raise for employee #' || emp_rec.employee_id ||
16 ' is $' || emp_rec.raise
17 );
18 END LOOP;
19 CLOSE c1;
20 END;
21 /
Raise for employee #114 is $550
Raise for employee #120 is $405
Raise for employee #121 is $428.75
Raise for employee #122 is $395
Raise for employee #123 is $325
Raise for employee #124 is $290
Raise for employee #145 is $700
Raise for employee #146 is $675
Raise for employee #147 is $600
Raise for employee #148 is $550
Raise for employee #149 is $525
Raise for employee #201 is $650
PL/SQL procedure successfully completed.
SQL>
7.2.2.6 Explicit Cursors that Accept Parameters
Example 7-11 Explicit Cursor that Accepts Parameters
SQL>
SQL> DECLARE
2 CURSOR c (job VARCHAR2, max_sal NUMBER) IS
3 SELECT last_name, first_name, (salary - max_sal) overpayment
4 FROM employees
5 WHERE job_id = job
6 AND salary > max_sal
7 ORDER BY salary;
8
9 PROCEDURE print_overpaid IS
10 last_name_ employees.last_name%TYPE;
11 first_name_ employees.first_name%TYPE;
12 overpayment_ employees.salary%TYPE;
13 BEGIN
14 LOOP
15 FETCH c INTO last_name_, first_name_, overpayment_;
16 EXIT WHEN c%NOTFOUND;
17 DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ ||
18 ' (by ' || overpayment_ || ')');
19 END LOOP;
20 END print_overpaid;
21
22 BEGIN
23 DBMS_OUTPUT.PUT_LINE('----------------------');
24 DBMS_OUTPUT.PUT_LINE('Overpaid Stock Clerks:');
25 DBMS_OUTPUT.PUT_LINE('----------------------');
26 OPEN c('ST_CLERK', 5000);
27 print_overpaid;
28 CLOSE c;
29
30 DBMS_OUTPUT.PUT_LINE('-------------------------------');
31 DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives:');
32 DBMS_OUTPUT.PUT_LINE('-------------------------------');
33 OPEN c('SA_REP', 10000);
34 print_overpaid;
35 CLOSE c;
36 END;
37 /
----------------------
Overpaid Stock Clerks:
----------------------
-------------------------------
Overpaid Sales Representatives:
-------------------------------
Vishney, Clara (by 500)
Abel, Ellen (by 1000)
Ozer, Lisa (by 1500)
PL/SQL procedure successfully completed.
SQL>
Example 7-12 Cursor Parameters with Default Values
SQL>
SQL> DECLARE
2 CURSOR c (location NUMBER DEFAULT 1700) IS
3 SELECT d.department_name,
4 e.last_name manager,
5 l.city
6 FROM departments d, employees e, locations l
7 WHERE l.location_id = location
8 AND l.location_id = d.location_id
9 AND d.department_id = e.department_id
10 ORDER BY d.department_id;
11
12 PROCEDURE print_depts IS
13 dept_name departments.department_name%TYPE;
14 mgr_name employees.last_name%TYPE;
15 city_name locations.city%TYPE;
16 BEGIN
17 LOOP
18 FETCH c INTO dept_name, mgr_name, city_name;
19 EXIT WHEN c%NOTFOUND;
20 DBMS_OUTPUT.PUT_LINE(dept_name || ' (Manager: ' || mgr_name || ')');
21 END LOOP;
22 END print_depts;
23
24 BEGIN
25 DBMS_OUTPUT.PUT_LINE('DEPARTMENTS AT HEADQUARTERS:');
26 DBMS_OUTPUT.PUT_LINE('--------------------------------');
27 OPEN c;
28 print_depts;
29 DBMS_OUTPUT.PUT_LINE('--------------------------------');
30 CLOSE c;
31
32 DBMS_OUTPUT.PUT_LINE('DEPARTMENTS IN CANADA:');
33 DBMS_OUTPUT.PUT_LINE('--------------------------------');
34 OPEN c(1800); -- Toronto
35 print_depts;
36 CLOSE c;
37 OPEN c(1900); -- Whitehorse
38 print_depts;
39 CLOSE c;
40 END;
41 /
DEPARTMENTS AT HEADQUARTERS:
--------------------------------
Administration (Manager: Whalen)
Purchasing (Manager: Colmenares)
Purchasing (Manager: Baida)
Purchasing (Manager: Himuro)
Purchasing (Manager: Raphaely)
Purchasing (Manager: Khoo)
Purchasing (Manager: Tobias)
Executive (Manager: Kochhar)
Executive (Manager: De Haan)
Executive (Manager: King)
Finance (Manager: Popp)
Finance (Manager: Greenberg)
Finance (Manager: Faviet)
Finance (Manager: Chen)
Finance (Manager: Urman)
Finance (Manager: Sciarra)
Accounting (Manager: Gietz)
Accounting (Manager: Higgins)
--------------------------------
DEPARTMENTS IN CANADA:
--------------------------------
Marketing (Manager: Hartstein)
Marketing (Manager: Fay)
PL/SQL procedure successfully completed.
SQL>
Example 7-13 Adding Formal Parameter to Existing Cursor
SQL>
SQL> DECLARE
2 CURSOR c (job VARCHAR2, max_sal NUMBER,
3 hired DATE DEFAULT TO_DATE('31-DEC-1999', 'DD-MON-YYYY')) IS
4 SELECT last_name, first_name, (salary - max_sal) overpayment
5 FROM employees
6 WHERE job_id = job
7 AND salary > max_sal
8 AND hire_date > hired
9 ORDER BY salary;
10
11 PROCEDURE print_overpaid IS
12 last_name_ employees.last_name%TYPE;
13 first_name_ employees.first_name%TYPE;
14 overpayment_ employees.salary%TYPE;
15 BEGIN
16 LOOP
17 FETCH c INTO last_name_, first_name_, overpayment_;
18 EXIT WHEN c%NOTFOUND;
19 DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ ||
20 ' (by ' || overpayment_ || ')');
21 END LOOP;
22 END print_overpaid;
23
24 BEGIN
25 DBMS_OUTPUT.PUT_LINE('-------------------------------');
26 DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives:');
27 DBMS_OUTPUT.PUT_LINE('-------------------------------');
28 OPEN c('SA_REP', 10000); -- existing reference
29 print_overpaid;
30 CLOSE c;
31
32 DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
33 DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives Hired After 2004:');
34 DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
35 OPEN c('SA_REP', 10000, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
36 -- new reference
37 print_overpaid;
38 CLOSE c;
39 END;
40 /
-------------------------------
Overpaid Sales Representatives:
-------------------------------
Vishney, Clara (by 500)
Abel, Ellen (by 1000)
Ozer, Lisa (by 1500)
------------------------------------------------
Overpaid Sales Representatives Hired After 2004:
------------------------------------------------
Vishney, Clara (by 500)
Ozer, Lisa (by 1500)
PL/SQL procedure successfully completed.
SQL>
7.2.2.7 Explicit Cursor Attributes
The syntax for the value of an explicit cursor attribute is
cursor_name
immediately followed byattribute
(for example,c1%ISOPEN
).
The explicit cursor attributes are:
%ISOPEN Attribute: Is the Cursor Open?
%FOUND Attribute: Has a Row Been Fetched?
%NOTFOUND Attribute: Has No Row Been Fetched?
%ROWCOUNT Attribute: How Many Rows Were Fetched?
7.2.2.7.1 %ISOPEN Attribute: Is the Cursor Open?
%ISOPEN
returnsTRUE
if its explicit cursor is open;FALSE
otherwise.
%ISOPEN
is useful for:
Checking that an explicit cursor is not already open before you try to open it.
Checking that an explicit cursor is open before you try to close it.
Example 7-14 %ISOPEN Explicit Cursor Attribute
SQL>
SQL> DECLARE
2 CURSOR c1 IS
3 SELECT last_name, salary FROM employees
4 WHERE ROWNUM < 11;
5
6 the_name employees.last_name%TYPE;
7 the_salary employees.salary%TYPE;
8 BEGIN
9 IF NOT c1%ISOPEN THEN
10 OPEN c1;
11 END IF;
12
13 FETCH c1 INTO the_name, the_salary;
14
15 IF c1%ISOPEN THEN
16 CLOSE c1;
17 END IF;
18 END;
19 /
PL/SQL procedure successfully completed.
SQL>
7.2.2.7.2 %FOUND Attribute: Has a Row Been Fetched?
%FOUND
returns:
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
is useful for determining whether there is a fetched row to process.
Example 7-15 %FOUND Explicit Cursor Attribute
SQL>
SQL> DECLARE
2 CURSOR c1 IS
3 SELECT last_name, salary FROM employees
4 WHERE ROWNUM < 11
5 ORDER BY last_name;
6
7 my_ename employees.last_name%TYPE;
8 my_salary employees.salary%TYPE;
9 BEGIN
10 OPEN c1;
11 LOOP
12 FETCH c1 INTO my_ename, my_salary;
13 IF c1%FOUND THEN -- fetch succeeded
14 DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
15 ELSE -- fetch failed
16 EXIT;
17 END IF;
18 END LOOP;
19 END;
20 /
Name = Baer, salary = 10000
Name = Fay, salary = 6000
Name = Gietz, salary = 8300
Name = Grant, salary = 2600
Name = Hartstein, salary = 13000
Name = Higgins, salary = 12008
Name = King, salary = 24000
Name = Mavris, salary = 6500
Name = OConnell, salary = 2600
Name = Whalen, salary = 4400
PL/SQL procedure successfully completed.
SQL>
7.2.2.7.3 %NOTFOUND Attribute: Has No Row Been Fetched?
%NOTFOUND
(the logical opposite of%FOUND
) returns:
NULL
after the explicit cursor is opened but before the first fetch
FALSE
if the most recent fetch from the explicit cursor returned a row
TRUE
otherwise%NOTFOUND
is useful for exiting a loop whenFETCH
fails to return a row,
Example 7-16 %NOTFOUND Explicit Cursor Attribute
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%NOTFOUND THEN -- fetch failed
EXIT;
ELSE -- fetch succeeded
DBMS_OUTPUT.PUT_LINE
('Name = ' || my_ename || ', salary = ' || my_salary);
END IF;
END LOOP;
END;
/
7.2.2.7.4 %ROWCOUNT Attribute: How Many Rows Were Fetched?
%ROWCOUNT
returns:
Zero after the explicit cursor is opened but before the first fetch
Otherwise, the number of rows fetched (an
INTEGER
)
Example 7-17 %ROWCOUNT Explicit Cursor Attribute
SQL>
SQL> DECLARE
2 CURSOR c1 IS
3 SELECT last_name FROM employees
4 WHERE ROWNUM < 11
5 ORDER BY last_name;
6
7 name employees.last_name%TYPE;
8 BEGIN
9 OPEN c1;
10 LOOP
11 FETCH c1 INTO name;
12 EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
13 DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name);
14 IF c1%ROWCOUNT = 5 THEN
15 DBMS_OUTPUT.PUT_LINE('--- Fetched 5th row ---');
16 END IF;
17 END LOOP;
18 CLOSE c1;
19 END;
20 /
1. Abel
2. Ande
3. Atkinson
4. Austin
5. Baer
--- Fetched 5th row ---
6. Baida
7. Banda
8. Bates
9. Bell
10. Bernstein
PL/SQL procedure successfully completed.
SQL>