1
2
3
4
5
6
7
8
9
DECLARE
    -- Variable declarations
BEGIN
    -- Executable statements
EXCEPTION
    -- Exception handling
END;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Numeric Types
v_integer       INTEGER;
v_number        NUMBER(10,2);   -- Precision, Scale
v_binary_float  BINARY_FLOAT;
v_binary_double BINARY_DOUBLE;
v_pls_integer   PLS_INTEGER;    -- Faster than NUMBER for counters
v_simple_int    SIMPLE_INTEGER; -- Non-null, no overflow checking (fastest)

-- Character Types
v_char     CHAR(10);          -- Fixed-length
v_varchar  VARCHAR2(32767);   -- Up to 32767 bytes in PL/SQL
v_clob     CLOB;              -- Preferred over deprecated LONG
v_nvarchar NVARCHAR2(100);    -- Unicode

-- Date and Time Types
v_date         DATE;
v_timestamp    TIMESTAMP;
v_timestamp_tz TIMESTAMP WITH TIME ZONE;
v_interval_ym  INTERVAL YEAR TO MONTH;
v_interval_ds  INTERVAL DAY TO SECOND;

-- JSON Type (since 21c, native binary JSON)
v_json         JSON;

-- Boolean (PL/SQL only; supported as a SQL type since 23ai)
v_flag BOOLEAN;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Basic declaration
salary NUMBER(8,2);

-- With initialization
total_sales NUMBER(10,2) := 0;

-- Declaring using %TYPE (reference column type)
emp_salary employees.salary%TYPE;

-- Default values
counter INTEGER DEFAULT 0;

-- NOT NULL constraint requires initialization
v_id NUMBER NOT NULL := 0;

1
2
3
4
5
6
7
8
9
10
11
-- Declaring constants
PI            CONSTANT NUMBER  := 3.14159;
MAX_EMPLOYEES CONSTANT INTEGER := 1000;

-- Literal examples
string_literal  VARCHAR2(50) := 'Hello, World!';
q_string        VARCHAR2(50) := q'[O'Brien's data]';  -- Q-quoting
numeric_literal NUMBER := 42;
date_literal    DATE   := DATE '2026-01-01';
ts_literal      TIMESTAMP := TIMESTAMP '2026-01-01 12:00:00';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- IF-THEN-ELSE
IF salary > 50000 THEN
    bonus := salary * 0.1;
ELSIF salary > 30000 THEN
    bonus := salary * 0.05;
ELSE
    bonus := 0;
END IF;

-- CASE Statement
CASE department
    WHEN 'Sales'     THEN commission_rate := 0.15;
    WHEN 'Marketing' THEN commission_rate := 0.10;
    ELSE                  commission_rate := 0.05;
END CASE;

-- Searched CASE expression
v_grade := CASE
             WHEN score >= 90 THEN 'A'
             WHEN score >= 80 THEN 'B'
             ELSE 'C'
           END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Basic LOOP
LOOP
    -- statements
    EXIT WHEN condition;
END LOOP;

-- Numeric FOR loop
FOR i IN 1..10 LOOP
    -- statements
END LOOP;

-- FOR loop with step (since 21c)
FOR i IN 1..100 BY 2 LOOP
    -- iterates 1, 3, 5, ...
END LOOP;

-- FOR loop iterating over a collection (since 21c)
FOR v_item IN VALUES OF my_collection LOOP
    -- ...
END LOOP;

-- WHILE Loop
WHILE counter <= 100 LOOP
    counter := counter + 1;
END LOOP;

-- CONTINUE / CONTINUE WHEN
FOR i IN 1..10 LOOP
    CONTINUE WHEN MOD(i,2) = 0;
    -- runs only for odd i
END LOOP;

1
2
3
4
5
6
7
8
-- Implicit cursor used in SELECT INTO
SELECT salary INTO emp_salary
FROM   employees
WHERE  employee_id = 100;

-- Implicit cursor attributes
-- SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Cursor Declaration
CURSOR emp_cursor IS
    SELECT employee_id, salary FROM employees;

-- Cursor with Parameters
CURSOR dept_cursor(p_dept_id NUMBER) IS
    SELECT * FROM employees WHERE department_id = p_dept_id;

-- Cursor FOR Loop (recommended; implicit OPEN/FETCH/CLOSE)
BEGIN
    FOR emp_rec IN (SELECT * FROM employees) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.last_name);
    END LOOP;
END;

