Understanding Block Types in PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural extension for SQL. It allows developers to write complex logic directly in the Oracle Database server. It integrates seamlessly with SQL, providing powerful capabilities such as loops, conditions, error handling, and modular programming.

What is a PL/SQL Block?

In PL/SQL, blocks are the fundamental units of code. Each block is a structured group of code statements which the PL/SQL engine executes. These blocks can:

• Execute SQL statements
• Display outputs
• Call other procedures
• Handle errors
• Process complex logic

There are two primary types of PL/SQL blocks:

• Anonymous Blocks
• Named Blocks (Procedures, Functions, Triggers, Packages)

---

🔹 Anonymous Blocks

As the name implies, anonymous blocks are unnamed blocks of PL/SQL code. They are not stored in the database and are mainly used for one-time operations or testing.

Syntax

DECLARE
   -- Declaration Section
BEGIN
   -- Execution Section
EXCEPTION
   -- Exception Handling Section
END;

Example: Simple Anonymous Block

DECLARE
   v_message VARCHAR2(100);
BEGIN
   v_message := 'Welcome to PL/SQL!';
   DBMS_OUTPUT.PUT_LINE(v_message);
END;

Explanation

• DECLARE: Declares a variable v_message.
• BEGIN: Assigns a value to the variable and prints it using DBMS_OUTPUT.PUT_LINE.
• EXCEPTION: Omitted here because no error handling is necessary for this simple case.

---

🔹 Named Blocks

Named blocks are stored in the Oracle database with a name and can be reused. These include:

• Procedures: Perform actions
• Functions: Return a value
• Packages: Group procedures/functions
• Triggers: Execute automatically on events

Example: Named Procedure

CREATE OR REPLACE PROCEDURE greet_user IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello, this is a named procedure!');
END;

Execution

BEGIN
   greet_user;
END;

---

🔸 Sections of a PL/SQL Block

A PL/SQL block is divided into three sections:

1. Declaration Section (optional)
2. Execution Section (mandatory)
3. Exception Handling Section (optional)

1. Declaration Section

Here, you declare variables, constants, cursors, or even custom types.

Example

DECLARE
   v_first_name VARCHAR2(30);
   v_last_name  VARCHAR2(30);
   c_status     CONSTANT NUMBER := 1;

• v_first_name and v_last_name are variables.
• c_status is a constant assigned a value of 1.

2. Execution Section

This section contains the logic and SQL statements.

Example: Fetching from a Table

DECLARE
   v_first_name employees.first_name%TYPE;
   v_last_name  employees.last_name%TYPE;
BEGIN
   SELECT first_name, last_name
   INTO v_first_name, v_last_name
   FROM employees
   WHERE employee_id = 100;

   DBMS_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name);
END;

• INTO clause assigns the result of the SELECT statement into the declared variables.
• DBMS_OUTPUT.PUT_LINE is used to print output to the console.

3. Exception Handling Section

Handles errors that occur during execution.

Example

DECLARE
   v_first_name employees.first_name%TYPE;
   v_last_name  employees.last_name%TYPE;
BEGIN
   SELECT first_name, last_name
   INTO v_first_name, v_last_name
   FROM employees
   WHERE employee_id = 9999;

   DBMS_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name);

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No employee found with given ID.');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;

• NO_DATA_FOUND handles the case where the query returns no row.
• OTHERS is a generic handler for any unexpected errors.

---

🛠 Can PL/SQL Blocks Execute DDL Statements?

PL/SQL blocks do not directly support DDL (Data Definition Language) like CREATE, ALTER, or DROP. However, using:

• Native Dynamic SQL (NDS) with EXECUTE IMMEDIATE
• DBMS_SQL Package

You can execute DDL dynamically.

Example using EXECUTE IMMEDIATE

BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE temp_table (id NUMBER)';
END;

Example using DBMS_SQL

DECLARE
   v_cursor INTEGER;
   v_status INTEGER;
BEGIN
   v_cursor := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(v_cursor, 'DROP TABLE temp_table', DBMS_SQL.NATIVE);
   v_status := DBMS_SQL.EXECUTE(v_cursor);
   DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;

---

Summary

---

Conclusion

PL/SQL blocks are the backbone of Oracle's server-side programming. Understanding the structure of anonymous and named blocks, along with proper usage of declarations, execution, and exception handling, allows developers to write powerful and efficient code.

By using techniques like dynamic SQL, we can also extend PL/SQL blocks to support complex scenarios including DDL execution.

Until next time, keep querying like a wizard — and stay curious. 🧙‍♂️