Introduction to PL/SQL — Oracle's Procedural SQL Extension

-- Anonymous PL/SQL block — runs immediately, not stored
DECLARE
    -- Variable declarations
    v_employee_name  VARCHAR2(50);   -- String up to 50 chars
    v_salary         NUMBER(10, 2);  -- Number with 2 decimal places
    v_department_id  NUMBER := 10;   -- Initialised to 10

BEGIN
    -- Query into variables using SELECT INTO
    SELECT first_name || ' ' || last_name, salary
    INTO   v_employee_name, v_salary
    FROM   employees
    WHERE  department_id = v_department_id
    AND    ROWNUM = 1;

    -- Output using DBMS_OUTPUT (enable with SET SERVEROUTPUT ON)
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Salary: $' || TO_CHAR(v_salary, '999,999.99'));

EXCEPTION
    -- Handle the case where no rows are found
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found in department ' || v_department_id);
END;
/  -- The slash executes the block in SQL*Plus and SQLDeveloper

DECLARE
    -- %TYPE anchors variable type to the actual column definition
    v_salary    employees.salary%TYPE;
    v_grade     VARCHAR2(10);
    v_counter   NUMBER;

BEGIN
    SELECT salary INTO v_salary
    FROM employees WHERE employee_id = 100;

    -- IF / ELSIF / ELSE
    IF v_salary > 20000 THEN
        v_grade := 'Executive';
    ELSIF v_salary > 10000 THEN
        v_grade := 'Senior';
    ELSIF v_salary > 5000 THEN
        v_grade := 'Mid-level';
    ELSE
        v_grade := 'Junior';
    END IF;

    DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);

    -- FOR loop (most common) — implicit counter, no DECLARE needed
    FOR v_counter IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_counter);
    END LOOP;

END;
/