PL blocks

A PL program consists of three blocks: the declarative part defined by keyword DECLARE, the executable part defined by keyword BEGIN, and the exception-handling part defined by keyword EXCEPTION.

These three parts play different roles:

• Declarative part: declares the variables, types, cursors, and partial stored procedure and functions used in the PL program.

• Executable part: displays the execution procedure and SQL statements, which form the main part of the PL program.

• Exception-handling part: displays exception handling information.

A PL block has the following syntax:

<< label >> -- Label (optional)
DECLARE  
    /* Declarative part: declares the variables, types, cursors, and partial stored procedures and functions used in the PL program. */
BEGIN
    /* Executable part: consists of procedures and SQL statements. It is the main part of the program. */
EXCEPTION
   /* Exception-handling part: handles exceptions. */
END;

PL blocks can be categorized into the following three types:

• Anonymous blocks: Dynamic structures that can be executed only once.

• Programs: The stored procedures, functions, and packages stored in a database. After a program is created on a database, the program can be called by other programs.

• Triggers: When an operation is performed on the database, some events will be triggered to enable corresponding programs to run automatically.

Example: A simple anonymous block that prints "Hello World" onto a 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 PL blocks is "/". You can also use the delimiter command to define a delimiter so that the entire definition is transferred to the server as a single statement. A delimiter can consist of a single character or multiple characters. Do not use the backslash (\) as a delimiter because it is usually considered as an escape character. Here is an example:

DELIMITER $$
DELIMITER //