CREATE FUNCTION Statement

The CREATE FUNCTION statement creates or replaces a standalone stored function or a call specification.

A standalone stored function is a function (a subprogram that returns a single value) that is stored in the database.

A call specification declares a Java method or a third-generation language (3GL) routine so that it can be called from PL/SQL. You can also use the SQL CALL statement to call such a method or routine. The call specification tells the database which Java method, or which named function in which shared library, to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value.

Prerequisites

To create or replace a standalone stored function in your own schema, you must have the CREATE PROCEDURE system privilege. To create or replace a standalone stored function in another user's schema, you must have the CREATE ANY PROCEDURE system privilege.

To invoke a call specification, you may need additional privileges, for example, EXECUTE privileges on a C library for a C call specification.

To embed a CREATE FUNCTION statement inside an Oracle precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.

Syntax

create_function ::=

(parameter_declaration ::=, datatype ::=, result_cache_clause ::=, declare_section ::=, body ::=)

invoker_rights_clause ::=

parallel_enable_clause ::=

streaming_clause ::=

call_spec ::=

Java_declaration ::=

C_declaration ::=

Keyword and Parameter Descriptions

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, re-creating, and regranting object privileges previously granted on the function. If you redefine a function, then the database recompiles it.

Users who had previously been granted privileges on a redefined function can still access the function without being regranted the privileges.

If any function-based indexes depend on the function, then the database marks the indexes DISABLED.

schema

Specify the schema to contain the function. If you omit schema, then the database creates the function in your current schema.

function_name

Specify the name of the function to be created.

RETURN datatype

For datatype, specify the data type of the return value of the function. The return value can have any data type supported by PL/SQL.

The data type cannot specify a length, precision, or scale. The database derives the length, precision, or scale of the return value from the environment from which the function is called.

If the return type is ANYDATASET and you intend to use the function in the FROM clause of a query, then you must also specify the PIPELINED clause and define a describe method (ODCITableDescribe) as part of the implementation type of the function.

You cannot constrain this data type (with NOT NULL, for example).

invoker_rights_clause

Specifies the AUTHID property of the member functions and procedures of the object type. For information about the AUTHID property, see "Using Invoker's Rights or Definer's Rights (AUTHID Clause)".

AUTHID Clause

• Specify CURRENT_USER if you want the function to execute with the privileges of CURRENT_USER. This clause creates an invoker's rights function.

  This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the function resides.

• Specify DEFINER if you want the function to execute with the privileges of the owner of the schema in which the function resides, and that external names resolve in the schema where the function resides. This is the default and creates a definer's rights function.

DETERMINISTIC

Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its parameters.

You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE. When the database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function. If you subsequently change the semantics of the function, then you must manually rebuild all dependent function-based indexes and materialized views.

Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function. The results of doing so will not be captured if the database chooses not to reexecute the function.

The following semantic rules govern the use of the DETERMINISTIC clause:

• You can declare a top-level subprogram DETERMINISTIC.

• You can declare a package-level subprogram DETERMINISTIC in the package specification but not in the package body.

• You cannot declare DETERMINISTIC a private subprogram (declared inside another subprogram or inside a package body).

• A DETERMINISTIC subprogram can call another subprogram whether the called program is declared DETERMINISTIC or not.

parallel_enable_clause

PARALLEL_ENABLE is an optimization hint indicating that the function can be executed from a parallel execution server of a parallel query operation. The function should not use session state, such as package variables, as those variables are not necessarily shared among the parallel execution servers.

• The optional PARTITION argument BY clause is used only with functions that have a REF CURSOR argument type. It lets you define the partitioning of the inputs to the function from the REF CURSOR argument.

  Partitioning the inputs to the function affects the way the query is parallelized when the function is used as a table function in the FROM clause of the query. ANY indicates that the data can be partitioned randomly among the parallel execution servers. Alternatively, you can specify RANGE or HASH partitioning on a specified column list.

• The optional streaming_clause lets you order or cluster the parallel processing by a specified column list.

  • ORDER BY indicates that the rows on a parallel execution server must be locally ordered.

  • CLUSTER BY indicates that the rows on a parallel execution server must have the same key values as specified by the column_list.

  • expr identifies the REF CURSOR parameter name of the table function on which partitioning was specified, and on whose columns you are specifying ordering or clustering for each slave in a parallel query execution.

The columns specified in all of these optional clauses refer to columns that are returned by the REF CURSOR argument of the function.

PIPELINED { IS | USING }

Specify PIPELINED to instruct the database to return the results of a table function iteratively. A table function returns a collection type (a nested table or varray). You query table functions by using the TABLE keyword before the function name in the FROM clause of the query. For example:

