PL/SQL architecture describes how PL/SQL code interacts with the Oracle Database engine during execution.
- The PL/SQL Engine processes procedural statements like loops and conditions.
- The SQL Engine executes SQL statements such as SELECT, INSERT, UPDATE and DELETE.
- Both engines work together for efficient execution of PL/SQL programs.
- PL/SQL blocks are sent to the Oracle server for processing.
- It reduces network traffic by executing multiple statements together.
Components of PL/SQL Architecture
1. PL/SQL Engine
The PL/SQL engine is responsible for executing procedural statements in a PL/SQL program. It handles operations like variables, loops, conditions, functions and exception handling.
Functions:
- Executes procedural code
- Handles loops and conditions
- Processes functions and procedures
- Sends SQL statements to SQL engine
2. SQL Engine
The SQL engine executes SQL statements present in the PL/SQL block. It processes commands like SELECT, INSERT, UPDATE and DELETE. It helps to communicates with the Oracle Database Server to retrieve or modify data.
Functions:
- Executes SQL queries
- Retrieves data from tables
- Updates and deletes records
- Communicates with database server
3. Oracle Database Server
The Oracle Database Server stores and manages all database data. It contains tables, views, indexes and other database objects. It helps to accesses the database server whenever data operations are needed.
Functions:
- Stores database data
- Maintains tables and indexes
- Manages transactions
- Provides security and recovery
Working of PL/SQL Architecture
Step 1: User Submits PL/SQL Block
The process starts when the user submits a PL/SQL block to the Oracle Database.
Step 2: PL/SQL Engine Processes Procedural Statements
The PL/SQL engine checks and executes all procedural statements such as:
- Variables
- Conditions
- Loops
- Functions
If a SQL statement is found, it is sent to the SQL engine.
Step 3: SQL Engine Executes SQL Statements
The SQL engine receives SQL commands like:
- SELECT
- INSERT
- UPDATE
- DELETE
It processes these queries and communicates with the Oracle Database Server.
Step 4: Oracle Database Server Performs Operations
The Oracle Database Server performs the required database operations such as:
- Retrieving data
- Updating records
- Inserting rows
- Deleting data
Step 5: Result is Returned
After execution:
- The SQL engine sends the result back to the PL/SQL engine.
- The PL/SQL engine returns the final output to the user.