Subprograms

A subprogram is a PL unit that contains many SQL and PL statements to solve specific problems 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, you use a stored procedure to perform an operation and a function 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 the following three types:

• Standalone subprograms: subprograms created in a schema.

• Subprograms in packages: subprograms created in the body of a package.

• Nested subprograms: subprograms created in a PL block.

Standalone subprograms are convenient for testing program logic. However, 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 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 that specifies the subprogram name and its parameter list (optional).

The structure of a subprogram is the same as that of a PL block, which includes:

• A declarative part (optional)

  The declarative part includes 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.

• An executable part (required)

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

• An exception-handling part (optional)

  The exception-handling part includes code for handling exceptions (runtime errors).

Adding comments to a subprogram improves the readability of the program. Comments can be added 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, except that it contains 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 and AS in a stored procedure or function is the subprogram declaration. The declarative part, executable part, and exception-handling part are the content of the subprogram.