PL/SQL Programming Fundamentals

The PL/SQL material originally continued my database course notes. I keep it as a separate article because procedural database programming requires a different way of thinking from SQL's relational query model. The original sequence on block structure, scope, cursors, exceptions and subprograms is preserved here.

Unit 1: Introduction to PL/SQL

Database programming with PL/SQL

PL/SQL is Oracle's procedural extension to SQL.

SQL is declarative. PL/SQL combines SQL statements with:

• variables,
• conditions,
• loops,
• error handling,
• procedures,
• functions,
• packages.

Its main use is to implement logic that should execute close to the database.

Block types

The basic execution unit of PL/SQL is the block.

Blocks appear in:

• anonymous blocks,
• stored procedures,
• stored functions,
• packages,
• triggers.

These notes focus primarily on anonymous blocks, procedures, functions and packages.

PL/SQL program structure

A basic block is:

DECLARE
    -- declarations
BEGIN
    -- executable statements
EXCEPTION
    -- error handlers
END;
/

DECLARE and EXCEPTION are optional when they are not needed. BEGIN and END delimit the executable part.

Executing blocks

PL/SQL blocks can be executed from Oracle client tools. SQLPlus was the main interface in the original course context. SQLPlus remains usable, while SQLcl, SQL Developer and other clients can work with the same database engine.

The essential point is not the client application but the syntax and execution semantics of the SQL and PL/SQL sent to the server.

SQL and PL/SQL files

SQL and PL/SQL commands can be stored in files and executed repeatedly. Compared with entering commands interactively, this supports:

• repeatability,
• version control,
• review,
• deployment automation.

Variables and constants

PL/SQL variables are declared in the declaration section.

DECLARE
    v_name VARCHAR2(100);
    v_count NUMBER := 0;
BEGIN
    NULL;
END;
/

A constant can be declared as:

c_vat CONSTANT NUMBER := 0.20;

Using SQL statements in PL/SQL

SQL can be executed directly inside PL/SQL. When exactly one row is expected, SELECT ... INTO is commonly used:

SELECT NAME, SALARY
INTO v_name, v_salary
FROM EMPLOYEE
WHERE EMPLOYEE_NO = 100;

Cardinality matters. If the query returns no rows or more than one row, PL/SQL may raise an exception.

DML

Data modification statements execute in the current transaction context:

UPDATE EMPLOYEE
SET SALARY = SALARY * 1.10
WHERE DEPARTMENT_NO = 20;

Transaction statements

When required:

COMMIT;
ROLLBACK;

may be used. Whether a reusable stored procedure should commit on its own is an architectural decision. In many application designs, the caller owns the transaction boundary so that several database operations can succeed or fail as one unit.

Bind variables

A bind variable separates the SQL structure from parameter values. Conceptually:

SQL structure + parameter values

Bind variables are important for:

• reducing SQL injection risk,
• reducing repeated parsing overhead,
• improving statement and plan sharing.

They are not a substitute for authorization or input validation, but they are the normal mechanism for supplying data values to parameterized SQL.

Declaration section

The DECLARE section can contain:

• variables,
• constants,
• types,
• cursors,
• local subprograms,
• exceptions.

Default values

A variable may be initialized with:

v_count NUMBER := 0;

DEFAULT can also be used where the PL/SQL grammar permits it.

NULL checks

PL/SQL inherits SQL's three-valued logic where applicable. A null test should therefore use IS NULL rather than equality:

IF v_value IS NULL THEN
    ...
END IF;

%TYPE

%TYPE derives a variable's type from another declared object, commonly a table column:

v_salary EMPLOYEE.SALARY%TYPE;

This reduces duplicated type declarations and helps PL/SQL code remain compatible when the column's declared type changes.

%ROWTYPE

%ROWTYPE represents the complete row structure of a table or cursor as a record:

v_employee EMPLOYEE%ROWTYPE;

Fields can then be accessed as:

v_employee.NAME
v_employee.SALARY

Data types

PL/SQL supports:

• numeric types,
• character types,
• date and time types,
• Boolean values,
• records,
• collections.

SQL and PL/SQL types are closely integrated, but not every PL/SQL type is valid as a SQL column type.

Expressions and operators

PL/SQL provides arithmetic, comparison and logical operators. Conditions must be written with null semantics in mind; an expression involving NULL can evaluate to unknown rather than simply true or false.

Comments

Single-line comment:

-- comment

Multi-line comment:

/*
  comment
*/

In production code, comments are most valuable when they explain intent, constraints or a non-obvious design reason rather than restating the syntax.

Unit 2: Control Flow

IF statement

IF v_salary > 50000 THEN
    ...
END IF;

Multiple branches can be expressed with:

IF condition THEN
    ...
ELSIF other_condition THEN
    ...
ELSE
    ...
END IF;

ELSE branch

ELSE defines the path taken when none of the preceding conditions is true.

Nested IF

An IF can contain another IF. Deep nesting, however, makes control flow difficult to reason about. Conditions and subprogram boundaries should remain as simple as the problem allows.

Basic LOOP

LOOP
    ...
END LOOP;

Without an exit path this loop is infinite.

Unconditional exit

EXIT;

terminates the current loop.

Conditional exit

EXIT WHEN v_count >= 10;

is often clearer than placing an IF containing EXIT inside the loop.

WHILE loop

WHILE v_count < 10 LOOP
    v_count := v_count + 1;
END LOOP;

The condition is evaluated before each iteration.

FOR loop

FOR i IN 1..10 LOOP
    ...
END LOOP;

This is convenient when iteration is over a known integer range.

Loop labels

Labels can name nested loops so that an exit or reference can target the intended loop explicitly.

GOTO

PL/SQL supports GOTO, but structured constructs such as IF, CASE, loops and subprograms normally produce clearer code. A GOTO should therefore have a specific, defensible reason rather than being used as ordinary control flow.

Unit 3: Collections and Records

PL/SQL tables and associative arrays

The historical term PL/SQL table is now generally discussed under associative arrays. A collection stores multiple values of the same element type under one variable.

Conceptually:

key -> value

Declaring a collection

DECLARE
    TYPE t_names IS TABLE OF VARCHAR2(100)
        INDEX BY PLS_INTEGER;

    v_names t_names;
BEGIN
    v_names(1) := 'Ali';
    v_names(2) := 'Ayse';
END;
/

Using collections

Elements are read and changed through their indexes. Collections are useful for temporary sets of values manipulated inside PL/SQL.

Collection methods

Common methods include:

• COUNT,
• FIRST,
• LAST,
• DELETE,
• EXISTS,
• NEXT,
• PRIOR.

Element count

v_names.COUNT

returns the number of existing elements.

First and last index

v_names.FIRST
v_names.LAST

return the current index boundaries. For sparse collections, assuming that every integer from 1 through COUNT exists is unsafe.

Deleting an element

v_names.DELETE(2);

can remove a particular element.

User-defined records

A PL/SQL record groups fields of different types under one logical object:

TYPE t_employee IS RECORD (
    employee_no NUMBER,
    name VARCHAR2(100),
    salary NUMBER
);

Record variable

v_employee t_employee;

A field is accessed as:

v_employee.name

%ROWTYPE with records

Instead of manually duplicating all column declarations:

v_employee EMPLOYEE%ROWTYPE;

can use the table's row definition directly. This improves type alignment with the schema, although application logic must still account for semantic schema changes.

Unit 4: Cursors

Cursor concept

A cursor is PL/SQL's mechanism for processing the rows of a SQL result set. SQL should remain set-oriented whenever possible; a cursor is appropriate when business logic genuinely requires row-by-row handling.

Implicit cursors

Oracle automatically manages implicit cursors for DML and relevant SQL operations. Their state can be inspected through attributes such as:

SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN

Explicit cursors

An explicit cursor can be declared for controlled iteration over a multi-row result:

CURSOR c_employee IS
SELECT EMPLOYEE_NO, NAME, SALARY
FROM EMPLOYEE
WHERE DEPARTMENT_NO = 20;

Cursor lifecycle

The classic explicit-cursor sequence is:

1. declare,
2. open,
3. fetch,
4. close.

Opening

OPEN c_employee;

prepares the cursor's execution context.

Fetching

FETCH c_employee
INTO v_employee_no, v_name, v_salary;

Each FETCH requests the next row.

Closing

CLOSE c_employee;

releases the cursor resources associated with the open cursor.

Cursor attributes

An explicit cursor provides attributes such as:

c_employee%FOUND
c_employee%NOTFOUND
c_employee%ROWCOUNT
c_employee%ISOPEN

Cursor row records

The cursor's row shape can be represented through %ROWTYPE:

v_row c_employee%ROWTYPE;

Cursor FOR loop

PL/SQL can manage opening, fetching and closing automatically:

FOR r IN (
    SELECT EMPLOYEE_NO, NAME
    FROM EMPLOYEE
    WHERE DEPARTMENT_NO = 20
) LOOP
    ...
END LOOP;

For simple iteration this is usually safer and less error-prone than manually managing OPEN, FETCH and CLOSE.

Parameterized cursor

CURSOR c_employee(p_department_no NUMBER) IS
SELECT EMPLOYEE_NO, NAME
FROM EMPLOYEE
WHERE DEPARTMENT_NO = p_department_no;

The same cursor definition can then be reused with different parameter values.

Unit 5: Exception Handling

Exception mechanism

PL/SQL handles runtime errors through exceptions. When an exception is raised, normal execution of the current block stops and control transfers to a matching handler in the EXCEPTION section.

BEGIN
    ...
EXCEPTION
    WHEN ... THEN
        ...
END;
/

Error handling is part of correct database programming, not an optional afterthought.

Predefined exceptions

Oracle exposes a number of common error conditions as named exceptions, including:

