16.3.3 The end Descriptor

You can append the name of the function directly after the END keyword when you complete your function, as shown here:

FUNCTION tot_sales (company_in IN INTEGER) RETURN NUMBER

IS

BEGIN

...

END tot_sales;

This name serves as a label that explicitly links the end of the program with its beginning. You should, as a matter of habit, use an END descriptor. It is especially important to do so when you have a function that spans more than a single page or that is one in a series of functions and procedures in a package body.

16.3.4 Calling a Function

A function is called as part of an executable PL/SQL statement wherever an expression can be used. The following examples illustrate how the various functions defined in Section 16.3.2 can be invoked.

• Assign the default value of a variable with a function call:

• DECLARE

• v_nickname VARCHAR2(100) :=

favorite_nickname ('Steven');

• Use a member function for the pet object type in a conditional expression:

• DECLARE

• my_parrot pet_t :=

• pet_t (1001, 'Mercury', 'African Grey',

• TO_DATE ('09/23/1996', 'MM/DD/YYYY'));

• BEGIN

• IF my_parrot.age < INTERVAL '50' YEAR -- 9i INTERVAL type

• THEN

• DBMS_OUTPUT.PUT_LINE ('Still a youngster!');

END IF;

• Retrieve a single row of book information directly into a record:

• DECLARE

• my_first_book books%ROWTYPE;

• BEGIN

• my_first_book := book_info.onerow ('1-56592-335-9');

...

• Obtain a cursor variable to overdue book information for a specific user:

• DECLARE

• my_overdue_info overdue_rct;

• BEGIN

• my_overdue_info :=

• book_info.overdue_info ('STEVEN_FEUERSTEIN');

...

• Call a function of one's own making from within a CREATE VIEW statement, utilizing a CURSOR expression to pass a result set as an argument to that function:

• CREATE OR REPLACE VIEW young_managers AS

• SELECT managers.employee_id manager_employee_id

• FROM employees managers

• WHERE Most_Reports_Before_Manager

• (

• CURSOR ( SELECT reports.hire_date FROM employees reports

• WHERE reports.manager_id = managers.employee_id

• ),

• managers.hire_date

) = 1;

16.3.5 Functions Without Parameters

If a function has no parameters, the function call is written without parentheses. The following code illustrates this with a call to a method named "age" of the pet_t object type:

IF my_parrot.age < INTERVAL '50' YEAR -- 9i INTERVAL type

In Oracle8i and later, you can also include empty open and close parentheses, as in:

IF my_parrot.age( ) < INTERVAL '50' YEAR

16.3.6 The Function Header

The portion of the function definition that comes before the IS keyword is called the function header. The header provides all the information a programmer needs to call that function, namely:

• The function name

• Modifiers to the definition and behavior of the function (e.g., is it deterministic? Does it run in parallel execution? Is it pipelined?)

• The parameter list, if any

• The RETURN datatype

A programmer should not need to look at the inside of the function (its body) in order to be able to call it properly from another program.

The header for the tot_sales function discussed earlier is:

FUNCTION tot_sales

(company_id_in IN company.company_id%TYPE,

status_in IN order.status_code%TYPE := NULL)

RETURN NUMBER

It consists of the module type, the name, a list of two parameters, and a RETURN datatype of NUMBER. This means that any PL/SQL statement or expression that references a numeric value can make a call to tot_sales to obtain that value. Here is one such statement:

DECLARE

v_sales NUMBER;

BEGIN

v_sales := tot_sales (1505, 'ACTIVE');

...