Name

CREATE FUNCTION

Synopsis

CREATE [OR REPLACE] FUNCTION
[schema.]function_name
[(argument [IN]
datatype[,argument [IN]
datatype.)]
RETURN datatype {IS | AS}
pl/sql_function_code

Creates a standalone function ( function_name).

Keywords

OR REPLACE

Specifies that if the function exists, it is to be replaced.

argument

Specifies the name of an argument to the function.

IN

Specifies that a value must be supplied for the argument when calling the function.

datatype

Specifies the datatype of the argument; this can be any PL/SQL datatype.

RETURN

Specifies the datatype (any PL/SQL datatype) of the function code ( pl/sql_function_code). You must specify either the IS or AS keyword.

pl/sql_function_code

Specifies the function code, written in PL/SQL.

Notes

This command creates a function as a standalone object in the specified (or default) schema. To include the function in a package, see the CREATE PACKAGE command. You must have the CREATE PROCEDURE or CREATE ANY PROCEDURE privilege to issue this command.

Example

The following example creates a function named get_balance:

CREATE function get_balance (acct VARCHAR2)
     RETURN NUMBER;
     AS
          acct_balance  NUMBER (12,2);
     BEGIN
          SELECT balance
          INTO acct_balance
          FROM account_master
          WHERE account_no = acct;
     RETURN acct_balance;
END;
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset