December 2002
Intermediate to advanced
928 pages
85h 29m
English
CREATE [OR REPLACE] FUNCTIONname[ (parameter[,parameter]) ] RETURNreturn_datatype[AUTHID {CURRENT_USER | DEFINER} ] [DETERMINISTIC] [PARALLEL_ENABLED] [PIPELINED] [AGGREGATE USING] {IS | AS} [declaration_section] BEGINexecutable_section[EXCEPTIONexception_section] END [name];
Program unit, or module, that executes zero or more statements and that returns a value through the RETURN clause. Like procedures, functions can also receive or return zero or more values through their parameter lists. A function must have at least one RETURN statement in the execution section. The RETURN clause in the function header specifies the datatype of the returned value.
A function can be called anywhere an expression of the same type can be used. You can call a function as follows:
In an assignment statement:
sales95 := tot_sales(1995,'C');
To set a default value:
DECLARE sales95 NUMBER DEFAULT tot_sales(1995,'C'); BEGIN
In a Boolean expression:
IF tot_sales(1995,'C') > 10000 THEN ...
In a SQL statement:
SELECT first_name ,surname FROM sellers WHERE tot_sales(1995,'C') > 1000;
As an argument in another program unit’s parameter list.
Common keywords: AUTHID, DETERMINISTIC, PARALLEL_ENABLE AGGREGATE USING, and PIPELINED.