Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Why can't you simply issue your SELECT (without involving plsql)? –  dezso May 22 '12 at 9:47
    
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. –  nax May 22 '12 at 10:58
2  
here are a few solutions: stackoverflow.com/questions/101033/… –  dezso May 22 '12 at 11:01
add comment

2 Answers

up vote 2 down vote accepted

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'))
/
share|improve this answer
1  
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) –  a_horse_with_no_name May 22 '12 at 15:00
    
Quite right, sorry. I will make the amendments now. –  Ryan Kenning May 23 '12 at 7:01
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.