Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
Release 11.2.1

Part Number E13070-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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:

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.

See also

Oracle Database PL/SQL Language Reference and Oracle Database SQL Language Reference