PL/SQL stands for Procedural Language extension of SQL. PL/SQL is a combination of SQL along with the features of procedural programming languages. Oracle Corporation has developed this language to enhance the capabilities of SQL.
Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored in the client-side or in the server-side.Oracle application can be built on client/server architecture.
PL/SQL programs can be written on the client side and queries can be written to manipulate or retrieve the data from the server on the server side.
Difference between SQL and PL/SQL
- SQL is a Structured Query Language fires a query to create or modify database objects at the Server.PL-SQL is a procedural language SQL,adds programming elements like variables, loops,operators etc. to SQL queries
- SQL is a data oriented language selects and manipulates sets of data.
PL/SQL is a procedural language create applications. - SQL query gets executed one by one causing two trips from the client to the server ,one for firing the request and another for bringing the result set. PL/SQL can execute multiple SQL queries together in a block at one go.
Benefits of PL/SQL:
- Structured blocks: 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. These blocks can be stored in the database and supports re-usability.
- Integration with SQL: The PL/SQL language is tightly integrated with SQL. The user does not have to perform data conversion between SQL and PL/SQL data types. This integration saves both learning time and processing time. PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions and operators.
- Full Portability: Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs.
- Procedural Language: PL SQL includes the procedural language constructs such as control flow statements which consists of (if else statements) and loops like (FOR loops).
- Efficient Error handling mechanism: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Run time errors can be trapped with Exception handling statements.
Once an exception is caught, specific actions can be taken depending upon the type of the exception . - Security: PL/SQL stored procedures protect the application code from tampering, hide the internal details, and restrict user’s access.
- Object oriented programming support: Oracle object types are user-defined types that make it possible to model real-world entities such as customers and purchase orders as objects in the database.
It supports encapsulation, modularity, maintainability and re-usability. - Better efficiency and performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
