PL/SQL

Introduction to PL/SQL

SQL, as a comprehensive database language, includes procedural extensions for more complex operations, often referred to as Persistent Stored Modules (PSM) in the SQL standard. PL/SQL (Procedural Language/Structured Query Language) being Oracle's specific implementation.

These extensions allow for the definition of programmatic blocks that include iteration, conditional statements, and other constructs, akin to general-purpose programming languages.

Key Features and Benefits

PL/SQL extends SQL's capabilities and offers several advantages for application development:

• Encapsulating Business Logic: It allows "business logic" to be stored directly within the database and executed from SQL statements.

• Integration with SQL: It allows for the seamless use of all SQL statements, functions, and data types directly within its procedural code blocks.

• Procedural Constructs: It adds standard programming constructs to SQL, including conditional statements (IF-THEN-ELSE, CASE), loops (FOR, WHILE), and variables.

• Improved Performance: PL/SQL code is executed on the database server, which reduces network traffic between the application and the database. Entire blocks of statements can be sent to the server at once, minimizing communication overhead.

• Modularity and Reusability: Code can be organized into reusable, named blocks such as procedures, functions, and packages which are stored on the database server. They can be invoked by multiple applications, reducing duplication of effort and improving software modularity

• Robust Error Handling: It includes a comprehensive exception handling mechanism that allows developers to gracefully catch and manage runtime errors.

• Enhanced Data Modeling: These procedural units can provide more complex types of derived data and enforce constraints beyond the capabilities of basic assertions and triggers.

PL/SQL Block Structure

Every PL/SQL program is constructed as a block. Blocks can be anonymous (unnamed) or named (as a procedure or function).

Anonymous Blocks / Compound Statements: These are the most fundamental building blocks, used to group multiple SQL and procedural statements logically.

• They typically start with a BEGIN keyword and end with END.
• They can optionally include a DECLARE section for defining local variables.
• A specific form, BEGIN ATOMIC ... END, ensures that all statements within it execute as a single, indivisible transaction.

Basic Structure

DECLARE
    -- Optional section: Declare variables, constants, cursors, and user-defined exceptions.
BEGIN
    -- Mandatory section: Contains the SQL and PL/SQL statements that are executed.
EXCEPTION
    -- Optional section: Contains handlers to manage runtime errors.
END;

Example: Anonymous Block

The following is a standard "Hello, World!" example. In Oracle development tools like SQL*Plus or SQL Developer, the forward slash (/) on a new line is used to execute the block.

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;
/

Note: The DELIMITER command is specific to the MySQL command-line client and is not used in Oracle's PL/SQL. The semicolon (;) terminates individual statements within the block, and the forward slash (/) executes the entire block.

Variables and Constants

Variables and constants can be declared within the DECLARE section of a PL/SQL block.

Declaring Variables

The basic syntax for declaring a variable is:

variable_name datatype [NOT NULL] [:= initial_value];

A best practice is to anchor a variable's data type to a database column using the %TYPE attribute. This ensures the variable's data type automatically updates if the column definition changes.

-- Variable 'v_last_name' will have the same data type as the 'last_name' column in the 'employees' table.
v_last_name employees.last_name%TYPE;

Declaring Constants

A constant holds a value that cannot be changed after it is initialized.

constant_name CONSTANT datatype := value;

DECLARE
    v_employee_id   employees.employee_id%TYPE := 100;
    v_employee_name employees.first_name%TYPE;
    c_tax_rate      CONSTANT NUMBER := 0.08;
BEGIN
    -- Select a value from a table into a variable
    SELECT first_name INTO v_employee_name
    FROM employees
    WHERE employee_id = v_employee_id;

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Tax Rate: ' || c_tax_rate);
END;
/

Control Structures

PL/SQL provides several structures to control the flow of execution.

Conditional Control

Conditional Statements: These blocks allow for conditional logic, enabling different actions based on specific conditions.

• IF-THEN-ELSEIF-ELSE-END IF statements are used to execute a sequence of statements based on a boolean expression.
• CASE statements provide another way to implement conditional logic, allowing different values to be returned based on specified conditions.

IF statements execute a sequence of statements based on a condition.

IF condition1 THEN
    -- statements to execute if condition1 is TRUE
ELSIF condition2 THEN
    -- statements to execute if condition2 is TRUE
ELSE
    -- statements to execute if all previous conditions are FALSE
END IF;

DECLARE
    v_score NUMBER := 85;
BEGIN
    IF v_score >= 90 THEN
        DBMS_OUTPUT.PUT_LINE('Grade: A');
    ELSIF v_score >= 80 THEN
        DBMS_OUTPUT.PUT_LINE('Grade: B');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Grade: C or below');
    END IF;
END;
/

CASE statements select an execution path from a set of alternatives.

CASE selector
    WHEN value1 THEN statements1;
    WHEN value2 THEN statements2;
    ELSE else_statements;
END CASE;

DECLARE
    v_grade CHAR(1) := 'B';
BEGIN
    CASE v_grade
        WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
        WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
        WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
        ELSE DBMS_OUTPUT.PUT_LINE('Needs Improvement');
    END CASE;
