What is PL/SQL?

PL/SQL (Procedural Language/Structured Query Language) is Oracle’s proprietary extension of SQL—designed specifically for Oracle databases. It blends the data manipulation power of SQL with the control structures of procedural programming languages like Ada and Pascal.

Unlike standard SQL, which is non-procedural and declarative, PL/SQL allows for writing complete programs using variables, conditions, loops, functions, procedures, and even object-oriented constructs.

Why "PL" in PL/SQL?

The “PL” stands for Procedural Language. PL/SQL was created to allow developers to write complex business logic inside the database layer, which SQL alone couldn’t handle efficiently.

Initially, PL/SQL was a pure procedural language, but Oracle enhanced it to support object-oriented programming (OOP) features, making it a hybrid language today.

Key Features of PL/SQL

Feature	Description
Procedural Logic	Supports variables, loops, conditional branching (`IF`, `WHILE`, `FOR`)
SQL Integration	Fully supports SQL statements for querying and data manipulation
Error Handling	Built-in `EXCEPTION` handling for better reliability
Modular Code	Allows creating procedures, functions, packages, and triggers
Object-Oriented	Supports object types, methods, and even limited inheritance (Oracle 8+)

Example (PL/SQL):

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

This is a basic anonymous block, the simplest PL/SQL unit. It runs line by line and prints a message using Oracle’s built-in DBMS_OUTPUT package.

Example Declaring Variables in PL/SQL:

DECLARE
   name VARCHAR2(50) := 'amantpoint';
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello, ' || name);
END;

Control Structures (Loops & Conditions)

Example IF Statement:

DECLARE
   name VARCHAR2(50) := 'amantpoint';
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello, ' || name);
END;

Example FOR Loop:

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

PL/SQL as Object-Oriented

Oracle now supports object types with methods:

Example:

CREATE TYPE student_t AS OBJECT (
   name VARCHAR2(100),
   grade NUMBER,
   MEMBER PROCEDURE display_info
);
/
CREATE TYPE BODY student_t AS
   MEMBER PROCEDURE display_info IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('Name: ' || name || ', Grade: ' || grade);
   END;
END;
/

DECLARE
   s student_t := student_t('Ravi', 90);
BEGIN
   s.display_info;
END;
/