Functions are the same as stored procedures, except that a value is returned after the execution of a function.
Function structure
You must declare the data type of the returned value when creating a function. The function structure is as follows:
FUNCTION name [ ( parameter_list ) ] RETURN sp_type
{ IS | AS }
[ declarative_part ]
BEGIN -- executable part begins
statement; [ statement; ]...
[ EXCEPTION ]
exception_handler; [ exception_handler; ]... ]
END;
Create a function
You can execute the CREATE FUNCTION statement to create a function. The syntax is as follows:
CREATE OR REPLACE FUNCTION func RETURN INT
IS
BEGIN
RETURN 1;
END;
/
Create a function in a package. The syntax is as follows:
CREATE OR REPLACE PACKAGE pack IS
FUNCTION func RETURN INT;
END;
/
CREATE OR REPLACE PACKAGE BODY pack IS
FUNCTION func RETURN INT
IS
BEGIN
RETURN 1;
END;
END;
/
Define a function inside a subprogram. The syntax is as follows:
DECLARE
FUNCTION func RETURN INT
IS
BEGIN
RETURN 1;
END;
BEGIN
NULL;
END;
/
