CODEWITHSUNDEEP
sqlplsqloracle10g
bviale
bviale

Reputation: 5375

How to re-use a SQL query in a PL/SQL procedure?

I am writing a PL/SQL procedure. In the body of this procedure, how can I use twice the same query without re-writing it ?

To simplify, let's say that I have this SQL query : 

SELECT * 
FROM mytable
WHERE age > 18

Is there a way to "store it", so I could do for example :

SELECT COUNT(*) INTO var1
FROM myQuery

I know the WITH ... AS keywords, but as I know it can be only used in the current statement, and I want to be able to call it from different statements.

Thanks !

Upvotes: 1

Views: 97

Answers (1)

Dave Costa
Dave Costa

Reputation: 48131

There are various possibilities. Here are the ones I think of immediately, there are probably others:

  • Declare an explicit CURSOR using your query, and use that cursor multiple times in the body of your procedure.

  • Store the query in a string variable, and use EXECUTE IMMEDIATE to run it multiple times

  • Execute the query once, storing the results in a local collection (nested table, most likely), and process those stored results multiple times

  • Create a function that executes the query and returns its results as a nested-table type. Then SELECT FROM TABLE( my_function ) multiple times

Upvotes: 2

Related Questions