RelationalDBDesign 




User-Defined Functions «Prev  Next»
Lesson 3Syntax of a function
ObjectiveIdentify the Correct Syntax of an Oracle Function

Syntax for Oracle Stored Function

The basic syntax of a stored function is shown in the following MouseOver.
Mouse over the keyword CREATE in the diagram below to receive additional information.
CREATE [OR REPLACE] FUNCTION function

Stored Function Syntax
The stored function has a basic structure that can be built into complex code if needed. Just include the required sections and add more items in the DECLARE section and more logic in the body section.
A stored function cannot return a LONG value.


CREATE FUNCTION

Purpose: Functions are defined using PL/SQL. Therefore, this section provides some general information but refers to Oracle Database PL/SQL Language Reference for details of syntax and semantics. Use the
CREATE FUNCTION 
statement to create a standalone stored function or a call specification.
  1. 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.
  2. 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 CALL SQL statement to call such a method or routine. The call specification tells Oracle 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.
Note: You can also create a function as part of a package using the CREATE PACKAGE statement.

ENABLE | DISABLE COMMIT IN PROCEDURE

Procedures and stored functions written in PL/SQL can issue COMMIT and ROLLBACK statements. If your application would be disrupted by a COMMIT or ROLLBACK statement not issued directly by the application itself, then specify DISABLE COMMIT IN PROCEDURE clause to prevent procedures and stored functions called during your session from issuing these statements.
You can subsequently allow procedures and stored functions to issue COMMIT and ROLLBACK statements in your session by issuing the ENABLE COMMIT IN PROCEDURE. Some applications automatically prohibit COMMIT and ROLLBACK statements in procedures and stored functions. Refer to your application documentation for more information.
The next lesson gives you a chance to create your own stored function.