Oracle PL/SQL - Cursor Parameters with Default Values

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Explicit Cursors

Introduction

When you create an explicit cursor with formal parameters, you can specify default values.

For a formal parameter with default value, its actual parameter is optional.

The following code creates an explicit cursor whose formal parameter represents a location ID.

Demo

SQL>
SQL> drop table emp;

Table dropped.--  w  ww . jav a  2s .co m

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20);
SQL>
SQL> drop table departments;

Table dropped.

SQL> CREATE TABLE departments(
  2  department_id NUMBER(4),
  3  department_name VARCHAR2(30)CONSTRAINT dept_name_nn NOT NULL,
  4  manager_id NUMBER(6),
  5  location_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO departments VALUES( 10, 'Administration', 200, 1700);
SQL> INSERT INTO departments VALUES( 20, 'Marketing', 201, 1000);
SQL> INSERT INTO departments VALUES( 30, 'Purchasing', 114, 1700);
SQL> INSERT INTO departments VALUES( 40, 'Human Resources', 203, 1000);
SQL> INSERT INTO departments VALUES( 50, 'Shipping', 121, 1700);
SQL>
SQL>
SQL> CREATE TABLE locations(
  2  location_id      NUMBER(4),
  3  street_address   VARCHAR2(40),
  4  postal_code      VARCHAR2(12),
  5  city             VARCHAR2(30),
  6  state_province   VARCHAR2(25),
  7  country_id       CHAR(2));
SQL>
SQL> INSERT INTO locations VALUES( 1000 , '1297 New York', '00989', 'Roma', NULL, 'IT');
SQL> INSERT INTO locations VALUES( 1700 , 'Main Street', '00000', 'New York', NULL, 'IT');
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, emp 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   emp.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    OPEN c;
 27    print_depts;
 28    CLOSE c;
 29
 30    DBMS_OUTPUT.PUT_LINE('DEPARTMENTS:');
 31    OPEN c(1000); -- Toronto
 32    print_depts;
 33    CLOSE c;
 34    OPEN c(1700);
 35    print_depts;
 36    CLOSE c;
 37  END;
 38  /
DEPARTMENTS AT HEADQUARTERS:
Administration (Manager: King)
DEPARTMENTS:
Marketing (Manager: Lee)
Administration (Manager: King)

PL/SQL procedure successfully completed.

SQL>
SQL>

Related Topic