PL/SQL for Beginners

A comprehensive guide to Oracle's procedural language extension for SQL

Introduction to PL/SQL

PL/SQL (Procedural Language extensions to SQL) is Oracle Corporation's procedural extension for SQL and the Oracle relational database.

Key Features
  • Block-structured language
  • Seamless SQL integration
  • Improved performance
  • Portable across Oracle environments
Basic Structure
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL World!');
END;
Did You Know?

PL/SQL was first introduced in 1991 with Oracle 6, revolutionizing database programming by combining SQL with procedural constructs.

Basic Structure

PL/SQL is organized into blocks, which can be nested within each other.

Block Components
  1. DECLARE (optional): Variables, cursors
  2. BEGIN (mandatory): Executable statements
  3. EXCEPTION (optional): Error handling
  4. END (mandatory): Block termination
Complete Example
DECLARE
  v_message VARCHAR2(50) := 'Hello World';
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_message);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error occurred');
END;

Variables and Data Types

PL/SQL supports various data types and variable declaration methods.

Category Data Types Description
Scalar NUMBER, VARCHAR2, DATE, BOOLEAN Single values with no internal components
Composite RECORD, TABLE Data structures with multiple components
Reference REF CURSOR Pointers to other data items
LOB BFILE, BLOB, CLOB Large objects like images or documents
Variable Declaration
DECLARE
  v_employee_id NUMBER(6);
  v_name VARCHAR2(50) := 'John Doe';
  v_hiredate DATE := SYSDATE;
  v_is_active BOOLEAN := TRUE;
BEGIN
  -- Your code here
END;
Best Practices
  • Use meaningful variable names
  • Initialize variables when declaring
  • Use %TYPE and %ROWTYPE for database consistency
  • Prefix variables with v_ to distinguish from columns

Control Structures

Control structures determine the flow of program execution.

Conditional
IF condition THEN
  -- statements
ELSIF condition THEN
  -- statements
ELSE
  -- statements
END IF;
Loops
FOR i IN 1..10 LOOP
  -- statements
END LOOP;

WHILE condition LOOP
  -- statements
END LOOP;
CASE
CASE grade
  WHEN 'A' THEN dbms_output.put_line('Excellent');
  WHEN 'B' THEN dbms_output.put_line('Good');
  ELSE dbms_output.put_line('Other');
END CASE;
Practical Example
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');
  END IF;
END;

SQL in PL/SQL

PL/SQL integrates seamlessly with SQL for database operations.

DML Operations
  • SELECT INTO: Retrieve single row
  • INSERT: Add new records
  • UPDATE: Modify existing records
  • DELETE: Remove records
Implicit Cursors
  • SQL%FOUND: True if rows affected
  • SQL%NOTFOUND: True if no rows
  • SQL%ROWCOUNT: Number of rows affected
Transactions
  • COMMIT: Save changes
  • ROLLBACK: Undo changes
  • SAVEPOINT: Create rollback points
Practical Example
BEGIN
  -- Insert new employee
  INSERT INTO employees VALUES(101, 'John Doe', 'Developer', SYSDATE, 5000);
  
  -- Update salary
  UPDATE employees SET salary = 5500 WHERE emp_id = 101;
  
  -- Check if update worked
  IF SQL%FOUND THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');
  ELSE
    ROLLBACK;
  END IF;
END;

Cursors

Cursors allow processing of multiple rows returned by SQL queries.

Cursor Types
  • Explicit: Programmer-defined with full control
  • Implicit: Automatically created for DML
  • Parameterized: Accept parameters for filtering
  • REF CURSOR: Dynamic cursor variable
Cursor Attributes
  • %FOUND: True if row fetched
  • %NOTFOUND: True if no more rows
  • %ISOPEN: True if cursor open
  • %ROWCOUNT: Number of rows fetched
Explicit Cursor
DECLARE
  CURSOR emp_cursor IS 
    SELECT * FROM employees WHERE dept_id = 10;
  v_emp employees%ROWTYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_emp;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.emp_name);
  END LOOP;
  CLOSE emp_cursor;
END;
Cursor FOR Loop
BEGIN
  FOR emp_rec IN (SELECT * FROM employees) LOOP
    DBMS_OUTPUT.PUT_LINE(emp_rec.emp_name || 
                        ' earns ' || emp_rec.salary);
  END LOOP;
END;

Exception Handling

PL/SQL provides robust error handling mechanisms.

Exception Types
  • Predefined: NO_DATA_FOUND, TOO_MANY_ROWS, etc.
  • User-defined: Custom exceptions you declare
  • Non-predefined: Named system exceptions
Handling Techniques
  • WHEN exception THEN: Catch specific errors
  • WHEN OTHERS THEN: Catch all unhandled errors
  • RAISE_APPLICATION_ERROR: Raise custom errors (-20000 to -20999)
Complete Example
DECLARE
  v_emp_id NUMBER := 9999;
  v_salary NUMBER;
  e_invalid_emp EXCEPTION;
BEGIN
  IF v_emp_id = 0 THEN
    RAISE e_invalid_emp;
  END IF;
  
  SELECT salary INTO v_salary 
  FROM employees WHERE emp_id = v_emp_id;
  
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
  WHEN e_invalid_emp THEN
    DBMS_OUTPUT.PUT_LINE('Invalid employee ID');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RAISE_APPLICATION_ERROR(-20001, 'Unexpected error');
