Relational DBMS (OLD) - Creating Procedures

 CBSE Class 12 Informatics Practices

Revision Notes
CHAPTER-14
CREATING PROCEDURES

Procedures makes the program modular and each module performs a specific function or task. Modular programming approach makes the program readable, manageable, reusable and reliable. A subprogram is a program unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the 'Modular design'. A subprogram can be invoked by another subprogram or program which is called the calling program.

PL/SQL supports modules of many types such as:
¦ Anonymous Blocks
¦ Procedures
¦ Functions
¦ Packages

A Module or Procedure is a logical unit of work ie,a logically grouped set of SQL and PL/SQL statements that together perform a specific task.
Types of PL/SQL Procedures:
PL/SQL supports two types of Procedures:
1. Local or Anonymous Procedure: These are unnamed procedures. These procedures are not stored as a database object in an Oracle database.
2. Stored Procedure: These are named procedures. These can accept input parameters and pass values to output parameters.

Need of Procedures:
1. Procedures makes a program modular and serve to meet the specific requirement.
2. They make a bigger program broken down into smaller and manageable units.
3. They enhance performance of a program by saving time in network traffic as they do not need recompilation as their compiled form is stored in the database.
4. They enhance reusability as a procedure once written can be used again or reused.
5. They provide a better database security.
6. They use shared memory resources.

PL/SQL structure of a named block:

HEADER
IS
DECLARATION SECTION BEGIN
EXCEPTION
END;

Therefore, there are four sections in the declaration of a stored procedure.
1. HEADER SECTION: Here the type of block whether procedure or Function or Package and its name and parameters are specified.
2. DECLARATION SECTION: Here local variables to this very block are specified.
3. EXECUTION SECTION: Here all the executable statements appear.
4. EXCEPTION SECTION: Here all the exceptions handling statements are specified.
The declaration and exception sections are optional in any PL/SQL block.
NOTE: THE PROCEDURE specification begins with the keyword PROCEDURE and ends with the procedure name or a parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses.

The PROCEDURE body begins with the keyword IS or AS and ends with the keyword END followed by an optional procedure name. The procedure body has three parts:a declarative part,an executable part, and an optional exception-handling part.
Example: A procedure showing all the four sections.

PARAMETER MODES:
The formal parameters of a procedure have following three major attributes.
1. Name of the Procedure.
2. MODE. (IN, OUT, IN OUT)
3. Data type.
The parameter modes define the behaviour of formal parameters.
The three modes are:

a. IN MODE: IN parameter lets user to pass values to the procedure being called inside.Inside the procedure,an IN parameter acts like a constant. Therefore, it can not be assigned a value.
b. OUT MODE: OUT parameter lets user to return the values to the caller of a procedure.ie, to the sub program ,which invokes the procedure.
c. IN OUT MODE: IN OUT parameter lets use pass initial values to the procedure being called and return updated values to the caller subprogram.
Inside the procedure,an IN OUT parameter acts like an initialized variable.

DIFFERENCE BETWEEN THE THREE MODES:

IN

OUT

IN OUT

The default.

Must be specified.

Must be specified.

Passes values to a procedure.

Returns values to the caller.

Passes initial values to a procedure; returns updated values to the caller.

Formal parameter acts like a constant.

Formal parameter acts like an uninitialized variable.

Formal parameter acts like an initialized variable.

Formal parameters cannot be assigned a value.

Formal parameter cannot be used in an expression; 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.

PARAMETER DECLARATION CONSTRAINTS:

The data type of a formal parameter can consist of any one of the following type of declarations:
¦ An unconstrained type name, such as NUMBER or VARCHAR2.
¦ A type that is constrained using the %TYPE or %ROWTYPE attributes.
NOTE: Numerically constrained types such as NUMBER(2) or VARCHAR(20) are not allowed in a parameter list.

Creating a Procedure

A procedure is created with the CREATE OR REPLACE PROCEDURE statement. 

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name;

Where,

  • procedure-name specifies the name of the procedure.

  • [OR REPLACE] option allows the modification of an existing procedure.

  • The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.

  • procedure-body contains the executable part.

  • The AS keyword is used instead of the IS keyword for creating a standalone procedure.

CREATE OR REPLACE PROCEDURE mycbseguide 
AS 
BEGIN 
   dbms_output.put_line('Hello Students! Welcome to mycbseguide'); 
END;

O/P:  Procedure created....

To Execute Procedure :
EXECUTE mycbseguide;
The above call will display − Hello Students! Welcome to mycbseguide.