Re: Newbie Question: Procedures vs. Functions

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Wed, 29 Jul 1998 17:39:14 GMT
Message-ID: <01bdbb27$968ec6e0$a504fa80@mndnet>

Idea behind a function is to return a single value.

      create or replace function  get_sysdate
      return date is
      BEGIN
      /*  **************  Reference from LIBRARY form LIB  ************* 
*/
      /*
         This function retrieves today's date.  It has time element in it
also.
      */
      DECLARE
         todays_date date;
         cursor   c1 is
         select   sysdate
           from   dual;
      BEGIN
         open     c1;
         fetch    c1
          into    todays_date;
         close    c1;
         return(todays_date);
      END;
      END;

/

The use would be something like if called from another procedure or function
todays_date_time := get_sysdate;

where todays_date_time is a PL/SQL date type variable.

---

---

With procedures you can input and output/return several values.

create or replace procedure       get_session_info
               (user_id                 in out  varchar2,
                todays_date             in out  date,
                oracle_session_id       in out  varchar2 )

is
BEGIN
/*

   This procedure retrieves the Oracle user(id), sysdate and Oracle    sessionid into the output parameters. */

   BEGIN                                -- BLOCK 1
      select   user, sysdate, userenv('sessionid')
        into   user_id, todays_date, oracle_session_id
        from   dual;
      --
      dbms_output.put_line('User: '||user_id||
         '          Today''s Datetime: '||
         to_char(todays_date,'dd-MON-yyyy hh24:mi:ss'));
      dbms_output.put_line('Oracle Session ID: '||oracle_session_id );
   EXCEPTION
      when others then
         null;
         /*
            Do not expect an EXCEPTION in this procedure. Safety feature.
         */
   END;                                 -- BLOCK 1
END;    -- of procedure                 get_session_info

/

Usage would be something like, if called in another procedure or function where the parameters are the PL/SQL variable declared in that othe rprocedure or function.

   get_session_info
      ( global_update_user_id,
        global_update_date,
        temp_varchar21
      );

In SQL*Plus you can execute a procedure or function if it does not have any parameters passed to it,
or returned(procedures) from it. For example if the procedure simply updates a table etc.

create or replace procedure update1
BEGIN
   update table1 set .......
END; In SQL*Plus, you can simply say

execute update1;

Good luck !!!

suresh.bhat_at_mitchell-energy.com

Scott C. <sdcairns_at_mindspring.com> wrote in article <6pksok$t2n$1_at_camel0.mindspring.com>... > What's the difference between a PROCEDURE and a FUNCTION? Where would I use

> one versus the other?
> 
> 
> 

Received on Wed Jul 29 1998 - 12:39:14 CDT