Oracle7 Server SQL Reference

CREATE FUNCTION

Purpose

To create a user function. A user function or stored 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 the SQL script DBMSSTDX.SQL . The exact name and location of this script may vary depending on your operating system.

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

If you are using Trusted Oracle7 in DBMS MAC mode, you can create a function in another user's schema if your DBMS label dominates the creation label of the other user.

To create a stored function, you must be using Oracle7 with PL/SQL installed. For more information, see PL/SQL User's Guide and Reference.

Syntax

Keywords and Parameters

OR REPLACE

recreates the function if it already exists. You can use this option to change the definition of an existing function without dropping, recreating, and regranting object privileges previously granted on the function. If you redefine a function, Oracle7 recompiles it. For information on recompiling functions, see the ALTER FUNCTION command .

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

schema

is the schema to contain the function. If you omit schema, Oracle7 creates the function in your current schema.

function

is the name of the function to be created.

argument

is the name of an argument to the function. If the function does not accept arguments, you can omit the parentheses following the function name.

specifies that you must supply a value for the argument when calling the function. This is the default.

OUT

specifies the function will set the value of the argument.

IN OUT

specifies that a value for the argument can be supplied by you and may be set by the function.

datatype

is the datatype of an argument. An argument can have any datatype supported by PL/SQL.

The datatype cannot specify a length, precision, or scale. Oracle7 derives the length, precision, or scale of an argument from the environment from which the function is called.

RETURN datatype

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

The datatype cannot specify a length, precision, or scale. Oracle7 derives the length, precision, or scale of the return value from the environment from which the function is called. For information on PL/SQL datatypes, see the PL/SQL User's Guide and Reference.

pl/sql_subprogram_body

is the definition of the function. Function definitions are written in PL/SQL. For information on PL/SQL, including

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.

Usage Notes

A stored function is a set of PL/SQL statements that you can call by name. Functions are very similar to procedures, except that a function explicitly returns a value to its calling environment. For a general discussion of procedures and functions, see the CREATE PROCEDURE command

The CREATE FUNCTION command creates a function as a stand-alone schema object. You can also create a function as part of a package. For information on creating packages, see the CREATE PACKAGE command .

When you create a stored function in Trusted Oracle7, it is labeled with your DBMS label.

Example

The following statement creates the function GET_BAL:

CREATE FUNCTION get_bal(acc_no IN NUMBER)

	RETURN NUMBER

IS

		acc_bal NUMBER(11,2);

	BEGIN

		SELECT balance

			INTO acc_bal

			FROM accounts

			WHERE account_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 datatype of ACC_NO is NUMBER.

The function returns the account balance. The RETURN clause of the CREATE FUNCTION statement specifies the datatype 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 ACCOUNTS table. The function uses a RETURN statement to return this value to the environment in which the function is called.

The above function can be used in a SQL statement. For example: