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

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'))
/

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.