CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
user12646608
user12646608

Reputation:

PL/SQL Stored Procedure - return records from a table

I am very new to doing SQL work. I was wondering how I return a select statement in a stored procedure in PL/SQL.

My understanding so far (that is little) is that I should put the return of the data in a table and assign the data within the table to a reference cursor. With that loaded then LOOP through the REF Cursor and present the data back?

Actually converting that into code for a stored procedure has lost me completely with little examples to see with my use case. Any help is appreciated.

Many thanks in advance :)

Upvotes: 0

Views: 2922

Answers (2)

Zynon Putney II
Zynon Putney II

Reputation: 695

Usually a stored procedure is used to perform manipulations of data in the database, and functions are used to return values or data. If you're just trying to use a select statement within a stored procedure, then you would want to use a cursor, which is something you declare like any other variable at the beginning of the procedure, and then open either implicitly or explicitly within the procedure code.

Example of an implicit cursor:

declare
  cursor sample_cur is  --this can be your select statement
    select sysdate as today from dual;

begin
  for rec in sample_cur loop
     -- step by step for each record you return in your cursor
     dbms_output.put_line(rec.today);
  end loop;
end;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

Here's one example: procedure has only one - OUT - parameter, which is a refcursor:

SQL> create or replace procedure p_test (par_rc out sys_refcursor)
  2  is
  3  begin
  4    open par_rc for select deptno, dname, loc from dept;
  5  end;
  6  /

Procedure created.

In order to call such a procedure, you need to store the result into something. In order to do that, I'll declare a variable (in SQL*Plus, which is a tool I use for this example) and call the procedure using begin-end block, providing the variable name as its parameter:

SQL> var l_rc refcursor;
SQL>
SQL> begin
  2    p_test (:l_rc);
  3  end;
  4  /

PL/SQL procedure successfully completed.

Print the result:

SQL> print l_rc

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

There might be other options, which depend on what you really are doing.

Upvotes: 1

Related Questions