PL/SQL - Procedures

Complete Tutorial 

Procedures:

1. What is a Procedure?

A Procedure is a PL/SQL sub program used to perform an action (transaction) which takes parameters as input and returns output.

2. What does a procedure consists?

Procedure consists is PL/SQL block (code) which consists a declarative part, an executable part, and an optional exception-handling part.  A procedure is compiled and stored in the database as a schema object. Procedures promote reusability and maintainability. When validated, they can be used in any number of applications. If the requirements change, only the procedure needs to be updated.

Oracle   Procedures has the following       

• Declarative part,
• Executable part,
• Exception-handling part.

The declarative part contains the following and these items are local and cease to exist when you exit the subprogram.

·         Declarations of types,

·         Cursors ( Names Control Structures)

·         Constants,

·         Variables

·         Exceptions

·         Nested subprograms.

The executable part contains

·         Statements that assign values

·         Control execution

·         Manipulate Oracle data.

Exception-handling part

 The exception-handling part contains exception handlers, which deal with exceptions raised during execution.

3. What is the Syntax of the PROCEDURE?

PROCEDURE name [ ( parameter [, parameter ... ] ) ]

IS

   [declaration statements]

BEGIN

   executable-statements

[ EXCEPTION

     exception handler statements]

END [ name ];

where each component is used in the following ways:

name

The name of the procedure comes directly after the keyword PROCEDURE.

parameters

An optional list of parameters that you define to both pass information into the procedure and send information out of the procedure, back to the calling program.

declaration statements

The declarations of local identifiers for that procedure. If you do not have any declarations, then there will not be any statements between the IS and BEGIN statements.

executable statements

The statements that the procedure executes when it is called. You must have at least one executable statement after the BEGIN and before the END or EXCEPTION keywords.

exception handler statements

The optional exception handlers for the procedure. If you do not explicitly handle any exceptions, then you can leave out the EXCEPTION keyword and simply terminate the execution section with the END keyword.

Below figure shows the apply_discount procedure, which contains all four sections of the named PL/SQL block, as well as a parameter list.

The apply_discount procedure

Calling a Procedure

A procedure is called as an executable PL/SQL statement. In other words, a call to a procedure must end with a semicolon (;) and be executed before and after other SQL or PL/SQL statements.

The following executable statement runs the apply_discount procedure:

apply_discount( new_company_id, 0.15 );  -- 15% discount

If the procedure does not have any parameters, then you must call the procedure without any parentheses:

display_store_summary;

Procedure Header

The portion of the procedure definition that comes before the IS keyword is called the procedure header. The header provides all the information a programmer needs to call that procedure, namely:

• The procedure name
• The parameter list, if any

A programmer does not need to know about the inside of the procedure in order to be able to call it properly from another program.

The header for the apply_discount procedure discussed above is:

PROCEDURE apply_discount

   (company_id_in IN company.company_id%TYPE,

    discount_in IN NUMBER)

It consists of the module type, the name, and a list of two parameters.

Procedure Body

The body of the procedure is the code required to implement the procedure. It consists of the declaration, execution, and exception sections of the function. Everything after the IS keyword in the procedure makes up that procedure's body.

Once again, the declaration and exception sections are optional. If you have no exception handlers, you will leave off the EXCEPTION keyword and simply enter the END statement to terminate the procedure. If you do not have any declarations, the BEGIN statement simply follows immediately after the IS keyword (see the do_nothing procedure below for an example of this structure.). You must supply at least one executable statement in a procedure. Here is my candidate for the procedure in PL/SQL with the smallest possible body:

PROCEDURE do_nothing IS

BEGIN

   NULL;

END;

Does the do_nothing procedure seem silly? A procedure that doesn't do anything can, in fact, be very useful when you are creating stubs for modules in a top-down design effort. I have also used this kind of procedure when building templates. My do_nothing procedure acts initially as a placeholder in my code, but then also provides a mechanism for customization of the templates.

