What is PL/SQL Basics?
PL/SQL Basics refers to the fundamental building blocks of Oracle’s Procedural Language extension to SQL. Often called “PL/SQL Fundamentals” or “PL/SQL Core Concepts,” it provides the structural and syntactic foundation for writing procedural code that executes within the Oracle Database.
The core purpose is to overcome SQL’s limitation as a declarative language by adding procedural capabilities like variables, conditional logic, loops, and error handling.
PL/SQL Blocks
PL/SQL programs are structured as blocks - logical units that group declarations and statements. Blocks can be named (functions, procedures) or anonymous (inline execution).
-- Anonymous PL/SQL Block structure
DECLARE
-- Variable declarations go here
BEGIN
-- Executable statements go here
EXCEPTION
-- Error handling goes here
END;
/
DECLARE Section
The DECLARE section is optional and contains definitions of variables, constants, cursors, and user-defined types that will be used in the block.
DECLARE
-- Variable declaration with data type
employee_id NUMBER(6) := 100;
-- Constant declaration
tax_rate CONSTANT NUMBER(4,2) := 0.15;
-- Variable with default value
employee_name VARCHAR2(50) DEFAULT 'John Doe';
-- Record type variable
TYPE emp_rec IS RECORD (
id employees.employee_id%TYPE,
name employees.last_name%TYPE
);
rec emp_rec;
BEGIN
-- Executable code follows
NULL;
END;
/
BEGIN Section
The BEGIN section contains the executable statements that form the main logic of the PL/SQL block.
The BEGIN section is where your business logic and SQL operations are executed.
DECLARE
total_salary NUMBER := 0;
emp_count NUMBER := 0;
BEGIN
-- SQL operation within PL/SQL
SELECT SUM(salary), COUNT(*)
INTO total_salary, emp_count
FROM employees
WHERE department_id = 50;
-- Procedural logic
IF emp_count > 0 THEN
-- Assignment statement
total_salary := total_salary * 1.1; -- 10% bonus
END IF;
-- Output result
DBMS_OUTPUT.PUT_LINE('Total adjusted salary: ' || total_salary);
END;
/
EXCEPTION Section
The EXCEPTION section handles runtime errors gracefully, preventing program termination.
Always include exception handling to make your PL/SQL code robust and production-ready.
DECLARE
emp_salary employees.salary%TYPE;
invalid_dept EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_dept, -2291);
BEGIN
-- This might raise an exception if department doesn't exist
SELECT salary INTO emp_salary
FROM employees
WHERE department_id = 999; -- Non-existent department
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employees found in specified department');
WHEN invalid_dept THEN
DBMS_OUTPUT.PUT_LINE('Invalid department reference');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/
END Statement
The END statement marks the conclusion of the PL/SQL block and can include the block label for clarity.
<<salary_calculator>>
DECLARE
-- declarations
BEGIN
-- executable statements
EXCEPTION
-- exception handlers
END salary_calculator; -- Labeled END for readability
/
Variables and Constants
Variables store values that can change during execution, while constants hold immutable values.
Use %TYPE to anchor variables to table columns, ensuring type consistency.
DECLARE
-- Variable (can be modified)
counter NUMBER := 0;
-- Constant (cannot be modified after declaration)
company_name CONSTANT VARCHAR2(30) := 'Oracle Corporation';
-- Variable using anchored datatype (%TYPE)
emp_last_name employees.last_name%TYPE;
BEGIN
counter := counter + 1; -- Valid: variable modification
-- company_name := 'New Name'; -- Invalid: constant cannot be modified
-- Using anchored variable
SELECT last_name INTO emp_last_name
FROM employees WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_last_name);
END;
/
Data Types
PL/SQL supports scalar, composite, reference, and LOB data types.
DECLARE
-- Scalar types
emp_id NUMBER(6);
emp_name VARCHAR2(50);
hire_date DATE := SYSDATE;
is_active BOOLEAN := TRUE;
-- Composite type (record)
TYPE employee_record IS RECORD (
id employees.employee_id%TYPE,
name employees.last_name%TYPE,
salary employees.salary%TYPE
);
emp_rec employee_record;
-- Composite type (collection)
TYPE name_array IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
names name_array;
BEGIN
-- Using scalar type
emp_id := 101;
emp_name := 'Smith';
-- Using record type
emp_rec.id := 102;
emp_rec.name := 'Johnson';
emp_rec.salary := 5000;
-- Using collection
names(1) := 'Alice';
names(2) := 'Bob';
END;
/
Control Structures
Control structures manage program flow through conditionals and loops.
Control structures are essential for implementing complex business logic.
DECLARE
score NUMBER := 85;
i NUMBER;
total NUMBER := 0;
BEGIN
-- Conditional (IF-THEN-ELSIF)
IF score >= 90 THEN
DBMS_OUTPUT.PUT_LINE('Grade: A');
ELSIF score >= 80 THEN
DBMS_OUTPUT.PUT_LINE('Grade: B');
ELSE
DBMS_OUTPUT.PUT_LINE('Grade: C or below');
END IF;
-- Loop (FOR)
FOR i IN 1..5 LOOP
total := total + i;
DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
END LOOP;
-- Loop (WHILE)
i := 1;
WHILE i <= 3 LOOP
DBMS_OUTPUT.PUT_LINE('While loop iteration: ' || i);
i := i + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total: ' || total);
END;
/
Why is PL/SQL Basics Important?
Procedural Abstraction
Structured Programming Principle: Encapsulates complex operations into manageable blocks, promoting maintainable code organization over unstructured spaghetti code.
DRY Principle
Don’t Repeat Yourself: Through variables, constants, and reusable blocks, PL/SQL eliminates code duplication by centralizing business logic within the database.
Separation of Concerns
SOLID Principle: Proper use of declaration, execution, and exception sections separates variable definition, business logic, and error handling into distinct responsibilities.
Advanced Nuances
Exception Propagation in Nested Blocks
Use nested blocks and exception propagation for sophisticated error handling strategies.
BEGIN
<<outer_block>>
DECLARE
result NUMBER;
BEGIN
<<inner_block>>
BEGIN
-- This exception will propagate to outer_block
SELECT 1/0 INTO result FROM dual;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Inner handled: ' || SQLERRM);
RAISE; -- Re-raise to outer block
END inner_block;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Outer handled: ' || SQLERRM);
END outer_block;
END;
/
DECLARE
table_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(table_not_exist, -942);
BEGIN
-- Attempt to query non-existent table
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM non_existent_table';
EXCEPTION
WHEN table_not_exist THEN
-- Dynamic recovery: create table and retry
EXECUTE IMMEDIATE 'CREATE TABLE non_existent_table (id NUMBER)';
DBMS_OUTPUT.PUT_LINE('Table created dynamically');
END;
/
Bulk Operations with Control Structures
Use FORALL for bulk DML operations to improve performance dramatically.
DECLARE
TYPE id_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE name_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
ids id_array;
names name_array;
BEGIN
-- Initialize collections
FOR i IN 1..5 LOOP
ids(i) := i * 100;
names(i) := 'Employee_' || i;
END LOOP;
-- Bulk SQL operation using FORALL
FORALL i IN 1..ids.COUNT
INSERT INTO employees_temp (employee_id, last_name)
VALUES (ids(i), names(i));
DBMS_OUTPUT.PUT_LINE('Bulk inserted: ' || SQL%ROWCOUNT || ' rows');
END;
/
How This Fits the Roadmap
Within the “PL/SQL Programming” section, PL/SQL Basics serves as the foundational layer upon which all advanced topics are built.
Absolute Prerequisites For:
- Stored Program Units (procedures, functions, packages)
- Advanced Cursor Techniques
- Collection Processing
- Error Management Strategies
- PL/SQL Compiler behavior understanding
Unlocks:
- Entire spectrum of database programming capabilities
- Sophisticated PL/SQL applications
- Enterprise-scale development
- Performance tuning opportunities