Description
PLSQL Block Structure is the most fundamental unit in PL/SQL. Having a solid understanding of the anonymous block will help to understand PL/SQL programming language easily. When a user want to remain anonymous, then the details regarding that user will be unavailable, all these are known as anonymous PL/SQL blocks. Functions and procedures are also known as the PL/SQL blocks and do not contain any valid name. Moreover, there is no header section in the anonymous block and uses Declare word in optional declaration section.
Syntax
Anonymous block without declaration and exception section
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
Syntax
Anonymous block inside a procedure
PROCEDURE get_happy IS
BEGIN
DECLARE
hiredate DATE;
BEGIN
....
END;
END get_happy;
Description
The PLSQL Block Structure of anonymous block consists of three sections - declaration, begin and end section. The declaration starts with the DECLARE keyword and ends at the BEGIN keyword. In this section, one should declare all the variables to be used within the block, and can declare variables that are based on simple datatypes, cursors and variables based on advanced data types. The declaration section contains a variable called I_counter and then closed with semicolon character.
This is with the exception of the DECLARE, BEGIN and EXCEPTION keywords. For the exception handler lines like when, other, then or here or for beginning constructs of conditional statements and loops, put a semicolon after the END keyword to mark the end of the anonymous block. Next, the execution section executes with the Begin keyword. This is the main body of the anonymous block and consists of executable lines of code like SQL statements, and numerical calculations. And the last section is the exception section or error handling section where error handling can be performed. The anonymous block ends with the END keyword. The declaration and exception section are optional, so the block starts with a BEGIN and ends with the END keyword.
Syntax
The syntax for PLSQL Block Structure of anonymous block is as follows:
[DECLARE
.....optional declaration statements .....]
BEGIN
.....executable statements.....
[EXCEPTION
.....optional exception handler statement.....]
END;
The square brackets describe the alternate part of the syntax. Moreover, the anonymous block itself is optional. BEGIN & END statements and one executable statements are required.
Examples
The below example describes the hello world program.
[c]
SQL> declare
2 message varchar2(20):='Hello,World!';
3 begin
4 dbms_output.put_line(message);
5 end;
6 /
Hello,world
PL/SQL procedure successfully completed.[/c]
Description
Nesting anonymous blocks is nothing but placing one anonymous block into the other anonymous block. In the codes, multiple number of nesting can be performed, but, typically doesn't need more than one nesting in most cases. This nesting anonymous blocks contains one outer block and one inner block. Each section contains individual declaration, execution and exceptions block. When some statements in the block may possibly raise an exception, but still continue to execute the rest of the statements and can wrap those SQL statements inside the inner block. And, it contains exception handler section, which handles the exceptions. After the inner block gets terminated, the execution statement resumes for the remaining statements in the outer block.
Syntax
DECLARE
I_outer NUMBER;
BEGIN
I_outer:=1;--Initialization of I_var
DECLARE
I_inner NUMBER;
BEGIN
I_inner:=2/0;
EXCEPTION
WHEN OTHERS THEN
--DO something
null;
END;
I_outer:=2;
EXCEPTION
WHEN OTHER THEN
--DO something
null;
END;
Description
A variable defined in the outer block has a scope in the outer section as well as all the inner blocks. And, I_outer is in scope, and can be accessed by the outer as well as the inner block. However, a variable declared in the inner block has a scope limited only to the inner block, and its sub-blocks are contained in the outer block. So, I_inner scope will be only within the inner block, and the outer block does not know about it, and cannot access it.
While defining a variable with the similar name in both the outer and inner block, the inner block variable will overwrite the visibility of the variable with the same name in the outer block. In this case, I_var is defined both in the outer and inner section. L_var defined in the outer section is visible in the outer section, but its visibility is overwritten by the variable with the similar name in the inner block. And, dbms_output.put_line for I_var in the inner block contains the value of two in the inner block. In the outer block, the I_var defined in the inner section is not within the scope and is not visible, hence prints the value as 1.
Syntax
Syntax for Scope of a variable.
DECLARE
I_outer NUMBER;
BEGIN
I_outer:=1;--Initialization of I_var
DECLARE
I_inner NUMBER;
BEGIN
I_outer:=2;
I_inner:=3;
END;
EXCEPTION
WHEN OTHERS THEN
--DO something
null;
END;
Syntax
Syntax for visibility of a variable.
DECLARE
I_outer NUMBER;
I_outer NUMBER;
BEGIN
I_outer:=1;
I_VAR:=1;
DECLARE
I_var NUMBER;
I_inner NUMBER;
BEGIN
I_var:=2;
I_inner:=2;
dbms_output.put_line('I_var in the inner block is'||I_var);
END;
dbms_output.put_line('I_var in the outer block is'||I_var);
EXCEPTION
WHEN OTHERS THEN
--DO something
null;
END;
Key Points
PLSQL Block Structure chapter draws out following main points.
- Anonymous block - Is the fundamental unit in PL/SQL.
- PLSQL Block Structure of anonymous block - Contains 3 sections- Declare, Begin and End.
- Nesting of anonymous block - Describes one anonymous block into another.
- Scope and visibility of a variable - Defines the scope and visibility in outer and inner blocks.