The scope of an identifier is the region of a PL unit from which you can reference the identifier. The visibility of an identifier is the region of a PL unit from which you can reference the identifier without qualifying the identifier.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
An identifier is local to the PL unit that declares it. If this PL unit contains sub-units, the identifier is a global identifier for the sub-units.
If a sub-unit declares another global identifier, the two identifiers are both in the sub-unit. However, only the local identifier is visible. To reference the global identifier, the sub-unit must use the name of the unit that declares the global identifier to qualify the global identifier. If the unit is not named, the sub-unit cannot reference the global identifier.
A PL unit cannot reference identifiers that are declared by other PL units at the same level, because these identifiers are not local or global to the block.
An identifier cannot be declared twice in the same PL unit. Otherwise, an error will occur when you reference duplicate identifiers.
You can declare an identifier in two different PL units. The identifier represents two different objects in the units. Modifying one of the objects does not affect the other one.
You need to specify unique names for labels and subprograms in the same scope to avoid confusion and exceptions.
-- External block
obclient> DECLARE
x CHAR; -- Scope of x (CHAR) starts
y FLOAT; -- Scope of y starts
BEGIN
-- x (CHAR) and y are visible
-- First sub-block:
DECLARE
x INTEGER; -- Scope of x (INTEGER) starts
z FLOAT; -- Scope of z starts
BEGIN
-- x (INTEGER), y, and z are visible
NULL;
END; -- Scopes of x (INTEGER) and z end
-- Second sub-block
DECLARE
w FLOAT; -- Scope of w starts
BEGIN
-- x (CHAR), y, and w are visible
NULL;
END; -- Scope of w ends
-- x (CHAR) and y are visible
END; -- Scopes of x (CHAR) and y end
/
Query OK, 0 rows affected
The preceding example shows the scopes and visibility of multiple identifiers. The first sub-block redeclares the global identifier x. To reference the global variable y, the first sub-block must use the name of the external block to qualify the identifier. However, the external block does not have name. Therefore, the first sub-block cannot reference the global variable x, and can reference only its local variable x. Sub-blocks are at the same level. Therefore the first sub-block cannot reference w, and the second sub-block cannot reference z.