Unit-14 Introduction to PL/SQL

Introduction to PL/SQL Blocks

PL/SQL (Procedural Language/SQL) is a powerful procedural extension to SQL developed by Oracle. It combines the data manipulation power of SQL with the processing capabilities of a procedural language, allowing for complex program logic within the Oracle database environment.

PL/SQL is tightly integrated with the SQL language, enabling developers to create applications that interact with the database efficiently. It allows for the declaration of variables, control flow statements (like IF-THEN and loops), error handling, and modular programming.

PL/SQL Block Structure

Every PL/SQL program is organized into logical blocks. These blocks can be nested and can be either anonymous (unnamed, executed once) or named (stored procedures, functions, packages, triggers).

A PL/SQL block consists of up to four sections, three of which are optional:

[DECLARE]
  -- Declarative section (optional)
  -- Variables, cursors, user-defined exceptions
BEGIN
  -- Executable section (mandatory)
  -- SQL statements, PL/SQL statements
[EXCEPTION]
  -- Exception-handling section (optional)
  -- Actions to perform when an error occurs
END;
/

DECLARE Section (Optional)

• This section is used to declare variables, constants, cursors, and user-defined exceptions that are local to the block.
• Declarations must be made before they are referenced in the executable section.
• Example: my_variable VARCHAR2(50) := 'Hello';

BEGIN Section (Mandatory)

• This is the heart of the PL/SQL block, where the actual executable code resides.
• It contains SQL statements (DML, DQL) and PL/SQL control statements (conditional statements, loops), function calls, etc.
• At least one executable statement is required for the block to be valid.

EXCEPTION Section (Optional)

• This section is used to handle errors (exceptions) that occur during the execution of the BEGIN section.
• It provides a structured way to gracefully manage runtime errors, preventing the program from crashing.
• Example: WHEN NO_DATA_FOUND THEN ...

END Section (Mandatory)

• This marks the end of the PL/SQL block.
• Every BEGIN must have a corresponding END;.
• The / (slash) on a new line after END; is an SQL*Plus/SQL Developer command to execute the PL/SQL block.

Example: Anonymous PL/SQL Block

An anonymous block is a PL/SQL block that is not named or stored in the database. It is executed only once when it is submitted.

DECLARE
  v_message VARCHAR2(100) := 'Hello, PL/SQL World!';
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_message);
END;
/

Exam Tip: To see the output of DBMS_OUTPUT.PUT_LINE, you must first enable it in your SQL client (e.g., SQL*Plus, SQL Developer) using SET SERVEROUTPUT ON;.

Advantages of PL/SQL

• Integration with SQL: Seamlessly combines the procedural capabilities with SQL's data manipulation.
• Improved Performance: Reduces network traffic by sending a block of statements to the database at once instead of individual SQL statements.
• Modularity: Allows breaking down complex problems into smaller, manageable units (procedures, functions, packages).
• Portability: PL/SQL applications can run on any operating system where Oracle Database is supported.
• Error Handling: Provides robust exception handling mechanisms.
• Security: Stored PL/SQL units can be granted specific permissions, enhancing database security.

Conditional Statements

Conditional statements allow a PL/SQL program to execute different sets of statements based on certain conditions. This introduces decision-making capabilities into your code.

IF-THEN-END IF Statement

The simplest form, it executes a set of statements only if a condition is true.

IF condition THEN
  statements;
END IF;

Example:

DECLARE
  v_score NUMBER := 75;
BEGIN
  IF v_score >= 60 THEN
    DBMS_OUTPUT.PUT_LINE('Passed the exam.');
  END IF;
END;
/

IF-THEN-ELSE-END IF Statement

Executes one set of statements if the condition is true, and another set if the condition is false.

IF condition THEN
  statements_if_true;
ELSE
  statements_if_false;
END IF;

Example:

DECLARE
  v_score NUMBER := 55;
BEGIN
  IF v_score >= 60 THEN
    DBMS_OUTPUT.PUT_LINE('Passed the exam.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Failed the exam.');
  END IF;
END;
/

IF-THEN-ELSIF-ELSE-END IF Statement

Used when there are multiple conditions to test. It tests conditions sequentially and executes the statements for the first true condition. If no conditions are true, the ELSE block (if present) is executed.

IF condition1 THEN
  statements1;
ELSIF condition2 THEN
  statements2;
ELSIF condition3 THEN
  statements3;
ELSE
  statements_else;
END IF;

Example:

DECLARE
  v_grade VARCHAR2(1);
  v_marks NUMBER := 88;
