PL/SQL: The Complete Guide

PL/SQL: The Complete Guide

Ready to go beyond SQL with some powerful procedural programming? In this guide, you’ll dive deep into PL/SQL, Oracle’s extension to SQL that lets you do more with your data. Let’s get started!


Table of Contents

  1. What is PL/SQL?
  2. Why Use PL/SQL?
  3. PL/SQL Block Structure
  4. Variables and Data Types
  5. Control Structures
  6. Cursors
  7. Exceptions
  8. Procedures and Functions
  9. Packages
  10. Triggers
  11. Records and Collections
  12. Object Types
  13. Dynamic SQL
  14. Best Practices
  15. Common Pitfalls
  16. Conclusion

What is PL/SQL?

PL/SQL is Oracle’s way of adding more power to SQL. It combines SQL’s data manipulation features with the flexibility of procedural programming. This means you can create complex scripts, functions, procedures, and even triggers, right inside your Oracle database.


Why Use PL/SQL?

Here’s why PL/SQL is awesome:

  • Performance: It reduces network traffic by sending SQL statements as a single block.
  • Modularity: You can reuse code through procedures and packages.
  • Error Handling: Offers robust exception handling to deal with runtime errors.
  • Security: Provides fine-tuned access control with stored procedures and packages.
  • Integration: Seamlessly integrates with SQL, giving you more control over data and transactions.

PL/SQL Block Structure

PL/SQL code is made up of logical blocks that can be nested.

DECLARE
   -- Variable declarations go here (optional)
BEGIN
   -- Executable statements
EXCEPTION
   -- Error handling (optional)
END;

Example:

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

Variables and Data Types

Declaring Variables

Declare variables in the DECLARE section of your PL/SQL block.

DECLARE
   v_employee_id NUMBER := 100;
   v_employee_name VARCHAR2(50);
BEGIN
   -- Do something with the variables
END;

Data Types

  • Scalar Types: Examples include NUMBER, VARCHAR2, DATE, BOOLEAN.
  • Composite Types: These include RECORD, TABLE, and VARRAY.
  • Reference Types: CURSOR types allow you to handle query results.
  • LOB Types: Used for large objects like BLOB and CLOB.

Anchored Declarations

  • %TYPE: Inherits a database column’s data type.
v_salary employees.salary%TYPE;
  • %ROWTYPE: Inherits the structure of a database row.
v_employee employees%ROWTYPE;

Control Structures

Conditional Statements

IF-THEN

IF condition THEN
   -- Code to run if condition is true
END IF;

IF-THEN-ELSE

IF condition THEN
   -- Code to run if condition is true
ELSE
   -- Code to run if condition is false
END IF;

IF-THEN-ELSIF

IF condition1 THEN
   -- Code to run if condition1 is true
ELSIF condition2 THEN
   -- Code to run if condition2 is true
ELSE
   -- Default action
END IF;

Loops

Simple Loop

LOOP
   -- Code inside loop
   EXIT WHEN condition;
END LOOP;

WHILE Loop

WHILE condition LOOP
   -- Code inside the loop
END LOOP;

FOR Loop

FOR counter IN [REVERSE] start..end LOOP
   -- Code inside the loop
END LOOP;

Example:

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

Cursors

Need to work with multiple rows in a query? That’s where cursors come in handy.

Implicit Cursors

Automatically created for single-row SQL queries.

DECLARE
   v_count NUMBER;
BEGIN
   SELECT COUNT(*) INTO v_count FROM employees;
END;

Explicit Cursors

Programmer-defined for queries returning multiple rows.

Declaring and Using an Explicit Cursor

DECLARE
   CURSOR c_employees IS
      SELECT employee_id, first_name FROM employees;
   v_employee_id employees.employee_id%TYPE;
   v_first_name employees.first_name%TYPE;
BEGIN
   OPEN c_employees;
   LOOP
      FETCH c_employees INTO v_employee_id, v_first_name;
      EXIT WHEN c_employees%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_employee_id || ': ' || v_first_name);
   END LOOP;
   CLOSE c_employees;
END;

Exceptions

Exceptions handle errors that happen during runtime.

Predefined Exceptions

  • NO_DATA_FOUND
  • TOO_MANY_ROWS
  • ZERO_DIVIDE
  • VALUE_ERROR
  • OTHERS: A catch-all for unhandled exceptions.

Handling Exceptions

BEGIN
   -- Risky operations
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      -- Handle no data found
   WHEN ZERO_DIVIDE THEN
      -- Handle divide by zero
   WHEN OTHERS THEN
      -- Handle all other errors
END;

Procedures and Functions

Procedures

Procedures perform actions but don’t return values directly.

Creating a Procedure

CREATE OR REPLACE PROCEDURE update_salary(p_emp_id NUMBER, p_increment NUMBER) AS
BEGIN
   UPDATE employees
   SET salary = salary + p_increment
   WHERE employee_id = p_emp_id;
END;

Executing a Procedure

BEGIN
   update_salary(101, 500);
END;

Functions

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

Creating a Function

CREATE OR REPLACE FUNCTION get_full_name(p_emp_id NUMBER) RETURN VARCHAR2 AS
   v_full_name VARCHAR2(100);
BEGIN
   SELECT first_name || ' ' || last_name INTO v_full_name
   FROM employees
   WHERE employee_id = p_emp_id;
   RETURN v_full_name;
END;

Using a Function

DECLARE
   v_name VARCHAR2(100);