The END Label

You can append the name of the procedure directly after the END keyword when you complete your procedure, as shown below:

PROCEDURE display_stores (region_in IN VARCHAR2) IS

BEGIN

   ...

END display_stores;

This name serves as a label that explicitly links up the end of the program with its beginning. You should as a matter of habit use an END label. It is especially important to do so when you have a procedure that spans more than a single page, or is one in a series of procedures and functions in a package body.

4. What is meant by Forward Declaration?

PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. In Forward declaration an identifier is declared before it is used. Therefore a   subprogram is declared before calling it. Although the formal parameter list appears in the forward declaration, it must also appear in the subprogram body. You can place the subprogram body anywhere after the forward declaration, but they must appear in the same program unit. For example, the following declaration of procedure calc_rating   is illegal because award_bonus calls procedure calc_rating.

DECLARE

   PROCEDURE calc_rating (...); -- forward declaration

   ...

   /* Define subprograms in alphabetical order. */

   PROCEDURE award_bonus (...) IS

   BEGIN

      calc_rating( ... );

      ...

   END;

   PROCEDURE calc_rating (...) IS 

   BEGIN 

      ... 

   END;

 5. What are the Parameter Modes in Procedure?

  There are three procedural parameter modes

·         IN (the default),

·         OUT  Parameter

·         IN OUT Parameter

 IN Mode

An IN parameter lets you pass values to the subprogram being called. Inside the subprogram, an IN parameter acts like a constant. Therefore, it cannot be assigned a value.

PROCEDURE validate_input(l_input_rec      IN  INPUT_REC_TYPE,

                         v_return_status  OUT VARCHAR2      ,

                         v_return_message OUT VARCHAR2

                          )

IS           

v_source_system                VARCHAR2(150);

v_source_system_identifier     VARCHAR2(150);

v_notes                        VARCHAR2(2000); 

v_status                       VARCHAR2(30) ;

v_service_request_number       VARCHAR2(64) ; 

v_input_type                   VARCHAR2(30) ;

v_attribute_name               VARCHAR2(30) ;

OUT Mode

An OUT parameter lets you return values to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like a variable. That means you can use an OUT formal parameter as if it were a local variable. You can change its value or reference the value in any way, as the following example shows:

PROCEDURE validate_input    (l_input_rec      IN  INPUT_REC_TYPE,

                         v_return_status      OUT VARCHAR2 ,

                         v_return_message OUT VARCHAR2

                          )

IS           

v_source_system                         VARCHAR2(150);

v_source_system_identifier      VARCHAR2(150);

v_notes                                         VARCHAR2(2000); 

 v_status                                       VARCHAR2(30) ;

v_service_request_number   VARCHAR2(64) ; 

 

IN OUT Mode

Criteria	OUT	IN OUT
Default	Must be Specified	Must be Specified
Passes values to a subprogram	Returns values to the caller	Passes initial values to a subprogram and returns updated values to the caller
Formal parameter acts like a constant	Formal parameter acts like a variable	Formal parameter acts like an initialized variable
Formal parameter cannot be assigned a value	Formal parameter must be assigned a value	Formal parameter should be assigned a value
Actual parameter can be a constant, initialized variable, literal, or expression	Actual parameter must be a variable	Actual parameter must be a variable
Actual parameter is passed by reference (a pointer to the value is passed in)	Actual parameter is passed by value (a copy of the value is passed out) unless NO COPY is specified	Actual parameter is passed by value (a copy of the value is passed in and out) unless NO COPY is specified

6.How to ALTER  a PROCEDURE

Using the ALTER PROCEDURE statement we can explicitly recompile a standalone stored procedure. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead. The pre-requisite is the Procedure must be in your own schema or one must have the   ALTER ANY PROCEDURE system Privilege.

<<<Previous                                                                                                                                                                                  Next>>>

Complete Tutorial