SUBPROGRAMS

What is Sub-programs?

Subprograms are named PL/SQL blocks which will be called with a group of parameters. PL/SQL has two sorts of subprograms, procedures, and functions.

Parts of Sub-programs:

1.      Subprograms have a declarative part where we declare types, cursors, constants, variables, exceptions, and nested subprograms.

2.    Subprograms have an executable part, with statements that assign values, control execution and manipulate Oracle data.

3.    Subprograms have an optional exception-handling part, which deals with semantic error conditions.

Reasons to Use Subprograms:

Subprograms support the event and maintenance of reliable, reusable code with the subsequent features:

Modularity:

Subprograms allow you to break a program into manageable, well-defined modules.

Easier Application Design:

When designing an application, you’ll defer the implementation details of the subprograms until you’ve got tested the most program, then refine them one step at a time

Maintainability:

You can change the implementation details of a subprogram without changing its invokers.

Package ability:

Subprograms can be grouped into packages, whose advantages are explained in “Reasons to Use Packages”.

Reusability:

Any number of applications, in many different environments, can use the same package subprogram or standalone subprogram.

Better Performance:

Each subprogram is compiled and stored in executable form, which may be invoked repeatedly. Because stored subprograms run within the database server, one invocation over the network can start an outsized job. This division of labor reduces network traffic and improves response times. Stored subprograms are cached and shared among all users, which has lowers memory requirements and invocation overhead.

Types of Sub-program:

1.     Procedure

2.   Function