Re: Beginner's question on Oracle stored procedure/function---HELP!!

From: John Chiu <johnymc_at_netscape.net>
Date: Sat, 4 Dec 1999 15:42:57 -0500
Message-ID: <82bu7i$66g$1@news2.tor.accglobal.net>

Dana Jian <djian_at_trasa.com> wrote in message news:384822d9$0$80466_at_news.choice.net...
> Hello,
>
> I'm a new user of PL/SQL in Oracle8, I have two questions as following:
>
> 1) Can function in Oracle server return more than one value? how?
>
> I was trying to use OUT type of parameters in a function on the Oracle
> server, but I got NULL(VB program as front-end) returned from that
function.
>
> Anyone knows the reason?

yes. you can defined a type as record which contains a group of datatypes.

declare
  type return_values is record (retval_1 number, retval2 number,.........);   function RETVAL (inputval number) return return_values is

          return_record return_values;
  begin

      ...... your coding here
      ...... make up a return record here
      return return_record;

  end;

>
> 2) Can function in Oracle server return a recordset--multiple rows with
> multiple columns? How??
>

yes.

by defining a cursor variable and return it from a function:

type resultset_cur_type is ref cursor return your_table%rowtype; function RETCUR (inputval number) return resultset_cur_type is

          return_cur resultset_cur_type;   begin

      open return_cur for
            select ....... from your_table where .....;
            return return_cur;

  end;

this function will return a cursor variable and you can use fetch in a loop to get individual row in the resultset.

HTH John Chiu

> I really appreciate the help!!!
>
> Dana Jian
> dan_jian_at_hotmail.com
>
>
Received on Sat Dec 04 1999 - 14:42:57 CST