In this post we will learn about PLSQL language, What is PL/SQL, and Why Use PLSQL
What is PL/SQL
- PL/SQL is Oracle’s proprietary extension to SQL that allows for procedural language capabilities
- It gives all the features of modern software engineering such as data encapsulation, exception handling, information hiding and object orientation
- It is also available in IBMDB2 and Times ten-in memory databases
- PL/SQL stands for “Procedural Language extensions to the Structured Query Language”
Why Use PLSQL
- Integration with development tools like Oracle forms, Reports
- Multiple statements can be grouped into a single plsql block and sent to the oracle server which results in less network traffic
- Modularized program development: Breakdown the complex logic/problem into a set of manageable, well-defined, logical modules and implement them using blocks
- Portability: Since it is native to the Oracle server, it can be moved easily
- It provides procedural language control structures like if condition, looping
- It has the capability to handle errors and raise an exception based on it
How PLSQL Works
- PLSQL engine is the technology which works on the PLSQL Blocks. PLSQL engine reads the block and converts it into machine code.
- PLSQL engine is present in Oracle Database and Oracle Tools Like Oracle forms
- The engine is invoked based on where you are executing the code. If it is through SQLPLUS in Oracle, then the PLSQL engine in the Oracle Database works on it
- When you submit the PLSQL code to the Oracle server. PLSQL engine in the Oracle Server read the code, compiles it and then separately the SQL statement and sends it to the SQL engine processor. The Procedural part is handled by the PLSQL engine only
- This is illustrated below using the simple diagram
Summary
- PLSQL is an extension to SQL
- PLSQL engine process the PLSQL Block
- Integration, Portability, error handling and Improved performance are some of the benefits of it
Related Articles
Oracle PLSQL records: Check out this article on the working of oracle plsql records. Also, find out the various ways to define it and assign value to it
PLSQL Tables: Check out this post for a detailed description of PLSQL Tables.How to manipulate it and work on it in Oracle PLSQL block and benefits
What is cursor in oracle: Check out this post to get a great overview of What is cursor in oracle. Details about Cursor parameter, cursor loop, cursor for an update
PLSQL Constants : Check out this post on Oracle PLSQL Constants, How do you declare a constant variable in PL SQL, how to Declare a constant in Oracle stored procedure
Oracle PLSQL documentation
Oracle PlSQL interview question