Anonymous block statement (PL/SQL)

The PL/SQL anonymous block statement is an executable statement that can contain PL/SQL control statements and SQL statements. It can be used to implement procedural logic in a scripting language. In PL/SQL contexts, this statement can be compiled and executed by the Db2® data server.

The anonymous block statement, which does not persist in the database, can consist of up to three sections: an optional declaration section, a mandatory executable section, and an optional exception section.

The optional declaration section, which can contain the declaration of variables, cursors, and types that are to be used by statements within the executable and exception sections, is inserted before the executable BEGIN-END block.

The optional exception section can be inserted near the end of the BEGIN-END block. The exception section must begin with the keyword EXCEPTION, and continues until the end of the block in which it appears.

Invocation

This statement can be executed from an interactive tool or command line interface such as the CLP. This statement can also be embedded within a PL/SQL procedure definition, function definition, or trigger definition. Within these contexts, the statement is called a block structure instead of an anonymous block statement.

Authorization

No privileges are required to invoke an anonymous block. However, the privileges held by the authorization ID of the statement must include all necessary privileges to invoke the SQL statements that are embedded within the anonymous block.

Syntax

Description

DECLARE: An optional keyword that starts the DECLARE statement, which can be used to declare data types, variables, or cursors. The use of this keyword depends upon the context in which the block appears.
declaration: Specifies a data type, variable, cursor, exception, or procedure declaration whose scope is local to the block. Each declaration must be terminated by a semicolon.
BEGIN: A mandatory keyword that introduces the executable section, which can include one or more SQL or PL/SQL statements. A BEGIN-END block can contain nested BEGIN-END blocks.
statement: Specifies a PL/SQL or SQL statement. Each statement must be terminated by a semicolon.
EXCEPTION: An optional keyword that introduces the exception section.
WHEN exception-condition: Specifies a conditional expression that tests for one or more types of exceptions.
THEN handler-statement: Specifies a PL/SQL or SQL statement that is executed if a thrown exception matches an exception in exception-condition. Each statement must be terminated by a semicolon.
END: A mandatory keyword that ends the block.

Examples

The following example shows the simplest possible anonymous block statement that the Db2 data server can compile:

BEGIN
    NULL;
END;

The following example shows an anonymous block that you can enter interactively through theDb2 CLP:

SET SERVEROUTPUT ON;

BEGIN
  dbms_output.put_line( 'Hello' );
END;

The following example shows an anonymous block with a declaration section that you can enter interactively through the Db2 CLP:

SET SERVEROUTPUT ON;

DECLARE
   current_date DATE := SYSDATE;
BEGIN
     dbms_output.put_line( current_date );
END;