Oracle PL/SQL Tutorial/Cursor/Cursor Parameter

An example of parameterized cursor using cursor FOR LOOP

   <source lang="sql">

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></source>

Open cursor with parameter

   <source lang="sql">

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.</source>

Parameterized cursor

   <source lang="sql">

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.</source>

Passing parameters to cursors

Basic Syntax for Passing Parameters in a Cursor

You can pass a literal value, as in

   <source lang="sql">

open c_emp (10);</source>