BEGIN
   v_name := get_full_name(101);
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;

Packages

Packages group related procedures, functions, and other code elements together.

Creating a Package Specification

CREATE OR REPLACE PACKAGE employee_pkg AS
   PROCEDURE hire_employee(p_name VARCHAR2, p_salary NUMBER);
   PROCEDURE fire_employee(p_emp_id NUMBER);
END employee_pkg;

Creating a Package Body

CREATE OR REPLACE PACKAGE BODY employee_pkg AS
   PROCEDURE hire_employee(p_name VARCHAR2, p_salary NUMBER) AS
   BEGIN
      INSERT INTO employees (employee_id, first_name, salary)
      VALUES (employees_seq.NEXTVAL, p_name, p_salary);
   END;

   PROCEDURE fire_employee(p_emp_id NUMBER) AS
   BEGIN
      DELETE FROM employees WHERE employee_id = p_emp_id;
   END;
END employee_pkg;

Triggers

Triggers are stored procedures that automatically execute when specific events occur.

Creating a Row-Level Trigger

CREATE OR REPLACE TRIGGER trg_update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
   :NEW.last_modified := SYSDATE;
END;

Creating a Statement-Level Trigger

CREATE OR REPLACE TRIGGER trg_logon_audit
AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO audit_log (user_name, login_time)
   VALUES (USER, SYSDATE);
END;

Records and Collections

Records

Records are composite data structures similar to rows in a table.

DECLARE
   TYPE employee_rec IS RECORD (
      employee_id employees.employee_id%TYPE,
      first_name  employees.first_name%TYPE,
      salary      employees.salary%TYPE
   );
   v_employee employee_rec;
BEGIN
   SELECT employee_id, first_name, salary INTO v_employee
   FROM employees
   WHERE employee_id = 101;
END;

Collections

Associative Arrays (Index-By Tables)

DECLARE
   TYPE salary_table IS TABLE OF employees.salary%TYPE INDEX BY PLS_INTEGER;
   v_salaries salary_table;
BEGIN
   v_salaries(1) := 5000;
   v_salaries(2) := 6000;
END;

Nested Tables

DECLARE
   TYPE name_table IS TABLE OF VARCHAR2(50);
   v_names name_table := name_table('Alice', 'Bob', 'Charlie');
BEGIN
   FOR i IN v_names.FIRST .. v_names.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(v_names(i));
   END LOOP;
END;

VARRAYs

DECLARE
   TYPE project_list IS VARRAY(5) OF VARCHAR2(30);
   v_projects project_list := project_list('Project A', 'Project B');
BEGIN
   -- Access elements using index
   DBMS_OUTPUT.PUT_LINE(v_projects(1));
END;

Object Types

Object types allow you to define complex data types in Oracle.

Creating an Object Type

CREATE TYPE address_typ AS OBJECT (
   street   VARCHAR2(50),
   city     VARCHAR2(50),
   zip_code VARCHAR2(10)
);

Using Object Types

DECLARE
   v_address address_typ := address_typ('123 Main St', 'Anytown', '12345');
BEGIN
   DBMS_OUTPUT.PUT_LINE('City: ' || v_address.city);
END;

Dynamic SQL

Dynamic SQL lets you execute SQL statements built at runtime.

Using EXECUTE IMMEDIATE

DECLARE
   v_table_name VARCHAR2(30) := 'employees';
   v_sql        VARCHAR2(1000);
   v_count      NUMBER;
BEGIN
   v_sql := 'SELECT COUNT(*) FROM ' || v_table_name;
   EXECUTE IMMEDIATE v_sql INTO v_count;
END;

Using DBMS_SQL Package

For more complex dynamic SQL tasks where column numbers aren’t known at compile time.

DECLARE
   v_cursor_id INTEGER;
   v_sql       VARCHAR2(1000) := 'SELECT first_name FROM employees';
   v_first_name VARCHAR2(50);
BEGIN
   v_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(v_cursor_id, v_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 1, v_first_name, 50);
   IF DBMS_SQL.EXECUTE_AND_FETCH(v_cursor_id) > 0 THEN
      DBMS_SQL.COLUMN_VALUE(v_cursor_id, 1, v_first_name);
      DBMS_OUTPUT.PUT_LINE('First Name: ' || v_first_name);
   END IF;
   DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
END;

Best Practices

  • Use Bind Variables: Helps prevent SQL injection and boost performance.
  • Exception Handling: Always handle exceptions to keep control of program flow.
  • Code Modularity: Organize code into reusable procedures and packages.
  • Documentation: Comment your code for clarity and maintainability.
  • Transaction Management: Control when to commit or rollback transactions.
  • Naming Conventions: Use consistent naming for your variables and functions.

Common Pitfalls

  • Unmanaged Cursors: Always close cursors to avoid memory leaks.
  • Unhandled Exceptions: Failing to handle errors can crash your programs.
  • Hardcoding Values: Use parameters instead of fixed values in your code.
  • Inefficient Loops: Use bulk operations like FORALL for better performance.
  • Overprivileged Users: Limit permissions to keep your database secure.

Conclusion

PL/SQL adds procedural power to SQL, making it easier to build robust, efficient database applications. By mastering PL/SQL, you can handle complex business logic directly in the database, improving performance and security.

Now it’s your turn—try using PL/SQL in your projects and see how it transforms your database workflows!


Test Your Knowledge!

Try to solve these PL/SQL challenges by writing the correct code. You can select your difficulty level and cycle through the questions within each level.

1

×