Oracle PL/SQL – Introduction and Basic Structure

Subject: Information Technology – Database Systems
Grade: 12th Grade – HTL Computer Science
Prerequisites: Oracle SQL (DDL, DML, DQL, Joins, Subqueries)
Author: HTL Pinkafeld – IF/IT

1. What is PL/SQL?

1.1 Definition and Context

PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural extension of SQL. While pure SQL is a declarative language (you describe what you want), PL/SQL additionally allows:

• Variables, data types, and assignments
• Control structures (IF, LOOP, FOR, WHILE)
• Error handling (Exception Handling)
• Reusable program modules (procedures, functions, packages)
• Automatic reactions to database operations (triggers)

SQL          → Declarative: "Give me all employees with salary > 3000"
PL/SQL       → Procedural: "For each employee: check salary, give bonus, save result"

1.2 History and Relevance

1.3 Execution Environments

PL/SQL code runs directly on the database server – not on the client. This has key advantages:

• Less network traffic: Multiple SQL statements are bundled in one block
• Security: Logic resides in the database, not in client code
• Performance: Data access without network round trips

Client (SQL Developer / Application)
    │  1× PL/SQL block sent
    ▼
Oracle Database Server
    ├── PL/SQL Engine (Procedural Code)
    └── SQL Engine (SQL statements inside the block)
    │  Result returned
    ▼
Client

2. The Anonymous PL/SQL Block

2.1 Basic Structure

A PL/SQL block consists of up to four sections:

DECLARE
    -- Declaration section (optional)
    -- Variables, constants, types, cursors
BEGIN
    -- Execution section (mandatory)
    -- SQL statements and PL/SQL statements
EXCEPTION
    -- Error handling section (optional)
    -- Reacts to runtime errors
END;
/

Note: The / at the end submits the block for execution in SQL*Plus or SQL Developer.

2.2 Minimal Block

The simplest valid PL/SQL block consists only of BEGIN and END:

BEGIN
    NULL; -- Empty block; NULL is the "no-operation" statement
END;
/

2.3 First Example

DECLARE
    v_name    VARCHAR2(50) := 'HTL Pinkafeld';
    v_year    NUMBER       := 2025;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Welcome to ' || v_name || ' – Class of ' || v_year);
END;
/

Output:

Welcome to HTL Pinkafeld – Class of 2025

2.4 Nested Blocks

PL/SQL blocks can be nested. Inner blocks have access to outer block variables, but not vice versa:

DECLARE
    v_outer VARCHAR2(20) := 'Outer';
BEGIN
    DECLARE
        v_inner VARCHAR2(20) := 'Inner';
    BEGIN
        DBMS_OUTPUT.PUT_LINE(v_outer || ' and ' || v_inner); -- OK
    END;
    -- DBMS_OUTPUT.PUT_LINE(v_inner); -- ERROR: v_inner not visible here
END;
/

3. Variables and Data Types

3.1 Declaration Syntax

variable_name  data_type  [NOT NULL]  [:= initial_value | DEFAULT initial_value];

Examples:

DECLARE
    v_first_name  VARCHAR2(30);                        -- NULL (no initial value)
    v_last_name   VARCHAR2(50) NOT NULL := 'Sample';   -- Mandatory initial value
    v_salary      NUMBER(8,2)  DEFAULT 0;              -- Initial value 0
    v_active      BOOLEAN      := TRUE;                -- Boolean
    v_date        DATE         := SYSDATE;             -- Current date
BEGIN
    v_first_name := 'John';
    DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name);
END;
/

3.2 Scalar Data Types – Overview

3.3 String Operations

DECLARE
    v_first  VARCHAR2(20) := 'John';
    v_last   VARCHAR2(20) := 'Doe';
    v_full   VARCHAR2(41);
BEGIN
    v_full := v_first || ' ' || v_last;            -- Concatenation with ||
    DBMS_OUTPUT.PUT_LINE(UPPER(v_full));            -- JOHN DOE
    DBMS_OUTPUT.PUT_LINE(LENGTH(v_full));           -- 8
    DBMS_OUTPUT.PUT_LINE(SUBSTR(v_full, 1, 4));     -- John
    DBMS_OUTPUT.PUT_LINE(INSTR(v_full, 'Doe'));     -- Position of 'Doe'
