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: Declare variables, types, cursors, and local stored procedures and functions used in PL.

• Execution block: Display the execution process and SQL statements, which are the main part of the program.

• Exception handling block: Display error handling.

The syntax of a PL block is as follows:

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

PL blocks can be divided into three types:

• Anonymous blocks: Dynamically constructed and executed only once.

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

• Triggers: Events are triggered when operations are performed on the database, and the corresponding programs are automatically executed.

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. The 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 //