CODEWITHSUNDEEP
plsql
IturPablo
IturPablo

Reputation: 1611

Use cursor in a select statement

Hope you can help me, Im trying to use a cursor in a sql query. I'm 99% sure this can be done, so please any suggestions? this is my code

FUNCTION filter (c_cur_trip_menu IN  c_menu_tripulantes) 
        RETURN c_menu_tripulantes IS 
    BEGIN
             SELECT * 
            FROM c_cur_trip_menu  , opciones_migradas 
            WHERE  mnu.mnu_idemnu = id_opcion;      


            RETURN c_cur_trip_menu;
    END filter;

What I want is to use the cursor as a table. thanks in advance.

Upvotes: 1

Views: 1454

Answers (2)

Alexander Tokarev
Alexander Tokarev

Reputation: 1035

You can't select from cursor (but you could create cursor from any select statement (using cursor(your query))) but table or pipelined functions could help you with your task. http://www.oracle-base.com/articles/misc/pipelined-table-functions.php helps you. Nevertheless I've never understood trying to move function approach for retrieving data in sql based DB - views solve it perfect. Functions are weak to be estimated by CBO so you create the place to make your queries work slower in advance. What for - no idea....

Upvotes: 0

Robert
Robert

Reputation: 25763

You can copy data from cursor to temporary table, but you cant use a cursor like a table. If the function you want to build in oracle you can't use select syntax to display data:

 FOR cur in c_cur_trip_menu
 LOOP
     dbms_output.put_line(cur.col1||' 'cur.col2);
 END LOOP;

Upvotes: 1

Related Questions