Subprograms

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

A subprogram can contain parameters, whose specific values are passed in 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 operations for many different database applications. Stored subprograms are divided into three categories:

• 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 subprograms is inconvenient. Therefore, after the program logic is determined, standalone subprograms are recommended to be placed into different packages based on business modules.

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

Subprogram structure

A subprogram starts with a subprogram heading, which specifies the name and parameter list (optional) of the subprogram.

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

• Declaration part (optional)

The declaration part includes declarations of types, constants, variables, exceptions, explicit cursors, and nested subprograms. These items are local to the subprogram and no longer exist 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 placed anywhere in the subprogram and are ignored by the compiler. Single-line comments start with two hyphens (--) and extend 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 function is the declaration of the subprogram, which includes the declaration part, execution part, and exception handling part.