Return many rows on a plsql Oracle10g

Question

I'm doing a little project and I need to do a plsql to return many rows.

I have someting like:

Table(id, data1, data2, from)

And the user gives me the value of from and I have to return all that.

The SQL is easy:

SELECT * FROM Table WHERE from = dataIntroduced

But, I don't know how the plsql can return that.

I read about collections but I think is not what I need

• András Váczi: Why can't you simply issue your SELECT (without involving plsql)?
• nax: because is a class project and they ask that way... i'm not confortable too with this because i can do views and selects as you say but they ask me to do in a procedure. the problem is i been searching oracle docs since 2 days now and i'm not able to find nothing about this.
• András Váczi: here are a few solutions: stackoverflow.com/questions/101033/…

Answer 1

One way of return select data from PL/SQL is using Oracle table functions. Below is an example where the line SELECT * FROM table_a where test_a = par1; should be replaced by your select statement + change the table name.

Create table Table_a
(test_a varchar2(1))
/
insert into table_a
values('a')
/
create or replace package test_package as
TYPE col_table_1 is table of TABLE_A%ROWTYPE;
function test_plsql_table(par1 varchar2) return col_table_1
 pipelined;
end;
/
create or replace package body test_package as
 function test_plsql_table(par1 varchar2) return col_table_1
 PIPELINED as
 cursor temp_cur is
 SELECT * FROM table_a where test_a = par1;
 begin
 for cur_rec in temp_cur loop
 pipe row(cur_rec);
 end loop;
 end;
end;
/
SELECT * from TABLE( test_package.test_plsql_table('a'))
/

• user1822: You should not use ; and / for regular DDL (it's correct for PL/SQL, but your create table and insert into will be executed twice by SQL*Plus (because / re-runs the current buffer)
• Ryan Kenning: Quite right, sorry. I will make the amendments now.
• SQLDevDBA: Good lord. I knew Pipeline functions were a thing but hadn't ever thought to use them this way. You're awesome and I have to go question some life choices.

Answer 2

The simplest possible approach is to return a cursor. Something like

create or replace function get_emps_in_dept( p_dept_in IN number )
 return sys_refcursor
is
 l_ret sys_refcursor;
begin
 open l_ret
 for select *
 from emp
 where deptno = p_dept_in;
 return l_ret;
end;

You can then call that in SQL*Plus by declaring a host variable (most front-end languages will treat a function returning a SYS_REFCURSOR very much like a SELECT statement that returns a cursor)

SQL> variable rc refcursor;
SQL> exec :rc := get_emps_in_dept( 30 );
PL/SQL procedure successfully completed.
SQL> print rc;
 EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
 DEPTNO
----------
 7499 ALLEN SALESMAN 7698 20-FEB-81 1601 300
 30
 7521 WARD SALESMAN 7698 22-FEB-81 1251 500
 30
 7654 MARTIN SALESMAN 7698 28-SEP-81 1251 1400
 30
 7698 BLAKE MANAGER 7839 01-MAY-81 2851
 30
 7844 TURNER SALESMAN 7698 08-SEP-81 1501 0
 30
 7900 JAMES CLERK 7698 03-DEC-81 951
 30
6 rows selected.

You can return a collection, either all at once or pipelined, but that requires more code to create and maintain the objects. I love pipelined table functions if you need to embed PL/SQL logic in the query itself that would be difficult to follow in SQL. But they're overkill if you just need to run a simple query and return the results.