Oracle PL/SQL Tutorial/Cursor/Cursor Parameter

An example of parameterized cursor using cursor FOR LOOP

SQL>
SQL>
SQL>
SQL>
SQL> create table product(
  2     product_id number(4)     not null,
  3     product_description varchar2(20) not null
  4  );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL>
SQL> create table company(
  2     product_id        number(4)    not null,
  3     company_id          NUMBER(8)    not null,
  4     company_short_name  varchar2(30) not null,
  5     company_long_name   varchar2(60)
  6  );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
  2    CURSOR cursorValue(p_product_id NUMBER) IS
  3      SELECT h.product_description,o.rupany_short_name
  4      FROM company o,product h
  5      WHERE o.product_id =h.product_id
  6      AND h.product_id =p_product_id
  7      ORDER by 2;
  8    v_company_rec cursorValue%ROWTYPE;
  9  BEGIN
 10
 11    FOR idx in cursorValue(1) LOOP
 12      dbms_output.put_line(rpad(idx.product_description,20," ")||" "||
 13      rpad(idx.rupany_short_name,30," "));
 14    END LOOP;
 15
 16    FOR idx in cursorValue(2) LOOP
 17      dbms_output.put_line(rpad(idx.product_description,20," ")||" "||
 18      rpad(idx.rupany_short_name,30," "));
 19    END LOOP;
 20  END;
 21  /
Java                 A Inc.
Java                 B Inc.
Java                 C Inc.
Oracle               D Inc.
Oracle               E Inc.
Oracle               F Inc.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL> drop table company;
Table dropped.
SQL>

Open cursor with parameter

SQL>
SQL>
SQL> create table employee (
  2  id                             number,
  3  employee_type_id               number,
  4  external_id                    varchar2(30),
  5  first_name                     varchar2(30),
  6  middle_name                    varchar2(30),
  7  last_name                      varchar2(30),
  8  name                           varchar2(100),
  9  birth_date                     date  ,
 10  gender_id                      number );
Table created.
SQL>
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
  2      cursor c_worker(aiv_last_name in employee.last_name%TYPE) is
  3      select first_name
  4      from   employee
  5      where  last_name like aiv_last_name||"%"
  6      order by id;
  7
  8      v_first_name employee.first_name%TYPE;
  9
 10  begin
 11    open c_worker("DOE");
 12    loop
 13      fetch c_worker into v_first_name;
 14
 15      if c_worker%notfound then
 16        close c_worker;
 17        exit;
 18      end if;
 19
 20      DBMS_OUTPUT.PUT_LINE(v_first_name);
 21    end loop;
 22  end;
 23  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employee;
Table dropped.

Parameterized cursor

SQL>
SQL> CREATE TABLE books(
  2    book_id  NUMBER  NOT NULL  PRIMARY KEY,
  3    title  VARCHAR2(200),
  4    author  VARCHAR2(200) );
Table created.
SQL>
SQL> INSERT INTO books VALUES(1, "Oracle SQL*Plus", "GENNICK,JONATHAN");
1 row created.
SQL>
SQL> INSERT INTO books VALUES(2, "Oracle PL/SQL Programming", "FEUERSTEIN,STEVEN");
1 row created.
SQL>
SQL> INSERT INTO books VALUES(3, "Oracle Built-in Packages", "FEUERSTEIN,STEVEN");
1 row created.
SQL>
SQL> set serveroutput on size 500000
SQL>
SQL> DECLARE
  2
  3     CURSOR books_cur(author_in IN books.author%TYPE) IS
  4     SELECT *
  5       FROM books
  6      WHERE author = author_in;
  7
  8     book_count PLS_INTEGER;
  9
 10  BEGIN
 11
 12     FOR book_rec IN books_cur (author_in => "FEUERSTEIN, STEVEN")
 13     LOOP
 14        -- ... process data ...
 15        book_count := books_cur%ROWCOUNT;
 16     END LOOP;
 17
 18     IF book_count > 10
 19     THEN
 20       dbms_output.put_line("Lotsa books, time for vacation.");
 21     ELSE
 22       dbms_output.put_line("Keep writing slacker.");
 23     END IF;
 24
 25  END;
 26  /
Keep writing slacker.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> DROP TABLE books;
Table dropped.

Passing parameters to cursors

Basic Syntax for Passing Parameters in a Cursor

You can pass a literal value, as in

open c_emp (10);