Search code examples
oracleplsqlcursor

How to define a cursor later?


In the Oracle Docs it mentions that a cursor has two parts, the declaration and the definition.

You can either declare it first (with cursor_declaration) and then define it later in the same block, subprogram, or package (with cursor_definition) or declare and define it at the same time (with cursor_definition).

I have looked but have not been able to find a code example which shows how a cursor gets defined at a later point.

I want to declare three cursors, but only define them as I go along. Cursor 1 will be removing the work that cursor 2 would need to be doing for example. I have data from from different systems and they can be matched in one of three ways. Once matched they dont need to be again, so the other matching cursors dont need those values which they would get if defined right at the start.

Here is how I am expecting it to look, but could be wrong

DECLARE
    CURSOR X IS ...; --cursor declaration and definition
    CURSOR Y; --cursor declaration
    CURSOR Z; --cursor declaration
BEGIN
    FOR i IN X LOOP
        --DO STUFF
    END LOOP;
    Y IS ...; --cursor definition
    FOR ii IN Y LOOP
        --DO STUFF
    END LOOP;
    Z IS ...; --cursor definition
    FOR iii IN Z LOOP
        --DO STUFF
    END LOOP;
END;

There are some related questions

  • This answer doesnt define the cursor in the body, but instead moved it up into the declare.
  • This question has a comment about running the query again, which isnt the point

Solution

  • I must confess I've never thought about or tried "deferring" the definition of the cursor inside a single block. But they are certainly correctly informing you that you can define the cursor declaration in the package specification and "hide" the definition in the body.

    Here's an example:

    CREATE PACKAGE species_pkg 
    IS 
       CURSOR species_cur 
          RETURN endangered_species%ROWTYPE; 
    END;
    /
    
    CREATE PACKAGE BODY species_pkg 
    IS 
       CURSOR species_cur 
          RETURN endangered_species%ROWTYPE 
       IS 
            SELECT * 
              FROM endangered_species 
          ORDER BY common_name; 
    END;
    /
    

    You can try it out with my LiveSQL script.

    But I really don't think that's what you want to do implement your solution. Instead, I would use cursor variables, as Mick Mnemonic shows you in his answer.

    The primary advantage of separating the cursor declaration from its definition is that allows you to follow the hallowed "information hiding" principle. Theoretically, a developer doesn't need to know about the implementation of the query, only the description of the dataset returned.