Unit 5: Programming Constructs in Databases

1. Programming Constructs in DBMS (PL/SQL)

Database programming extensions (like Oracle's PL/SQL or Microsoft's T-SQL) allow procedural logic to be executed directly within the database server. This reduces network traffic and enhances performance.

1.1 Flow Control Statements

These constructs allow the database to make decisions and repeat instructions.

• Conditional Control (IF statements):
  • IF-THEN: Executes a sequence of statements only if a condition is true.
  • IF-THEN-ELSE: Executes one sequence if true, another if false.
  • IF-THEN-ELSIF: Checks multiple conditions sequentially.
• Iterative Control (Loops):
  • Simple Loop: Runs until an EXIT condition is met.
  • WHILE Loop: Runs as long as a condition remains true.
  • FOR Loop: Runs for a specific range of integers or cursor rows.

-- Example: FOR LOOP
BEGIN
   FOR i IN 1..10 LOOP
      INSERT INTO temp_table VALUES(i);
   END LOOP;
   COMMIT;
END;

1.2 Stored Procedures

A stored procedure is a named collection of SQL statements and procedural logic compiled and stored in the database.

• Characteristics:
  • May have input (IN), output (OUT), and input/output (IN OUT) parameters.
  • Does not strictly require a return value.
  • Can execute DML statements (INSERT, UPDATE, DELETE).
• Advantages:
  • Precompiled: Faster execution as the plan is cached.
  • Security: Users can be granted permission to execute the procedure without full table access.
  • Modularity: Logic is centralized in the database.

1.3 Functions

A function is similar to a procedure but must return a single value.

• Usage: Can be called directly within a standard SQL statement (e.g., SELECT my_function(col1) FROM table).
• Key Difference: Procedures are generally used to perform actions (business logic); Functions are used to compute values.

1.4 Cursors

A cursor is a temporary work area created in the system memory when a SQL statement is executed. It acts as a pointer to the context area.

• Types of Cursors:
  1. Implicit Cursors: Automatically created by the system for DML statements (INSERT, UPDATE, DELETE) and single-row SELECT statements. Attributes include %FOUND, %NOTFOUND, %ROWCOUNT.
  2. Explicit Cursors: User-defined cursors for processing multiple rows returned by a query.
• Steps for Explicit Cursors:
  1. Declare: Define the cursor with a SELECT statement.
  2. Open: Execute the query and bind variables.
  3. Fetch: Load the current row into variables.
  4. Close: Release the memory.

1.5 Triggers

A trigger is a stored program that automatically executes (fires) in response to specific events.

• Timing: BEFORE or AFTER the event.
• Events: INSERT, UPDATE, DELETE.
• Level:
  • Row-level: Fires once for each row affected.
  • Statement-level: Fires once for the transaction, regardless of how many rows are affected.
• Use Cases: Enforcing complex integrity constraints, auditing changes, automatically updating derived columns.

1.6 Exception Handling

Mechanism to handle runtime errors (exceptions) so processing can continue or terminate gracefully.

• Structure: BEGIN ... EXCEPTION ... END;
• Types:
  • Pre-defined Exceptions: Standard errors like NO_DATA_FOUND or ZERO_DIVIDE.
  • User-defined Exceptions: Custom errors defined by the programmer using RAISE.

2. Database Transaction Processing

2.1 Transaction System Concepts

A Transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit; it must be fully completed or not executed at all.

2.2 Desirable Properties of Transactions (ACID)

For a database to remain consistent, transactions must adhere to the ACID properties:

1. Atomicity: The "All or Nothing" rule. If any part of the transaction fails, the entire transaction is rolled back.
2. Consistency: The database must move from one valid state to another. Constraints (integrity, data types) must be satisfied.
3. Isolation: Concurrent transactions should not interfere with each other. Intermediate states of a transaction should be invisible to other transactions.
4. Durability: Once a transaction commits, the changes are permanent, even in the event of a system failure.

3. Schedules and Serializability

3.1 Schedules

A schedule is the chronological order in which instructions of multiple concurrent transactions are executed.

• Serial Schedule: Transactions are executed one after another (no interleaving). Always consistent but poor performance.
• Concurrent Schedule: Instructions from different transactions are interleaved. Better performance/throughput.

3.2 Serializability

A concurrent schedule is serializable if its outcome is equivalent to some serial execution of the same transactions. This ensures data consistency in concurrent environments.

3.3 Conflict Serializability

A schedule is conflict serializable if it can be transformed into a serial schedule by swapping non-conflicting operations.

• Conflict: Two operations conflict if:
  1. They belong to different transactions.
  2. They access the same data item.
  3. At least one of them is a WRITE operation.
• Conflict Types: Read-Write (RW), Write-Read (WR), Write-Write (WW).
• Testing: We use a Precedence Graph. If the graph has no cycles, the schedule is conflict serializable.

4. Concurrency Control and Recoverability

4.1 Concurrency Control Protocols

Mechanisms to ensure isolation and serializability in a multi-user environment.

Lock-Based Protocols

Transactions must obtain locks on data items before accessing them.

• Shared Lock (S): Read-only access. Multiple transactions can hold an S-lock on the same item.
• Exclusive Lock (X): Read and Write access. Only one transaction can hold an X-lock.

Two-Phase Locking (2PL) Protocol

Ensures conflict serializability.

1. Growing Phase: A transaction may obtain locks but cannot release any.
2. Lock Point: The moment the final lock is acquired.
3. Shrinking Phase: A transaction may release locks but cannot obtain any new ones.

Note: 2PL guarantees serializability but does not prevent Deadlocks.

4.2 Recoverability

Recoverability determines whether the database can be restored to a consistent state if a transaction fails.

1. Recoverable Schedules:

   • If Transaction reads a value written by , then must commit before commits.
   • Prevents durability issues where a dependent transaction commits data based on a transaction that later aborts.

2. Cascadeless Schedules:

   • Avoids the "Cascading Rollback" problem.
   • If reads a value written by , then must commit before reads that value.
   • Essentially prevents Dirty Reads.

3. Strict Schedules:

   • Most restrictive and easiest to recover.
   • If wants to read or write a value written by , must commit or abort before can proceed.
   • Usually implemented via Exclusive Locks held until commit.