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