Functions are Stored Procedure Language (SPL) programs that are called as expressions. When evaluated, a function returns a value that is substituted in the expression in which the function is embedded.

Functions can receive values from the calling program in the form of input parameters. In addition to the fact that the function, itself, returns a value, a function can return extra values to the caller in the form of output parameters. However, we do not recommend that you use output parameters in functions.

Create a function

The CREATE FUNCTION command defines and names a standalone function that will be stored in the database.
CREATE [ OR REPLACE ] FUNCTION name [ (parameters) ]
RETURN data_type [ AUTHID { DEFINER | CURRENT_USER } ] { IS | AS }
[ declarations ] BEGIN
statements
END [ name ];                

name is the identifier of the function. If you specify the [OR REPLACE] clause and a function with the same name already exists in the schema, the new function will overwrite the existing one. If you do not specify the [OR REPLACE] clause, the new function will not overwrite the existing function with the same name in the same schema. parameters is a list of formal parameters. data_type is the data type of the value returned by the RETURN statement of the function. If the AUTHID clause is omitted or if AUTHID DEFINER is specified, the rights of the function owner are used to determine access privileges to database objects. In addition, the search path of the function owner is used to resolve unqualified object references. If the CURRENT_USER clause is specified, the rights of the current user who calls the function are used to determine access privileges. In addition, the search path of the current user is used to resolve unqualified object references. declarations are variable, cursor, or type declarations. statements are SPL program statements. The BEGIN - END block may contain an EXCEPTION section.

The following example shows a simple function that requires no parameters.
CREATE OR REPLACE FUNCTION simple_function
    RETURN VARCHAR2
IS
BEGIN
    RETURN 'That''s All Folks!' ;
END simple_function;
The following function requires two input parameters. For more information about function parameters, see the following sections.
CREATE OR REPLACE FUNCTION emp_comp (
    p_sal           NUMBER,
    p_comm          NUMBER
) RETURN NUMBER
IS
BEGIN
    RETURN (p_sal + NVL(p_comm, 0)) * 24;
END emp_comp;

Call a function

A function can be used anywhere an expression can appear within an SPL statement. You can call a function by specifying its name followed by its parameters enclosed in parenthesis.
name [ ([ parameters ]) ] 
name is the name of the function. parameters is a list of actual parameters.
Note If no actual parameters are specified, the function may be called with an empty parameter list, or the opening and closing parenthesis may be omitted.
The following example shows how to call the function from another SPL program.
BEGIN
    DBMS_OUTPUT.PUT_LINE(simple_function);
END;

That's All Folks!
A function is typically used within a SQL statement, as shown in the following example.
SELECT empno "EMPNO", ename "ENAME", sal "SAL", comm "COMM",
    emp_comp(sal, comm) "YEARLY COMPENSATION" FROM emp;

 EMPNO | ENAME  |   SAL   |  COMM   | YEARLY COMPENSATION
-------+--------+---------+---------+---------------------
  7369 | SMITH  |  800.00 |         |            19200.00
  7499 | ALLEN  | 1600.00 |  300.00 |            45600.00
  7521 | WARD   | 1250.00 |  500.00 |            42000.00
  7566 | JONES  | 2975.00 |         |            71400.00
  7654 | MARTIN | 1250.00 | 1400.00 |            63600.00
  7698 | BLAKE  | 2850.00 |         |            68400.00
  7782 | CLARK  | 2450.00 |         |            58800.00
  7788 | SCOTT  | 3000.00 |         |            72000.00
  7839 | KING   | 5000.00 |         |           120000.00
  7844 | TURNER | 1500.00 |    0.00 |            36000.00
  7876 | ADAMS  | 1100.00 |         |            26400.00
  7900 | JAMES  |  950.00 |         |            22800.00
  7902 | FORD   | 3000.00 |         |            72000.00
  7934 | MILLER | 1300.00 |         |            31200.00
(14 rows)

Delete a function

You can run the DROP FUNCTION command to remove a function from the database.
DROP FUNCTION name [ (parameters) ]; 
name is the name of the function to be deleted.
Note You must specify the parameter list in PolarDB under specific circumstances such as an overloaded function. However, Oracle requires that the parameter list always be omitted.
In the following example, a function is deleted.
DROP FUNCTION simple_function;