Oracle CREATE FUNCTION

Use the CREATE FUNCTION statement to create a standalone stored function or a call specification.

A stored function (also called a user function or user-defined function) is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression.

Prerequisites

Before a stored function can be created, the user SYS must run a SQL script that is commonly called DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.

To create a function in your own schema, you must have the CREATE PROCEDURE system privilege. To create a function in another user's schema, you must have the CREATE ANY PROCEDURE system privilege. To replace a function in another user's schema, you must have the ALTER 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.

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 Oracle 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 Oracle Database marks the indexes DISABLED.

Schema

Specify the schema to contain the function. If you omit schema, Oracle Database creates the function in your current schema.

PARAMETERS

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared

1. IN - Specify IN to indicate that you must supply a value for the argument when calling the procedure.
2. OUT - Specify OUT to indicate that the procedure passes a value for this argument back to its calling environment after execution.
3. IN OUT - Specify IN OUT to indicate that you must supply a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution.

If you do not specify IN, OUT, and IN OUT, then the argument defaults to IN.

RETURN Clause

For datatype, specify the datatype of the return value of the function. Because every function must return a value, this clause is required. The return value can have any datatype supported by PL/SQL.

Examples:

The Syntax for a Function is:

CREATE [OR REPLACE] FUNCTION function_name
   [ (parameter [,parameter]) ]

   RETURN return_datatype

IS | AS

   [declaration_section]

BEGIN
   executable_section

[EXCEPTION
   exception_section]

END [function_name];

The following is a simple example of a function:

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
   cnumber number;

   cursor c1 is
   select course_number
     from courses_tbl
     where course_name = name_in;

BEGIN

   open c1;
   fetch c1 into cnumber;

   if c1%notfound then
      cnumber := 9999;
   end if;

   close c1;

RETURN cnumber;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

This function is called FindCourse. It has one parameter called name_in and it returns a number. The function will return the course number if it finds a match based on course name. Otherwise, it returns a 99999.

You could then reference your new function in an SQL statement as follows:

select course_name, FindCourse(course_name) as course_id
from courses
where subject = 'Mathematics';