PL/SQL

PL/SQL stands for Procedural Language/Structured Query Language. It is Oracle Corporation’s procedural extension to SQL, designed for seamless integration with Oracle databases.

Key Characteristics

• Combines SQL with procedural programming constructs like loops, conditions, exceptions.

• Developed for performance, modularity, and security in Oracle database applications.

• Supports stored procedures, functions, packages, and triggers.

Why PL/SQL?

• Extends SQL with procedural features (IF, LOOP, CASE, etc.).

• Improves performance by reducing communication between client and server.

• Supports error handling with EXCEPTION.

• Allows modular development using procedures, functions, and packages.

• Enables code reuse and maintainability.

• Offers portability across Oracle environments.

Features of PL/SQL

• Tightly integrated with SQL

• Offers variables, types, loops, conditions, exceptions

• Supports object-oriented concepts

• Allows modular programming using procedures/functions

• Supports static and dynamic SQL

• Has robust exception handling

• Can interact with web technologies

PL/SQL Block Structure

Every PL/SQL program is written as a block, and blocks can be nested.

Structure

DECLARE
   -- Declarations (optional)
BEGIN
   -- Executable statements (mandatory)
EXCEPTION
   -- Error handling (optional)
END;

Example

DECLARE
  message VARCHAR2(50) := 'Hello, World!';
BEGIN
  DBMS_OUTPUT.PUT_LINE(message);
END;
/

PL/SQL Data Types

Scalar Types

Type	Description
NUMBER	Numeric values
VARCHAR2	Variable-length character data
CHAR	Fixed-length character data
BOOLEAN	TRUE, FALSE, or NULL
DATE	Date and time

Examples

DECLARE
  emp_id NUMBER;
  name VARCHAR2(50);
  is_active BOOLEAN := TRUE;
BEGIN
  NULL;
END;
/

Variables and Constants

Declaring Variables

variable_name datatype [NOT NULL := value];

Assigning Values

variable_name := value;

Get from Table

SELECT column INTO variable FROM table WHERE condition;

Constants

constant_name CONSTANT datatype := value;

Example

DECLARE
  salary_increase CONSTANT NUMBER := 10;
  emp_name VARCHAR2(100);
BEGIN
  emp_name := 'John';
END;

Control Structures

Conditional

IF condition THEN
  -- statements
ELSIF another_condition THEN
  -- more statements
ELSE
  -- default
END IF;

CASE

CASE variable
  WHEN 'A' THEN ...
  WHEN 'B' THEN ...
  ELSE ...
END CASE;

---

Loops

Simple Loop

LOOP
  -- statements
  EXIT WHEN condition;
END LOOP;

WHILE Loop

WHILE condition LOOP
  -- statements
END LOOP;

FOR Loop

FOR i IN 1..10 LOOP
  -- statements
END LOOP;

NULLs in PL/SQL

• NULL means unknown or undefined.

• Cannot compare with = or !=, use IS NULL or IS NOT NULL.

Error Handling (EXCEPTION Block)

BEGIN
  -- executable code
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No result found');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Some error occurred');
END;