Unit 5: Pl-SQL

I. Orientation

PL/SQL (Procedural Language/SQL) is Oracle Database's procedural extension to SQL. It combines SQL's declarative data-manipulation capabilities with procedural programming constructs such as variables, conditions, loops, procedures, functions, and exception handling. PL/SQL code is normally executed inside the Oracle Database server, where it can process data close to its storage location.

• Governing principle: SQL specifies what data operation is required, while PL/SQL can specify how a sequence of operations should be performed.
• Execution context: PL/SQL programs are compiled and executed by the Oracle Database PL/SQL engine.
• Program-unit model: Code may be written as anonymous blocks or stored as procedures, functions, packages, and triggers.
• SQL integration: A PL/SQL block can contain SQL statements such as SELECT, INSERT, UPDATE, DELETE, and transaction-control statements.
• Block convention: A PL/SQL program is organized into logical blocks with optional declarations and exception handling, but an executable section is required.
• Data orientation: PL/SQL variables and records can represent database values, rows, and result sets.
• Database dependency: PL/SQL syntax and many built-in features are specific to Oracle Database, although its procedural ideas resemble those of other programming languages.

II. Overview of PL/SQL

A. Overview of PL/SQL

PL/SQL is a block-structured language designed to bring procedural control directly into database applications.

• Language combination: PL/SQL embeds SQL statements within procedural code, allowing a program to retrieve a value, test it, and perform a related update in one unit.
• Block structure: A block can declare variables, execute statements, and handle errors locally.
• Supported program units: Common units include:
  • Anonymous blocks: Unnamed blocks submitted for immediate execution.
  • Procedures: Stored programs that perform actions and may accept parameters.
  • Functions: Stored programs that return a value.
  • Packages: Groups of related declarations and implementations.
  • Triggers: Programs executed automatically when specified database events occur.
• Execution model: The client sends a PL/SQL block to the server; the PL/SQL engine processes procedural statements and passes embedded SQL statements to the SQL engine.
• Data access: A SELECT ... INTO statement retrieves a single row into PL/SQL variables. If no row or multiple rows are returned, an exception may occur.

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM employees;

    DBMS_OUTPUT.PUT_LINE('Employees: ' || v_count);
END;
/

• Symbol definition: v_count is a numeric variable; COUNT(*) calculates the number of rows; DBMS_OUTPUT.PUT_LINE displays text when server output is enabled.
• Language syntax: Statements normally end with semicolons, identifiers are not case-sensitive by default, and the slash / is a client tool command that submits the completed block.

B. Applications and limitations

PL/SQL is effective for database-centered logic, but its use should match the responsibility of the database layer.

• Database applications: Validation, calculations, reporting routines, batch processing, auditing, and transaction workflows can be implemented close to the data.
• Reduced transfer: A loop executed on the server avoids repeatedly sending individual SQL statements from an application.
• Transaction coordination: Several related changes can be executed and committed or rolled back as one logical operation.
• Main limitation: PL/SQL is primarily Oracle-specific, so migration to another database system may require rewriting syntax and program units.
• Performance limitation: Row-by-row processing, commonly called “slow-by-slow” processing, can be less efficient than a single set-based SQL statement for large data volumes.
• Maintenance concern: Business logic distributed across triggers, packages, and application code can become difficult to trace without clear ownership and documentation.

III. Differences between SQL and PL/SQL

A. Differences between SQL and PL/SQL

SQL and PL/SQL work together, but they differ in purpose, structure, execution, and control capabilities.

• Purpose: SQL is a data-oriented language for querying and modifying relational data; PL/SQL is a procedural language for organizing multiple operations and decisions.
• Nature of execution: A SQL statement is generally executed as an individual command, whereas a PL/SQL block is submitted as a complete program unit.
• Control structures: SQL directly provides statements such as SELECT and UPDATE; PL/SQL additionally provides IF, CASE, LOOP, WHILE, and FOR.
• Variables: SQL statements can use bind variables, but PL/SQL declares and manipulates local variables using types such as NUMBER, VARCHAR2, %TYPE, and %ROWTYPE.
• Error handling: SQL reports statement errors to the client; PL/SQL can catch predefined or user-defined exceptions in an EXCEPTION section.
• Modularity: SQL commonly consists of separate statements; PL/SQL supports procedures, functions, packages, and triggers.
• Result handling: A normal SQL query may return a result set to a client. A PL/SQL SELECT ... INTO statement is intended to assign one row to variables unless a cursor is used.
• Processing style: SQL favors set-based processing, such as updating all employees in a department in one statement. PL/SQL supports iterative processing when each row requires procedural logic.

B. Paired comparison: SQL and PL/SQL

The central contrast is declarative set processing versus procedural control over a sequence of database operations.

1. SQL: set-oriented operation
   

   SQL

      UPDATE employees
      SET salary = salary * 1.10
      WHERE department_id = 10;

   • Meaning: The database determines how to update every qualifying row.
   • Concrete elements: salary * 1.10 increases the existing salary by 10%; department_id = 10 restricts the affected rows.

2. PL/SQL: controlled sequence
   

   SQL

      BEGIN
          UPDATE employees
          SET salary = salary * 1.10
          WHERE department_id = 10;
   
          IF SQL%ROWCOUNT = 0 THEN
              DBMS_OUTPUT.PUT_LINE('No employees updated');
          END IF;
      END;
      /

   • Meaning: PL/SQL executes the SQL statement and then tests its result.
   • Concrete elements: SQL%ROWCOUNT reports the number of rows affected by the most recent DML statement; IF introduces conditional behavior.
   • Combined use: PL/SQL does not replace SQL. It supplies program flow around SQL statements, while SQL remains the principal mechanism for relational data access.

