Introduction to PL/SQL

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

What Is PL/SQL?

PL/SQL stands for Procedural Language extensions to SQL. It is Oracle's built-in programming language that wraps SQL with the full power of procedural logic — variables, conditions, loops, error handling, and reusable program units. Where SQL answers the question "what data do I want?", PL/SQL answers "what should I do with it?" You write PL/SQL directly inside Oracle — it runs on the database server, close to the data, which makes it fast and transactionally safe.

PL/SQL is used to write stored procedures, functions, triggers, and packages — all of which live inside the database and can be called from applications, scheduled jobs, or other PL/SQL programs. Even without writing reusable objects, anonymous PL/SQL blocks let you run procedural logic on demand directly in SQL Developer or SQL*Plus.

• PL/SQL runs inside the Oracle database engine — not in the application layer
• Every PL/SQL program is built from blocks — the fundamental unit of PL/SQL code
• A block has up to four sections: DECLARE, BEGIN, EXCEPTION, and END
• Only BEGIN and END are mandatory — DECLARE and EXCEPTION are optional
• DBMS_OUTPUT.PUT_LINE() prints text to the output console — used for debugging and learning
• PL/SQL is case-insensitive for keywords and identifiers — convention is uppercase keywords, lowercase variable names

The PL/SQL Block Structure

Every PL/SQL program — from the simplest script to a complex stored procedure — follows the same four-section structure. The DECLARE section defines variables and constants. The BEGIN section contains the executable statements. The EXCEPTION section handles errors. The END keyword closes the block. A semicolon after END terminates the block, and a forward slash / on its own line tells SQL Developer or SQL*Plus to execute it.

-- The basic PL/SQL block structure.
-- DECLARE and EXCEPTION are optional -- BEGIN and END are always required.
DECLARE
    v_message   VARCHAR2(100);   -- variable declaration: name  datatype
    v_count     NUMBER := 0;     -- := is the assignment operator in PL/SQL
BEGIN
    v_message := 'Hello from PL/SQL';
    v_count   := v_count + 1;
    DBMS_OUTPUT.PUT_LINE(v_message);          -- prints to the output console
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/    -- the forward slash executes the block in SQL Developer / SQL*Plus

• := is the PL/SQL assignment operator — SQL uses = for comparison, PL/SQL uses := for assignment
• DBMS_OUTPUT.PUT_LINE() sends text to the output buffer — in SQL Developer enable it with View → DBMS Output
• SQLERRM returns the error message for the most recent exception — useful in EXCEPTION handlers
• The forward slash / is not part of PL/SQL syntax — it is a SQL*Plus and SQL Developer command that submits the block for execution

Variables and Data Types

Variables are declared in the DECLARE section with the format variable_name datatype [DEFAULT value];. PL/SQL supports all Oracle SQL data types — VARCHAR2, NUMBER, DATE, BOOLEAN — plus the special %TYPE and %ROWTYPE anchors. %TYPE declares a variable with the same data type as a specific table column, so the variable automatically adapts if the column definition changes. %ROWTYPE declares a variable that holds an entire row of a table.

DECLARE
    -- Standard variable declarations
    v_product_name   VARCHAR2(200);
    v_price          NUMBER(10, 2)  DEFAULT 0;
    v_order_date     DATE           DEFAULT SYSDATE;
    v_is_active      BOOLEAN        := TRUE;
    -- %TYPE anchor: inherits the data type of products.price
    -- If the column definition changes, this variable adapts automatically
    v_anchored_price dataplexa_store.products.price%TYPE;
    -- %ROWTYPE anchor: holds an entire row from the products table
    v_product_row    dataplexa_store.products%ROWTYPE;
BEGIN
    -- Assign a value by querying the database -- INTO puts the result into the variable
    SELECT product_name, price
    INTO   v_product_name, v_price
    FROM   dataplexa_store.products
    WHERE  product_id = 7;
    DBMS_OUTPUT.PUT_LINE('Product: ' || v_product_name);
    DBMS_OUTPUT.PUT_LINE('Price:   ' || v_price);
    -- Fetch an entire row using %ROWTYPE
    SELECT *
    INTO   v_product_row
    FROM   dataplexa_store.products
    WHERE  product_id = 7;
    DBMS_OUTPUT.PUT_LINE('Category: ' || v_product_row.category);
END;
/

• SELECT ... INTO is how PL/SQL retrieves data from the database into variables — it must return exactly one row or an exception is raised
• %TYPE is preferred over hardcoding a data type — if products.price changes from NUMBER(10,2) to NUMBER(12,2) the variable adapts without a code change
• %ROWTYPE lets you access individual columns as v_product_row.column_name — cleaner than declaring a separate variable per column
• BOOLEAN exists in PL/SQL but not in Oracle SQL — you cannot store a BOOLEAN in a table column

Conditional Logic — IF / ELSIF / ELSE

