Introduction to PL/SQL : 1

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL. It is a powerful, block-structured language that enables developers to write complex database applications with procedural constructs like loops, conditions, and exception handling.

Key Features of PL/SQL:

1. Block Structure – PL/SQL programs are divided into blocks, making them modular and easy to debug.

2. Tight Integration with SQL – It allows seamless execution of SQL queries within its procedural code.

3. Procedural Constructs – Supports loops (FOR, WHILE), conditionals (IF-ELSE), and exception handling (EXCEPTION).

4. Error Handling – Provides robust exception handling mechanisms.

5. Performance Enhancements – Supports bulk processing (BULK COLLECT, FORALL) to improve performance.

6. Security Features – Allows defining access control and privilege management.

7. Triggers & Stored Procedures – Enables the creation of database triggers, functions, and procedures

PL/SQL Architecture

PL/SQL follows a layered architecture that ensures efficient interaction between the PL/SQL engine, SQL engine, and database server. The architecture consists of the following components:

Key Components of PL/SQL Architecture

The architecture consists of the following major components:

(a) PL/SQL Engine

• The PL/SQL engine processes PL/SQL blocks and executes procedural statements.

• It is located inside the Oracle Database Server.

• The engine separates procedural and SQL statements.

  • Procedural statements (loops, conditions) are processed within the PL/SQL engine.

  • SQL statements (SELECT, INSERT, UPDATE, DELETE) are sent to the SQL engine for execution.

(b) SQL Engine

• The SQL engine handles SQL queries and interacts with the database.

• It executes SQL statements inside PL/SQL programs.

(c) Database Server

• Stores tables, views, indexes, triggers, procedures, and functions.

• Executes SQL queries sent by the SQL engine.

• Returns data/results back to the PL/SQL program.

---

Execution Flow of a PL/SQL Program

1. User Submits PL/SQL Block – The PL/SQL block is sent for execution.

2. PL/SQL Engine Processes the Block – It separates procedural and SQL statements.

3. Procedural Statements Executed – The PL/SQL engine directly executes procedural logic.

4. SQL Engine Executes SQL Statements – The SQL engine interacts with the database.

5. Results Returned – The processed data is returned to the PL/SQL engine and displayed.

Context Switching: A Performance Concern

• When PL/SQL calls SQL Engine, a context switch occurs.

• If done excessively, it slows down execution.

• There are ways to minimize this, like using bulk operations instead of row-by-row processing.

Oracle Pluggable Database (Multitenant) Architecture

Oracle Multitenant Architecture was introduced in Oracle Database 12c to improve database consolidation and management. It enables multiple Pluggable Databases (PDBs) to run inside a single Container Database (CDB) while sharing system resources.

Key Components of Multitenant Architecture

(a) Container Database (CDB)

• The CDB is the main database that holds Pluggable Databases (PDBs).

• It contains shared system resources such as memory, background processes, and redo logs.

• A CDB includes:

  • Root Container (CDB$ROOT) – Stores system metadata, common users, and shared dictionary objects.

  • Seed Pluggable Database (PDB$SEED) – A template database used to create new PDBs.

  • Pluggable Databases (PDBs) – Independent databases plugged into the CDB.

(b) Pluggable Database (PDB)

• A PDB is an independent database within the CDB.

• It has its own schema, users, and application data.

• Each PDB operates as a separate database but shares system resources from the CDB.

• PDBs can be plugged into or unplugged from a CDB.

(c) Application Containers

• Introduced in Oracle 12.2, it allows multiple PDBs to share an Application Root Container.

• Useful for SaaS applications where different clients (PDBs) use shared application logic.

Oracle Multitenant Architecture Diagram

PL/SQL Variables

• What are variables?

  • Temporary storage for data during program execution.

  • Used to store, manipulate, and reuse values.

  • Essential for passing parameters to subprograms.

• Why do we need variables?
  ✅ Store values for later use.
  ✅ Reduce repetitive queries.
  ✅ Improve efficiency and flexibility in PL/SQL programs.

Types of Variables in PL/SQL

Oracle categorizes variables into four main groups:

1. Scalar Data Types → Hold a single value (simple types).

2. Reference Data Types → Store memory locations (pointers).

3. Large Objects (LOBs) → Store external files (images, text).

4. Composite Data Types → Store multiple values (collections, records)

Scalar Data Types in PL/SQL

Character Data Types

