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