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
-- Numeric Types
v_integer INTEGER;
v_number NUMBER(10,2);  -- Precision, Scale
v_binary_float BINARY_FLOAT;
v_binary_double BINARY_DOUBLE;

-- Character Types
v_char CHAR(10);        -- Fixed-length
v_varchar VARCHAR2(100);-- Variable-length
v_long LONG;            -- Up to 2GB

-- Date and Time Types
v_date DATE;
v_timestamp TIMESTAMP;
v_timestamp_tz TIMESTAMP WITH TIME ZONE;

-- Boolean (PL/SQL only)
v_flag BOOLEAN;

1
2
3
4
5
6
7
8
9
10
11
12
-- 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;

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

-- Literal examples
string_literal VARCHAR2(50) := 'Hello, World!';
numeric_literal NUMBER := 42;
date_literal DATE := TO_DATE('2025-01-01', 'YYYY-MM-DD');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Basic LOOP
LOOP
    -- statements
    EXIT WHEN condition;
END LOOP;

-- FOR Loop
FOR i IN 1..10 LOOP
    -- statements
END LOOP;

-- WHILE Loop
WHILE counter <= 100 LOOP
    -- statements
    counter := counter + 1;
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
-- %FOUND, %NOTFOUND, %ROWCOUNT

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
-- 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 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
BEGIN
    -- Code that might raise an exception
    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);
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
    -- Declare custom exception
    insufficient_funds EXCEPTION;
    account_balance NUMBER;
BEGIN
    -- Raise custom exception
    IF account_balance < withdrawal_amount THEN
        RAISE 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
22
CREATE OR REPLACE PROCEDURE calculate_bonus(
    p_employee_id IN NUMBER,
    p_bonus_percentage IN NUMBER
) IS
    v_salary employees.salary%TYPE;
BEGIN
    -- Procedure logic
    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(100, 10);
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR REPLACE FUNCTION get_annual_salary(
    p_employee_id IN NUMBER
) RETURN NUMBER 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
9
10
11
12
13
14
15
16
CREATE OR REPLACE PACKAGE employee_pkg 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';
    
    -- Implementation of procedures and functions
    PROCEDURE hire_employee(
        p_name IN VARCHAR2, 
        p_department IN VARCHAR2
    ) IS
    BEGIN
        -- Hiring logic
    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
12
13
14
CREATE OR REPLACE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    -- Validate data before insertion
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
    END IF;
    
    -- Set default values
    :NEW.hire_date := SYSDATE;
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
4
-- Declare type
TYPE employee_list IS VARRAY(10) OF NUMBER;
v_employees employee_list := employee_list(1, 2, 3);

1
2
3
4
-- Declare type
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
10
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
9
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE TYPE employee_type AS OBJECT (
    employee_id NUMBER,
    name VARCHAR2(100),
    salary NUMBER,
    
    -- Member method
    MEMBER FUNCTION get_annual_salary RETURN NUMBER
);
/

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

1
2
3
4
5
6
7
8
9
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;

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

1
2
3
4
5
6
7
8
9
10
11
DECLARE
    v_table_name VARCHAR2(100);
    v_dynamic_sql VARCHAR2(500);
    v_result NUMBER;
BEGIN
    v_table_name := 'EMPLOYEES';
    v_dynamic_sql := 'SELECT COUNT(*) FROM ' || v_table_name;
    
    EXECUTE IMMEDIATE v_dynamic_sql INTO v_result;
END;

1
2
3
4
5
BEGIN
    DBMS_OUTPUT.ENABLE(buffer_size => NULL);
    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.PUTF(v_file, 'Report generated on %s', SYSDATE);
    UTL_FILE.FCLOSE(v_file);
END;