PL/SQL Fundamentals

20.1 What is PL/SQL?

Oracle's procedural extension to SQL. It runs inside the database engine and is tightly integrated with SQL.

Benefits: performance (fewer round trips), modularity, security, portability across platforms.

The PL/SQL engine executes procedural statements. SQL statements go to the SQL engine, and each switch between the two is a context switch.

20.2 Block Structure

Block types: anonymous, named (procedure, function, package, trigger, type body).

Nested blocks, variable scope and visibility, labels (<<outer>> with outer.v_name).

SET SERVEROUTPUT ON
DECLARE                                   -- optional
    v_name   employees.last_name%TYPE;    -- anchored type
    v_salary NUMBER(10,2) := 0;
    c_tax    CONSTANT NUMBER := 0.18;
    v_emp    employees%ROWTYPE;           -- whole row
BEGIN                                     -- mandatory
    SELECT last_name, salary INTO v_name, v_salary FROM employees WHERE employee_id = 100;
    DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_salary || ', tax ' || v_salary * c_tax);
EXCEPTION                                 -- optional
    WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Not found');
END;
/

20.3 Variables and Data Types

Scalar: NUMBER, PLS_INTEGER, SIMPLE_INTEGER, VARCHAR2(32767), DATE, TIMESTAMP, BOOLEAN.

Composite: records and collections (Module 26).

Reference: REF CURSOR. LOB types.

Anchored types: %TYPE, %ROWTYPE. Subtypes: SUBTYPE money_t IS NUMBER(12,2);.

Bind variables (:v) and substitution variables (&v).

20.4 Control Structures

-- IF
IF v_salary > 20000 THEN v_band := 'A';
ELSIF v_salary > 10000 THEN v_band := 'B';
ELSE v_band := 'C';
END IF;

-- CASE statement and expression
CASE v_grade WHEN 'A' THEN v_msg := 'Excellent'; WHEN 'B' THEN v_msg := 'Good'; ELSE v_msg := 'Other'; END CASE;
v_msg := CASE WHEN v_salary > 10000 THEN 'High' ELSE 'Normal' END;

-- Basic LOOP
LOOP
    v_i := v_i + 1;
    EXIT WHEN v_i > 10;
    CONTINUE WHEN MOD(v_i, 2) = 0;
    DBMS_OUTPUT.PUT_LINE(v_i);
END LOOP;

-- WHILE
WHILE v_i <= 10 LOOP v_i := v_i + 1; END LOOP;

-- Numeric FOR
FOR i IN 1 .. 10 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP;
FOR i IN REVERSE 1 .. 10 LOOP NULL; END LOOP;
FOR i IN 1 .. 20 BY 2 LOOP NULL; END LOOP;            -- 21c iteration controls

-- GOTO (avoid), NULL statement

20.5 SQL Inside PL/SQL

SELECT INTO must return exactly one row (otherwise NO_DATA_FOUND or TOO_MANY_ROWS).

DML with RETURNING INTO. Implicit cursor attributes: SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND.

DDL requires dynamic SQL (EXECUTE IMMEDIATE).