pl/sql-03

Using nested blocks

A nested block can be nested in a PL/SQL block, which can be placed either in the execution part of the external block or in the exception handling section, but not in the Outer block Declaration section
Internally nested blocks can access variables declared by an outer nested block, but external declaration blocks cannot access variables in the inner declaration block

Declare
V_deptno Number (2): = 50;
V_dname VARCHAR2 (12);

Begin
Begin
Select Dname into V_dname from scott.dept where deptno = V_deptno;
Dbms_output.put_line (' Hello Search Department is: ' | | v_dname ');
End;
Declare
V_loc VARCHAR2 (10): = ' Shenzhen Nanshan ';
Begin
Update SCOTT. DEPT Set loc = v_loc where deptno = V_deptno;
Dbms_output.put_line (' Inside the block successfully updated the Department data! ‘);
End;
Exception when No_data_found
Then BEGIN
INSERT into scott.dept values (V_DEPTNO, ' Information department ', ' Shenzhen ');
Dbms_output.put_line (' Successfully recovers data in exception handling module! ‘);
Exception when others
Then
Dbms_output.put_line (' Unknown exception! ‘);
End
End

This procedure has a total of three begin--end structures.
The first set of begin-end structures is to guide the whole program block, so it is the main structure;
The second one is for querying department information
Begin
Select Dname into V_dname from scott.dept where deptno = V_deptno;
Dbms_output.put_line (' Hello Search Department is: ' | | v_dname ');
End;
In fact, this begin-end structure can be removed without affecting program execution

The third is to update the department information, the complete code is as follows

Declare
V_loc VARCHAR2 (10): = ' Shenzhen Nanshan ';
Begin
Update SCOTT. DEPT Set loc = v_loc where deptno = V_deptno;
Dbms_output.put_line (' Inside the block successfully updated the Department data! ‘);
End;

The begin-end structure here is not to be removed, because variable definitions are made before subsequent statements are executed.
namely: Declare
V_loc VARCHAR2 (10): = ' Shenzhen Nanshan ';
Variables cannot be redefined in a single begin-end structure, unless the variable is determined to be not part of this begin-end structure at this time, so begin-end cannot be deleted here.

Attached: Using named nesting blocks

<< External Blocks >>
Declare
V_deptno Number (2): = 50;
V_dname VARCHAR2 (12);

Begin
<< Query Employee name Block >>
Begin
Select Dname into V_dname from scott.dept where deptno = V_deptno;
Dbms_output.put_line (' Hello Search Department is: ' | | v_dname ');
End;
<< Update Employee Department blocks >>
Declare
V_loc VARCHAR2 (10): = ' Shenzhen Nanshan ';
Begin
Update SCOTT. DEPT Set loc = v_loc where deptno = V_deptno;
Dbms_output.put_line (' Inside the block successfully updated the Department data! ‘);
End;
Exception when No_data_found
Then BEGIN
INSERT into scott.dept values (V_DEPTNO, ' Information department ', ' Shenzhen ');
Dbms_output.put_line (' Successfully recovers data in exception handling module! ‘);
Exception when others
Then
Dbms_output.put_line (' Unknown exception! ‘);
End
End

pl/sql-03