Skip to main content

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

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel