2.3.20 CREATE FUNCTION

Table of Contents Previous Next


2 The SQL Language : 2.3 SQL Commands : 2.3.20 CREATE FUNCTION

CREATE FUNCTION -- define a new function
CREATE [ OR REPLACE ] FUNCTION name [ (parameters) ]
RETURN data_type
| COST execution_cost
| ROWS result_rows
| SET
configuration_parameter
{ TO
value | = value | FROM CURRENT }
...]
{ IS | AS }
[ declarations ]
statements
END [ name ];
CREATE FUNCTION defines a new function. CREATE OR REPLACE FUNCTION will either create a new function, or replace an existing definition.
To update the definition of an existing function, use CREATE OR REPLACE FUNCTION. It is not possible to change the name or argument types of a function this way (if you tried, you would actually be creating a new, distinct function). Also, CREATE OR REPLACE FUNCTION will not let you change the return type of an existing function. To do that, you must drop and recreate the function.
name is the identifier of the function. If you specify the [OR REPLACE] clause and a function with the same name already exists in the schema, the new function will replace the existing one. If you do not specify [OR REPLACE], the new function will not replace the existing function with the same name in the same schema.
parameters is a list of formal parameters.
data_type is the data type of the value returned by the function’s RETURN statement.
declarations are variable, cursor, type, or subprogram declarations. If subprogram declarations are included, they must be declared after all other variable, cursor, and type declarations.
statements are SPL program statements (the BEGIN - END block may contain an EXCEPTION section).
These attributes inform the query optimizer about the behavior of the function; you can specify only one choice. VOLATILE is the default behavior.
IMMUTABLE indicates that the function cannot modify the database and always reaches the same result when given the same argument values; it does not do database lookups or otherwise use information not directly present in its argument list. If you include this clause, any call of the function with all-constant arguments can be immediately replaced with the function value.
STABLE indicates that the function cannot modify the database, and that within a single table scan, it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for function that depend on database lookups, parameter variables (such as the current time zone), etc.
VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made. Please note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away.
DETERMINISTIC is a synonym for IMMUTABLE. A DETERMINISTIC function cannot modify the database and always reaches the same result when given the same argument values; it does not do database lookups or otherwise use information not directly present in its argument list. If you include this clause, any call of the function with all-constant arguments can be immediately replaced with the function value.
A LEAKPROOK function has no side effects, and reveals no information about the values used to call the function.
CALLED ON NULL INPUT (the default) indicates that the procedure will be called normally when some of its arguments are NULL. It is the author's responsibility to check for NULL values if necessary and respond appropriately.
RETURNS NULL ON NULL INPUT or STRICT indicates that the procedure always returns NULL whenever any of its arguments are NULL. If these clauses are specified, the procedure is not executed when there are NULL arguments; instead a NULL result is assumed automatically.
SECURITY DEFINER specifies that the function will execute with the privileges of the user that created it; this is the default. The key word EXTERNAL is allowed for SQL conformance, but is optional.
The SECURITY INVOKER clause indicates that the function will execute with the privileges of the user that calls it. The key word EXTERNAL is allowed for SQL conformance, but is optional.
The AUTHID DEFINER clause is a synonym for [EXTERNAL] SECURITY DEFINER. If the AUTHID clause is omitted or if AUTHID DEFINER is specified, the rights of the function owner are used to determine access privileges to database objects.
The AUTHID CURRENT_USER clause is a synonym for [EXTERNAL] SECURITY INVOKER. If AUTHID CURRENT_USER is specified, the rights of the current user executing the function are used to determine access privileges.
The PARALLEL clause enables the use of parallel sequential scans (parallel mode). A parallel sequential scan uses multiple workers to scan a relation in parallel during a query in contrast to a serial sequential scan.
When set to UNSAFE, the function cannot be executed in parallel mode. The presence of such a function in a SQL statement forces a serial execution plan. This is the default setting if the PARALLEL clause is omitted.
When set to RESTRICTED, the function can be executed in parallel mode, but the execution is restricted to the parallel group leader. If the qualification for any particular relation has anything that is parallel restricted, that relation won't be chosen for parallelism.
When set to SAFE, the function can be executed in parallel mode with no restriction.
COST execution_cost
execution_cost is a positive number giving the estimated execution cost for the function, in units of cpu_operator_cost. If the function returns a set, this is the cost per returned row. Larger values cause the planner to try to avoid evaluating the function more often than necessary.
ROWS result_rows
result_rows is a positive number giving the estimated number of rows that the planner should expect the function to return. This is only allowed when the function is declared to return a set. The default assumption is 1000 rows.
SET configuration_parameter { TO value | = value | FROM CURRENT }
The SET clause causes the specified configuration parameter to be set to the specified value when the function is entered, and then restored to its prior value when the function exits. SET FROM CURRENT saves the session's current value of the parameter as the value to be applied when the function is entered.
If a SET clause is attached to a function, then the effects of a SET LOCAL command executed inside the function for the same variable are restricted to the function; the configuration parameter's prior value is restored at function exit. An ordinary SET command (without LOCAL) overrides the SET clause, much as it would do for a previous SET LOCAL command, with the effects of such a command persisting after procedure exit, unless the current transaction is rolled back.
Please Note: The STRICT, LEAKPROOF, PARALLEL, COST, ROWS and SET keywords provide extended functionality for Advanced Server and are not supported by Oracle.
The function emp_comp takes two numbers as input and returns a computed value. The SELECT command illustrates use of the function.
Function sal_range returns a count of the number of employees whose salary falls in the specified range. The following anonymous block calls the function a number of times using the arguments’ default values for the first two calls.
The following example demonstrates using the AUTHID CURRENT_USER clause and STRICT keyword in a function declaration:
Include the STRICT keyword to instruct the server to return NULL if any input parameter passed is NULL; if a NULL value is passed, the function will not execute.
The dept_salaries function executes with the privileges of the role that is calling the function. If the current user does not have sufficient privileges to perform the SELECT statement querying the emp table (to display employee salaries), the function will report an error. To instruct the server to use the privileges associated with the role that defined the function, replace the AUTHID CURRENT_USER clause with the AUTHID DEFINER clause.

2 The SQL Language : 2.3 SQL Commands : 2.3.20 CREATE FUNCTION

Table of Contents Previous Next