Introduction to PL/SQL

PL/SQL (Procedural Language/SQL) is Oracle's proprietary procedural extension to SQL. Where SQL retrieves and manipulates data declaratively, PL/SQL adds variables, control flow, error handling, and modular code — all executing inside the Oracle Database engine, eliminating round-trips to the application layer.

Why Oracle Invented PL/SQL

Oracle released PL/SQL in 1991 to solve a fundamental problem: applications issuing many individual SQL statements suffered from enormous network latency. By running procedural logic inside the database, entire algorithms execute in one round-trip. PL/SQL also enforces business rules centrally, so they apply regardless of which application connects.

Key advantages:

• Performance — SQL and procedural logic co-exist in the same engine; no context-switch overhead for every statement.
• Portability — logic lives in the database, not scattered across application servers.
• Security — grant EXECUTE on a procedure without exposing the underlying tables.
• Maintainability — one place to fix bugs that affect every caller.

PL/SQL vs SQL

Anonymous vs Named Blocks

PL/SQL code comes in two forms:

Anonymous block — compiled and executed immediately, not stored:

-- Enable output in SQL*Plus / SQL Developer
SET SERVEROUTPUT ON;

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL!');
END;
/

Named block — stored in the data dictionary and reused:

• Stored procedures (CREATE OR REPLACE PROCEDURE)
• Functions (CREATE OR REPLACE FUNCTION)
• Packages (CREATE OR REPLACE PACKAGE)
• Triggers (CREATE OR REPLACE TRIGGER)

Your First PL/SQL Block

SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE
    v_message   VARCHAR2(100) := 'Hello, Oracle World!';
    v_emp_name  VARCHAR2(100);
BEGIN
    -- Print a literal message
    DBMS_OUTPUT.PUT_LINE(v_message);

    -- Query the employees table (Oracle HR schema)
    SELECT first_name || ' ' || last_name
    INTO   v_emp_name
    FROM   employees
    WHERE  employee_id = 100;

    DBMS_OUTPUT.PUT_LINE('CEO: ' || v_emp_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee 100 not found.');
END;
/

Output:

Hello, Oracle World!
CEO: Steven King

Where PL/SQL Is Used

Oracle Version Overview

The Execution Model

When you run a PL/SQL block, Oracle:

1. Compiles it to bytecode (or native machine code with PLSQL_CODE_TYPE=NATIVE).
2. Executes in the PL/SQL engine inside the Oracle server process.
3. Sends SQL statements to the SQL engine and receives results back — this crossing is the main performance cost that BULK COLLECT and FORALL are designed to minimise.

Summary

• PL/SQL is Oracle's procedural extension — it adds variables, loops, conditionals, and exception handling to SQL.
• Code runs inside the Oracle engine — no network round-trip per statement.
• Anonymous blocks run once; named blocks (procedures, functions, packages, triggers) are stored and reused.
• The Oracle HR schema (employees, departments, jobs) is the playground for all examples in this guide.