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
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.
CREATE OR REPLACE FUNCTION simple_function
RETURN VARCHAR2
IS
BEGIN
RETURN 'That''s All Folks!' ;
END simple_function;
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
name [ ([ parameters ]) ]
BEGIN
DBMS_OUTPUT.PUT_LINE(simple_function);
END;
That's All Folks!
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
DROP FUNCTION name [ (parameters) ];
DROP FUNCTION simple_function;