PL/SQL Tutorial

Introduction

-- Introduction to PL/SQL
-- PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural extension to SQL.
-- It allows you to write code that can control how the database operations are executed.
-- PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages.

-- Example of a simple PL/SQL block:
BEGIN
  DBMS_OUTPUT.PUT_LINE('Welcome to PL/SQL!');
END;
/ 
-- The '/' is used to execute the block in Oracle's SQL*Plus.
    

PL/SQL (Procedural Language/Structured Query Language) is an Oracle extension of SQL, which allows you to write code that can control data operations with loops, conditions, and other programming features. It's widely used for creating stored procedures, triggers, and functions within the database.

PL/SQL Variables

-- Declaring Variables in PL/SQL
-- Variables are temporary storage for data in a PL/SQL block.
-- You must declare variables before you use them.

DECLARE
  -- Declaring variables
  employee_name VARCHAR2(50);
  employee_salary NUMBER;
BEGIN
  -- Assigning values to variables
  employee_name := 'Jane Doe';
  employee_salary := 50000;
  
  -- Output the values
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name);
  DBMS_OUTPUT.PUT_LINE('Employee Salary: $' || employee_salary);
END;
/ 
    

In PL/SQL, variables are placeholders for data that you want to manipulate during code execution. They are declared in the DECLARE section. In the example, employee_name is declared as a VARCHAR2 (string type) and employee_salary as a NUMBER.

PL/SQL Constants

-- Declaring Constants in PL/SQL
-- Constants are similar to variables, but their value cannot be changed once set.

DECLARE
  -- Declaring constants
  MAX_EMPLOYEES CONSTANT NUMBER := 200;
  COMPANY_NAME CONSTANT VARCHAR2(100) := 'Tech Corp';
BEGIN
  -- Attempt to modify the value (will cause an error)
  -- MAX_EMPLOYEES := 300;  -- Uncommenting this line will result in an error.

  -- Output the constant values
  DBMS_OUTPUT.PUT_LINE('Max Employees: ' || MAX_EMPLOYEES);
  DBMS_OUTPUT.PUT_LINE('Company Name: ' || COMPANY_NAME);
END;
/ 
  

Constants are similar to variables, but their values cannot change after they are set. This is helpful for data that must remain constant throughout the code. Use the CONSTANT keyword during declaration to make a variable a constant.

PL/SQL Control Statements

Control statements in PL/SQL are used to control the flow of execution. They help in making decisions, looping through code, and handling conditions. Below are some commonly used control statements with examples.

PL/SQL IF Statement

-- PL/SQL IF Statement
DECLARE
  salary NUMBER := 4500;
BEGIN
  -- Simple IF statement to check a condition
  IF salary > 4000 THEN
    DBMS_OUTPUT.PUT_LINE('High Salary');
  END IF;

  -- IF-ELSE example
  IF salary > 5000 THEN
    DBMS_OUTPUT.PUT_LINE('Very High Salary');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Moderate Salary');
  END IF;

  -- IF-ELSIF-ELSE example
  IF salary > 7000 THEN
    DBMS_OUTPUT.PUT_LINE('Very High Salary');
  ELSIF salary > 3000 THEN
    DBMS_OUTPUT.PUT_LINE('Moderate Salary');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Low Salary');
  END IF;
END;
/
  

The IF statement checks a condition. If the condition is true, the code inside the block runs. You can also use ELSE and ELSIF for multiple conditions.

PL/SQL CASE Statement

-- PL/SQL CASE Statement
DECLARE
  grade CHAR := 'B';
BEGIN
  CASE grade
    WHEN 'A' THEN
      DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN
      DBMS_OUTPUT.PUT_LINE('Good');
    WHEN 'C' THEN
      DBMS_OUTPUT.PUT_LINE('Fair');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Poor');
  END CASE;
END;
/
  

The CASE statement is used for conditional branching based on multiple possible values of a variable. It is similar to a switch statement in other programming languages.

PL/SQL Loop

-- PL/SQL Simple LOOP
DECLARE
  counter NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('Counter is: ' || counter);
    counter := counter + 1;
    EXIT WHEN counter > 5;
  END LOOP;
END;
/
  

A LOOP is used to execute a set of statements multiple times. It runs indefinitely until a condition with EXIT is met.

PL/SQL EXIT Loop

-- PL/SQL EXIT LOOP Example
DECLARE
  counter NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('Counter is: ' || counter);
    counter := counter + 1;
    EXIT WHEN counter > 3;  -- Exit the loop when the condition is met
  END LOOP;