IV. Benefits and Features of PL/SQL

A. Benefits and Features of PL/SQL

PL/SQL improves database programming by combining procedural expressiveness with direct access to Oracle data and services.

• Modularity: Procedures and functions divide a large task into named units with defined parameters and return values.
• Encapsulation: Packages can expose a public specification while hiding implementation details in the package body.
• Reusability: A stored procedure can be called by multiple applications, reducing duplicated SQL and business logic.
• Error handling: The EXCEPTION section can respond to conditions such as NO_DATA_FOUND, TOO_MANY_ROWS, and DUP_VAL_ON_INDEX.
• Performance: Sending one complete block can reduce network round trips compared with sending many separate statements.
• Security: Users can be granted permission to execute a procedure without receiving direct privileges on every underlying table.
• Maintainability: Named program units centralize frequently used rules, such as salary validation or order processing.
• Portability within Oracle systems: Stored PL/SQL units can be reused by applications written in different client languages, including Java, Python, and application-server frameworks.
• Strong typing: Variables may use explicit types or database-derived types:
  • %TYPE: Gives a variable the type of an existing column or variable.
  • %ROWTYPE: Creates a record with fields corresponding to an entire table or cursor row.
• Cursor support: Explicit cursors allow controlled processing of multiple rows when set-based SQL alone is insufficient.
• Collection support: Associative arrays, nested tables, and varrays allow groups of values to be handled in memory.
• Dynamic SQL: EXECUTE IMMEDIATE supports statements whose structure is known only at runtime.
• Transaction support: COMMIT, ROLLBACK, and savepoints coordinate changes, although transaction boundaries should be designed carefully.
• Built-in integration: Packages such as DBMS_OUTPUT, UTL_FILE, and DBMS_SCHEDULER provide access to database-supported services, subject to privileges and configuration.

B. Applications and limitations

The benefits of PL/SQL are strongest when logic is data-intensive, transactional, and closely tied to Oracle structures.

• Validation: A procedure can verify stock availability before inserting an order line.
• Batch processing: A scheduled PL/SQL program can calculate monthly totals for many accounts.
• Auditing: A trigger can record the user, timestamp, and old or new values after a relevant DML event.
• Controlled access: A procedure can expose only approved operations, such as increasing a salary within a permitted range.
• Bulk processing: BULK COLLECT and FORALL can reduce context switches when many rows must be transferred between the SQL and PL/SQL engines.
• Limitation of excessive procedural code: A single SQL statement is often clearer and faster than a PL/SQL loop performing the same set-based task.
• Limitation of shared resources: Long-running blocks may hold locks, consume memory, or delay other transactions.
• Limitation of hidden effects: Triggers may execute automatically and create side effects that are not visible at the point where an application issues DML.

V. Basic Structure of a PL/SQL Block

A. Basic Structure of a PL/SQL Block

A PL/SQL block consists of an optional declaration section, a required executable section, and an optional exception-handling section.

[DECLARE
    declaration statements]
BEGIN
    executable statements
[EXCEPTION
    exception handlers]
END;
/

• DECLARE section: Defines local variables, constants, cursors, records, and user-defined exceptions. It is optional.
• BEGIN ... END section: Contains executable statements and is mandatory, even when the block performs only one operation.
• EXCEPTION section: Handles runtime errors raised during execution. It is optional but important for predictable recovery.
• Block terminator: END; terminates the PL/SQL block; / submits it in tools such as SQL*Plus and SQL Developer.
• Scope: An identifier declared in a block is normally visible only within that block and its nested sub-blocks.
• Nested blocks: A block may appear inside another block, allowing local declarations and localized exception handling.

B. Overview of PL/SQL

The block structure gives each program unit a predictable lifecycle: declare required data, execute operations, and respond to failures.

• Declaration example:
  
  SQL

    DECLARE
        v_salary employees.salary%TYPE;
        c_bonus CONSTANT NUMBER := 500;

  • v_salary: Inherits the data type of employees.salary.
  • c_bonus: Is a constant whose value cannot be reassigned.
• Executable example: Assignment uses :=, while equality comparison uses =.
  
  SQL

    BEGIN
        v_salary := v_salary + c_bonus;

• Exception example:
  
  SQL

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Employee was not found');
    END;
    /

  • NO_DATA_FOUND: A predefined exception commonly raised when a single-row SELECT ... INTO returns no rows.
• Declaration order: Variables must be declared before they are referenced in the executable section.
• Statement discipline: Every PL/SQL statement ends with ;, including assignments, SQL statements, and control statements.

C. Basic Structure of a PL/SQL Block

A complete block demonstrates how declarations, SQL, control flow, and exception handling operate together.

DECLARE
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary
    INTO v_salary
    FROM employees
    WHERE employee_id = 100;

    IF v_salary < 3000 THEN
        UPDATE employees
        SET salary = salary + 500
        WHERE employee_id = 100;
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee 100 does not exist');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('The query returned multiple rows');
END;
/

• Data declaration: v_salary receives the same type as the salary column, reducing type mismatch risk.
• Single-row retrieval: SELECT ... INTO requires the query to return exactly one row for normal completion.
• Decision logic: The IF condition adds procedural behavior; only salaries below 3000 receive the 500 increase.
• Error coverage: NO_DATA_FOUND handles zero rows, while TOO_MANY_ROWS handles more than one row.
• Transaction implication: The UPDATE changes transaction data, but the block does not automatically commit it. The calling session must decide whether to issue COMMIT or ROLLBACK.
• Design rule: Keep blocks focused, use set-based SQL where appropriate, validate assumptions about returned rows, and handle exceptions at the level where meaningful recovery is possible.