Introduction to PL/SQL

Manashwee Tripathi

June 2, 2022

PL/SQL (Procedural Language/Structured Query Language) is Oracle’s extension of SQL that allows users to write procedural code, enabling more complex and structured database interactions. It combines SQL’s power with procedural constructs such as loops, conditions, and exception handling, making it an essential tool for database developers and administrators.

This guide explores PL/SQL’s features, advantages, architecture, and key components, along with examples to help you understand its practical applications.

What is PL/SQL?

PL/SQL is a block-structured programming language that allows developers to write procedural logic inside the database. Unlike SQL, which executes statements individually, PL/SQL executes a block of statements as a single unit, improving performance and reducing network traffic.

Key Features of PL/SQL

  • Block-Structured Language: Code is written in blocks, making it modular and reusable.
  • Tight Integration with SQL: Allows seamless execution of SQL statements.
  • Exception Handling: Provides error-handling capabilities.
  • Stored Procedures and Functions: Supports reusable subprograms.
  • Triggers: Enables automatic execution of code in response to events.
  • Portability: Works across different Oracle database environments.
  • Better Performance: Reduces network traffic and optimizes execution.

Architecture of PL/SQL

PL/SQL consists of three main components:

  1. PL/SQL Engine: Processes PL/SQL code within the Oracle database or client-side tools.
  2. Database Server: Executes SQL statements inside PL/SQL blocks.
  3. PL/SQL Block Structure: Consists of three sections:
    • Declaration Section: Defines variables, constants, cursors, etc.
    • Executable Section: Contains SQL and procedural statements.
    • Exception Handling Section: Handles runtime errors.

PL/SQL Block Structure

A basic PL/SQL block follows this structure:

DECLARE
   variable_name datatype;
BEGIN
   -- Executable statements
EXCEPTION
   -- Error handling
END;
/

Example:

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

Output:

Hello, PL/SQL!

Variables and Data Types in PL/SQL

PL/SQL supports various data types:

  • Scalar Types: NUMBER, VARCHAR2, CHAR, DATE, BOOLEAN
  • Composite Types: RECORD, TABLE
  • Reference Types: REF CURSOR
  • LOB Types: BLOB, CLOB

Declaring Variables

DECLARE
   v_name VARCHAR2(50);
   v_age NUMBER;
BEGIN
   v_name := 'John Doe';
   v_age := 30;
   DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Age: ' || v_age);
END;
/

Control Structures in PL/SQL

PL/SQL includes control structures like conditional statements and loops.

1. Conditional Statements

IF-THEN-ELSE

DECLARE
   v_salary NUMBER := 5000;
BEGIN
   IF v_salary > 4000 THEN
      DBMS_OUTPUT.PUT_LINE('High Salary');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Low Salary');
   END IF;
END;
/

2. Loops

Basic 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;
/

FOR Loop

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

Cursors in PL/SQL

Cursors handle multiple rows returned by a SQL query.

Implicit Cursor

DECLARE
   v_count NUMBER;
BEGIN
   SELECT COUNT(*) INTO v_count FROM employees;
   DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_count);
END;
/

Explicit Cursor

DECLARE
   CURSOR emp_cursor IS SELECT name, salary FROM employees;
   v_name employees.name%TYPE;
   v_salary employees.salary%TYPE;
BEGIN
   OPEN emp_cursor;
   LOOP
      FETCH emp_cursor INTO v_name, v_salary;
      EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name || ', Salary: ' || v_salary);
   END LOOP;
   CLOSE emp_cursor;
END;
/

Stored Procedures in PL/SQL

Stored procedures allow code reuse and modular programming.

CREATE OR REPLACE PROCEDURE greet_user (p_name IN VARCHAR2) IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END;
/

-- Calling the procedure
BEGIN
   greet_user('Alice');
END;
/

Functions in PL/SQL

Functions return a value and can be used in SQL expressions.

CREATE OR REPLACE FUNCTION get_bonus (p_salary NUMBER) RETURN NUMBER IS
BEGIN
   RETURN p_salary * 0.1;
END;
/

-- Calling the function
DECLARE
   v_bonus NUMBER;
BEGIN
   v_bonus := get_bonus(5000);
   DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
END;
/

Triggers in PL/SQL

Triggers automatically execute in response to database events.

CREATE OR REPLACE TRIGGER emp_salary_trigger
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
   IF :NEW.salary < 3000 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Salary too low!');
   END IF;
END;
/

Exception Handling in PL/SQL

PL/SQL provides robust exception handling mechanisms.

DECLARE
   v_num NUMBER := 10;
   v_den NUMBER := 0;
   v_result NUMBER;
BEGIN
   v_result := v_num / v_den;
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');
END;
/

Advantages of PL/SQL

  • Performance Improvement: Reduced network traffic due to block execution.
  • Security: Access control through stored procedures and functions.
  • Code Reusability: Stored procedures, functions, and packages promote modularity.
  • Robust Error Handling: Built-in exception handling mechanism.
  • Data Integrity: Triggers enforce business rules.

Conclusion

PL/SQL is a powerful tool for database programming, offering procedural constructs that enhance SQL’s capabilities. Whether working with stored procedures, triggers, or exception handling, mastering PL/SQL can significantly improve database performance and maintainability. By leveraging PL/SQL effectively, developers can create scalable, secure, and efficient database applications.

Leave a Comment