× PL/SQL - Overview PL/SQL - Basic Syntax PL/SQL - Data Types PL/SQL - Variables PL/SQL - Constants and Literals PL/SQL - Operators PL/SQL - Conditions PL/SQL -ifelse PL/SQL -elsif PL/SQL -nestedif PL/SQL -Case PL/SQL -Searched Case PL/SQL -Basic Loop PL/SQL -For Loop PL/SQL -While Loop PL/SQL - Strings PL/SQL - Arrays PL/SQL - Procedures PL/SQL - Functions PL/SQL - Cursors PL/SQL - Records PL/SQL - Exceptions PL/SQL - Triggers PL/SQL - Packages PL/SQL - Collections PL/SQL - Transactions PL/SQL - Date & Time PL/SQL - DBMS Output PL/SQL - Object Oriented
  • iconPLSQL Online Training In Andhra Pradesh and Telangana
  • icon9010519704

Opening Hours :7AM to 9PM

PL/SQL - Procedures

PL/SQL - Procedures
In this chapter, we will discuss Procedures in PL/SQL. 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.
A subprogram can be created

1.At the schema level
2.Inside a package
3.Inside a PL/SQL block

At the schema level, subprogram is a standalone subprogram. It is created with the CREATE PROCEDURE or the CREATE FUNCTION statement. It is stored in the database and can be deleted with the DROP PROCEDURE or DROP FUNCTION statement.

A subprogram created inside a package is a packaged subprogram. It is stored in the database and can be deleted only when the package is deleted with the DROP PACKAGE statement. We will discuss packages in the chapter 'PL/SQL - Packages'.

PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters. PL/SQL provides two kinds of subprograms

Functions − These subprograms return a single value; mainly used to compute and return a value.
Procedures − These subprograms do not return a value directly; mainly used to perform an action.

This chapter is going to cover important aspects of a PL/SQL procedure. We will discuss PL/SQL function in the next chapter.

Parts of a PL/SQL Subprogram
Each PL/SQL subprogram has a name, and may also have a parameter list. Like anonymous PL/SQL blocks, the named blocks will also have the following three parts

1.Declarative Part
It is an optional part. However, the declarative part for a subprogram does not start with the DECLARE keyword. It contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution.
2.Executable Part
This is a mandatory part and contains statements that perform the designated action.
This is again an optional part. It contains the code that handles run-time errors.

Creating a Procedure
A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
  < procedure_body > 
END procedure_name;

1.procedure-name specifies the name of the procedure.
2.[OR REPLACE] option allows the modification of an existing procedure.
3.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.
4.procedure-body contains the executable part.
5.The AS keyword is used instead of the IS keyword for creating a standalone procedure.

Example Program
The following example creates a simple procedure that displays the string 'Hello World!' on the screen when executed.
   dbms_output.put_line('Hello World!'); 
When the above code is executed using the SQL prompt, it will produce the following result

Procedure created.

Executing a Standalone Procedure
A standalone procedure can be called in two ways

1.Using the EXECUTE keyword
2.Calling the name of the procedure from a PL/SQL block

The above procedure named 'greetings' can be called with the EXECUTE keyword as
EXECUTE greetings;
The above call will display
Hello World

The procedure can also be called from another PL/SQL block
The above call will display
Hello World

PL/SQL procedure successfully completed.
Deleting a Standalone Procedure
A standalone procedure is deleted with the DROP PROCEDURE statement. Syntax for deleting a procedure is
DROP PROCEDURE procedure-name; 
You can drop the greetings procedure by using the following statement
DROP PROCEDURE greetings; 

Parameter Modes in PL/SQL Subprograms
The following table lists out the parameter modes in PL/SQL subprograms

An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter. You can also initialize it to a default value; however, in that case, it is omitted from the subprogram call. It is the default mode of parameter passing. Parameters are passed by reference.
An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed by value.
An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and the value can be read.

The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression. Formal parameter must be assigned a value. Actual parameter is passed by value.
IN & OUT Mode Example 1
This program finds the minimum of two values. Here, the procedure takes two numbers using the IN mode and returns their minimum using the OUT parameters.
   a number; 
   b number; 
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 
   IF x < y THEN 
      z:= x; 
      z:= y; 
   END IF; 
   a:= 23; 
   b:= 45; 
   findMin(a, b, c); 
   dbms_output.put_line(' Minimum of (23, 45) : ' || c); 
When the above code is executed at the SQL prompt, it produces the following result
Minimum of (23, 45) : 23

IN & OUT Mode Example 2
This procedure computes the square of value of a passed value. This example shows how we can use the same parameter to accept a value and then return another result.
   a number; 
PROCEDURE squareNum(x IN OUT number) IS 
  x := x * x; 
   a:= 23; 
   dbms_output.put_line(' Square of (23): ' || a); 
When the above code is executed at the SQL prompt, it produces the following result
Square of (23): 529

Methods for Passing Parameters
Actual parameters can be passed in three ways

1.Positional notation
2.Named notation
3.Mixed notation
4.Positional Notation

In positional notation, you can call the procedure as
findMin(a, b, c, d);

In positional notation, the first actual parameter is substituted for the first formal parameter; the second actual parameter is substituted for the second formal parameter, and so on. So, a is substituted for x, b is substituted for y, c is substituted for z and d is substituted for m.

Named Notation
In named notation, the actual parameter is associated with the formal parameter using the arrow symbol ( => ). The procedure call will be like the following
findMin(x => a, y => b, z => c, m => d);

Mixed Notation
In mixed notation, you can mix both notations in procedure call; however, the positional notation should precede the named notation.
The following call is legal
findMin(a, b, c, m => d);

However, this is not legal:
findMin(x => a, b, c, d);

Key Points

  • PL/SQL - Procedures
  • Image