Subprograms

A subprogram is a procedural language (PL) unit that contains a collection of SQL statements and PL statements. You can use a subprogram to resolve specific issues or execute a group of related tasks.

A subprogram can contain parameters, and callers pass values to these parameters. A subprogram can be a stored procedure or a function. In most cases, you can use a stored procedure to perform an operation and a function to calculate and return a value.

A stored subprogram is a subprogram stored in a database to perform complex logic operations for different database applications. Stored subprograms are classified into the following three types:

• Independent subprograms that are created at the schema level

• Subprograms created in a package

• Nested subprograms created in a PL block

Independent subprograms facilitate program logic tests, but they are difficult to manage due to the large volume. We recommend that you place independent subprograms into different packages based on the business modules after you determine the program logic.

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

Structure of a subprogram

A subprogram starts with a heading that specifies the name and optionally the parameter list of the subprogram.

The structure of a subprogram is the same as that of a PL block, which consists of the following parts:

• Declarative part (optional)

  This part contains declarations for types, constants, variables, exceptions, explicit cursors, and nested subprograms. These items are local to the subprogram and cease to exist after the subprogram is executed.

• Executable part (required)

  This part contains statements for value assignment, execution control, and data manipulation.

• Exception-handling part (optional)

  This part contains code for handling exceptions or runtime errors.

You can add comments anywhere in a subprogram to improve the readability of the subprogram. The comments will be ignored by the compiler. A single-line comment starts with double hyphens (--) and extends to the end of the line. A multi-line comment starts with a slash-asterisk pair (/*) and ends with an asterisk-slash pair (*/).

The following sample code shows the structure of a stored procedure:

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

A function contains at least one RETURN clause, while a stored procedure does not, as shown in the following function structure:

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 the stored procedure or function and IS | AS is the declaration of a subprogram. The declarative part, executable part, and exception-handling part form the content body of the subprogram.