в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:59:24Z

1 версия

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

==A labeled block.==



<source lang="sql">

SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> <<l_InsertIntoTemp>>
2 DECLARE
3 v_Num1 NUMBER := 1;
4 v_Num2 NUMBER := 2;
5 v_String1 VARCHAR2(50) := "Hello World!";
6 v_String2 VARCHAR2(50) := "-- This message brought to you by PL/SQL!";
7 v_OutputStr VARCHAR2(50);
8 BEGIN
9 INSERT INTO MyTable (num_col, char_col)
10 VALUES (v_Num1, v_String1);
11 INSERT INTO MyTable (num_col, char_col)
12 VALUES (v_Num2, v_String2);
13
14 SELECT char_col
15 INTO v_OutputStr
16 FROM MyTable
17 WHERE num_col = v_Num1;
18 DBMS_OUTPUT.PUT_LINE(v_OutputStr);
19
20 SELECT char_col
21 INTO v_OutputStr
22 FROM MyTable
23 WHERE num_col = v_Num2;
24 DBMS_OUTPUT.PUT_LINE(v_OutputStr);
25
26 ROLLBACK;
27
28 END l_InsertIntoTemp;
29 /
Hello World!
-- This message brought to you by PL/SQL!
PL/SQL procedure successfully completed.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>

</source>

==Block with label==



<source lang="sql">

SQL> <<l_outer_block>>
2 DECLARE
3 visibleValue VARCHAR2(30);
4 hiddenValue VARCHAR2(30);
5 BEGIN
6 visibleValue := "visibleValue";
7 hiddenValue := "hiddenValue";
8
9 DBMS_OUTPUT.PUT_LINE("OUTER BLOCK");
10 DBMS_OUTPUT.PUT_LINE(visibleValue);
11 DBMS_OUTPUT.PUT_LINE(hiddenValue);
12
13 DECLARE
14 hiddenValue NUMBER(10);
15 BEGIN
16 DBMS_OUTPUT.PUT_LINE("INNER BLOCK");
17 l_outer_block.hiddenValue := "inner hiddenValue";
18 DBMS_OUTPUT.PUT_LINE(l_outer_block.hiddenValue);
19 EXCEPTION
20 WHEN OTHERS
21 THEN
22 DBMS_OUTPUT.PUT_LINE("hiddenValue of type VARCHAR2 was...hidden");
23 END;
24 END;
25 /
OUTER BLOCK
visibleValue
hiddenValue
INNER BLOCK
inner hiddenValue
PL/SQL procedure successfully completed.
SQL>

</source>

==Changing labeled loop execution with EXIT statements==



<source lang="sql">

SQL>
SQL> -- Changing labeled loop execution with EXIT statements.
SQL> BEGIN
2 <<outerloop>>
3 FOR i IN 1..2 LOOP
4 <<innerloop>>
5 FOR j IN 1..4 LOOP
6 DBMS_OUTPUT.PUT_LINE("Outer Loop counter is " || i || " Inner Loop counter is " || j);
7 EXIT outerloop WHEN j = 3;
8 END LOOP innerloop;
9 END LOOP outerloop;
10 END;
11 /
Outer Loop counter is 1 Inner Loop counter is 1
Outer Loop counter is 1 Inner Loop counter is 2
Outer Loop counter is 1 Inner Loop counter is 3
PL/SQL procedure successfully completed.
SQL>

</source>

==Loop with a Label==



<source lang="sql">

SQL> BEGIN
2 <<l_For_Loop>>
3 FOR v_count IN 1 .. 20
4 LOOP
5 DBMS_OUTPUT.PUT_LINE("Iteration: "||v_count);
6 END LOOP l_For_Loop;
7 END;
8 /
Iteration: 1
Iteration: 2
Iteration: 3
Iteration: 4
Iteration: 5
Iteration: 6
Iteration: 7
Iteration: 8
Iteration: 9
Iteration: 10
Iteration: 11
Iteration: 12
Iteration: 13
Iteration: 14
Iteration: 15
Iteration: 16
Iteration: 17
Iteration: 18
Iteration: 19
Iteration: 20
PL/SQL procedure successfully completed.
SQL>

</source>

==Use label to mark outer loop and inner loop==



<source lang="sql">

SQL>
SQL> BEGIN
2 <<outerloop>>
3 FOR v_outerloopcounter IN 1..2 LOOP
4 <<innerloop>>
5 FOR v_innerloopcounter IN 1..4 LOOP
6 DBMS_OUTPUT.PUT_LINE("Outer Loop counter is " ||
7 v_outerloopcounter ||
8 " Inner Loop counter is " || v_innerloopcounter);
9 END LOOP innerloop;
10 END LOOP outerloop;
11 END;
12 /
Outer Loop counter is 1 Inner Loop counter is 1
Outer Loop counter is 1 Inner Loop counter is 2
Outer Loop counter is 1 Inner Loop counter is 3
Outer Loop counter is 1 Inner Loop counter is 4
Outer Loop counter is 2 Inner Loop counter is 1
Outer Loop counter is 2 Inner Loop counter is 2
Outer Loop counter is 2 Inner Loop counter is 3
Outer Loop counter is 2 Inner Loop counter is 4
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> --

</source>

==Using labels with loops==



<source lang="sql">

SQL>
SQL> -- Using labels with loops.
SQL> BEGIN
2 <<outerloop>>
3 FOR i IN 1..2 LOOP
4 <<innerloop>>
5 FOR j IN 1..4 LOOP
6 DBMS_OUTPUT.PUT_LINE("Outer Loop counter is " || i ||
7 " Inner Loop counter is " || j);
8 END LOOP innerloop;
9 END LOOP outerloop;
10 END;
11 /
Outer Loop counter is 1 Inner Loop counter is 1
Outer Loop counter is 1 Inner Loop counter is 2
Outer Loop counter is 1 Inner Loop counter is 3
Outer Loop counter is 1 Inner Loop counter is 4
Outer Loop counter is 2 Inner Loop counter is 1
Outer Loop counter is 2 Inner Loop counter is 2
Outer Loop counter is 2 Inner Loop counter is 3
Outer Loop counter is 2 Inner Loop counter is 4
PL/SQL procedure successfully completed.
SQL>
SQL>

</source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия