в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:59:39Z

1 версия

Новая страница

==Identifiers defined in the declaration of the child block are only in scope and visible within the child block itself.==



<source lang="sql">

SQL>
SQL>
SQL> declare
2 l_parent_number number;
3 begin
4 -- l_parent_number is visible and in scope
5 l_parent_number := 1;
6
7 declare
8 l_child_number number := 2;
9 begin
10 -- l_child_number is visible and in scope
11 dbms_output.put_line("parent + child = " ||
12 to_char(l_parent_number + l_child_number));
13 end;
14
15 -- l_child_number is now not visible nor in scope:
16 l_child_number := 2;
17 end;
18 /
l_child_number := 2;
*
ERROR at line 16:
ORA-06550: line 16, column 3:
PLS-00201: identifier "L_CHILD_NUMBER" must be declared
ORA-06550: line 16, column 3:
PL/SQL: Statement ignored

SQL>
SQL>

</source>

==if you try to reference a variable before you declare it because PL/SQL requires an identifier be declared before we use it in our code==



<source lang="sql">

SQL>
SQL>
SQL> declare
2 myNumber number := another_number;
3 another_number number := 10;
4 begin
5 null;
6 end;
7 /
myNumber number := another_number;
*
ERROR at line 2:
ORA-06550: line 2, column 22:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 2, column 12:
PL/SQL: Item ignored

SQL>
SQL>
SQL> declare
2 another_number number := 10;
3 myNumber number := another_number;
4 begin
5 null;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL>

</source>

==Illustrates the scope of various identifiers==



<source lang="sql">

SQL>
SQL> --Illustrates the scope of various identifiers.
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 a_name VARCHAR2(30) := "A Name";
3
4 PROCEDURE name_print IS
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE(a_name);
7 END;
8
9
10 BEGIN
11 DBMS_OUTPUT.PUT_LINE(a_name);
12
13 DECLARE
14 b_name VARCHAR2(30) := "a Name";
15 BEGIN
16 DBMS_OUTPUT.PUT_LINE("Inside nested block");
17 DBMS_OUTPUT.PUT_LINE(a_name);
18 DBMS_OUTPUT.PUT_LINE(b_name);
19 name_print;
20 END;
21
22 DBMS_OUTPUT.PUT_LINE("Back in the main block");
23 --But we cannot compile the following line because b_name
24 --is not defined in this block.
25 --DBMS_OUTPUT.PUT_LINE(b_name);
26 name_print;
27 END;
28
29
30 /
A Name
Inside nested block
A Name
a Name
A Name
Back in the main block
A Name
PL/SQL procedure successfully completed.
SQL>

</source>

==Override your scope access to containing blocks by reusing an identifier in a nested block==



<source lang="sql">

SQL>
SQL> DECLARE
2 current_block VARCHAR2(10) := "Outer";
3 outer_block VARCHAR2(10) := "Outer";
4 BEGIN
5 dbms_output.put_line("[current_block]["||current_block||"]");
6 DECLARE
7
8 current_block VARCHAR2(10) := "Inner";
9 BEGIN
10 dbms_output.put_line("[current_block]["||current_block||"]");
11 dbms_output.put_line("[outer_block]["||outer_block||"]");
12 END;
13 dbms_output.put_line("[current_block]["||current_block||"]");
14 END;
15 /
[current_block][Outer]
[current_block][Inner]
[outer_block][Outer]
[current_block][Outer]
PL/SQL procedure successfully completed.
SQL>

</source>

==Reference Type in another block as well==



<source lang="sql">

SQL>
SQL> CREATE OR REPLACE TYPE NameList AS
2 VARRAY(20) OF VARCHAR2(30);
3 /
Type created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 v_Names2 NameList;
3 BEGIN
4 NULL;
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL>

</source>

==This type is local to this block==



<source lang="sql">

SQL>
SQL>
SQL> CREATE OR REPLACE TYPE NameList AS
2 VARRAY(20) OF VARCHAR2(30);
3 /
Type created.
SQL>
SQL>
SQL> DECLARE
2
3 TYPE DateList IS VARRAY(10) OF DATE;
4
5 v_Dates DateList;
6 v_Names NameList;
7 BEGIN
8 NULL;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>

</source>

==Variable scope in a PL/SQL==



<source lang="sql">

SQL>
SQL>
SQL> declare -- begin first block
2 l_text varchar2(20);
3 begin
4 l_text := "First Block";
5 dbms_output.put_line(l_text);
6 declare -- begin second block
7 l_more_text varchar2(20);
8 begin
9 l_more_text := "Second Block";
10 dbms_output.put_line(l_more_text);
11 end; -- end second block
12 end; -- end first block
13 /
First Block
Second Block
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

</source>

==Variable scope in variable "declare" block==



<source lang="sql">

SQL>
SQL>
SQL> -- variable scope
SQL>
SQL> declare
2 l_number number := l_another_number;
3 l_another_number number := 10;
4 begin
5 null;
6 end;
7 /
l_number number := l_another_number;
*
ERROR at line 2:
ORA-06550: line 2, column 24:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 2, column 14:
PL/SQL: Item ignored

</source>

==variable scope with nested block==



<source lang="sql">

SQL>
SQL> declare
2 l_parent_number number;
3 begin
4
5 l_parent_number := 1;
6
7 declare
8 l_child_number number := 2;
9 begin
10
11 dbms_output.put_line("parent + child = " ||
12 to_char(l_parent_number + l_child_number));
13 end;
14
15
16 l_child_number := 2;
17 end;
18 /
l_child_number := 2;
*
ERROR at line 16:
ORA-06550: line 16, column 5:
PLS-00201: identifier "L_CHILD_NUMBER" must be declared
ORA-06550: line 16, column 5:
PL/SQL: Statement ignored

SQL>

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/PL SQL/Variable Scope - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия