# PL/SQL Programming Fundamentals

> PL/SQL notes covering block structure, variables, control flow, collections, records, cursors, exceptions, procedures, functions and packages.

- Author: Muhammet Ali Köker
- Language: en
- Canonical: https://alikoker.com.tr/en/plsql-programming-fundamentals
- Translation: https://alikoker.com.tr/plsql-programlama-temelleri
- Published: 2015-02-07T17:25:00+03:00
- Modified: 2026-07-24T20:50:00+03:00
- Verified: 2026-08-08T15:00:00+03:00
- Type: article

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:

```sql
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. SQL*Plus was the main interface in the original course context. SQL*Plus 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.

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

A constant can be declared as:

```sql
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:

```sql
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:

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

### Transaction statements

When required:

```sql
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:

```text
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:

```sql
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:

```sql
IF v_value IS NULL THEN
    ...
END IF;
```

### %TYPE

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

```sql
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:

```sql
v_employee EMPLOYEE%ROWTYPE;
```

Fields can then be accessed as:

```sql
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:

```sql
-- comment
```

Multi-line comment:

```sql
/*
  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

```sql
IF v_salary > 50000 THEN
    ...
END IF;
```

Multiple branches can be expressed with:

```sql
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

```sql
LOOP
    ...
END LOOP;
```

Without an exit path this loop is infinite.

### Unconditional exit

```sql
EXIT;
```

terminates the current loop.

### Conditional exit

```sql
EXIT WHEN v_count >= 10;
```

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

### WHILE loop

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

The condition is evaluated before each iteration.

### FOR loop

```sql
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:

```text
key -> value
```

### Declaring a collection

```sql
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

```sql
v_names.COUNT
```

returns the number of existing elements.

### First and last index

```sql
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

```sql
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:

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

### Record variable

```sql
v_employee t_employee;
```

A field is accessed as:

```sql
v_employee.name
```

### %ROWTYPE with records

Instead of manually duplicating all column declarations:

```sql
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:

```text
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN
```

### Explicit cursors

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

```sql
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

```sql
OPEN c_employee;
```

prepares the cursor's execution context.

### Fetching

```sql
FETCH c_employee
INTO v_employee_no, v_name, v_salary;
```

Each `FETCH` requests the next row.

### Closing

```sql
CLOSE c_employee;
```

releases the cursor resources associated with the open cursor.

### Cursor attributes

An explicit cursor provides attributes such as:

```text
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`:

```sql
v_row c_employee%ROWTYPE;
```

### Cursor FOR loop

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

```sql
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

```sql
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.

```sql
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:

```sql
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:

```sql
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

```sql
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:

```sql
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:

```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:

```sql
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:

```sql
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:

```sql
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:

```sql
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.

## Cite This Work

Köker, M. A. (2015). PL/SQL Programming Fundamentals. alikoker.com.tr. https://alikoker.com.tr/en/plsql-programming-fundamentals

- BibTeX: https://alikoker.com.tr/en/plsql-programming-fundamentals.bib
- RIS: https://alikoker.com.tr/en/plsql-programming-fundamentals.ris
- CSL-JSON: https://alikoker.com.tr/en/plsql-programming-fundamentals.csl.json