BEGIN
  IF v_marks >= 90 THEN
    v_grade := 'A';
  ELSIF v_marks >= 80 THEN
    v_grade := 'B';
  ELSIF v_marks >= 70 THEN
    v_grade := 'C';
  ELSE
    v_grade := 'F';
  END IF;
  DBMS_OUTPUT.PUT_LINE('Student Grade: ' || v_grade);
END;
/

CASE Statement

The CASE statement provides an alternative to complex IF-THEN-ELSIF structures, especially when testing a single expression against multiple distinct values.

There are two forms of the CASE statement:

1. Simple CASE Statement

Compares a single expression against multiple possible values.

CASE expression
  WHEN value1 THEN result1;
  WHEN value2 THEN result2;
  ...
  [ELSE result_else;]
END CASE;

Example:

DECLARE
  v_day_number NUMBER := 3;
  v_day_name VARCHAR2(10);
BEGIN
  CASE v_day_number
    WHEN 1 THEN v_day_name := 'Monday';
    WHEN 2 THEN v_day_name := 'Tuesday';
    WHEN 3 THEN v_day_name := 'Wednesday';
    ELSE v_day_name := 'Unknown';
  END CASE;
  DBMS_OUTPUT.PUT_LINE('Day: ' || v_day_name);
END;
/

2. Searched CASE Statement

Allows for different conditions (like IF-THEN-ELSIF).

CASE
  WHEN condition1 THEN result1;
  WHEN condition2 THEN result2;
  ...
  [ELSE result_else;]
END CASE;

Example:

DECLARE
  v_marks NUMBER := 72;
  v_grade VARCHAR2(1);
BEGIN
  CASE
    WHEN v_marks >= 90 THEN v_grade := 'A';
    WHEN v_marks >= 80 THEN v_grade := 'B';
    WHEN v_marks >= 70 THEN v_grade := 'C';
    ELSE v_grade := 'F';
  END CASE;
  DBMS_OUTPUT.PUT_LINE('Student Grade: ' || v_grade);
END;
/

Comparison: IF Statement vs. CASE Statement

Common Mistake: Forgetting END IF; or END CASE; will result in a compilation error.

Loops

Loops in PL/SQL allow a sequence of statements to be executed repeatedly. This is crucial for tasks that require iterative processing of data or performing an action a fixed number of times.

Basic LOOP (LOOP...END LOOP)

This is the simplest form of a loop. It executes the statements inside the loop indefinitely until an EXIT or EXIT WHEN statement is encountered.

LOOP
  statements;
  EXIT WHEN condition; -- Condition to terminate the loop
END LOOP;

Example:

DECLARE
  v_counter NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
    v_counter := v_counter + 1;
    EXIT WHEN v_counter > 5; -- Exit when counter exceeds 5
  END LOOP;
END;
/

Important Note: Without an EXIT WHEN or EXIT statement, a LOOP will become an infinite loop, potentially consuming system resources until manually stopped or an error occurs.

WHILE LOOP

The WHILE loop executes a sequence of statements repeatedly as long as a specified condition evaluates to true. The condition is tested at the beginning of each iteration.

WHILE condition LOOP
  statements;
END LOOP;

Example:

DECLARE
  v_counter NUMBER := 1;
BEGIN
  WHILE v_counter <= 5 LOOP
    DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
    v_counter := v_counter + 1;
  END LOOP;
END;
/

Observation: If the condition is initially false, the WHILE loop will not execute even once.

FOR LOOP (Numeric FOR Loop)

The FOR loop is used when you know in advance how many times you want to execute a loop. It's often used for iterating a specific number of times or iterating over a range of integers.

FOR loop_variable IN [REVERSE] lower_bound .. upper_bound LOOP
  statements;
END LOOP;

• The loop_variable is implicitly declared as an integer and is local to the loop. You cannot declare it explicitly or modify its value inside the loop.
• lower_bound and upper_bound are integers or expressions that evaluate to integers.
• REVERSE keyword makes the loop iterate from upper_bound down to lower_bound.

Example:

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

  FOR j IN REVERSE 1 .. 5 LOOP
    DBMS_OUTPUT.PUT_LINE('Iteration (descending): ' || j);
  END LOOP;
END;
/

Exam Tip: The FOR loop does not require an explicit counter declaration or an EXIT WHEN clause; it automatically handles initialization, increment/decrement, and termination.

Important Considerations for Loops

• Infinite Loops: A common mistake is creating an infinite loop by failing to provide a proper exit condition for BASIC or WHILE loops.
• CONTINUE Statement: Used to immediately jump to the next iteration of the loop, skipping the remaining statements in the current iteration.
• GOTO Statement: While available, GOTO statements are generally discouraged in modern programming practice as they can make code harder to read and maintain. They transfer control unconditionally to a labeled statement.

