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 -- Start execution
statement; [ statement; ]...
[ EXCEPTION ]
exception_handler; [ exception_handler; ]... ]
END;
Compared with a stored procedure, a function contains at least one RETURN clause. The structure of a function is as follows:
FUNCTION name [ ( parameter_list ) ] RETURN data_type [ clauses ]
{ IS | AS }
[ declarative_part ]
BEGIN
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.
Benefits of subprograms
Compared with client applications, subprograms have the following benefits:
Better performance
The cost generated by network transmission is reduced. Code can be compiled in advance. You can also use the cache mechanism to reduce performance consumption during execution.
Lower memory consumption
The shared memory mechanism of the database reduces memory consumption if multiple users execute the same stored procedure.
Increased productivity
A stored procedure can help reduce code logic and increase productivity.
Enhanced security
You can enhance security by specifying the privilege information of stored procedures.
Inheritability
You can access stored procedures that are defined by other users after you obtain relevant privileges.
Execution of a subprogram
You can use one of the following methods to execute a subprogram:
Use OBClient.
Call the subprogram in an application.
Call the subprogram by using another subprogram or a trigger.
Dependency management
Objects that are referenced in the package of a subprogram constitute the dependent objects of the subprogram. The database automatically tracks and manages these dependencies.
For example, if you change the table definition that a subprogram depends on, the subprogram must be re-compiled to remain valid. In most cases, the dependency management module of the database automatically re-compiles the subprogram after a dependency is modified.