RDBMS Lab (ME-606) - Unit 4 Short Notes

UNIT 4: Advanced SQL, Stored Procedures, Triggers & Transaction Control

4.1 Procedural Extensions to SQL

4.1.1 Introduction to PL/SQL (Oracle) & T-SQL (SQL Server)

• Purpose: Adds procedural capabilities (loops, conditions, variables) to standard SQL's declarative nature.

• Block Structure (PL/SQL):

  
  DECLARE
  
    -- Variable declarations
  
  BEGIN
  
    -- Executable statements
  
  EXCEPTION
  
    -- Error handling
  
  END;
  
  

4.1.2 Variables, Constants & Data Types

• Declaration: variable_name [CONSTANT] datatype [:= initial_value];

• %TYPE & %ROWTYPE (PL/SQL):

  • %TYPE: Inherits datatype/size from a table column/variable. Ensures type consistency.

  • %ROWTYPE: Represents a record variable with fields corresponding to all columns of a table or cursor row.

4.1.3 Control Structures

• Conditional: IF condition THEN ... ELSIF ... ELSE ... END IF; or CASE expression WHEN ... THEN ... END CASE;

• Loops:

  • LOOP ... END LOOP; (Infinite, exit with EXIT)

  • WHILE condition LOOP ... END LOOP;

  • FOR counter IN lower..upper LOOP ... END LOOP; (Counter implicit)

• EXIT / CONTINUE: Control loop flow.

4.1.4 Cursors

• Implicit Cursor: Automatically created for SELECT...INTO and DML. Attributes: SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT.

• Explicit Cursor: For multi-row queries.

  • Lifecycle: DECLARE cursor_name IS SELECT ...; → OPEN cursor_name; → FETCH cursor_name INTO variables; → CLOSE cursor_name;

  • Attributes: cursor_name%FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.

  • Cursor FOR Loop: Simplifies explicit cursor handling (implicit open, fetch, close). FOR rec IN cursor_name LOOP ... END LOOP;

• Parameterized Cursor: CURSOR c_name(param datatype) IS SELECT ... WHERE col = param;

4.1.5 Exception Handling

• Predefined Exceptions: NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, etc.

• User-Defined: EXCEPTION e_name; → RAISE e_name;

• WHEN OTHERS THEN: Catches all unhandled exceptions.

• SQLERRM: Returns error message string.

• RAISE_APPLICATION_ERROR(number, message):

  \boxed{\text{Raise custom application error with specific error number (-20000 to -20999).}}

4.2 Stored Procedures and Functions

Syntax Skeleton:

CREATE [OR REPLACE] PROCEDURE/FUNCTION name(

    param1 [IN|OUT|IN OUT] datatype

) IS/AS

BEGIN

    -- Body

    RETURN expression; -- For FUNCTION only

END;

Advantages: Modularity, Reusability, Security (grant EXECUTE), Performance (reduce network traffic, parsed once).

4.3 Database Triggers

4.3.1 Introduction

• Definition: Stored procedures that execute automatically in response to a DML event (INSERT, UPDATE, DELETE) on a table.

• Timing: BEFORE (validate/modify data) or AFTER (log, enforce complex rules).

• Scope: FOR EACH ROW (row-level) vs. FOR EACH STATEMENT (statement-level, default).

4.3.2 Syntax & Creation

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF}

{INSERT | UPDATE [OF column] | DELETE}

ON table_name

[FOR EACH ROW]

[WHEN (condition)]

DECLARE

  -- Declarations (optional)

BEGIN

  -- Trigger body

END;

• :OLD & :NEW: Qualifiers to access column values of the row being modified.

  • INSERT: :NEW only.

  • DELETE: :OLD only.

  • UPDATE: Both (:OLD = old value, :NEW = new value).

4.3.3 Common Use Cases

• Enforce complex cross-table integrity.

• Maintain audit trails (INSERT into audit_log table).

• Update summary/derived tables.

• Enforce security (restrict DML times).

4.3.4 Restrictions & Mutating Table Error

• Mutating Table Error (ORA-04091): Occurs when a row-level trigger tries to read or modify the same table that the trigger is fired on.

• Reason: Table is in an intermediate state (mid-transaction).

• Solutions:

  1. Use statement-level trigger if row-level access isn't essential.

  2. Use a compound trigger (Oracle 11g+) with a collection to store affected rows and process after statement.

  3. Move logic to a procedure called from the application.