Cursors

In PL/SQL, a cursor is a pointer to a private SQL area in the Oracle's memory (called the 'context area'). This context area stores all the information needed to execute a SQL statement, including the number of rows processed, the actual rows returned by a query, and the processing stage.

Cursors are primarily used to process multiple rows returned by a SELECT statement, one row at a time. This process is often referred to as "row-by-row processing" or "iterating through a result set".

Types of Cursors

PL/SQL supports two types of cursors:

• Implicit Cursors: Automatically created and managed by Oracle for all SQL statements (DML operations like INSERT, UPDATE, DELETE, and single-row SELECT INTO statements).
• Explicit Cursors: Manually declared and managed by the programmer for SELECT statements that return multiple rows.

Implicit Cursors

Oracle implicitly opens a cursor for every DML statement and for SELECT INTO statements. You don't explicitly declare or control these cursors. However, you can check the status and results of the most recent implicit cursor operation using SQL Cursor Attributes.

• SQL%ROWCOUNT: Returns the number of rows affected by the last DML statement (INSERT, UPDATE, DELETE) or SELECT INTO.
• SQL%FOUND: Returns TRUE if the last DML statement affected one or more rows, or if a SELECT INTO statement returned exactly one row.
• SQL%NOTFOUND: Returns TRUE if the last DML statement affected no rows, or if a SELECT INTO statement returned no rows.
• SQL%ISOPEN: Always FALSE for implicit cursors because Oracle closes them immediately after execution.

Example: Implicit Cursor Attributes

BEGIN
  UPDATE employees SET salary = salary * 1.10 WHERE department_id = 10;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('No employees in department 10.');
  END IF;

  -- Example for SELECT INTO
  DECLARE
    v_emp_name VARCHAR2(100);
  BEGIN
    SELECT first_name INTO v_emp_name FROM employees WHERE employee_id = 9999; -- Assuming no such employee
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee 9999 not found (SQL%NOTFOUND is true here).');
  END;
END;
/

Explicit Cursors

When a SELECT statement is expected to return more than one row, you must use an explicit cursor to process these rows individually. This gives you more control over the fetch process.

Steps to use Explicit Cursors

1. DECLARE: Define the cursor by naming it and associating it with a SELECT statement. This happens in the DECLARE section.
2. OPEN: Execute the SELECT statement and identify the set of rows (the active set) that satisfies the query criteria. This happens in the BEGIN section.
3. FETCH: Retrieve rows one by one from the active set into PL/SQL variables. This also happens in the BEGIN section, typically within a loop.
4. CLOSE: Release the resources held by the cursor. This happens in the BEGIN section after all rows have been processed.

Explicit Cursor Attributes

Similar to implicit cursors, explicit cursors also have attributes, but they refer to the explicit cursor's state.

• cursor_name%ROWCOUNT: Returns the number of rows fetched so far by the cursor.
• cursor_name%FOUND: Returns TRUE if the most recent FETCH statement returned a row.
• cursor_name%NOTFOUND: Returns TRUE if the most recent FETCH statement failed to return a row.
• cursor_name%ISOPEN: Returns TRUE if the cursor is currently open.

Example: Explicit Cursor

DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 50;
  v_emp_id   employees.employee_id%TYPE;
  v_first_name employees.first_name%TYPE;
  v_last_name  employees.last_name%TYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_emp_id, v_first_name, v_last_name;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_first_name || ' ' || v_last_name);
  END LOOP;
  CLOSE emp_cursor;
END;
/

Cursor FOR Loop

PL/SQL provides a more convenient way to handle explicit cursors using a FOR loop. The cursor FOR loop implicitly declares a record variable, opens the cursor, fetches rows, and closes the cursor automatically. This significantly reduces the boilerplate code.

FOR record_name IN cursor_name LOOP
  statements;
END LOOP;

Example: Cursor FOR Loop

BEGIN
  FOR emp_rec IN (SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 50) LOOP
    DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id || ', Name: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
  END LOOP;
END;
/

Observation: The emp_rec variable is implicitly declared as a record type, with fields corresponding to the SELECT list of the cursor. It is local to the loop.

Common Mistakes with Cursors

• Forgetting to Close a Cursor: This can lead to resource leaks and ORA-01000: maximum open cursors exceeded errors. The Cursor FOR loop automatically handles closing.
• Opening an Already Open Cursor: An explicit cursor must be closed before it can be opened again. Check cursor_name%ISOPEN before opening if needed.
• SELECT INTO Returning Multiple Rows: Using SELECT INTO when a query might return more than one row will raise a TOO_MANY_ROWS exception. This is why explicit cursors are needed for multi-row queries.
• Not Handling NO_DATA_FOUND for SELECT INTO: A SELECT INTO that returns no rows will raise a NO_DATA_FOUND exception.

