All about PL/SQL

INTRODUCTION TO PL/SQL

What is PL/SQL?

PL/SQL is Oracle's procedural language extension to SQL. PL/SQL enables you to mix SQL statements with procedural constructs. With PL/SQL, you can define and execute PL/SQL program units such as procedures, functions, and packages.
PL/SQL program units generally are categorized as anonymous blocks and stored procedures.
An anonymous block is a PL/SQL block that appears within your application and it is not named or stored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear.
A stored procedure is a PL/SQL block that Oracle stores in the database and can be called by name from an application. When you create a stored procedure, Oracle parses the procedure and stores its parsed representation in the database. Oracle also allows you to create and store functions (which are similar to procedures) and packages (which are groups of procedures and functions).

Why Use PL/SQL?

Since SQL is a non-procedural language we can not use SQL statements to manipulate Oracle data with procedural features like data flow control statements, variable declaration etc.SQL language also lacks the error handling feature. So an application developer can overcome this limitations by the use of PL/SQL ,which combines the data manipulating power of SQL with the data processing power of procedural languages . The error handling feature is also an important feature provide by PL/SQL.
Another fact is by using PL/SQL block we can minimize the network traffic. The reason behind this is,PL/SQl send an entire block of statements to the Oracle engine at one time.

The Structure of PL/SQL block

A PL/SQL block is having three parts:

A Declarative part , an Executable part and an Exception handling part

The declarative part and Exception handling parts are optional. The order of the parts is logical. First comes the declarative part, in which items can be declared. Once declared, items can be manipulated in the executable part. Exceptions raised during execution can be dealt with in the exception-handling part.