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 have 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 operations for many different database applications. Stored subprograms are divided 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 inconvenient. 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 part of other maintainable features, such as packages and triggers.

Subprogram structure

A subprogram starts with a subprogram title, which specifies its name and parameter list (optional).

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

• 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 execution ends.

• 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 will be 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.