Functions are exactly the same as stored procedures. The only difference is that a function returns a value after execution, whereas a stored procedure does not.
Function structure
Compared with stored procedures, a function must declare the return type when it is created. 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 create a function by using the CREATE FUNCTION statement. 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;
/