PL/SQL uses IF / ELSIF / ELSE / END IF for conditional branching. You can nest conditions and chain as many ELSIF branches as needed. Note the spelling — Oracle uses ELSIF, not ELSEIF. A CASE statement is also available and often cleaner than a long chain of ELSIF when comparing one variable against many possible values.

DECLARE
    v_salary     dataplexa_hr.employees.salary%TYPE;
    v_emp_name   VARCHAR2(200);
    v_grade      VARCHAR2(20);
BEGIN
    SELECT first_name || ' ' || last_name, salary
    INTO   v_emp_name, v_salary
    FROM   dataplexa_hr.employees
    WHERE  employee_id = 105;
    -- IF / ELSIF / ELSE -- note the spelling: ELSIF not ELSEIF
    IF    v_salary >= 80000 THEN
        v_grade := 'Senior';
    ELSIF v_salary >= 60000 THEN
        v_grade := 'Mid-level';
    ELSIF v_salary >= 40000 THEN
        v_grade := 'Junior';
    ELSE
        v_grade := 'Entry';
    END IF;
    DBMS_OUTPUT.PUT_LINE(v_emp_name || ' | Salary: ' || v_salary || ' | Grade: ' || v_grade);
END;
/

• Every IF block must end with END IF; — forgetting it raises a compilation error
• ELSIF — one word, no space — is Oracle's spelling; ELSEIF is not valid PL/SQL
• Conditions are evaluated top to bottom — the first branch that is TRUE executes and the rest are skipped

Loops

PL/SQL provides three loop types. A basic loop runs until an explicit EXIT statement is reached. A WHILE loop checks its condition before each iteration. A FOR loop iterates over a numeric range automatically, declaring the loop counter variable implicitly — you do not need to declare it in DECLARE. All loops end with END LOOP;.

DECLARE
    v_counter   NUMBER := 1;
    v_total     NUMBER := 0;
BEGIN
    -- Basic loop with EXIT WHEN
    LOOP
        v_total   := v_total + v_counter;
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 5;   -- exits when condition is true
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Basic loop total (1 to 5): ' || v_total);
    -- FOR loop -- counter declared implicitly, no need to declare i in DECLARE
    v_total := 0;
    FOR i IN 1..5 LOOP             -- i goes from 1 to 5 inclusive
        v_total := v_total + i;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('FOR loop total (1 to 5):   ' || v_total);
    -- WHILE loop
    v_total   := 0;
    v_counter := 1;
    WHILE v_counter <= 5 LOOP
        v_total   := v_total + v_counter;
        v_counter := v_counter + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('WHILE loop total (1 to 5): ' || v_total);
END;
/

• All three loops produce the same result — choose the type that best matches the logic: FOR when the range is known, WHILE when the exit condition is dynamic, basic loop when the exit point is in the middle
• The FOR loop counter i is read-only inside the loop — you cannot assign a value to it
• EXIT WHEN condition is cleaner than IF condition THEN EXIT; END IF; — both work
• A loop with no EXIT condition and no WHILE clause runs forever — Oracle will eventually raise a resource error

Exception Handling

When an error occurs inside a PL/SQL block Oracle raises an exception. Control jumps immediately to the EXCEPTION section. If there is no EXCEPTION section, the error propagates to the calling environment. Oracle provides named exceptions for common errors — NO_DATA_FOUND when a SELECT INTO returns zero rows, TOO_MANY_ROWS when it returns more than one, ZERO_DIVIDE for division by zero. WHEN OTHERS catches any exception not handled by a named handler.

DECLARE
    v_product_name   dataplexa_store.products.product_name%TYPE;
    v_price          dataplexa_store.products.price%TYPE;
BEGIN
    SELECT product_name, price
    INTO   v_product_name, v_price
    FROM   dataplexa_store.products
    WHERE  product_id = 9999;   -- no product with this id exists
    DBMS_OUTPUT.PUT_LINE(v_product_name || ': ' || v_price);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- raised when SELECT INTO returns zero rows
        DBMS_OUTPUT.PUT_LINE('No product found with that ID.');
    WHEN TOO_MANY_ROWS THEN
        -- raised when SELECT INTO returns more than one row
        DBMS_OUTPUT.PUT_LINE('Query returned more than one row.');
    WHEN OTHERS THEN
        -- catches anything not handled above
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/

• NO_DATA_FOUND is the most common exception in PL/SQL — always handle it when using SELECT INTO
• SQLERRM returns the Oracle error message text — SQLCODE returns the error number
• WHEN OTHERS must be the last handler in the EXCEPTION section — placing a named handler after it causes a compilation error
• After the EXCEPTION section runs, execution does not return to the BEGIN section — the block ends after the handler completes

Lesson Summary

Practice Questions

Quiz

Next up — PL/SQL Procedures & Functions — How to create reusable named program units in Oracle, the difference between a procedure and a function, and how parameters work.