CREATE FUNCTION

Create a stand-alone function or a call spec.

Syntax:

   CREATE [OR REPLACE] FUNCTION [schema.]function [arguments_clause]
      RETURN datatype [invoke_clause]
         [PIPELINED] AS plsql_function_body

   CREATE [OR REPLACE] FUNCTION [schema.]function [arguments_clause]
      RETURN datatype [invoke_clause]
         [PIPELINED | AGGREGATE] USING schema.implementation_type

   CREATE [OR REPLACE] FUNCTION [schema.]function [arguments_clause]
      RETURN datatype [invoke_clause]
         AS LANGUAGE JAVA NAME 'string'

   CREATE [OR REPLACE] FUNCTION [schema.]function [arguments_clause]
      RETURN datatype [invoke_clause]
         AS LANGUAGE C [NAME name] LIBRARY lib_name [WITH CONTEXT][PARAMETERS params]

arguments_clause:
   (argument [IN|OUT|IN OUT] [NOCOPY datatype])

invoke_clause: 
any combination of…
   AUTHID CURRENT_USER
   AUTHID DEFINER
   DETERMINISTIC
   PARALLEL_ENABLE parallel_clause

parallel_clause:
   (PARTITION argument BY ANY) [{ORDER|CLUSTER} BY (column1,column2..)]

   (PARTITION argument BY {HASH|RANGE}(column1,column2..)) [{ORDER|CLUSTER} BY (column1,column2..)]

The AUTHID clause lets you specify whether the function executes with the privileges and in the schema of the user who owns it or with the privileges and in the schema of CURRENT_USER.

DETERMINISTIC indicates that the function should returns the same result value whenever it is called with the same argument values.

AGGREGATE USING will identify the function as an aggregate function, that evaluates a group of rows and returns a single row.

PIPELINED will instruct Oracle to return the results of a table function iteratively.
Table functions require the TABLE keyword before the function name in the query FROM clause.
For example: Select * from TABLE( My_Function(…))

A table function returns a collection type (a nested table or varray).

"All things are created twice. There's a mental or first creation, and a physical or second creation of all things. You have to make sure that the blueprint, the first creation, is really what you want" - Stephen Covey, (7 Habits)

Related Oracle Commands:

FUNCTION - ALTER FUNCTION
FUNCTION - DROP FUNCTION
PACKAGE - CREATE PACKAGE
LIBRARY - CREATE LIBRARY

Related Views:

 CODE_PIECES 
 CODE_SIZE 
 DBA_SOURCE      ALL_SOURCE      USER_SOURCE