PL/SQL Tutorial

Introduction

  • Pl/SQL stands for "Procedural Language extension of SQL" used in Oracle. PL/SQL is integrated with Oracle database (since version 7).
  • It is a block structure language.
  • The programs of PL/SQL are logical blocks that can contain any number of nested sub-blocks.
  • The functionalities of PL/SQL are usually extended after each release of Oracle database.
  • Although PL/SQL is closely integrated with SQL language, it adds some programming constraints that are not available in SQL.
  • PL/SQL includes procedural language elements like conditions and loops.
  • It allows declaration of the constants and variables, procedures and functions, triggers etc.

Advantages of PL/SQL

  • PL/SQL is development tool that supports SQL data manipulation and conditional checking, branching and looping.
  • It provides facility to deal with errors, as required and displays user-friendly messages when error occurs.
  • Allows declaration and use of variables in blocks of code. These variables are used to store intermediate results of a query for later processing either in SQL or PL/SQL.
  • Using PL/SQL, all sorts of calculations can be done quickly and efficiently without using oracle engine which improves transaction performance.
  • Applications written in PL/SQL are portable to any computer hardware and operating system, where Oracle is operational.
    For example: PL/SQL code blocks written for a DOS version of Oracle will run on its Linux/ Unix version without any modifications.

Disadvantages of SQL

  • SQL does not provide the programming techniques of condition checking, looping and branching which is very important for data testing before its permanent storage. Pl/SQL does this.
  • SQL statements are passed to the Oracle Engine one at a time. While executing SQL statement, each time a call is made to the engine's resources. This increase traffic in the network that decreases the speed of data processing especially in a multi-user environment.
  • SQL has no facility of error handling.