SELECT * FROM TABLE(function_name(...))

the database then returns rows as they are produced by the function.

• If you specify the keyword PIPELINED alone (PIPELINED IS ...), then the PL/SQL function body should use the PIPE keyword. This keyword instructs the database to return single elements of the collection out of the function, instead of returning the whole collection as a single value.

• You can specify the PIPELINED USING implementation_type clause if you want to predefine an interface containing the start, fetch, and close operations. The implementation type must implement the ODCITable interface and must exist at the time the table function is created. This clause is useful for table functions that will be implemented in external languages such as C++ and Java.

  If the return type of the function is ANYDATASET, then you must also define a describe method (ODCITableDescribe) as part of the implementation type of the function.

AGGREGATE USING

Specify AGGREGATE USING to identify this function as an aggregate function, or one that evaluates a group of rows and returns a single row. You can specify aggregate functions in the select list, HAVING clause, and ORDER BY clause.

When you specify a user-defined aggregate function in a query, you can treat it as an analytic function (one that operates on a query result set). To do so, use the OVER analytic_clause syntax available for built-in analytic functions. See Oracle Database SQL Language Reference for syntax and semantics of analytic functions.

In the USING clause, specify the name of the implementation type of the function. The implementation type must be an object type containing the implementation of the ODCIAggregate routines. If you do not specify schema, then the database assumes that the implementation type is in your own schema.

Restriction on Creating Aggregate Functions If you specify this clause, then you can specify only one input argument for the function.

body

The required executable part of the function and, optionally, the exception-handling part of the function.

declare_section

The optional declarative part of the function. Declarations are local to the function, can be referenced in body, and cease to exist when the function completes execution.

call_spec

Use the call_spec to map a Java or C method name, parameter types, and return type to their SQL counterparts. In Java_declaration, 'string' identifies the Java implementation of the method.

EXTERNAL

In earlier releases, EXTERNAL was an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle recommends that you use the LANGUAGE C syntax.

Examples

Creating a Function: Examples The following statement creates the function get_bal on the sample table oe.orders:

CREATE FUNCTION get_bal(acc_no IN NUMBER) 
   RETURN NUMBER 
   IS acc_bal NUMBER(11,2);
   BEGIN 
      SELECT order_total 
      INTO acc_bal 
      FROM orders 
      WHERE customer_id = acc_no; 
      RETURN(acc_bal); 
    END;
/

The get_bal function returns the balance of a specified account.

When you call the function, you must specify the argument acc_no, the number of the account whose balance is sought. The data type of acc_no is NUMBER.

The function returns the account balance. The RETURN clause of the CREATE FUNCTION statement specifies the data type of the return value to be NUMBER.

The function uses a SELECT statement to select the balance column from the row identified by the argument acc_no in the orders table. The function uses a RETURN statement to return this value to the environment in which the function is called.

The function created in the preceding example can be used in a SQL statement. For example:

SELECT get_bal(165) FROM DUAL; 

GET_BAL(165)
------------
        2519

The hypothetical following statement creates a PL/SQL standalone function get_val that registers the C routine c_get_val as an external function. (The parameters have been omitted from this example.)

CREATE FUNCTION get_val
   ( x_val IN NUMBER,
    y_val IN NUMBER,
    image IN LONG RAW )
   RETURN BINARY_INTEGER AS LANGUAGE C
      NAME "c_get_val"
      LIBRARY c_utils
      PARAMETERS (...);

Creating Aggregate Functions: Example The next statement creates an aggregate function called SecondMax to aggregate over number values. It assumes that the object type SecondMaxImpl routines contains the implementations of the ODCIAggregate routines:

CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER
    PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;

You would use such an aggregate function in a query like the following statement, which queries the sample table hr.employees:

SELECT SecondMax(salary) "SecondMax", department_id
      FROM employees
      GROUP BY department_id
      HAVING SecondMax(salary) > 9000
      ORDER BY "SecondMax", department_id;

SecondMax DEPARTMENT_ID
--------- -------------
    13500            80
    17000            90

Using a Packaged Procedure in a Function: Example The following statement creates a function that uses a DBMS_LOB.GETLENGTH procedure to return the length of a CLOB column:

CREATE OR REPLACE FUNCTION text_length(a CLOB) 
   RETURN NUMBER DETERMINISTIC IS
BEGIN 
  RETURN DBMS_LOB.GETLENGTH(a);
END;

Related Topics

• ALTER FUNCTION Statement

• CREATE PROCEDURE Statement

• DROP FUNCTION Statement

• Function Declaration and Definition for information about creating a function in a PL/SQL block

• Parameter Declaration

• Chapter 8, "Using PL/SQL Subprograms"