7 PL/SQL Static SQL 7.1-7.2

最新推荐文章于 2024-10-24 16:38:12 发布

DB架构

最新推荐文章于 2024-10-24 16:38:12 发布

阅读量223

点赞数

分类专栏： PL/SQL 文章标签： sql 数据库 java

本文链接：https://blog.csdn.net/u011868279/article/details/129683108

版权

PL/SQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

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 and NEXTVAL,

LEVEL

OBJECT_VALUE

7.1.2.1 CURRVAL and NEXTVAL in PL/SQL

You can use sequence_name.CURRVAL and sequence_name.NEXTVAL in a PL/SQL expression wherever you can use a NUMBER expression. However:

Using sequence_name.CURRVAL or sequence_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 and sequence_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 returns FALSE, 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 no SELECT or DML statement has run

TRUE if a SELECT 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 of SQL%FOUND) returns:

NULL if no SELECT or DML statement has run

FALSE if a SELECT statement returned one or more rows or a DML statement affected one or more rows

TRUE otherwise

The SQL%NOTFOUND attribute is not useful with the PL/SQL SELECT INTO statement, because:

If the SELECT INTO statement returns no rows, PL/SQL raises the predefined exception NO_DATA_FOUND immediately, before you can check SQL%NOTFOUND.

A SELECT INTO statement that invokes a SQL aggregate function always returns a value (possibly NULL). After such a statement, the SQL%NOTFOUND attribute is always FALSE, so checking it is unnecessary.

7.2.1.4 SQL%ROWCOUNT Attribute: How Many Rows Were Affected?

SQL%ROWCOUNT returns:

NULL if no SELECT or DML statement has run

Otherwise, the number of rows returned by a SELECT statement or affected by a DML statement (an INTEGER)

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 the FETCH statement), and close the explicit cursor (with the CLOSE 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 set

Positions 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 by attribute (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 returns TRUE 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 when FETCH 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>