INTRODUCTION TO SUBPROGRAMS

A PL/SQL block of code that accepts parameters and can be invoked is called a subprogram.
There are two types of subprograms: Procedures and Functions. A function is used for calculating value and a procedure is used to do an action. A subprogram can be considered as a module that is integrated to build a large program. This helps to give a modular architecture.
A subprogram consists of the following sections:

#1) Declarative Section It is not a mandatory part of the code and it is optional to mention the keyword DECLARE. It has the constants, variables, nested subprograms, exceptions, and so on. These belong to a particular subprogram and have no existence once the subprogram is no longer present.
#2) Executable Section It is a required part of the code. It is used for assigning values, flow, and manipulation of data. Thus this part of the code always has a specific task to carry out.
#3) Exception Handling Section It is not a mandatory part of the code and it primarily takes care of the exceptions encountered during execution.
A subprogram can be built inside a package, within a block of PL/SQL or in a schema.
A schema level subprogram is an independent one that mainly deals with the CREATE function or procedure. It is stored in the database and we can perform delete or drop operations on them.
A subprogram defined inside a package is called a packaged subprogram. It remains in the database until the package is deleted with drop operation.
A block of PL/SQL can be called with the help of functions (returns a value) and procedures (does not return value).