Blocks in PL/SQL

PL/SQL blocks are the basic units of a PL/SQL program. A block contains declarations, executable statements and exception handling code.

• They are the fundamental building blocks of PL/SQL programs.
• They can contain declaration, execution and exception sections.
• They help organize and structure PL/SQL code efficiently.
• They support modular and reusable programming.

• Declaration Section: A PL/SQL block starts with the declaration section, where variables, constants, cursors and other Oracle objects are declared and initialized if needed.
• Begin Section: This section contains SQL and PL/SQL statements that define the program logic. It includes data manipulation, loops, conditions and other executable statements.
• Exception Section: This section handles errors that occur during program execution. It helps manage runtime errors caused by syntax, logic or invalid operations.
• End Section: The END keyword marks the termination of the PL/SQL block. A slash (/) is often used to execute the block in SQL*Plus or Oracle environments.

Types of PL/SQL Blocks 

PL/SQL blocks are of two types:

Anonymous Blocks

In PL/SQL, blocks that do not have a name or header are anonymous blocks. These blocks are not stored in the database and are executed only once. Anonymous blocks are standalone blocks that are not stored in the database like functions, procedures or triggers.

Example: The following program finds the greatest number using an anonymous block.

DECLARE
    a NUMBER := 10;
    b NUMBER := 100;
    c NUMBER;
BEGIN
    IF a > b THEN
        c := a;
    ELSE
        c := b;
    END IF;

    DBMS_OUTPUT.PUT_LINE('Maximum number is: ' || c);
END;
/

Output:

Maximum number is: 100

Named Blocks

In PL/SQL, blocks that have a name or header are named blocks. These blocks are stored in the database and can be executed multiple times. Named blocks include functions, procedures, packages and triggers.

Example: The following program finds the greatest number using a named block with a procedure.

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE find_max
AS
   a NUMBER := 10;
   b NUMBER := 100;
   c NUMBER;

BEGIN
   IF a > b THEN
      c := a;
   ELSE
      c := b;
   END IF;

   DBMS_OUTPUT.PUT_LINE('Maximum number in 10 and 100: ' || c);
END;
/

BEGIN
   find_max;
END;
/

Output:

Maximum number in 10 and 100: 100

Note:

In PL/SQL, every statement must end with a semicolon (`;`). The forward slash (`/`) is used to execute the complete PL/SQL block in tools like SQL*Plus and Oracle SQL Developer.