Back to Subject
Practice MCQ

Unit 5 - Notes

INT306

Unit 5: Programming Constructs in Databases

Database Management Systems (DBMS) have evolved beyond simple data storage and retrieval. Modern SQL databases support Procedural Language extensions to SQL (e.g., PL/SQL in Oracle, T-SQL in SQL Server, PL/pgSQL in PostgreSQL). These extensions allow developers to write complex logic, variables, and control structures directly within the database server.

1. Flow Control Statements

Standard SQL is a declarative language (you describe what you want, not how to get it). To implement logic, procedural extensions introduce flow control statements similar to C, Java, or Python.

A. Conditional Control (Selection)

These structures allow code to execute based on specific conditions.

1. IF-THEN-ELSE
Executes a sequence of statements only if a condition is true.

  • Syntax:
    SQL
        IF condition THEN
       statements;
    ELSIF condition THEN
       statements;
    ELSE
       statements;
    END IF;
  • Example:
    SQL
        DECLARE
       v_salary NUMBER := 6000;
    BEGIN
       IF v_salary > 10000 THEN
          DBMS_OUTPUT.PUT_LINE('High Salary');
       ELSIF v_salary > 5000 THEN
          DBMS_OUTPUT.PUT_LINE('Average Salary');
       ELSE
          DBMS_OUTPUT.PUT_LINE('Low Salary');
       END IF;
    END;

2. CASE Statements
Selects one sequence of statements to execute from many options.

  • Simple CASE: Compares a single expression to a list of values.
  • Searched CASE: Evaluates a list of Boolean conditions.

B. Iterative Control (Loops)

Used to execute a block of code repeatedly.

1. Basic LOOP
Executes indefinitely until an EXIT condition is met.

SQL
LOOP
   -- Logic here
   v_counter := v_counter + 1;
   EXIT WHEN v_counter > 5;
END LOOP;

2. WHILE LOOP
Executes as long as a condition remains true. Checked at the start of the loop.

SQL
WHILE v_counter <= 5 LOOP
   -- Logic here
   v_counter := v_counter + 1;
END LOOP;

3. FOR LOOP
Iterates over a specific range of integers.

SQL
FOR i IN 1..5 LOOP
   DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
END LOOP;

2. Functions

A Function is a named PL/SQL block that compiles and acts as a subprogram. The defining characteristic of a function is that it must return a value.

Key Characteristics

  • Purpose: Used primarily for computations.
  • Usage: Can be called within a standard SQL statement (e.g., SELECT my_func() FROM dual).
  • Parameters: Usually takes IN parameters (read-only).

Syntax

SQL
CREATE OR REPLACE FUNCTION function_name (parameter_list)
RETURN return_datatype
IS
   -- Declaration section
BEGIN
   -- Executable section
   RETURN value;
END;

Example: Calculate Annual Salary

SQL
CREATE OR REPLACE FUNCTION calculate_annual_tax (p_monthly_sal IN NUMBER)
RETURN NUMBER
IS
   v_annual_sal NUMBER;
BEGIN
   v_annual_sal := p_monthly_sal * 12;
   RETURN v_annual_sal * 0.10; -- Returning 10% tax
END;
/
-- Usage
SELECT emp_name, calculate_annual_tax(salary) FROM employees;

3. Stored Procedures

A Stored Procedure is a precompiled collection of SQL statements and optional control flow logic stored under a name and processed as a unit.

Key Characteristics

  • Purpose: Used to execute business logic, modify data, or perform administrative tasks.
  • Usage: Cannot be called inside a SELECT statement; executed via CALL or EXECUTE.
  • Parameters:
    • IN: Passes a value into the procedure.
    • OUT: Returns a value to the caller.
    • IN OUT: Passes a value in, modifies it, and returns the modified value.

Advantages

  1. Performance: Procedures are precompiled and cached, reducing parsing time.
  2. Network Traffic: Sends a single command to the server instead of multiple SQL lines.
  3. Security: Grants permission to execute the procedure without granting table access.

Syntax

SQL
CREATE OR REPLACE PROCEDURE procedure_name (parameter_list)
IS
   -- Local variables
BEGIN
   -- SQL statements
END;

Example: Give Raise

SQL
CREATE OR REPLACE PROCEDURE give_raise (p_emp_id IN NUMBER, p_percent IN NUMBER)
IS
BEGIN
   UPDATE employees
   SET salary = salary + (salary * (p_percent/100))
   WHERE employee_id = p_emp_id;
   
   COMMIT;
