PL/SQL is Oracle’s procedural language extension to SQL. PL/SQL allows you to mix SQL statements with procedural statements like IF statement, Looping structures etc. PL/SQL is the superset of SQL. It uses SQL for data retrieval and manipulation and uses its own statements for data processing.

PL/SQL program units are generally categorized as follows:

·        Anonymous blocks

·        Stored procedures

Anonymous block:

    This is a PL/SQL block that appears within your application. In many applications PL/SQL blocks can appear where SQL statements can appear. Such blocks are called as Anonymous blocks

Stored Procedure:

   This is a PL/SQL block that is stored in the database with a name. Application programs can execute these procedures using the name. Oracle also allows you to create functions, which are same as procedures but return a value, and packages, which are a collection of procedures and functions.

Need for PL/SQL:

     SQL statements are defined in term of constraints we wish to fix on the result of a query. Such a language is commonly referred to as declarative. This contrasts with the so called procedural languages where a program specifies a list of operations to be performed sequentially to achieve the desired result. PL/SQL adds selective  (i.e. if...then...else...) and iterative constructs (i.e. loops) to SQL.
PL/SQL is most useful to write triggers  and stored procedures. Stored procedures are units of procedural code stored in a compiled form within the database,

PL/SQL Architecture:

   Every PL/SQL block is first executed by PL/SQL engine. This is the engine that compiles and executes PL/SQL blocks. PL/SQL engine is available in Oracle Server and certain Oracle tools such as Oracle Forms and Oracle Reports.

    PL/SQL engine executes all procedural statements ofa PL/SQL of the block, but sends SQL command to SQL statements executorin the Oracle RDBMS. That means PL/SQL separates SQL commands from PL/SQL commands and executes PL/SQL commands using Procedural statement executor, which is a part of PL/SQL engine.

PL/SQL Architecture

Features of PL/SQL:

The following are important features of PL/SQL.

Block structure

PL/SQL is a block-structured language. Each program written in PL/SQL is written as a block. Blocks can also be nested. Each block is meant for a particular task.

Variables and constants

    PL/SQL allows you to declare variables and constants. Variables are used to store values temporarily.Variables and constants can be used in SQL and PL/SQL procedural statements just like an expression.

Control structures

    PL/SQL allows control structures like IF statement, FOR loop, WHILE loop to be used in the block. Control structures are most important extension to SQL in PL/SQL. Control structures allow any data process possible in PL/SQL.

Exception handling

    PL/SQL allows errors, called as exceptions, to be detected and handled. Whenever there is a predefined error PL/SQL raises an exception automatically. These exceptions can be handled to recover from errors.


    PL/SQL allows process to be divided into different modules. Subprograms called as procedures and functions can be defined and invoked using the name. These subprograms can also take arameters.


    A cursor is a private SQL area used to execute SQL statements and store processing information. PL/SQLimplicitly uses cursors for all DML commands and SELECT command that returns only one row. And it also allows you to define explicit cursor to deal with multiple row queries.

Built-in functions

    Most of the SQL functions that we have seen so far in SQL are available in PL/SQL. These functions can be used to manipulate variables of PL/SQL.

Advantages Of PL/SQL

A Simple these are the Advantages of PL/SQL:

    Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.
     Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
     Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
    Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

PL/SQL Block:

Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.

    The Declaration section (optional).
    The Execution section (mandatory).
    The Exception Handling (or Error) section (optional).

Declaration Section:

     The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.

Execution Section:

   The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.
Exception Section:

      The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.

Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.

PL/SQL Data types:
PL/SQL provides a variety of predefined data types, which can be divided into four categories:

Scalar- Represents a single value.

Composite-Is a collection of components

Reference -Is a pointer that points to another item.

LOB -Holds a lob locator.

The following are the data types in various categoryies


Composite -RECORD, TABLE and VARRAY.

Reference -REF CURSOR, REF Object type


PL/SQL Variables and Constants:


    A variable is nothing but a name given to a storage area that our programs can manipulate. Each variable in PL/SQL has a specific data type, which determines the size and layout of the variable's memory; the range of values that can be stored within that memory and the set of operations that can be applied to the variable.

The name of a PL/SQL variable consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters. By default, variable names are not case-sensitive. You cannot use a reserved PL/SQL keyword as a variable name.

PL/SQL programming language allows to define various types of variables, which we will cover in subsequent chapters like date time data types, records, collections, etc. For this chapter, let us study only basic variable types.

Variable Declaration in PL/SQL

PL/SQL variables must be declared in the declaration section or in a package as a global variable. When you declare a variable, PL/SQL allocates memory for the variable's value and the storage location is identified by the variable name.

The syntax for declaring a variable is:

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Initializing Variables in PL/SQL

Whenever you declare a variable, PL/SQL assigns it a default value of NULL. If you want to initialize a variable with a value other than the NULL value, you can do so during the declaration, using either of the following:

·         The DEFAULT keyword

·         The assignment operator

For example:

counter binary_integer := 0;

greetings varchar2(20) DEFAULT 'Have a Good Day';

You can also specify that a variable should not have a NULL value using the NOT NULL constraint. If you use the NOT NULL constraint, you must explicitly assign an initial value for that variable.

It is a good programming practice to initialize variables properly otherwise, sometimes program would produce unexpected result. Try the following example which makes use of various types of variables:


   a integer := 10;

   b integer := 20;

   c integer;

   f real;


   c := a + b;

   dbms_output.put_line('Value of c: ' || c);

   f := 70.0/3.0;

   dbms_output.put_line('Value of f: ' || f);



Scope of Pl/SQL Variables:

PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.

    Local variables - These are declared in a inner block and cannot be referenced by outside Blocks.
    Global variables - These are declared in a outer block and can be referenced by its itself and by its inner blocks.

 For Example: In the below example we are creating two variables in the outer block and assigning thier product to the third variable created in the inner block. The variable 'var_mult' is declared in the inner block, so cannot be accessed in the outer block i.e. it cannot be accessed after line 11. The variables 'var_num1' and 'var_num2' can be accessed anywhere in the block.


2>  var_num1 number;

3>  var_num2 number;


5>  var_num1 := 100;

6>  var_num2 := 200;


8>   var_mult number;

9>   BEGIN

10>    var_mult := var_num1 * var_num2;

11>   END;

12> END;

13> /

PL/SQL Constants:

As the name implies a constant is a value used in a PL/SQL Block that remains unchanged throughout the program. A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.

For example: If you want to write a program which will increase the salary of the employees by 25%, you can declare a constant and use it throughout the program. Next time when you want to increase the salary again you can change the value of the constant which will be easier than changing the actual value throughout the program.

General Syntax to declare a constant is:

constant_name CONSTANT datatype := VALUE;

    constant_name is the name of the constant i.e. similar to a variable name.
    The word CONSTANT is a reserved word and ensures that the value does not change.
    VALUE - It is a value which must be assigned to a constant when it is declared. You cannot assign a value later.

For example, to declare salary_increase, you can write code as follows:


salary_increase CONSTANT number (3) := 10;

You must assign a value to a constant at the time you declare it. If you do not assign a value to a constant while declaring it and try to assign a value in the execution section, you will get a error. If you execute the below Pl/SQL block you will get error.


 salary_increase CONSTANT number(3);


 salary_increase := 100;

 dbms_output.put_line (salary_increase);



Post a Comment