PL/SQL Blocks: Structure, Types, and Variable Management

PL/SQL (Procedural Language/Structured Query Language) uses blocks as the fundamental unit of programming. Every PL/SQL program is composed of one or more blocks that structure the code into manageable units.

This article dives deep into:

• Anonymous Blocks
• Named Blocks (Procedures and Functions)
• Scope and Visibility of Variables
• Declaration and Initialization of Variables

1. Anatomy of a PL/SQL Block

A PL/SQL block has three major sections:

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

2. Anonymous Blocks

An anonymous block is a PL/SQL block without a name. It is typically used for short, ad-hoc operations or for testing code.

Example: Anonymous Block

DECLARE
   v_message VARCHAR2(100);
BEGIN
   v_message := 'Hello from an anonymous block!';
   DBMS_OUTPUT.PUT_LINE(v_message);
END;
/

Output:

Hello from an anonymous block!

Use Cases:

• Testing code
• Running one-time scripts
• Embedding simple logic in tools like SQL*Plus or SQL Developer

Anonymous blocks cannot be stored in the database permanently.

3. Named Blocks

Named blocks include procedures, functions, packages, and triggers. These are reusable and stored in the database schema.

3.1. Stored Procedure (Named Block)

A procedure is a named block that performs a task but does not return a value.

CREATE OR REPLACE PROCEDURE greet_user(p_name IN VARCHAR2) IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END;
/

Execution:

BEGIN
   greet_user('Alice');
END;
/

Output:

Hello, Alice!

3.2. Function (Named Block)

Functions are named blocks that return a value.

plsqlCopyEditCREATE OR REPLACE FUNCTION square_number(p_num IN NUMBER)
RETURN NUMBER IS
BEGIN
   RETURN p_num * p_num;
END;
/

Usage:

DECLARE
   v_result NUMBER;
BEGIN
   v_result := square_number(6);
   DBMS_OUTPUT.PUT_LINE('Square is: ' || v_result);
END;
/

Output:

Square is: 36

4. Scope and Visibility of Variables

In PL/SQL, the scope of a variable defines where it can be accessed. Variables can be declared:

• In the main block (global to all nested blocks)
• In a nested (inner) block (local to that block)

Example: Scope Demonstration

DECLARE
   v_global VARCHAR2(50) := 'I am global';
BEGIN
   DBMS_OUTPUT.PUT_LINE(v_global); -- Accessible

   DECLARE
      v_local VARCHAR2(50) := 'I am local';
   BEGIN
      DBMS_OUTPUT.PUT_LINE(v_local);   -- Accessible
      DBMS_OUTPUT.PUT_LINE(v_global);  -- Accessible
   END;

   -- DBMS_OUTPUT.PUT_LINE(v_local); -- Error: v_local is not visible here
END;
/

Rule of Thumb:

• Outer block variables are visible to inner blocks.
• Inner block variables are hidden from outer blocks.

5. Declaration and Initialization of Variables

Variables must be declared before use in the DECLARE section.

5.1 Syntax

variable_name data_type [:= initial_value];

Example: Declaring Variables

DECLARE
   v_name     VARCHAR2(30) := 'John';
   v_age      NUMBER := 30;
   v_status   BOOLEAN := TRUE;
BEGIN
   IF v_status THEN
      DBMS_OUTPUT.PUT_LINE(v_name || ' is ' || v_age || ' years old.');
   END IF;
END;
/

Output:

John is 30 years old.

5.2 Using %TYPE and %ROWTYPE

To make code robust and consistent with table definitions:

DECLARE
   v_emp_name employees.last_name%TYPE;
   v_emp_record employees%ROWTYPE;
BEGIN
   SELECT last_name INTO v_emp_name FROM employees WHERE employee_id = 100;
   DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name);
END;
/

6. Best Practices for PL/SQL Blocks

• Always use meaningful variable names
• Initialize variables to avoid NULL issues
• Handle exceptions for runtime safety
• Use %TYPE and %ROWTYPE to tie variables to DB structure

Conclusion

Understanding the structure of PL/SQL blocks, knowing how to use anonymous and named blocks, and mastering variable declaration and scope are critical to writing clean and efficient PL/SQL code. Whether you’re scripting simple anonymous logic or developing enterprise-grade stored procedures, these foundational elements provide the building blocks for robust PL/SQL programming.