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 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
BEGIN
NULL;
END;
SET SERVEROUTPUT ON;
BEGIN
dbms_output.put_line( 'Hello' );
END;
SET SERVEROUTPUT ON;
DECLARE
current_date DATE := SYSDATE;
BEGIN
dbms_output.put_line( current_date );
END;