PL blocks

A PL block consists of three parts: the declaration part (specified by the DECLARE keyword), the execution part (specified by the BEGIN keyword), and the exception handling part (specified by the EXCEPTION keyword).

The three parts are described as follows:

• Declaration part: You can declare variables, types, cursors, and local stored procedures and functions in this part.

• Execution part: You can write the execution process and SQL statements in this part. This is the main part of the program.

• Exception handling part: You can write error handling statements in this part.

The syntax of a PL block is as follows:

<< label >> -- optional
DECLARE
    /* Declaration part: You can declare variables, types, cursors, and local stored procedures and functions in this part. */
BEGIN
    /* Execution part: You can write the execution process and SQL statements in this part. This is the main part of the program. */
EXCEPTION
   /* Exception handling part: You can write error handling statements in this part. */
END;

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

• Anonymous block: It is dynamically constructed and can be executed only once.

• Program: It is stored in the database as a stored procedure, function, or package. After it is created in the database, it can be called by other programs.

• Trigger: It is automatically triggered when a specified operation is performed on the database. Then, the corresponding program is executed.

Example: The following anonymous block prints "Hello World" on 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 block 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 //