Monday, 27 July 2015

Introduction to PL/SQL

PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.

Advantages of PL/SQL

1. 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.
2. Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops, WHILE loops).
3.  Better Performance: Without PL/SQL, Oracle must process SQL statements one at a time. Programs  
    that issue many SQL statements require multiple calls to the database, resulting in significant network and 
     performance overhead. 
                        With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can  
     drastically reduce network traffic between the database and an application.
            PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are efficient. Because stored procedures execute in the database server, a single call over the network can start a large job. This division of work reduces network traffic and improves response times. Stored procedures are cached and shared among users, which lowers memory requirements and invocation overhead.

4. 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.

5.Full Portability: Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs.

6. Support for Developing Web Applications and Pages: You can use PL/SQL to develop Web applications and Server Pages (PSPs).
The PL/SQL Engine:

Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).

A Simple PL/SQL Block:

Each PL/SQL program consists of SQL and PL/SQL statements which form a PL/SQL block.
A PL/SQL Block consists of three sections:

· The Declaration section (optional).
· The Execution section (mandatory).
· The Exception (or Error) Handling 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.
This is how a sample PL/SQL Block looks.

Variable declaration
Program Execution
Exception handling

Block Types

A PL/SQL program comprises one or more blocks. These blocks can be entirely separate or nested within another block. There are three types of blocks that make up a PL/SQL program. They are:
  1.  Anonymous blocks
  2.  Procedures
  3.  Functions

Anonymous blocks: 

                   Anonymous blocks are unnamed blocks. They are declared inline at the point in an application where they are to be executed and are compiled each time the application is executed. These blocks are not stored in the database. They are passed to the PL/SQL engine for execution at run time. If you want to execute the same block again, you have to rewrite the block. You are unable to invoke or call the block that you wrote earlier because blocks are anonymous and do not exist after they are executed.


                Subprograms are complementary to anonymous blocks. They are named PL/SQL blocks that are stored in the database. Because they are named and stored, you can invoke them whenever you want (depending on your application). You can declare them either as procedures or as functions. You typically use a procedure to perform an action and a function to compute and return a value. You can store subprograms at the server or application level.

NOTE: A function is similar to a procedure, except that a function must return a value.

Create an Anonymous Block

f_name VARCHAR2(20);
SELECT first_name INTO f_name FROM employees WHERE

Test the Output of a PL/SQL Block

Enable output in iSQL*Plus with the following command:
  •  Use a predefined Oracle package and its procedure: DBMS_OUTPUT.PUT_LINE

f_name VARCHAR2(20);
SELECT first_name INTO f_name FROM employees WHERE
DBMS_OUTPUT.PUT_LINE('The First Name of the
Employee is' || f_name);


Post a comment

Find Us On Facebook

Contact Us


Email *

Message *

C Programming


C++ Tutorial


Java Tutorial


software engineering


MS Office


Database Management