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_name
(parameter_name IN datatype, ...)
RETURN datatype AS
BEGIN
-- Declaration section
DECLARE
variable_name datatype;
...
BEGIN
-- Body statements
body statements ....;
-- Return a value
RETURN return_value;
END;
END;
CREATE [OR REPLACE] FUNCTION function
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.
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.
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.
BEGIN indicates the beginning of the function's definition.
Lines 5-8: 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.
The body section of the function is where all the work is done. This is a required section.
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.
This END clause closes the body section. It is only needed if you have a DECLARE section.
The END clause completes the entire function syntax and is required.
Stored functions in Oracle's PL/SQL are subprograms
Stored functions in Oracle's PL/SQL are subprograms that encapsulate a sequence of PL/SQL statements and return a single value. They are particularly useful for performing complex computations and can be used in SQL statements.
The general syntax for creating a function in Oracle PL/SQL is as follows:
CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [type [, ...]])]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [function_name];
Here's what each element means:
CREATE [OR REPLACE] FUNCTION function_name: This statement is used to create the function. If the function already exists, you can use the OR REPLACE option to modify it.
(parameter_name [type [, ...]]): This optional section is where you declare any parameters that the function accepts. Each parameter has a name, type, and possibly a default value.
RETURN return_datatype: This is where you specify the datatype of the value that the function will return.
IS | AS: These are interchangeable keywords that begin the declaration section of the function.
declaration_section: This optional section is where you declare any local variables or exceptions.
BEGIN: This keyword begins the executable section of the function.
executable_section: This is where you put the code that the function will execute. It should end with a RETURN statement that provides the value that the function returns.
EXCEPTION: This optional keyword begins the exception-handling section of the function.
exception_section: This optional section is where you handle any exceptions that the function might raise.
END [function_name]: This statement ends the function. You can optionally include the function name after the END keyword.
Here's an example of a simple stored function in Oracle PL/SQL:
CREATE OR REPLACE FUNCTION calculate_total(p_price NUMBER, p_quantity NUMBER)
RETURN NUMBER
IS
v_total NUMBER(9,2);
BEGIN
v_total := p_price * p_quantity;
RETURN v_total;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END calculate_total;
In this example, the calculate_total function accepts two parameters:
p_price and
p_quantity.
It calculates the total value by multiplying the price by the quantity, and then returns this value. If an exception occurs during the calculation, the function returns NULL.
Stored function Basic Structure
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.
PL/SQL 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.
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.
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.