END;
/

4. Constants and Scalar Types

4.1 Constants

The CONSTANT keyword makes a variable immutable. An initial value is mandatory:

DECLARE
    c_vat      CONSTANT NUMBER := 0.20;       -- 20% VAT
    c_pi       CONSTANT NUMBER := 3.14159265;
    c_company  CONSTANT VARCHAR2(50) := 'HTL Pinkafeld';
BEGIN
    DBMS_OUTPUT.PUT_LINE('VAT: ' || (100 * c_vat) || ' %');
    -- c_vat := 0.19;  -- ERROR: Constants cannot be changed
END;
/

4.2 Subtypes

SUBTYPE defines custom named types based on existing types:

DECLARE
    SUBTYPE t_name    IS VARCHAR2(50);
    SUBTYPE t_salary  IS NUMBER(8,2);

    v_employee t_name   := 'Anna Baker';
    v_wage     t_salary := 3450.00;
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_employee || ': ' || v_wage || ' USD');
END;
/

5. %TYPE and %ROWTYPE

5.1 %TYPE – Inherit Column Type

With %TYPE, a variable automatically inherits the data type of a table column. This makes code more robust against schema changes:

DECLARE
    -- v_ename has the same type as the ENAME column in EMP
    v_ename   emp.ename%TYPE;
    v_sal     emp.sal%TYPE;
    v_deptno  emp.deptno%TYPE := 10;
BEGIN
    SELECT ename, sal
    INTO   v_ename, v_sal
    FROM   emp
    WHERE  empno = 7369;

    DBMS_OUTPUT.PUT_LINE(v_ename || ' earns ' || v_sal || ' USD');
END;
/

5.2 %ROWTYPE – Inherit Full Row Type

With %ROWTYPE, a variable can hold an entire table row. Fields correspond to table columns:

DECLARE
    v_emp  emp%ROWTYPE;    -- Contains all fields of the EMP table
BEGIN
    SELECT *
    INTO   v_emp
    FROM   emp
    WHERE  empno = 7839;

    DBMS_OUTPUT.PUT_LINE('Name:       ' || v_emp.ename);
    DBMS_OUTPUT.PUT_LINE('Job:        ' || v_emp.job);
    DBMS_OUTPUT.PUT_LINE('Department: ' || v_emp.deptno);
    DBMS_OUTPUT.PUT_LINE('Salary:     ' || v_emp.sal);
END;
/

Advantage: If columns are added or types changed, %ROWTYPE adapts automatically – no code changes needed.

6. Composite Types – RECORD

6.1 Defining a RECORD Type

A RECORD is a user-defined composite type, similar to a struct in C or a class in Java (without methods):

DECLARE
    -- Define a custom RECORD type
    TYPE t_person IS RECORD (
        first_name   VARCHAR2(30),
        last_name    VARCHAR2(50),
        birth_date   DATE,
        active       BOOLEAN := TRUE
    );

    -- Declare a variable of this type
    v_person t_person;
BEGIN
    v_person.first_name := 'Maria';
    v_person.last_name  := 'Baker';
    v_person.birth_date := TO_DATE('2005-09-15', 'YYYY-MM-DD');

    DBMS_OUTPUT.PUT_LINE(v_person.first_name || ' ' || v_person.last_name);
    DBMS_OUTPUT.PUT_LINE('Born: ' || TO_CHAR(v_person.birth_date, 'DD/MM/YYYY'));
END;
/

6.2 RECORD vs. %ROWTYPE

7. DBMS_OUTPUT – Output in PL/SQL

7.1 Activation

For output to be visible, server output must be activated:

-- In SQL*Plus or SQL Developer:
SET SERVEROUTPUT ON

-- Or within the PL/SQL block:
DBMS_OUTPUT.ENABLE(1000000);  -- 1 MB buffer

7.2 Output Procedures

BEGIN
    -- Output a line with newline:
    DBMS_OUTPUT.PUT_LINE('Line 1');
    DBMS_OUTPUT.PUT_LINE('Line 2');

    -- Without newline:
    DBMS_OUTPUT.PUT('Part A ');
    DBMS_OUTPUT.PUT('Part B');
    DBMS_OUTPUT.NEW_LINE;  -- Explicit newline

    -- Numbers must be converted to VARCHAR2:
    DBMS_OUTPUT.PUT_LINE('Value: ' || TO_CHAR(42.5, '999.99'));
