RE: A Question on the Basics of PL/SQL

Functions are used when when a "value" must be returned. The quotes are around value because the return type is not necessarily scalar. Under most circumstances a function only returns one value. I believe in sticking to that rule, although Oracle does not always:

Here's a line from the package header for dbms_standard:

function dictionary_obj_name_list (object_list out ora_name_list_t)

                return binary_integer; 

The function returns both a binary integer and a table of varchar2(64)'s.

Functions are called in pl/sql using the following format <variable> := function_name(<parameter_list>);
The parameters are nearly always "in" types. Functions which return a datatype which can be handled by sqlplus and meet
the restrict_references criteria can be invoked from sqlplus thus

select <function_name>(<column_name>) from <table_name>.

Procedures are used when you want more than one value to be returned or no values at all.

Packages are collections of procedures, functions, variable definitons, type definitions etc. It is a good idea to place your functions and procedures in a package.

A cursor is how Oracle, a set based database communicates with record oriented programming languages. They are not really related to functions, procedures, or packages.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu    

-----Original Message-----
Sent: Thursday, June 28, 2001 2:17 PM
To: Multiple recipients of list ORACLE-L

I am a bit confused as to when to use the following. I cannot seem to find any definitive rules.

• function
• cursor
• procedure
• package

If someone can point me to a white paper, or whatever, that defines the rules for their usage I would be most thankful.

Thanks,
Ken Janusz, CPIM

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: kjanusz_at_att.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).