END;

Stored Procedures and Functions

Reusable program units stored in the database.

Procedures
  • Perform actions without returning values
  • Can have multiple OUT parameters
  • Called as standalone statements
  • Use CREATE OR REPLACE PROCEDURE
Functions
  • Must return a single value
  • Can be used in SQL statements
  • Called as part of expressions
  • Use CREATE OR REPLACE FUNCTION
Procedure Example
CREATE OR REPLACE PROCEDURE update_salary(
  p_emp_id IN NUMBER,
  p_amount IN NUMBER,
  p_status OUT VARCHAR2
) AS
BEGIN
  UPDATE employees 
  SET salary = salary + p_amount 
  WHERE emp_id = p_emp_id;
  
  IF SQL%ROWCOUNT = 0 THEN
    p_status := 'Employee not found';
  ELSE
    p_status := 'Salary updated';
    COMMIT;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    p_status := 'Error: ' || SQLERRM;
    ROLLBACK;
END;
Function Example
CREATE OR REPLACE FUNCTION get_employee_name(
  p_emp_id IN NUMBER
) RETURN VARCHAR2 AS
  v_name VARCHAR2(100);
BEGIN
  SELECT emp_name INTO v_name
  FROM employees 
  WHERE emp_id = p_emp_id;
  
  RETURN v_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 'Unknown';
END;

Packages

Packages group related program units together.

Package Spec
  • Public interface (what's visible)
  • Declares types, variables, constants
  • Declares procedures and functions
  • No implementation details
Package Body
  • Implementation of spec components
  • Can include private components
  • Optional initialization section
  • Can be recompiled separately
Package Example
-- Specification
CREATE OR REPLACE PACKAGE emp_pkg AS
  -- Public procedure
  PROCEDURE update_salary(p_emp_id NUMBER, p_amount NUMBER);
  
  -- Public function
  FUNCTION get_employee_name(p_emp_id NUMBER) RETURN VARCHAR2;
  
  -- Public constant
  g_max_salary CONSTANT NUMBER := 100000;
END emp_pkg;

-- Body
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  -- Private function
  FUNCTION is_valid_emp(p_emp_id NUMBER) RETURN BOOLEAN IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count 
    FROM employees WHERE emp_id = p_emp_id;
    RETURN v_count > 0;
  END;
  
  -- Implement public procedure
  PROCEDURE update_salary(p_emp_id NUMBER, p_amount NUMBER) IS
  BEGIN
    IF is_valid_emp(p_emp_id) THEN
      UPDATE employees 
      SET salary = salary + p_amount 
      WHERE emp_id = p_emp_id;
    END IF;
  END;
  
  -- Implement public function
  FUNCTION get_employee_name(p_emp_id NUMBER) RETURN VARCHAR2 IS
    v_name VARCHAR2(100);
  BEGIN
    SELECT emp_name INTO v_name
    FROM employees WHERE emp_id = p_emp_id;
    RETURN v_name;
  END;
  
BEGIN
  -- Initialization section (runs once per session)
  DBMS_OUTPUT.PUT_LINE('Employee package initialized');
END emp_pkg;

Triggers

Triggers are stored programs that automatically execute in response to events.

Trigger Timing
  • BEFORE: Execute before the triggering event
  • AFTER: Execute after the triggering event
  • INSTEAD OF: Replace the triggering event (for views)
Trigger Levels
  • Row-level: Fires for each affected row (FOR EACH ROW)
  • Statement-level: Fires once per statement (default)
Trigger Example
CREATE OR REPLACE TRIGGER audit_employee_changes
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
  v_action VARCHAR2(10);
BEGIN
  IF INSERTING THEN
    v_action := 'INSERT';
  ELSIF UPDATING THEN
    v_action := 'UPDATE';
  ELSE
    v_action := 'DELETE';
  END IF;
  
  INSERT INTO emp_audit (
    audit_id, 
    emp_id, 
    action, 
    change_date,
    old_salary,
    new_salary
  ) VALUES (
    audit_seq.NEXTVAL,
    :OLD.emp_id,
    v_action,
    SYSDATE,
    :OLD.salary,
    :NEW.salary
  );
END;
Mutating Table Issue

A common trigger error occurs when a trigger tries to query or modify the table that fired the trigger. To avoid this:

  • Use compound triggers (Oracle 11g+)
  • Use package variables to store intermediate data
  • Restructure your logic to avoid the conflict

Ready to Master PL/SQL?

Practice these concepts with real-world examples and exercises

Start Practicing Now

Related database resources

PL/SQL builds on Oracle SQL—pair these notes with the Oracle track, then branch to other engines when job specs span stacks.

Use this as a study map: master SQL foundations first, then deepen with procedures, packages, and triggers on Oracle.

Oracle SQL and exams: Oracle SQL notes, Oracle MCQ practice, Oracle interview questions, Oracle query examples, Oracle tips and tricks, the Oracle SQL cheatsheet, and Oracle certifications.

MySQL SQL: MySQL SQL notes, MySQL MCQs, and MySQL interview questions for stored-program differences versus Oracle.

SQL Server: SQL Server SQL notes, SQL Server MCQs, and SQL Server interview questions for T-SQL procedural parallels.

Document data: MongoDB notes and MongoDB interview questions when APIs mix Oracle backends with document stores.

Return to the Databases hub for the full index of notes, queries, interviews, and cheatsheets across engines.