CREATE FUNCTION

The CREATE FUNCTION statement creates a standalone stored function.

Required privilege

CREATE PROCEDURE (if owner) or CREATE ANY PROCEDURE (if not owner)

SQL syntax

CREATE [OR REPLACE] FUNCTION [Owner.]FunctionName 
     [(argument [IN|OUT|IN OUT][NOCOPY] datatype [DEFAULT expr][,...])]
     RETURN datatype [invoker_rights_clause] [DETERMINISTIC]
     {IS|AS} plsql_function_body

The syntax for the invoker_rights_clause:

AUTHID {CURRENT_USER|DEFINER}

You can specify invoker_rights_clause or DETERMINISTIC in any order.

Parameters

The CREATE FUNCTION statement has the parameters:

Parameter	Description
`OR REPLACE`	Specify `OR REPLACE` to re-create the function if it already exists. Use this clause to change the definition of an existing function without dropping and re-creating it. When you re-create a function, TimesTen recompiles it.
`FunctionName`	Name of function.
`argument`	Name of argument or parameter. You can specify 0 or more parameters for the function. If you specify a parameter, you must specify a data type for the parameter. The data type must be a PL/SQL data type. For more information on PL/SQL data types, see Oracle Database PL/SQL Language Reference.
`IN\| OUT \|IN OUT`	Parameter modes. `IN` is a read-only parameter. You can pass the parameter's value into the function but the function cannot pass the parameter's value out of the function and back to the calling PL/SQL block.The value of the parameter cannot be changed. `OUT` is a write-only parameter. Use an `OUT` parameter to pass a value back from the function to the calling PL/SQL block. You can assign a value to the parameter. `IN OUT` is a read/write parameter. You can pass values into the function and return a value back to the calling program (either the original, unchanged value or a new value set within the function. `IN` is the default.
`NOCOPY`	Specify `NOCOPY` to instruct TimesTen to pass the parameter as fast as possible. You can enhance performance when passing a large value such as a record, an index-by-table, or a varray to an OUT or IN OUT parameter. IN parameters are always passed `NOCOPY`. For more information on `NOCOPY`, see Oracle Database SQL Language Reference.
`DEFAULT` `expr`	Use this clause to specify a default value for the parameter. You can specify `:=` in place of the keyword `DEFAULT`.
`RETURN` `datatype`	Required clause. A function must return a value. You must specify the data type of the return value of the function. Do not specify a length, precision, or scale for the data type. The data type is a PL/SQL data type. For more information on PL/SQL data types, see Oracle Database PL/SQL Language Reference.
`invoker_rights_clause`	Lets you specify whether the function executes with the privileges of the user who owns it or with the privileges of the `CURRENT_USER`. Specify `CURRENT_USER` to indicate that the function executes with the privileges of the `CURRENT_USER`. Specify `DEFINER` to indicate that the function executes with the privileges of the owner of the function. `DEFINER` is the default. For more information, see Oracle Database SQL Language Reference.
`DETERMINISTIC`	Specify `DETERMINISTIC` to indicate that the function should return the same result value whenever it is called with the same values for its parameters. For more information on the `DETERMNISTIC` clause, see Oracle Database SQL Language Reference.
`IS\|AS`	Specify either `IS` or `AS` to declare the body of the function.
`plsql_function_spec`	Specifies the function body.

Restrictions

TimesTen does not support:

parallel_enable_clause. You can specify the clause, but it has no effect.
call_spec clause
AS EXTERNAL

The CREATE FUNCTION statement is not replicated.

When you create or replace a function, the privileges granted on the function remain the same. If you drop and re-create the object, the object privileges that were granted on the original object are revoked.

Examples

Create function get_sal with one input parameter. Return salary as type NUMBER.

Command> CREATE OR REPLACE FUNCTION get_sal
       >   (p_id employees.employee_id%TYPE) RETURN NUMBER IS
       >   v_sal employees.salary%TYPE := 0;
       > BEGIN
       >   SELECT salary INTO v_sal FROM employees
       >     WHERE employee_id = p_id;
       >   RETURN v_sal;
       > END get_sal;
       > /
 
Function created.