Triggers

A trigger is a stored PL/SQL program unit that automatically executes (fires) in response to a specific database event. These events can include DML operations (INSERT, UPDATE, DELETE), DDL operations (CREATE, ALTER, DROP), or system events (database startup/shutdown, logon/logoff).

Triggers are primarily used to enforce business rules, audit data changes, maintain data integrity, and automate complex database actions.

Components of a Trigger

A trigger definition typically includes:

1. Triggering Event: The DML, DDL, or system event that causes the trigger to fire (e.g., INSERT OR UPDATE OF salary ON employees).
2. Trigger Timing: Specifies whether the trigger fires BEFORE or AFTER the triggering event, or INSTEAD OF the event (for views).
3. Trigger Level: Determines how many times the trigger body executes:
   • ROW-level trigger: Fires once for each row affected by the triggering DML statement. (Requires FOR EACH ROW clause).
   • STATEMENT-level trigger: Fires once per DML statement, regardless of how many rows are affected (or if no rows are affected). This is the default.
4. Trigger Body: The PL/SQL block that contains the actions to be performed when the trigger fires.
5. Optional WHEN clause: A boolean condition that must be true for the trigger to fire for a particular row (only for row-level triggers).

Syntax for Creating a DML Trigger

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE [OF column [, column ...]] | DELETE}
ON table_name
[REFERENCING OLD AS old NEW AS new] -- Only for row-level triggers
[FOR EACH ROW]                    -- Specifies a row-level trigger
[WHEN (condition)]                -- Only for row-level triggers
PL/SQL_block;

`OLD` and `NEW` Pseudorecords

For row-level triggers, Oracle provides two special pseudorecords: OLD and NEW.

• :OLD: Refers to the row's values before the DML operation.
• :NEW: Refers to the row's values after the DML operation.

These pseudorecords allow you to access and compare column values of the affected row. For INSERT operations, :OLD values are NULL. For DELETE operations, :NEW values are NULL.

By default, OLD and NEW are used. You can rename them using the REFERENCING OLD AS old_value NEW AS new_value clause.

`WHEN` Clause

The WHEN clause can be used only with row-level triggers to specify an additional condition that must be true for the trigger body to execute. If the WHEN condition evaluates to false, the trigger does not fire for that particular row, even if the triggering event occurred.

CREATE OR REPLACE TRIGGER audit_salary_increase
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > OLD.salary * 1.10) -- Trigger only if salary increased by > 10%
BEGIN
  -- Audit log entry
  INSERT INTO audit_log (operation, old_value, new_value)
  VALUES ('Salary Increase', :OLD.salary, :NEW.salary);
END;
/

Example: DML Trigger for Auditing

Let's create a trigger that logs changes to the employees table into an audit_log table whenever an employee's salary is updated.

-- First, create an audit table (if it doesn't exist)
CREATE TABLE audit_log (
  audit_id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  table_name      VARCHAR2(100),
  column_name     VARCHAR2(100),
  old_value       VARCHAR2(4000),
  new_value       VARCHAR2(4000),
  change_date     TIMESTAMP DEFAULT SYSTIMESTAMP,
  changed_by      VARCHAR2(100) DEFAULT USER
);

-- Now, create the trigger
CREATE OR REPLACE TRIGGER trg_audit_employee_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  IF :OLD.salary != :NEW.salary THEN -- Only log if salary actually changed
    INSERT INTO audit_log (table_name, column_name, old_value, new_value)
    VALUES ('EMPLOYEES', 'SALARY', :OLD.salary, :NEW.salary);
  END IF;
END;
/

How to test:

1. SET SERVEROUTPUT ON;
2. UPDATE employees SET salary = salary + 100 WHERE employee_id = 100;
3. SELECT * FROM audit_log; -- You should see an entry.

Advantages and Disadvantages of Triggers

Common Mistakes with Triggers

• Infinite Trigger Loops: A trigger updating the same table that caused it to fire can lead to an infinite loop, crashing the session.
• Overly Complex Logic: Triggers should be kept simple and focused. Complex logic is better placed in stored procedures or functions.
• Violating Mutating Table Error: A BEFORE or AFTER row-level trigger cannot read or modify the table that is currently being modified by the triggering statement (though there are workarounds, it's a common error).
• Not Testing Thoroughly: Triggers can have wide-ranging impacts, so thorough testing for various scenarios is crucial.
• Improper Use of `OLD` and `NEW`: Forgetting the : prefix (e.g., OLD.salary instead of :OLD.salary) or trying to use them in statement-level triggers.