Introduction to PL SQL Block Structure

If you’ve worked with Oracle databases, you’ve definately heard of PL/SQL—Oracle’s very own procedural extension of SQL. It’s the secret sauce behind everything from simple data operations to full-blown enterprise applications. PL SQL Block Structure is the heart of the PL/SQL.

At the core of PL/SQL is something that might seem simple at first but is incredibly powerful once you understand it: the PL/SQL block. Whether you’re a beginner or brushing up your skills, this guide breaks it all down—block by block—so you can write clean, error-resistant code that actually does what you want. You can learn more on PL/SQL by exploring cursors and for loop.

What’s in a PL/SQL Block Structure?

Think of a PL/SQL block as a small self-contained program that can live inside your database. It comes in three parts:

DECLARE
   -- Here you set up your variables, constants, and exceptions (Optional)
BEGIN
   -- This is where the actual logic goes (Required)
EXCEPTION
   -- And this is your safety net to catch errors (Optional)
END;
/

💡 Tip: The slash / at the end isn’t part of PL/SQL itself—it just tells tools like SQL*Plus or SQL Developer, “Hey, I’m done with this block. Go ahead and run it!”

🛠️ Part 1: DECLARE Section of PL SQL Block

The DECLARE section is like the prep table in a kitchen. Before you start cooking up logic, you prep all your ingredients—variables, constants, cursors, even your own custom error messages.

Example: Giving an Employee a Bonus

DECLARE
   l_employee_id    employees.employee_id%TYPE := 1001;
   l_employee_name  employees.first_name%TYPE;
   l_salary         employees.salary%TYPE;
   l_bonus          NUMBER;
   c_tax_rate       CONSTANT NUMBER := 0.15;

   CURSOR emp_cur 
   IS
   SELECT first_name, salary
     FROM employees
    WHERE employee_id = l_employee_id;

   e_salary_too_low EXCEPTION;
   PRAGMA EXCEPTION_INIT(e_salary_too_low, -20001);
BEGIN
   OPEN emp_cur;
   FETCH emp_cur INTO l_employee_name, l_salary;
   CLOSE emp_cur;

   IF l_salary < 1000 THEN
      RAISE e_salary_too_low;
   ELSE
      l_bonus := (l_salary * 0.10) - (l_salary * c_tax_rate);
      DBMS_OUTPUT.PUT_LINE('Employee: ' ||l_employee_name);
      DBMS_OUTPUT.PUT_LINE('Bonus after tax: ' || l_bonus);
   END IF;

EXCEPTION
   WHEN e_salary_too_low THEN
      DBMS_OUTPUT.PUT_LINE('Error: Salary too low for bonus calculation.');
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Error: No such employee found.');
END;
/

⚙️ Part 2: BEGIN – The Execution Section of PL SQL Block

This is where the real action happens.

In the BEGIN section, you write all the logic you need—fetching data, running calculations, updating tables, calling procedures… you name it. This section has to be there, even if the other two are optional.

Part 3: EXCEPTION Section of PL SQL Block

Things don’t always go as planned. That’s where the EXCEPTION block shines. It lets you catch errors gracefully and give helpful feedback—or log them for debugging later.

Common Exceptions:

Your own user-defined exceptions – Custom rules you want to enforce

NO_DATA_FOUND – Nothing came back from your query

TOO_MANY_ROWS – More than one row came back when only one was expected

WHEN_OTHERS – will catch any exception that is not catch in earlier exception. WHEN_OTHERS should be placed as the last execption.

🎯 Creating Your Own Exceptions

Sometimes Oracle’s built-in errors just don’t cover what you need. Maybe an order exceeds a business-defined limit. Or a record doesn’t meet certain approval rules.

That’s when user-defined exceptions come in handy.

DECLARE
   v_product_id     NUMBER := 500;
   v_order_quantity NUMBER := 150;
   v_max_quantity   NUMBER := 100;
   e_quantity_exceeded EXCEPTION;
BEGIN
   IF v_order_quantity > v_max_quantity THEN
      RAISE e_quantity_exceeded;
   ELSE
      DBMS_OUTPUT.PUT_LINE('Order accepted!');
   END IF;
EXCEPTION
   WHEN e_quantity_exceeded THEN
      DBMS_OUTPUT.PUT_LINE('Error: You can’t order more than ' || v_max_quantity || ' units.');
END;
/

🔁 Bonus: Nested Blocks and Variable Scope

PL/SQL lets you nest blocks inside each other like Russian dolls. This is great for organizing your logic and keeping variables scoped locally.

DECLARE
   v_outer_var VARCHAR2(50) := 'Outer';
BEGIN
   DBMS_OUTPUT.PUT_LINE(v_outer_var);
   --
   DECLARE
      v_inner_var VARCHAR2(50) := 'Inner';
   BEGIN
      DBMS_OUTPUT.PUT_LINE(v_outer_var); -- OK
      DBMS_OUTPUT.PUT_LINE(v_inner_var); -- OK
   END;
   -- DBMS_OUTPUT.PUT_LINE(v_inner_var); -- ❌ Won’t work (out of scope)
END;
/

🧠 Best Practices (From Experience!)

• 🛠 Catch known exceptions individually: Your future self will thank you during debugging.
• ❗ Be careful with WHEN OTHERS: It catches everything, which sounds nice… until it hides something important.
• 🧾 Use RAISE_APPLICATION_ERROR: This is how you talk back to the application in a user-friendly way.
• 🧹 Clean up properly: Always close cursors and release resources.
• 💡 Comment wisely: Explain why, not just what.

📚 Quick Summary

🎓 Keep Exploring

Want to dive deeper into PL/SQL? These resources are great for developers at all levels:

💬 Oracle Developer Community Forum

📘 Oracle PL/SQL Documentation

🧑‍🏫 Top-Rated PL/SQL Courses – Udemy, LinkedIn Learning