Oracle PL/SQL - Passing Parameters to Explicit Cursor FOR LOOP Statement

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Explicit Cursor FOR LOOP Statement

Introduction

The following code declares and defines an explicit cursor that accepts two parameters.

And then the code uses it in an explicit cursor FOR LOOP statement to display the results.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  w w w. j av 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-2000', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-2010', 'dd-MON-yyyy'), 'CODER', 25000, NULL, NULL, 20);
SQL>
SQL>
SQL>
SQL> DECLARE
  2    CURSOR c1  (job VARCHAR2, max_wage NUMBER) IS
  3      SELECT * FROM emp
  4      WHERE job_id = job
  5      AND salary > max_wage;
  6  BEGIN
  7    FOR person IN c1('CODER', 3000)
  8    LOOP
  9       -- process data record
 10      DBMS_OUTPUT.PUT_LINE (
 11        'Name = ' || person.last_name || ', salary = ' ||
 12        person.salary || ', Job Id = ' || person.job_id
 13      );
 14    END LOOP;
 15  END;
 16  /
Name = King, salary = 24000, Job Id = CODER
Name = Lee, salary = 25000, Job Id = CODER

PL/SQL procedure successfully completed.

SQL>

Related Topic