How-to: write Oracle stored procedure & function

Both stored procedure & function could be thought of as a sequence of SQL statements to be executed. Typically, a stored procedure performs a task whereas a function computes and return a value.

Typically, tools such as TOAD or Oracle SQL Developer would have UI/wizards to assist in the creation. The syntax are pretty similar in both.

Syntax for creating stored procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];

An example would be:

CREATE OR REPLACE PROCEDURE myprocin(x VARCHAR) IS
BEGIN
INSERT INTO oracle_table VALUES(x);
END;

Syntax for creating stored function:

CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [function_name];

An example would be:

CREATE OR REPLACE FUNCTION modulo(x IN NUMBER, y IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN x%y;
END;