Subprograms

A subprogram is a PL unit that contains many SQL and PL statements to solve a specific problem or perform a set of related tasks.

A subprogram can contain parameters, whose values are provided by the caller. A subprogram can be a stored procedure or a function. Typically, a stored procedure is used to perform an operation, and a function is used to perform a calculation and return a value.

A stored subprogram is a subprogram stored in the database. It implements complex logic for many different database applications. Stored subprograms are categorized into three types:

• Standalone subprograms, which are created in a schema.

• Subprograms in packages, which are created in the package body.

• Nested subprograms, which are created in a PL block.

Standalone subprograms are convenient for testing program logic, but managing a large number of standalone subprograms is not convenient. Therefore, after the program logic is determined, standalone subprograms are recommended to be placed in different packages according to business modules.

Subprograms are an important component of other maintainable features, such as packages and triggers.

Subprogram structure

A subprogram starts with a subprogram heading that specifies its name and its parameter list (optional).

The structure of a subprogram is consistent with that of a PL block, and includes:

• Declaration part (optional)

The declaration part includes the declarations of types, constants, variables, exceptions, explicit cursors, and nested subprograms. These items are local to the subprogram and are no longer available after the subprogram is executed.

• Execution part (required)

The execution part includes assignment statements, control statements, and data manipulation statements.

• Exception handling part (optional)

The exception handling part includes code to handle exceptions (runtime errors).

Adding comments to a subprogram improves the readability of the program. Comments can be added at any location in the subprogram and are ignored by the compiler. Single-line comments start with two hyphens (--) and continue to the end of the line. Multi-line comments start with a slash and an asterisk (/*) and end with an asterisk and a slash (*/).

The structure of a stored procedure is as follows:

PROCEDURE name [ ( parameter_list ) ]
{ IS | AS }
[ declarative_part ]
BEGIN -- executable part begins
statement; [ statement; ]...
[ EXCEPTION ]
exception_handler; [ exception_handler; ]... ]
END;

The structure of a function is similar to that of a stored procedure, but it includes at least one RETURN clause:

FUNCTION name [ ( parameter_list ) ] RETURN data_type [ clauses ]
{ IS | AS }
[ declarative_part ]
BEGIN
-- At least one RETURN statement
statement; [ statement; ]...
[ EXCEPTION ]
exception_handler; [ exception_handler; ]... ]
END;

The code between IS | AS in a stored procedure or a function is the declaration part of the subprogram, which includes the declaration part, execution part, and exception handling part.