• CHAR(n) → Fixed-length string (max 32,767 bytes).

• VARCHAR2(n) → Variable-length string (max 32,767 bytes).

  • CHAR always allocates the full space.

  • VARCHAR2 only uses needed space.

Numeric Data Types

• NUMBER(p, s) → Fixed-length numeric with precision (p: total digits) and scale (s: decimal places).

  • Example: NUMBER(3,2) stores values like 1.23 (3 digits, 2 decimal places).

• BINARY_INTEGER / PLS_INTEGER → Faster and smaller than NUMBER, range: -2 billion to +2 billion.

• BINARY_FLOAT → 5 bytes, ends with "f" (e.g., 2.07f).

• BINARY_DOUBLE → 9 bytes, ends with "d" (e.g., 2.07d).

  → Binary float and double types do not raise exceptions in calculations.

  → Use PLS_INTEGER for performance, NUMBER for precision

  → BINARY_FLOAT and BINARY_DOUBLE are faster but less accurate.

Boolean Data Type

• BOOLEAN → Holds TRUE, FALSE, or NULL (default).

  • In other languages, default is FALSE.

  • In PL/SQL, default is NULL.

  • Cannot be printed using DBMS_OUTPUT.PUT_LINE.

Date & Time Data Types

• DATE → Stores date + time (hour, minute, second).

  • Use SYSDATE or CURRENT_DATE for the current date.

• TIMESTAMP(n) → Like DATE but includes milliseconds (n: precision from 0 to 9, default 6).

• TIMESTAMP WITH TIME ZONE → Stores timezone info with the timestamp.

• TIMESTAMP WITH LOCAL TIME ZONE → Stored in DB timezone, but returned in session timezone.

• INTERVAL YEAR TO MONTH → Stores year-month differences.

• INTERVAL DAY TO SECOND → Stores day-hour-minute-second differences.

    v_text varchar2(50) NOT NULL DEFAULT 'Hello';
    v_number1 number := 50;
    v_number2 number(2) := 50.42;
    v_number3 number(10,2) := 50.42;
    v_number4 PLS_INTEGER := 50;
    v_number5 BINARY_float := 50.42;
    v_DATE1 DATE := '22-NOV-18 12:01:32';
    v_DATE2 timestamp := systimestamp;
    v_DATE3 timestamp(9) WITH TIME ZONE := systimestamp;
    v_DATE4 interval day(4) to second (3) := '124 02:05:21.012 ';
    v_DATE5 interval year to month := '12-3';

Using %TYPE Attribute

• Dynamically assigns a variable the same data type as a database column.

• Useful for maintaining consistency with database schema changes.

• A variable can inherit the type of another variable.

• %TYPE does not enforce NOT NULL constraints. Only assign the data type.

DECLARE  
    v_type employees.job_id%TYPE;  
    v_type2 v_type%TYPE;  
BEGIN  
    v_type := 'IT_PROG';  
    v_type2 := 'SA_MAN';  
    DBMS_OUTPUT.PUT_LINE(v_type2);  
END;

PL/SQL Variable Scoping

• Scope refers to the range within which a variable is valid or accessible.

• Variables in PL/SQL have specific scopes based on their placement in blocks.

• Example: A variable declared in an outer block is accessible inside inner blocks, but not vice versa.

begin <<outer>>
DECLARE
  v_outer VARCHAR2(50) := 'Outer Variable!';
  v_text  VARCHAR2(20) := 'Out-text';
BEGIN 
  DECLARE
    v_text  VARCHAR2(20) := 'In-text';
    v_inner VARCHAR2(30) := 'Inner Variable';
  BEGIN
    dbms_output.put_line('inside -> ' || v_outer);
    dbms_output.put_line('inside -> ' || v_inner);
    dbms_output.put_line('inner -> ' || v_text);
    dbms_output.put_line('outer -> ' || outer.v_text);
  END;
  dbms_output.put_line('inside -> ' || v_inner);  
  dbms_output.put_line(v_outer);
  dbms_output.put_line(v_text);
END;
END outer;

Bind Variables in PL/SQL

• Bind variables are declared in a host environment and can be used across multiple blocks in SQL and PL/SQL.

• Unlike local variables, bind variables have a session-wide scope (only within the current worksheet).

• Bind variables must be declared before use and assigned within a BEGIN ... END block.

• Bind variables can be assigned to PL/SQL variables and used in queries.

