PL blocks

A PL program consists of three blocks: the declaration block (DECLARE), the execution block (BEGIN), and the exception handling block (EXCEPTION).

The three blocks serve the following purposes:

• Declaration block: declares variables, types, cursors, and local stored procedures and functions used in PL.

• Execution block: displays the execution process and SQL statements. It is the main part of the program.

• Exception handling block: displays error handling.

The syntax of a PL block is as follows:

<< label >> --Label (optional)
DECLARE
    /* Declaration block: declares variables, types, cursors, and local stored procedures and functions used in PL */
BEGIN
    /* Execution block: displays the execution process and SQL statements. It is the main part of the program. */
EXCEPTION
   /* Exception handling block: displays error handling */
END;

A PL block can be classified into one of the following three types:

• Anonymous block: dynamically constructed and executed only once.

• Program: stored procedures, functions, and packages stored in the database. After they are created in the database, they can be called by other programs.

• Trigger: automatically executes a program when an event is triggered in the database.

Example: a simple anonymous block that prints "Hello World" to the screen.

obclient> DECLARE
        V_Str varchar(20) := 'World';
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Hello '||V_Str);
    END;
    /
Query OK, 0 rows affected (0.01 sec)

Hello World

The default delimiter of a PL block is "/". You can use the delimiter command to customize the delimiter so that the entire definition can be passed to the server as a single statement. A delimiter can be a single character or multiple characters. Avoid using the backslash () character, which is generally used as an escape character. Here is an example:

DELIMITER $$
DELIMITER //