END;
/

Loop Control

Looping Statements: These blocks facilitate iteration over a set of statements or query results.

• WHILE ... DO ... END WHILE: Executes a sequence of statements repeatedly as long as a specified boolean condition remains true.
• REPEAT ... UNTIL ... END REPEAT: Executes a sequence of statements at least once, and then repeatedly until a specified boolean condition becomes true.
• FOR ... AS CURSOR FOR ... DO ... END FOR: This is a cursor-based loop that iterates over the results of a query, executing a statement list for each tuple. Loops can be named, and a LEAVE statement can be used to exit a loop.

Simple Loop: Executes statements repeatedly until an EXIT condition is met.

LOOP
    -- statements
    EXIT WHEN some_condition;
END LOOP;

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

WHILE Loop: Executes as long as a specified condition is TRUE.

WHILE condition LOOP
    -- statements
END LOOP;

DECLARE
    v_counter NUMBER := 5;
BEGIN
    WHILE v_counter > 0 LOOP
        DBMS_OUTPUT.PUT_LINE('Countdown: ' || v_counter);
        v_counter := v_counter - 1;
    END LOOP;
END;
/

FOR Loop: Executes for a fixed number of iterations. The loop counter is implicitly declared.

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

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

Error Handling

The EXCEPTION block is used to catch and handle errors that occur during the execution of the BEGIN block.

DECLARE
    v_employee_name employees.first_name%TYPE;
BEGIN
    -- This query will fail if employee_id 999 does not exist.
    SELECT first_name INTO v_employee_name
    FROM employees
    WHERE employee_id = 999;

    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_name);

EXCEPTION
    -- This specific exception handler catches the error for "no rows found".
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: No employee was found with that ID.');

    -- The OTHERS handler catches all other unhandled exceptions.
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
        -- SQLERRM and SQLCODE are built-in functions to get error details.
        DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
END;
/

Basic Practice

It's important to note that PL/SQL is specific to Oracle databases. MySQL, which has its own, very similar procedural language. The following examples use the correct syntax for your MySQL database.

A Note on DELIMITER

When writing multi-statement commands in the mysql> client, you need to temporarily change the delimiter from the default semicolon (;) so the client doesn't end your procedure definition prematurely. We'll use // as the temporary delimiter.

Stored Procedures

Stored procedures are reusable blocks of code that you can execute on demand.

Question 1: Create a procedure that accepts a major name and returns all students in that major.

Answer: This procedure takes a major name as an IN (input) parameter and uses it in a SELECT statement.

DELIMITER //

CREATE PROCEDURE GetStudentsByMajor(IN major_name VARCHAR(100))
BEGIN
    SELECT *
    FROM Students
    WHERE Major = major_name;
END //

DELIMITER ;

To run this procedure:

CALL GetStudentsByMajor('Computer Science');

---

Question 2: Create a procedure that takes a Course ID and returns the number of enrolled students.

Answer: This procedure uses an IN parameter for the Course ID and an OUT (output) parameter to return the count.

DELIMITER //

CREATE PROCEDURE GetEnrollmentCount(IN c_id INT, OUT student_count INT)
BEGIN
    SELECT COUNT(*) INTO student_count
    FROM Enrollment
    WHERE CourseID = c_id;
END //

DELIMITER ;

To run this procedure:

CALL GetEnrollmentCount(201, @count);
SELECT @count AS 'Students in Course 201';

Functions

Functions are similar to procedures but must return a single value.

Question: Create a function that takes a StudentID and returns the student's full name.

Answer: This function is useful for embedding in other queries to get formatted data.

DELIMITER //

CREATE FUNCTION GetStudentName(s_id INT)
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE student_name VARCHAR(100);
    SELECT Name INTO student_name
    FROM Students
    WHERE StudentID = s_id;
    RETURN student_name;
END //

DELIMITER ;

To use this function:

SELECT GetStudentName(1001);

Triggers

Triggers are special procedures that automatically run in response to DML events (INSERT, UPDATE, DELETE).

Question: Create an audit table and a trigger that logs an entry whenever a student's major is updated.

Answer: This is a common use case for triggers, allowing you to track changes to important data.

Step 1: Create the audit table.

CREATE TABLE StudentAudit (
    AuditID INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT,
    OldMajor VARCHAR(100),
    NewMajor VARCHAR(100),
    ChangeTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create the trigger.

This trigger will fire after any UPDATE on the Students table.

DELIMITER //

CREATE TRIGGER AfterMajorUpdate
AFTER UPDATE ON Students
FOR EACH ROW
BEGIN
    IF OLD.Major <> NEW.Major THEN
        INSERT INTO StudentAudit(StudentID, OldMajor, NewMajor)
        VALUES (OLD.StudentID, OLD.Major, NEW.Major);
    END IF;
END //

DELIMITER ;

To test this trigger:

-- First, update a student's major
UPDATE Students SET Major = 'Computer Engineering' WHERE StudentID = 1001;

-- Now, check the audit table to see the logged change
SELECT * FROM StudentAudit;