VARIABLE var_text VARCHAR2(30);
VARIABLE var_number NUMBER;
DECLARE
    v_text VARCHAR2(30);
BEGIN
    :var_text := 'Hello PL/SQL';
    :var_number := 20;
    v_text := :var_text
    dbms_output.put_line(v_text);
    dbms_output.put_line(:var_text);
END;
/
PRINT var_text;
select * from employees where employee_id = :var_number;

• Bind variable can be printed outside of the block using print .

Limitations of Bind Variables

❌ Cannot assign values during declaration (must be inside BEGIN ... END).
❌ Limited Data Types – Only VARCHAR2, NUMBER, and CLOB are allowed.
❌ No Precision for Numbers – Cannot specify NUMBER(10,2), only NUMBER.
❌ Not Accessible Across multiple Worksheets – Scope is limited to the current session.

Control Structures in PL/SQL

IF Statement

DECLARE
    VAR1 NUMBER NOT NULL:=3;
BEGIN
   IF VAR1 > 5 THEN
    DBMS_OUTPUT.PUT_LINE('MORE THAN 5');
   ELSIF VAR1=4 THEN
    DBMS_OUTPUT.PUT_LINE('4');
   ELSE
    DBMS_OUTPUT.PUT_LINE('LESS OR EQUAL TO 5');
   END IF;
END;

Case Expression

DECLARE 
    CODE VARCHAR2(5) := 'C';
    VAR2 NUMBER ;
BEGIN
    VAR2 := CASE CODE
            WHEN 'A' THEN 5
            WHEN 'B' THEN 6
            ELSE 100
            END;
    DBMS_OUTPUT.PUT_LINE(VAR2);       
END;

Case Statement

DECLARE
    VAR1 VARCHAR2(5) := 6;
BEGIN
    CASE
        WHEN var1=5 THEN
            DBMS_OUTPUT.PUT_LINE('5555555555');
        ELSE
            DBMS_OUTPUT.PUT_LINE('------------');
        END CASE;
END;

Loop in PL/SQL

• The loop runs indefinitely unless an EXIT condition is met.

• If the condition is never satisfied, it results in an infinite loop.

• Infinite loops can cause session hangs and require manual termination, leading to rollback of all operations.

• The EXIT statement can be placed anywhere inside the loop.

Basic Loop

DECLARE
    VAR1 NUMBER(2) :=1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(VAR1);
        VAR1 := VAR1+1;
        EXIT WHEN VAR1=11;
    END LOOP;
END;

While Loop

DECLARE
    var1 NUMBER :=0;
BEGIN
    WHILE var1 < 10 LOOP
        DBMS_OUTPUT.PUT_LINE(var1);
        var1 := var1+1;
        EXIT WHEN var1 =5;
    END LOOP;
END;

For Loop

BEGIN
    FOR i IN 1..4 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;

BEGIN
  FOR i IN REVERSE 1..3 LOOP
    dbms_output.put_line('My counter is : ' || i);
  END LOOP;
END;

Loop with lable

BEGIN
    <<outer_loop>>
    For i in 1..5 Loop
        DBMS_OUTPUT.PUT_LINE('aaaaaaaa');
        <<inner_loop>>
        for i in 1..5 loop
            DBMS_OUTPUT.PUT_LINE('bbbbbbbbbbbb');
        End LOOP inner_loop;
    End Loop outer_loop;
END;

Continue Statement

BEGIN
  FOR i IN  1..3 LOOP
    -- if i=1 then 
    --     CONTINUE;
    -- end if;
    CONTINUE WHEN i=2;
    dbms_output.put_line('My counter is : ' || i);
  END LOOP;
END;

GOTO Statement in PL/SQL

The GOTO statement in PL/SQL is used to transfer control unconditionally to a labeled statement. It allows you to jump from one part of the program to another, making execution non-linear.

Restrictions on GOTO Statement

🚫 Cannot jump into:

• IF, CASE, or LOOP statements.

• Inner blocks from an outer block.

• A subprogram (procedure, function) from another scope.

• An EXCEPTION handler block.

✅ Can jump out of:

• Inner loops to outer loops.

• Exception handlers to an outer block.

BEGIN
    for i in 1..9 loop
        DBMS_OUTPUT.PUT_LINE(i);
        if i=5 then
            GOTO end_point;
        END IF;
    END LOOP;
    <<end_point>>
    DBMS_OUTPUT.PUT_LINE('End point..');
END;