Stored Subprograms

A subprogram is a PL/SQL unit that consists of SQL and PL/SQL statements that solve a specific problem or perform a set of related tasks. A subprogram can have parameters, whose values are supplied by the invoker. A subprogram can be either a procedure or a function. Typically, you use a procedure to perform an action and a function to compute and return a value.
A stored subprogram is a subprogram that is stored in the database. Because they are stored in the database, stored programs can be used as building blocks for many different database applications.
There are two kinds of stored subprograms:

• Standalone subprogram, which is created at schema level
• Package subprogram, which is created inside a package.

Standalone subprograms are useful for testing pieces of program logic, but when you are sure that they work as intended, Oracle recommends that you put them into packages.

About Packages

A package is a PL/SQL unit that consists of related subprograms and that declared cursors and variables that they use.
Oracle recommends that you put your subprograms into packages. Some reasons are:

• Packages allow you to hide implementation details from client programs.
• Package subprograms must be qualified with package names when invoked from outside the package, which ensures that their names will always work when invoked from outside the package.

About Subprogram Structure

A subprogram follows PL/SQL block structure, it has

• Declarative part (optional)
  The declarative part contains declarations of types, constants, variables, exceptions, declared cursors, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution.
• Executable part (required)
  The executable part contains statements that assign values, control execution, and manipulate data.
• Exception-handling part (optional)
  The exception-handling part contains code that handles exceptions.

Comments can appear anywhere in PL/SQL code. The PL/SQL compiler ignores them. Adding comments to your program promotes readability and aids understanding, A single-ling comment start with a -- and extends to the end of the line. A multiline comment starts with a /* and ends with a */
The structure of a procedure is:

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

The structure of a function is like that of a procedure, except that it includes a RETURN clause and at least one RETURN statement.

FUNCTION name [ ( parameter_list ) ] RETURN data_type [ clauses ]
{ IS | AS }
  [ declarative_part ]
BEGIN -- executable part begins
  -- at least one statement must be a RETURN statement
  statement; [ statement; ] ...
[ EXCEPTION -- executable part ends, exception-handling part begins]
  exception_handler; 
END;

About Package Structure

A package always has a specification, and it usually has a body.
The package specification defines the package, declaring the types, variables, constants, exceptions, declared cursors, and subprograms that can be referenced from outside the package. A package specification is an application program interface(API). It has all the information that client programs need to invoke its subprograms, but no information about their implementation.
The package body defines the queries for the declared cursors, and the code for the subprograms, that are declared in the package specification.
The package body can also define local subprograms, which are not declared in the specification and can be invoked only be other subprograms in the package.
Package body contents are hidden from client programs. You can change the package body without invalidating the applications that call the package.