• NO_DATA_FOUND,
• TOO_MANY_ROWS,
• ZERO_DIVIDE,
• DUP_VAL_ON_INDEX.

Example:

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        ...

Non-predefined server errors

An Oracle error number that does not already have a convenient predefined PL/SQL name can be associated with a user-declared exception using PRAGMA EXCEPTION_INIT.

Error codes and messages

Inside an exception context:

SQLCODE
SQLERRM

provide the numeric code and message text. They can be useful for logging and for translating low-level database failures into an application's error model.

User-defined exceptions

DECLARE
    e_invalid_salary EXCEPTION;
BEGIN
    IF v_salary < 0 THEN
        RAISE e_invalid_salary;
    END IF;
EXCEPTION
    WHEN e_invalid_salary THEN
        ...
END;
/

Domain rules can therefore be represented as explicit exceptions rather than as unexplained control-flow failures.

Exceptions and transactions

Catching an exception does not automatically produce the desired transaction outcome. The design must decide:

• which changes should be rolled back,
• which layer owns commit or rollback,
• whether the error should be re-raised,
• what information is safe and useful to log.

This distinction becomes important when several procedure calls participate in one larger unit of work.

Unit 6: Subprograms

Subprogram concept

Reusable named PL/SQL blocks are subprograms. The two basic kinds are:

• procedures,
• functions.

Subprograms can:

• reduce duplicated code,
• centralize database logic,
• provide an authorization boundary,
• establish a stable API between applications and database internals.

Common structure

A subprogram can contain:

• a name,
• parameters,
• declarations,
• an executable section,
• an exception section.

Procedures

A procedure performs an operation:

CREATE OR REPLACE PROCEDURE UPDATE_SALARY (
    p_employee_no IN NUMBER,
    p_new_salary IN NUMBER
) AS
BEGIN
    UPDATE EMPLOYEE
    SET SALARY = p_new_salary
    WHERE EMPLOYEE_NO = p_employee_no;
END;
/

Local procedures

A procedure can be declared inside another PL/SQL block when it is only needed locally. This is useful for decomposing a larger block without creating a schema-level object.

Stored procedures

A schema-level procedure is compiled and stored as a database object. Multiple application components can call the same implementation if the interface is appropriate for shared use.

Calling procedures

From PL/SQL:

BEGIN
    UPDATE_SALARY(100, 75000);
END;
/

Client drivers provide their own callable-statement mechanisms for invoking stored procedures.

IN, OUT and IN OUT parameters

IN supplies an input value.

OUT allows the procedure to return a value through a parameter.

IN OUT allows both input and output.

Parameter direction is part of the interface contract and should be explicit rather than surprising to the caller.

Functions

A function returns a value:

CREATE OR REPLACE FUNCTION ANNUAL_SALARY (
    p_monthly_salary IN NUMBER
) RETURN NUMBER AS
BEGIN
    RETURN p_monthly_salary * 12;
END;
/

Stored functions

A schema-level function is a database object. Under the applicable Oracle rules it can also be called from SQL expressions. Side effects, purity expectations and SQL-call restrictions must be considered when a function is intended for use inside SQL.

Packages

A package groups related PL/SQL declarations and implementations under one schema object. It can contain:

• types,
• constants,
• variables,
• cursors,
• exceptions,
• procedures,
• functions.

Package specification

The package specification is the externally visible interface:

CREATE OR REPLACE PACKAGE EMPLOYEE_API AS
    PROCEDURE UPDATE_SALARY(
        p_employee_no IN NUMBER,
        p_new_salary IN NUMBER
    );

    FUNCTION ANNUAL_SALARY(
        p_monthly_salary IN NUMBER
    ) RETURN NUMBER;
END EMPLOYEE_API;
/

Package body

The package body contains the implementation of the declared subprograms:

CREATE OR REPLACE PACKAGE BODY EMPLOYEE_API AS

    PROCEDURE UPDATE_SALARY(
        p_employee_no IN NUMBER,
        p_new_salary IN NUMBER
    ) AS
    BEGIN
        UPDATE EMPLOYEE
        SET SALARY = p_new_salary
        WHERE EMPLOYEE_NO = p_employee_no;
    END;

    FUNCTION ANNUAL_SALARY(
        p_monthly_salary IN NUMBER
    ) RETURN NUMBER AS
    BEGIN
        RETURN p_monthly_salary * 12;
    END;

END EMPLOYEE_API;
/

The specification/body split separates the stable interface from implementation detail.

Using packages

A package member can be called as:

EMPLOYEE_API.UPDATE_SALARY(...)

Packages are useful for placing related database operations under one namespace rather than exposing a large collection of unrelated schema-level procedures.

Design value of packages

A well-designed package:

• hides internal implementation details,
• exposes a narrow API,
• promotes reuse,
• allows privileges to be granted at a meaningful boundary,
• groups related operations together.

As a database programming layer grows, coherent package boundaries can reduce coupling and maintenance cost compared with an unstructured collection of independent procedures.