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. Function structure:
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. Syntax:
CREATE OR REPLACE FUNCTION func RETURN INT
IS
BEGIN
RETURN 1;
END;
/
Create a function in a package. Syntax:
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. Syntax:
DECLARE
FUNCTION func RETURN INT
IS
BEGIN
RETURN 1;
END;
BEGIN
NULL;
END;
/