User-Defined Functions «Prev 

Syntax Examples of stored functions

Syntax examples of stored functions
Syntax examples of stored functions

 [(parameter_name IN datatype,...)]
  RETURN datatype AS
        variable_name datatype;
	  body statements ...
	  RETURN return_value;

Line 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.
Line 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.
Line 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.
Line 4: 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.
Line 9: The body section of the function is where all the work is done. This is a required section.
Line 10: 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.
Line 11: This END clause closes the body section It is only needed if you have a DECLARE section.
Line 12: The END clause completes the entire function syntax and is required.

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
END [function_name];

Here's what each element means:
  1. 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.
  2. (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.
  3. RETURN return_datatype: This is where you specify the datatype of the value that the function will return.
  4. IS | AS: These are interchangeable keywords that begin the declaration section of the function.
  5. declaration_section: This optional section is where you declare any local variables or exceptions.
  6. BEGIN: This keyword begins the executable section of the function.
  7. 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.
  8. EXCEPTION: This optional keyword begins the exception-handling section of the function.
  9. exception_section: This optional section is where you handle any exceptions that the function might raise.
  10. 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)
   v_total NUMBER(9,2);
   v_total := p_price * p_quantity;
   RETURN v_total;
END calculate_total;
In this example, the calculate_total function accepts two parameters:
  1. p_price and
  2. 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.