END;

4. Cursors

SQL operates on sets of data (returning multiple rows), while procedural languages process one record at a time. A Cursor is a temporary work area created in the system memory when a SQL statement is executed. It allows row-by-row processing.

Types of Cursors

1. Implicit Cursors

Automatically created by the system when a DML statement (INSERT, UPDATE, DELETE) or a SELECT...INTO statement is executed.

  • Attributes:
    • %FOUND: Returns TRUE if DML affected at least one row.
    • %ROWCOUNT: Returns number of rows affected.

2. Explicit Cursors

Programmer-defined cursors for queries that return more than one row.

Explicit Cursor Lifecycle

  1. DECLARE: Define the cursor with a SELECT statement.
  2. OPEN: Execute the query and bind variables.
  3. FETCH: Retrieve the current row into variables.
  4. CLOSE: Release memory.

Syntax and Example

SQL
DECLARE
   v_name employees.first_name%TYPE;
   v_sal  employees.salary%TYPE;
   
   -- 1. Declare
   CURSOR c_emp IS 
      SELECT first_name, salary FROM employees WHERE department_id = 10;
BEGIN
   -- 2. Open
   OPEN c_emp;
   
   LOOP
      -- 3. Fetch
      FETCH c_emp INTO v_name, v_sal;
      
      -- Exit condition using Attribute
      EXIT WHEN c_emp%NOTFOUND;
      
      DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_sal);
   END LOOP;
   
   -- 4. Close
   CLOSE c_emp;
END;

5. Triggers

A Trigger is a stored program that is automatically executed (fired) or invoked by the DBMS in response to specified events.

Types of Triggers

  1. DML Triggers: Fired by INSERT, UPDATE, or DELETE.
  2. DDL Triggers: Fired by CREATE, ALTER, or DROP.
  3. Database Event Triggers: Fired by logon/logoff, startup/shutdown.

Trigger Timing and Scope

  • Timing:
    • BEFORE: Fires before the check constraints and actual data modification. (Good for validation).
    • AFTER: Fires after the modification. (Good for auditing).
    • INSTEAD OF: Used on Views; executes logic instead of the DML command.
  • Scope:
    • Row-Level (FOR EACH ROW): Fires once for every row affected.
    • Statement-Level: Fires once per transaction, regardless of how many rows are affected.

The :NEW and :OLD Qualifiers

  • :NEW – Accesses the new value being inserted/updated.
  • :OLD – Accesses the original value before update/delete.

Example: Audit Trigger

Logs changes to salary in an audit_table.

SQL
CREATE OR REPLACE TRIGGER trg_audit_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
   INSERT INTO salary_audit (emp_id, old_sal, new_sal, change_date)
   VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;

6. Exception Handling

Exception handling is a mechanism to handle runtime errors (exceptions) so that the normal flow of the application can be maintained or terminated gracefully.

Block Structure

Exceptions are handled in the EXCEPTION section of a PL/SQL block.

SQL
BEGIN
   -- Executable statements
EXCEPTION
   WHEN exception_name THEN
      -- Error handling logic
   WHEN OTHERS THEN
      -- Fallback for unknown errors
END;

Types of Exceptions

1. Pre-defined Exceptions

Standard errors defined by the DBMS.

  • NO_DATA_FOUND: A SELECT INTO returns no rows.
  • TOO_MANY_ROWS: A SELECT INTO returns more than one row.
  • ZERO_DIVIDE: Attempting to divide by zero.
  • DUP_VAL_ON_INDEX: Constraint violation (duplicate primary key).

2. User-defined Exceptions

Declared by the programmer and raised explicitly.

Usage of RAISE and RAISE_APPLICATION_ERROR

  • RAISE exception_name;: Stops execution and jumps to the exception block.
  • RAISE_APPLICATION_ERROR(code, message);: Returns a custom error code (-20000 to -20999) and message to the client application.

Example

SQL
DECLARE
   v_stock NUMBER;
   e_out_of_stock EXCEPTION; -- User defined
BEGIN
   SELECT quantity INTO v_stock FROM inventory WHERE product_id = 101;
   
   IF v_stock < 1 THEN
      RAISE e_out_of_stock; -- Raise user defined
   END IF;

   UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 101;

EXCEPTION
   WHEN e_out_of_stock THEN
      DBMS_OUTPUT.PUT_LINE('Error: Product is out of stock.');
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Error: Product ID does not exist.');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END;