END;
/
  

Use EXIT to leave a loop when a specific condition is true. This prevents infinite loops by providing an exit strategy.

PL/SQL WHILE Loop

-- PL/SQL WHILE Loop Example
DECLARE
  counter NUMBER := 1;
BEGIN
  WHILE counter <= 5 LOOP
    DBMS_OUTPUT.PUT_LINE('Counter is: ' || counter);
    counter := counter + 1;
  END LOOP;
END;
/
  

The WHILE loop executes as long as the condition remains true. If the condition is false from the beginning, the loop does not run.

PL/SQL FOR Loop

-- PL/SQL FOR Loop Example
BEGIN
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Value of i: ' || i);
  END LOOP;
END;
/
  

The FOR loop automatically iterates a variable from a start value to an end value. It is concise and easy to use for counting loops.

PL/SQL CONTINUE

-- PL/SQL CONTINUE Example
BEGIN
  FOR i IN 1..5 LOOP
    IF i = 3 THEN
      CONTINUE;  -- Skip the current iteration if i is 3
    END IF;
    DBMS_OUTPUT.PUT_LINE('Value of i: ' || i);
  END LOOP;
END;
/
  

The CONTINUE statement skips the current iteration of a loop and continues with the next iteration.

PL/SQL GOTO

-- PL/SQL GOTO Example
DECLARE
  counter NUMBER := 1;
BEGIN
  <>  -- Label definition
  DBMS_OUTPUT.PUT_LINE('Before Loop');
  
  LOOP
    counter := counter + 1;
    IF counter = 3 THEN
      GOTO my_label;  -- Jump to the label
    END IF;
    EXIT WHEN counter > 5;
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('After Loop');
END;
/
  

The GOTO statement transfers control to a labeled section of code. It can make code difficult to read, so use it carefully.

PL/SQL Procedure

-- PL/SQL Procedure Example
CREATE OR REPLACE PROCEDURE greet_user (name IN VARCHAR2) AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, ' || name || '!');
END greet_user;
/
-- Call the Procedure
BEGIN
  greet_user('John');
END;
/
  

A PL/SQL Procedure is a subprogram that performs a specific task. It does not return a value but can accept parameters. Use the CREATE OR REPLACE PROCEDURE statement to define a procedure.

PL/SQL Function

-- PL/SQL Function Example
CREATE OR REPLACE FUNCTION get_square (num IN NUMBER) RETURN NUMBER IS
BEGIN
  RETURN num * num;
END get_square;
/
-- Call the Function
BEGIN
  DBMS_OUTPUT.PUT_LINE('Square of 5 is: ' || get_square(5));
END;
/
  

A PL/SQL Function is similar to a procedure, but it returns a value. It is defined using CREATE OR REPLACE FUNCTION and must have a RETURN statement specifying the type of the value returned.

PL/SQL Cursor

-- PL/SQL Cursor Example
DECLARE
  CURSOR emp_cursor IS
    SELECT first_name, last_name FROM employees WHERE department_id = 10;
  emp_record employees%ROWTYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_record.first_name || ' ' || emp_record.last_name);
  END LOOP;
  CLOSE emp_cursor;
END;
/
  

A PL/SQL Cursor is a pointer to the context area, where SQL results are processed. There are two types: explicit (manually controlled) and implicit (automatically managed). Cursors are used to handle multiple rows from a query.

PL/SQL Exception

-- PL/SQL Exception Handling Example
DECLARE
  num1 NUMBER := 10;
  num2 NUMBER := 0;
  result NUMBER;
BEGIN
  -- Deliberate division by zero error
  result := num1 / num2;
  DBMS_OUTPUT.PUT_LINE('Result is: ' || result);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END;
/
  

PL/SQL Exceptions are used to handle errors that occur during program execution. The EXCEPTION block catches errors like division by zero and handles them gracefully. Use WHEN to specify the error type.

PL/SQL Trigger

-- PL/SQL Trigger Example
CREATE OR REPLACE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('About to insert a new employee: ' || :NEW.first_name || ' ' || :NEW.last_name);
END;
/
-- Insert to Trigger
INSERT INTO employees (first_name, last_name, department_id) VALUES ('Jane', 'Doe', 20);
  

A PL/SQL Trigger is a stored procedure that runs automatically when a specific event occurs in the database, like inserting, updating, or deleting a record. Triggers are useful for enforcing rules or auditing.