How do I return a table using a procedure that has a cursor in it ? PL/SQL

Question

I have a procedure that is given one value as input, after doing some process, using a cursor within that procedure, i want the procedure to return a table.

this one value that is given as an input_param is a non unique ID that is used to identify multiple rows.

so that way I can run a command like :

select * from {call(procedure_name(input_Param)}

My knowledge of PLSQL is limited.

I'm not sure if a procedure can have a a cursor definition inside it, and if that is possible then how do i return an entire Table from the procedure.

BTW: This procedure has to be called using a select statement, and if not a select statement then it should return a table, with a select * at the end. If I have to specify whih columns to output instead of select * do I need to provide all those column names as input_Params ? ie. If I want the procedure to return only a small number of columns what do I do?

Thanks

Answer 1

You need to use PIPELINED function. Example below, link to more information at the end.

CREATE TABLE test_pipe (
  id NUMBER,
  name VARCHAR2(20),
  salary NUMBER
);

INSERT INTO test_pipe VALUES (1, 'Smith', 5000);
INSERT INTO test_pipe VALUES (2, 'Brown', 8000);
INSERT INTO test_pipe VALUES (3, 'Bay', 10000);

COMMIT;


CREATE TYPE t_pipe_row_test AS OBJECT (
  name VARCHAR2(20),
  salary NUMBER
);
/

CREATE TYPE t_pipe_test_tab IS TABLE OF t_pipe_row_test;
/

CREATE OR REPLACE FUNCTION test_func_pipe(p_min_salary IN NUMBER)
  RETURN t_pipe_test_tab
  PIPELINED
AS
BEGIN
  FOR v_rec IN (SELECT name, salary
                  FROM test_pipe
                WHERE salary >= p_min_salary)
  LOOP
    PIPE ROW (t_pipe_row_test(v_rec.name, v_rec.salary));
  END LOOP;
END;
/

SELECT * FROM TABLE(test_func_pipe(6000));

Output:

NAME                     SALARY
-------------------- ----------
Brown                      8000 
Bay                       10000 

More about pipelined functions by Tim Hall