END;
/

7.3 Number Formats with TO_CHAR

DECLARE
    v_num NUMBER := 1234567.89;
BEGIN
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_num, '9,999,999.99'));  -- 1,234,567.89
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_num, 'FM999G999D99'));  -- 1234567.89 (no spaces)
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI'));
END;
/

8. Scalar Expressions and Assignments

8.1 Assignment Operator

PL/SQL uses := for assignments (not = like in other languages). The = operator is exclusively used for comparison:

DECLARE
    v_a NUMBER := 10;
    v_b NUMBER := 3;
    v_result NUMBER;
BEGIN
    v_result := v_a + v_b;    -- Addition
    v_result := v_a - v_b;    -- Subtraction
    v_result := v_a * v_b;    -- Multiplication
    v_result := v_a / v_b;    -- Division (note: floating point!)
    v_result := v_a ** 2;     -- Power (10²)
    v_result := MOD(v_a, v_b); -- Modulo (remainder)

    DBMS_OUTPUT.PUT_LINE('10 mod 3 = ' || v_result);  -- 1
END;
/

8.2 Comparison Operators

8.3 Logical Operators

DECLARE
    v_x      NUMBER  := 15;
    v_active BOOLEAN := TRUE;
BEGIN
    IF v_x > 10 AND v_active THEN
        DBMS_OUTPUT.PUT_LINE('Condition met');
    END IF;

    IF v_x < 5 OR NOT v_active THEN
        DBMS_OUTPUT.PUT_LINE('Alternative');
    END IF;
END;
/

NULL logic: NULL AND TRUE = NULL, NULL OR TRUE = TRUE, NOT NULL = NULL
Always use IS NULL / IS NOT NULL for comparisons with NULL!

9. SQL in PL/SQL – SELECT INTO

9.1 Querying a Single Value

The SELECT INTO statement reads exactly one row into variables:

DECLARE
    v_ename  VARCHAR2(10);
    v_sal    NUMBER;
BEGIN
    SELECT ename, sal
    INTO   v_ename, v_sal
    FROM   emp
    WHERE  empno = 7839;

    DBMS_OUTPUT.PUT_LINE('Boss: ' || v_ename || ', Salary: ' || v_sal);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found!');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('More than one row found!');
END;
/

Important: SELECT INTO must return exactly one row.
No result → NO_DATA_FOUND, more than one row → TOO_MANY_ROWS

9.2 Aggregate Functions

Aggregate functions always return exactly one value – safe for SELECT INTO:

DECLARE
    v_count   NUMBER;
    v_max_sal NUMBER;
    v_avg_sal NUMBER;
BEGIN
    SELECT COUNT(*), MAX(sal), AVG(sal)
    INTO   v_count, v_max_sal, v_avg_sal
    FROM   emp
    WHERE  deptno = 20;

    DBMS_OUTPUT.PUT_LINE('Employees:   ' || v_count);
    DBMS_OUTPUT.PUT_LINE('Max salary:  ' || v_max_sal);
    DBMS_OUTPUT.PUT_LINE('Avg salary:  ' || ROUND(v_avg_sal, 2));
END;
/

9.3 DML in PL/SQL

INSERT, UPDATE, DELETE, and MERGE can be used directly in PL/SQL blocks:

DECLARE
    v_empno NUMBER := 9999;
BEGIN
    -- INSERT
    INSERT INTO emp (empno, ename, job, deptno, sal, hiredate)
    VALUES (v_empno, 'TESTUSER', 'CLERK', 10, 2000, SYSDATE);

    -- UPDATE
    UPDATE emp
    SET    sal = sal * 1.10
    WHERE  empno = v_empno;

    -- DELETE
    DELETE FROM emp WHERE empno = v_empno;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Transactions completed.');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

10. Summary and Outlook

10.1 Core Concepts of This Chapter

10.2 Common Mistakes for Beginners

10.3 Outlook

The next chapter covers Control Structures:

• IF / ELSIF / ELSE for branching
• CASE for multiple selections
• LOOP, WHILE, FOR for loops
• EXIT and CONTINUE for loop control