PL/SQL Brain Teaser: how many ways can you define a cursor in PL/SQL?

According to the PL/SQL User Guide,
cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
So how many ways can you define a cursor in PL/SQL?

Let's include in this ways in which you can get Oracle to define a cursor for you, as well.

Please submit a comment with JUST ONE example of a cursor definition. You can submit multiple ideas, but with multiple comments. 

If you include several in a single comment, I will take only the first one that has not already been suggested

Comments

  1. You can define a dynamic cursor a.k.a REF CURSOR

    ReplyDelete
    Replies
    1. Forgot an example :)

      DECLARE
      TYPE t_ref_cur IS REF CURSOR;
      cur_ref t_ref_cur;
      v_sql_stmt VARCHAR2(4000 CHAR);
      BEGIN
      v_sql_stmt := 'SELECT COL1,COL2 FROM TABLE1 WHERE 1 = 1 AND COL1 = :1';

      <>
      OPEN cur_ref FOR v_sql_stmt USING 'blabla';
      LOOP
      FETCH ... BULK COLLECT ...
      EXIT WHEN ...

      DO SOME STUFF
      END LOOP x;
      CLOSE cur_ref;
      END;

      Delete
  2. an implicit cursor in a select statement:
    select dummy from dual

    ReplyDelete
  3. an implicit cursor for an update statement:
    update emp set sal = sal * 1.1

    ReplyDelete
  4. an explicit cursor for a cursor for loop
    declare
    cursor c is
    select empno from emp;
    begin
    for r in c loop
    -- do exciting stuff
    null;
    end loop;
    end;

    ReplyDelete
  5. an implicit cursor for a cursor for loop
    begin
    for r in (select empno from emp) loop
    -- do exciting stuff
    null;
    end loop;
    end;

    ReplyDelete
  6. an explicit cursor for a while loop
    declare
    cursor c is
    select * from emp;
    r c%rowtype;
    begin
    open c;
    fetch c into r;
    while c%found loop
    -- do exciting stuff
    fetch c into r;
    end loop;
    close c;
    end;

    ReplyDelete
  7. explicit cursor:

    declare
    cur_x is
    select ...;

    v_x cur_x%rowtype;
    begin
    open cur_x;
    loop
    fetch
    --do some stuff
    end loop;
    close_cur_x;
    end;

    ReplyDelete
  8. Dynamic cursor definition?

    EXECUTE IMMEDIATE 'SELECT ' || p_col || ' FROM ' || p_table INTO v_variable;

    ReplyDelete
  9. cursor expression:

    SELECT dname
    ,CURSOR ( SELECT ename
    FROM emp e
    WHERE (e.deptno = d.deptno)
    ORDER BY ename)
    FROM dept d
    ORDER BY dname;

    ReplyDelete
  10. Cursor as part of pipelined function? Not sure whether it counts as Christian Chelemen also described something similar.

    select * from table(pip_fnc(cursor(select * from table)));

    ReplyDelete
  11. I went through all comments here and on LinkedIn. Here's the summary:

    Cursor variable (REF CURSOR)
    Implicit cursor: SELECT
    Implicit cursor: UPDATE, DELETE, INSERT
    Explicit cursor in block (DECLARE CURSOR my_cur IS SELECT ...)
    Cursor FOR loop (FOR rec IN (SELECT …))
    Dynamic SQL cursor (EXECUTE IMMEDIATE)
    CURSOR Expression
    FORALL statement (FORALL [UPDATE | DELETE | INSERT ]
    Dynamic SQL cursor (DBMS_SQL.OPEN_CURSOR)

    But there is still at least one way to define a cursor in PL/SQL code that is not shown above. Can anyone figure it out?

    ReplyDelete
  12. I don't see any cursor passing arguments for variables...

    ReplyDelete
  13. I think the CURSOR expression should be excluded from the list. As it really is part of the SQL language. In plsql you can pass this into a variable or parameter of cause. But then this is a cursor variable (sysrefcursor or strong ref cursor).

    ReplyDelete
  14. Implicit result sets were not mentioned yet it seems. However it also does not really create a new cursor. Instead it allows to return a cursor to the caller, e.g. sql*plus.

    Here is an example

    create or replace procedure testReturnResult(cnt in binary_integer)
    authid definer
    is
    myCur sys_refcursor;
    begin
    open myCur for select level num# from dual connect by level <= cnt;
    DBMS_SQL.RETURN_RESULT (myCur);
    end testReturnResult;
    /

    SQL> execute testReturnResult(17);

    PL/SQL-Prozedur erfolgreich abgeschlossen.

    ResultSet #1

    NUM#
    ----------
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11

    NUM#
    ----------
    12
    13
    14
    15
    16
    17

    17 Zeilen ausgewählt.

    SQL>

    ReplyDelete
  15. @sven, you are right, CURSOR is SQL operator, thanks for clarifying.

    The only other thing I was looking for was defining a cursor in a package specification - it's an explicit cursor, but has a different form from the sort you declare in a block, because you can "hide" the SELECT in the package body:

    CREATE OR REPLACE PACKAGE my_pkg AUTHID DEFINER
    AS
    CURSOR emps_cur RETURN employees%ROWTYPE;
    END;
    /

    CREATE OR REPLACE PACKAGE BODY my_pkg
    AS
    CURSOR emps_cur RETURN employees%ROWTYPE
    IS
    SELECT *
    FROM employees;
    END;
    /

    ReplyDelete
  16. Really, never thinked about ot.

    ReplyDelete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts