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
CREATE [OR REPLACE] FUNCTION function
  1. Use CREATE FUNCTION to begin the definition of the function. You can add the "OR REPLACE" clause if you want to replace an existing function. Replace function_name with an actual function name. Naming standards apply just like any database object.
  2. A function does not require any parameters. However, if you specify parameters, you only can use incoming parameters. Enclose all parameters in a set of parentheses and separate them with commas. A later module in this course explains more about how to use parameters.
  3. The RETURN clause and the keyword "AS" are required when defining a function. The RETURN clause defines the datatype of the field returned by the function
  4. BEGIN indicates the beginning of the function's definition.
  5. The DECLARE section is optional. This is where variables are defined. If you use a DECLARE section, you must add the keyword BEGIN to mark the beginning of the function body
  6. The body section of the function is where all the work is done. This is a required section.
  7. There must be at least one RETURN clause inside the body of the function. The return_value can be a variable, an expression, or a literal
  8. This END clause closes the body section It is only needed if you have a DECLARE section.
  9. The END clause completes the entire function syntax and is required.

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.