-- Manual cursor processing
DECLARE
    CURSOR c_employees IS SELECT * FROM employees;
    emp_record c_employees%ROWTYPE;
BEGIN
    OPEN c_employees;
    LOOP
        FETCH c_employees INTO emp_record;
        EXIT WHEN c_employees%NOTFOUND;
        -- Process record
    END LOOP;
    CLOSE c_employees;
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
    TYPE emp_refcur IS REF CURSOR;
    rc emp_refcur;
    r  employees%ROWTYPE;
BEGIN
    OPEN rc FOR 'SELECT * FROM employees WHERE department_id = :d' USING 10;
    LOOP
        FETCH rc INTO r;
        EXIT WHEN rc%NOTFOUND;
        -- process
    END LOOP;
    CLOSE rc;
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
BEGIN
    SELECT salary INTO v_salary
    FROM   employees
    WHERE  employee_id = -1;  -- Will raise NO_DATA_FOUND
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple employees found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        RAISE;  -- best practice: re-raise unless truly handled
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
    insufficient_funds EXCEPTION;
    PRAGMA EXCEPTION_INIT(insufficient_funds, -20010);
    account_balance NUMBER;
BEGIN
    IF account_balance < withdrawal_amount THEN
        RAISE insufficient_funds;
        -- or: RAISE_APPLICATION_ERROR(-20010, 'Insufficient funds');
    END IF;
EXCEPTION
    WHEN insufficient_funds THEN
        DBMS_OUTPUT.PUT_LINE('Insufficient funds');
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE OR REPLACE PROCEDURE calculate_bonus(
    p_employee_id      IN NUMBER,
    p_bonus_percentage IN NUMBER
) IS
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary
    FROM   employees
    WHERE  employee_id = p_employee_id;

    UPDATE employees
    SET    salary = salary * (1 + p_bonus_percentage/100)
    WHERE  employee_id = p_employee_id;
END;
/

-- Calling the procedure
BEGIN
    calculate_bonus(p_employee_id => 100, p_bonus_percentage => 10);
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE OR REPLACE FUNCTION get_annual_salary(
    p_employee_id IN NUMBER
) RETURN NUMBER
    DETERMINISTIC
    RESULT_CACHE
IS
    v_annual_salary NUMBER;
BEGIN
    SELECT salary * 12 INTO v_annual_salary
    FROM   employees
    WHERE  employee_id = p_employee_id;

    RETURN v_annual_salary;
END;
/

-- Using the function
SELECT get_annual_salary(employee_id) FROM employees;

1
2
3
4
5
6
7
8
WITH
  FUNCTION fmt(p NUMBER) RETURN VARCHAR2 IS
  BEGIN
      RETURN TO_CHAR(p, 'FM999,999.00');
  END;
SELECT fmt(salary) FROM employees;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE PACKAGE employee_pkg
    AUTHID DEFINER  -- or CURRENT_USER for invoker rights
IS
    -- Public declarations
    PROCEDURE hire_employee(
        p_name       IN VARCHAR2,
        p_department IN VARCHAR2
    );

    FUNCTION get_employee_count(
        p_department IN VARCHAR2
    ) RETURN NUMBER;

    -- Public constants
    max_salary CONSTANT NUMBER := 100000;
END employee_pkg;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE OR REPLACE PACKAGE BODY employee_pkg IS
    -- Private variables and constants
    g_company_name VARCHAR2(100) := 'ACME Corp';

    PROCEDURE hire_employee(
        p_name       IN VARCHAR2,
        p_department IN VARCHAR2
    ) IS
    BEGIN
        -- Hiring logic
        NULL;
    END hire_employee;

    FUNCTION get_employee_count(
        p_department IN VARCHAR2
    ) RETURN NUMBER IS
        v_count NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_count
        FROM   employees
        WHERE  department = p_department;

        RETURN v_count;
    END get_employee_count;
END employee_pkg;
/

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
    END IF;
    :NEW.hire_date := SYSDATE;
END;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE OR REPLACE TRIGGER emp_compound_trg
FOR INSERT OR UPDATE ON employees
COMPOUND TRIGGER
    TYPE id_t IS TABLE OF employees.employee_id%TYPE;
    g_ids id_t := id_t();

    AFTER EACH ROW IS
    BEGIN
        g_ids.EXTEND;
        g_ids(g_ids.LAST) := :NEW.employee_id;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        -- bulk post-processing using g_ids
        NULL;
    END AFTER STATEMENT;
