Syntax Examples of stored functions

CREATE [OR REPLACE]FUNCTION function_name
 [(parameter_name IN datatype,...)]
  RETURN datatype AS
    BEGIN
     [DECLARE
        variable_name datatype;
	    ...
	  BEGIN]
	  body statements ...
	  RETURN return_value;
	  [END;
     ]
END;

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
IS | AS
[declaration_section]
BEGIN
   executable_section
   [EXCEPTION
      exception_section]
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)
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:

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.