Summary: This tutorial teaches you about the PL/SQL program language and its architecture.
Introduction to PL/SQL #
PL/SQL stands for Procedural Language/Structured Query Language. PL/SQL is an extension to the SQL language, specifically designed for the Oracle Database.
While SQL is excellent for interacting with data in a relational database management system (RDBMS), it is not designed to make complex programs.
SQL is declarative, meaning you tell SQL what data you want, and SQL will figure out how to get the data.
For example, when you write a SELECT statement, you tell the Oracle Database what data you want. You don’t instruct how the Database should find that data, such as which index to use, how to join tables, or which blocks to read; the Oracle Database will figure it out.
PL/SQL is imperative, allowing you to define how to get data and how to manipulate it. Note that PL/SQL also uses declarative parts of SQL, utilizing the best of both worlds.
Here’s how you can find PL/SQL helpful:
- Procedural constructs: PL/SQL supports programming constructs like if-else, loops, and variables, allowing you to write complex logic.
- Highly structured and Readable: PL/SQL is a highly structured and readable language. Its constructs express the intent of the code clearly, and it is straightforward to learn.
- Standard and Portable: PL/SQL is a standard and portable language for Oracle Database development. If you develop a program that executes on an Oracle Database, you can quickly move it to another compatible Oracle Database with minimal changes.
- Embedded language: PL/SQL can only be executed within an Oracle Database. It was not designed to be used as a standalone language like Java, C#, and C++. In other words, you cannot develop a PL/SQL program that runs on a system that does not have an Oracle Database.
- High-performance & integrated: PL/SQL offers specific constructs, such as the FORALL statement, to improve database performance by reducing the number of round trips between the database and the application.
PL/SQL architecture #
The following picture illustrates the PL/SQL architecture:
How it works:
- The PL/SQL engine compiles PL/SQL code into bytecode and executes the executable code. It can only be installed in an Oracle Database server or an application development tool like Oracle Forms.
- Once you submit a PL/SQL block to the Oracle Database server, the PL/SQL engine collaborates with the SQL engine to compile and execute the code.
- PL/SQL engine runs the procedural elements while the SQL engine processes the SQL statements.
Now, you should have a basic understanding of PL/SQL programming language and its architecture. Let’s create the first working PL/SQL anonymous block.