END;
/

1
2
3
4
5
6
7
CREATE OR REPLACE TRIGGER logon_trigger
AFTER LOGON ON DATABASE
BEGIN
    DBMS_OUTPUT.PUT_LINE('User logged in: ' || USER);
END;
/

1
2
3
TYPE employee_list IS VARRAY(10) OF NUMBER;
v_employees employee_list := employee_list(1, 2, 3);

1
2
3
TYPE name_list IS TABLE OF VARCHAR2(50);
v_names name_list := name_list('John', 'Jane', 'Alice');

1
2
3
4
5
6
7
8
9
DECLARE
    TYPE salary_map IS TABLE OF NUMBER
        INDEX BY VARCHAR2(50);
    employee_salaries salary_map;
BEGIN
    employee_salaries('John') := 50000;
    employee_salaries('Jane') := 60000;
END;

1
2
3
4
5
6
7
8
DECLARE
    TYPE int_list IS TABLE OF INTEGER;
    v_nums int_list;
BEGIN
    -- Qualified expression
    v_nums := int_list(10, 20, 30);
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE OR REPLACE TYPE employee_type AS OBJECT (
    employee_id NUMBER,
    name        VARCHAR2(100),
    salary      NUMBER,
    MEMBER FUNCTION get_annual_salary RETURN NUMBER
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY employee_type AS
    MEMBER FUNCTION get_annual_salary RETURN NUMBER IS
    BEGIN
        RETURN salary * 12;
    END;
END;
/

-- Inheritance
CREATE OR REPLACE TYPE manager_type UNDER employee_type (
    team_size NUMBER
);
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DECLARE
    TYPE emp_id_list IS TABLE OF NUMBER;
    v_emp_ids emp_id_list;
BEGIN
    SELECT employee_id
    BULK COLLECT INTO v_emp_ids
    FROM   employees
    WHERE  department_id = 10;
END;

-- LIMIT clause to cap memory use
DECLARE
    CURSOR c IS SELECT * FROM employees;
    TYPE emp_t IS TABLE OF employees%ROWTYPE;
    rows emp_t;
BEGIN
    OPEN c;
    LOOP
        FETCH c BULK COLLECT INTO rows LIMIT 1000;
        EXIT WHEN rows.COUNT = 0;
        -- process batch
    END LOOP;
    CLOSE c;
END;

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
    TYPE id_list IS TABLE OF NUMBER;
    v_ids id_list := id_list(1, 2, 3);
BEGIN
    FORALL i IN INDICES OF v_ids SAVE EXCEPTIONS
        UPDATE employees
        SET    salary = salary * 1.1
        WHERE  employee_id = v_ids(i);

    -- Inspect SQL%BULK_EXCEPTIONS / SQL%BULK_ROWCOUNT afterward
END;

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
    v_table_name   VARCHAR2(128);
    v_dynamic_sql  VARCHAR2(500);
    v_result       NUMBER;
BEGIN
    v_table_name  := 'EMPLOYEES';
    v_dynamic_sql := 'SELECT COUNT(*) FROM '
                  || DBMS_ASSERT.SQL_OBJECT_NAME(v_table_name);

    EXECUTE IMMEDIATE v_dynamic_sql INTO v_result;
END;

1
2
3
4
5
BEGIN
    DBMS_OUTPUT.ENABLE(buffer_size => NULL);  -- unlimited
    DBMS_OUTPUT.PUT_LINE('Debug message');
END;

1
2
3
4
5
6
7
8
9
DECLARE
    v_cursor NUMBER;
    v_result NUMBER;
BEGIN
    v_cursor := DBMS_SQL.OPEN_CURSOR;
    -- Dynamic SQL operations
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;

1
2
3
4
5
6
7
8
DECLARE
    v_file UTL_FILE.FILE_TYPE;
BEGIN
    v_file := UTL_FILE.FOPEN('OUTPUT_DIR', 'report.txt', 'W');
    UTL_FILE.PUT_LINE(v_file, 'Report generated on ' || SYSDATE);
    UTL_FILE.FCLOSE(v_file);
END;

1
2
3
4
5
6
7
8
DECLARE
    jo JSON_OBJECT_T := JSON_OBJECT_T();
BEGIN
    jo.put('name', 'Alice');
    jo.put('salary', 60000);
    DBMS_OUTPUT.PUT_LINE(jo.to_string);
END;