Returning multiple values from a function in oracle

Question

I have the following table:

T_TYPE_ID T_TYPE     T_TYPE_PRICE T_TYPE_START_DATE   T_TYPE_END_DATE    
   1      student       10.95     01.04.2015 00:00:00 30.06.2015 00:00:00
   2      Concession    5.5       01.04.2015 00:00:00 30.06.2015 00:00:00

I need to: Create FUNC_get_ t_type_end_date function This function should contain the following input parameter: t_type_p and the following output parameters: t_type_price_p and t_type_end_date_p. It should return 1 if having a record with t_type as t_type_p otherwise return 0. In addition, in the case of having a record, it should assign the latest t_type_end_date to t_type_end_date_p and t_type_price to t_type_price_p. Please note that t_type_end_date_p can be null; it means that the associated price is currently valid.

I have written the following code:

CREATE OR REPLACE FUNCTION FUNC_get_t_type_end_date
  ( t_type_p IN VARCHAR2)
RETURN NUMBER
    AS
    cnum NUMBER;

    CURSOR cr1 IS
    SELECT t_type
      FROM ticket_type
      WHERE t_type = t_type_p;

  BEGIN
  OPEN cr1;
    FETCH cr1 INTO cnum;
    IF cr1%NOTFOUND THEN
      cnum := 0;
      END IF;
      CLOSE cr1;

     RETURN cnum;
   END;

I did not get any clue on how to return multiple values from a function. I am using oracle.

Hint:

create or replace FUNCTION FUNC_get_ t_type_end_date (…)
return number 
as   
    -- define a variable to return a number and assign 0 to it
    -- define a cursor to obtain t_type_price, t_type_end_date of the given t_type_p. The t_type_end_date values should be sorted in descending order – to do so the first record will contain either null or the latest of t_type_end_date
BEGIN
  -- open cursor
  -- fetch the first record from the cursor to t_type_price_p and t_type_end_date_p
  -- if (having a record) then …  
  -- close cursor
  RETURN …
END;

may be instead of function you can use procedure with `out` parameters — , Jun 23 '15 at 05:53
Hate that name prefix thing you have going on there. That's a lot of extra characters everyone ends up having to type. — David Aldridge, Jun 23 '15 at 06:53
Oh also, that explicit cursor methodology is slow, harder to write, harder to maintain, and therefore more error prone. — David Aldridge, Jun 23 '15 at 06:56

Hawk · Answer 1 · 2015-06-23T05:10:38.760

4

It is not possible to return more than one variable from function. However, it is possible to return a customized variable (i.e. record) type that contains multiple values. To do this, you need first to define type contains the three variables you want to return as follows:

TYPE new_type is record(cnum number, t_type_end_date_p timestamp, t_type_price_p  timestamp);

Then you can use it in your function as follows:

CREATE OR REPLACE FUNCTION FUNC_get_t_type_end_date  ( t_type_p IN VARCHAR2)
RETURN new_type AS 

new_type_variable newtype;

CURSOR cr1 IS
SELECT t_type
  FROM ticket_type
  WHERE t_type = t_type_p;

BEGIN
OPEN cr1;
FETCH cr1 INTO cnum;
IF cr1%NOTFOUND THEN
  SELECT 0, null, null into new_type_variable from dual;
ELSE
  SELECT 1, cr1.t_type_end_date, cr1.t_type_price into new_type_variable from dual;
END IF;
  CLOSE cr1;

 RETURN new_type_variable ;
END;

edited Jun 23 '15 at 05:10

answered Jun 23 '15 at 04:43

Hawk

5,060
12
49
74

I don't know what you are trying to do. Please read the question properly. – Binary Terror Jun 23 '15 at 04:49
2

@user3803989 I'm trying to help you. I did read the question properly. If there is something does match, clarify it – Hawk Jun 23 '15 at 05:12
3

@user380989 this is the correct answer. Return a record type. Or use out parameters. – Patrick Hofman Jun 23 '15 at 05:29

ksa · Accepted Answer · 2015-06-23T06:16:58.360

2

Try this:

create or replace function func_get_t_type_end_date(t_type_p in varchar2, t_type_price_p out number, t_type_end_date_p out date) return number
    as
        cnum NUMBER;

    CURSOR cr1 IS
    SELECT t_type_price,t_type_end_date
    FROM ticket_type t
    WHERE t_type = t_type_p
        and not exists (select 1
                                  from ticket_type t2
                                  where t2.t_type = t.t_type
                                  and t2.t_type_end_date>t.t_type_end_date);

  BEGIN
  OPEN cr1;
    FETCH cr1 INTO t_type_price_p,t_type_end_date_p;
    IF cr1%NOTFOUND THEN
      cnum := 0;
            else
            cnum := 1;
      END IF;
      CLOSE cr1;

     RETURN cnum;   end;

Example of using:

set serveroutput on
declare
v_result number;
v_type_price_p number;
v_type_end_date_p date;
begin
    v_result:=func_get_t_type_end_date(t_type_p=>'student',t_type_price_p=>v_type_price_p,t_type_end_date_p=>v_type_end_date_p);
    dbms_output.put_line('Result: '||v_result);
    dbms_output.put_line('Price: '||v_type_price_p);
    dbms_output.put_line('End date: '||v_type_end_date_p);        
end;
/

edited Jun 23 '15 at 06:16

answered Jun 23 '15 at 05:14

ksa

403
1
5
15

I need to use a cursor to retieve the values. – Binary Terror Jun 23 '15 at 05:26
What should I do to call the fucntion? Should I pass only the t_type or the other variables as well? – Binary Terror Jun 23 '15 at 05:42
You must specified all parameters. See example in the answer. – ksa Jun 23 '15 at 05:46
When I tried to print the output using v_type_price_p in the execution block, it says variable is not declared. – Binary Terror Jun 23 '15 at 06:07
See the updated example of using. Probably, you have mistyped in printing. – ksa Jun 23 '15 at 06:19

Returning multiple values from a function in oracle

2 Answers2