4.4 Transaction Management & Concurrency Control

4.4.1 Transaction Concepts & ACID

• Atomicity (A): All or nothing. \boxed{\text{Undo via log if failure.}}

• Consistency (C): Preserves database integrity constraints.

• Isolation (I): Concurrent transactions don't interfere.

• Durability (D): Committed changes survive system crash. \boxed{\text{Redo via log.}}

4.4.2 SQL Transaction Statements

• COMMIT; – Makes changes permanent.

• ROLLBACK; – Undoes all changes in transaction.

• ROLLBACK TO SAVEPOINT sp_name; – Partial rollback.

• SAVEPOINT sp_name; – Sets a rollback point.

• SET TRANSACTION ISOLATION LEVEL ...;

4.4.3 Concurrency Problems

4.4.4 Isolation Levels & Locking

• Isolation Levels (Increasing Strictness):

  | Level | Dirty Read | Non-repeatable Read | Phantom Read | | :--- | :---: | :---: | :---: | | Read Uncommitted | Possible | Possible | Possible | | Read Committed | Prevented | Possible | Possible | | Repeatable Read | Prevented | Prevented | Possible | | Serializable | Prevented | Prevented | Prevented |

• Locking Granularity: Row-level (most common, high concurrency), Page-level, Table-level (low concurrency).

• Locking Modes:

  • Shared (S): For reading. Multiple transactions can hold S lock.

  • Exclusive (X): For writing. Only one transaction can hold X lock; blocks all others.

• Two-Phase Locking (2PL):

  1. Growing Phase: Transaction can acquire locks but not release any.

  2. Shrinking Phase: Transaction can release locks but cannot acquire new ones.

  \boxed{\text{2PL ensures conflict-serializability but can cause deadlocks.}}

• Deadlocks:

  • Detection: Wait-for graph cycle.

  • Prevention: Timestamp ordering (Wait-Die: older waits, younger aborts; Wound-Wait: older wounds, younger waits).

4.5 Recovery Systems

4.5.1 Failure Classification

• Transaction Failure: Logical error (constraint violation, deadlock victim). Local to transaction.

• System Crash: Power loss, OS crash. Volatile storage lost.

• Media Failure: Disk crash. Non-volatile storage lost.

4.5.2 Storage Structures

• Volatile Storage: Main memory (cache, buffers). Fast, lost on crash.

• Non-volatile Storage: Disk. Persistent, slow.

• Stable Storage: Conceptually, data that survives all failures (achieved via replication or RAID).

4.5.3 Recovery Mechanisms (Log-Based)

• Write-Ahead Logging (WAL) Protocol:

  \boxed{\text{Before any database page is written to disk, the corresponding log record must be flushed to stable storage.}}

• Log Record Contains: <Transaction ID, Data Item, Old Value, New Value>.

• Undo (Rollback): Use Old Value from log to revert changes of a failed/aborted transaction.

• Redo (Roll-forward): Use New Value from log to re-apply committed changes after a crash (some changes might be on disk, some in memory).

• Checkpoints: Periodically, system writes all dirty buffers (modified in memory) to disk and writes a <CHECKPOINT> record to log. Reduces recovery time (only process log after last checkpoint).

• Recovery Process (After Crash):

  1. Analysis Phase: Find last checkpoint. Identify winner (committed) and loser (active) transactions at crash time.

  2. Redo Phase: Re-apply all updates (from last checkpoint onward) of winner and loser transactions (idempotent operation).

  3. Undo Phase: Rollback all loser transactions by following log backward and applying Old Values.

4.6 Practical Implementation & Lab Exercises

• PL/SQL Programs: Combine variables, control structures, cursors, and exception handling to solve business logic (e.g., generate reports, process payroll).

• Triggers: Create AFTER INSERT/UPDATE audit triggers. Implement complex integrity checks that span multiple tables (using :NEW/:OLD).

• Transaction Demos:

  • Use two SQL sessions to simulate lost update and dirty read under READ UNCOMMITTED.

  • Demonstrate SAVEPOINT by intentionally causing an error after a savepoint and rolling back to it.

  • Show how SERIALIZABLE isolation level prevents phantoms (may get ORA-08177 serialization failure).

• Case Study: Integrate all concepts—add procedures for common operations (e.g., place_order), triggers for audit (audit_emp_salary_change), and ensure transactions in application code use proper commit/rollback.