Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Posts
    3

    Unanswered: Returning values from a procedure

    I am writing an Oracle stored procedure for the first time in a long time, and I can't remember how this works. I want to write a procedure that takes a parameter and selects from a table based on that parameter, and displays the results to the user.

    So I want something like:

    create or replace procedure test(p in char)
    as
    BEGIN
    select col1 from table where col=p;
    END;
    /

    There could be a variable number of results returned. How do I accomplish that?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    REF CURSOR is what you are looking for, I guess.
    Code:
    SQL> create or replace procedure print_rec
      2    (par_deptno in number,
      3     par_rec    out sys_refcursor
      4    )
      5  is
      6  begin
      7    open par_rec for 'select * from emp where deptno = ' || par_deptno;
      8  end;
      9  /
    
    Procedure created.
    
    SQL> var rc refcursor
    SQL> exec print_rec(10, :rc);
    
    PL/SQL procedure successfully completed.
    
    SQL> print :rc
    
         EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
          7782 CLARK      MANAGER         7839 09.06.1981 00:00:00       3250                    10
          7839 KING       PRESIDENT            17.11.1981 00:00:00       5800                    10
          7934 MILLER     CLERK           7782 23.12.1982 00:00:00       2100                    10
    
    SQL>

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Littlefoot View Post
    REF CURSOR is what you are looking for, I guess
    You can also create table function that returns a result set.

    Code:
    CREATE TYPE result_row AS OBJECT (id number, full_name varchar(50))
    /
    
    CREATE TYPE result_row_table AS TABLE OF result_row
    /
    
    
    CREATE OR REPLACE FUNCTION get_records(p varchar)
    RETURN result_row_table
    PIPELINED
    IS
      return_row result_row := result_row(null, null);
    BEGIN
      FOR t_rec IN (SELECT id, first_name, last_name 
                    FROM emp
                    WHERE last_name LIKE p||'%') LOOP
        return_row.id := t_rec.id;
        return_row.full_name := t_rec.first_name||' '||t_rec.last_name;
        PIPE ROW (return_row);
      END LOOP;
    END;
    /
    And the function can then be used like this:
    Code:
    SELECT * 
    FROM table(get_records('M'))
    which might be easier to use than a ref_cursor depending on your environment.

  4. #4
    Join Date
    Apr 2010
    Posts
    3
    Thank you both very much for your responses!

    This is something that drives me crazy about Oracle. In SQL Server, you can simply "select column from table" and it will display the results to the user. That seems so much simpler and straightforward to me.

    If anybody knows why Oracle doesn't allow that, or can help me to understand (and therefore remember) their philosophy about this, I would definitely appreciate your comments. This really bugs me. It almost makes me want to abandon Oracle and go back to SQL Server.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by verypuzzled View Post
    That seems so much simpler and straightforward to me.
    And a lot more limited as far as I can tell.

    As far as I know you are limited to "straight" SELECTs in SQL Server. With Oracle's way, you can return anything. You could even read the data from a text file, or apply other procedural calculations on each row while you retrieve it (without the need to create a temporary table or to keep the whole result set in memory.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •