Oracle SQL Create function or procedure returning a table

Question

In SQL Server, I can just use 'RETURNS TABLE' and it will do the job. But I can't find how to do the same in Oracle SQL

I have the following SELECT statement that needs to be put in a function or procedure:

SELECT a.CodAcord, a.Descr
FROM FreqSoce f
LEFT JOIN Acord a ON a.CodAcord = f.CodAcord
WHERE f.codSoce = codSoce;

codSoce is an INTEGER IN parameter, and I need to return a.CodAcord and a.Descr as result from my function/procedure.
Is there a simple way to do this? Without having to deal with temp variables and/or advanced content...

EDIT: Aditional info:
- I need to return a.CodAcord and a.Descr, but when I did some research to know how to return more than one variable using SQL Functions or Procedures, all I could find was that this was only possible by returning a TABLE. If there's a way to return more than one item from a Function or Procedure, please let me know.
- The use of Functions or Procedures is strictly required.
- I'm using SQL Developer.

Do you need to return a.CodAcord and a.Descr for a given codScoce, or do you need to return a table? I don't see the connection. Also: do you need a procedure for this, or is the use of bind variables sufficient? What do you use to interface with Oracle: SQL*Plus? Toad? SQL Developer? Each has its means for allowing you to "input" a bind variable and produce the output based on that. Please clarify and we can take it from there. — user5683823
– user5683823, Commented Apr 24, 2016 at 17:13

Daniela Petruzalek · Accepted Answer · 2016-10-26 01:39:20Z

1

You can achieve a table as a return value from function by using a pipelined table function. Please see the example below:

-- Create synthetic case
CREATE TABLE Acord AS
SELECT rownum CodAcord, 'Description ' || rownum Descr
  FROM dual CONNECT BY LEVEL <= 5;

CREATE TABLE FreqSoce AS
SELECT rownum CodSoce, rownum CodAcord
  FROM dual CONNECT BY LEVEL <= 10;

-- Test dataset
SELECT a.CodAcord, a.Descr
  FROM FreqSoce f
  LEFT JOIN Acord a ON a.CodAcord = f.CodAcord
 WHERE f.CodSoce = 10;

-- Here begins actual code
-- Create an object type to hold each table row
CREATE OR REPLACE TYPE typ_acord AS OBJECT(
  CodAcord NUMBER,
  Descr    VARCHAR2(40)
);
/

-- Create a collection type to hold all result set rows
CREATE OR REPLACE TYPE tab_acord AS TABLE OF typ_acord;
/

-- Our function that returns a table
CREATE OR REPLACE FUNCTION getAcord(pCodSoce IN NUMBER)
RETURN tab_acord PIPELINED
AS
BEGIN
  FOR x IN (SELECT a.CodAcord, a.Descr
              FROM FreqSoce f
              LEFT JOIN Acord a ON a.CodAcord = f.CodAcord
             WHERE f.CodSoce = pCodSoce)
  LOOP
    PIPE ROW (typ_acord(x.CodAcord, x.Descr));
  END LOOP;
END;
/

-- Testing the function (please note the TABLE operator)
SELECT * FROM TABLE(getAcord(5));

answered Oct 26, 2016 at 1:39

Daniela Petruzalek

4964 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ulrik Larsen Over a year ago

Best answer, that I have found so far, to this problem. Thnx

collapsar · Accepted Answer · 2016-04-24 18:04:30Z

0

Take the following as a code template:

CREATE OR REPLACE PACKAGE tacord AS
    TYPE ttabAcord IS TABLE OF ACord%ROWTYPE;
END tacord;
/
show err

CREATE OR REPLACE PACKAGE BODY tacord AS
BEGIN
    NULL;
END tacord;
/
show err

CREATE OR REPLACE FUNCTION demo RETURN tacord.ttabAcord AS
    to_return   tacord.ttabAcord;
BEGIN
               SELECT a.*
    BULK COLLECT INTO to_return
                 FROM FreqSoce f
            LEFT JOIN Acord a ON a.CodAcord = f.CodAcord
                WHERE f.codSoce = codSoce
                    ;
    RETURN to_return;
END demo;
/
show err

Key points:

%ROWTYPE represents the datatype of a database table's record
BULK COLLECT INTO inserts a complete result set into a plsql data structure
Iteration over the table contents is availabel by the plsql collection methods, namely .FIRST, .LAST, .NEXT.

edited Apr 24, 2016 at 18:04

answered Apr 24, 2016 at 17:49

collapsar

17.4k5 gold badges40 silver badges66 bronze badges

3 Comments

J. Doeydoey Over a year ago

the code compiles, but executing using SQL Developer returns nothing, is there a special way to execute this?

J. Doeydoey Over a year ago

So what is the common way to execute the procedure?

AndrewMcCoist Over a year ago

In the example above, @collapsar uses a FUNCTION. To use a function in PL/SQL context you have to use the value returned, e.g. assign function result to some variable.

Collectives™ on Stack Overflow

Oracle SQL Create function or procedure returning a table

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related