в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:41Z

1 версия

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

== A nested block which declares and prints a variable of the same name==



<source lang="sql">
SQL>
SQL> set echo on
SQL> set serveroutput on
SQL> declare
2 state_name varchar2(30);
3
4 begin
5 state_name := "A";
6
7 declare
8 state_name varchar2(30);
9 begin
10 state_name := "B";
11 dbms_output.put_line(state_name);
12 end;
13
14 --Now print the state_name variable"s value in the outer block.
15 dbms_output.put_line (state_name);
16 end;
17 /
B
A
PL/SQL procedure successfully completed.
SQL>
SQL></source>

== Call function in PL/SQL==



<source lang="sql">
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> CREATE OR REPLACE function letterCounter(myString VARCHAR2)
2 RETURN NUMBER IS
3 v_current_position INTEGER := 1;
4 v_counter NUMBER := 0;
5 BEGIN
6 WHILE v_current_position <= LENGTH(myString) LOOP
7 IF SUBSTR(myString,v_current_position,1) != " " THEN
8 v_counter := v_counter + 1;
9 ELSE
10 NULL;
11 END IF;
12 v_current_position := v_current_position + 1;
13 END LOOP;
14 RETURN v_counter ;
15 END letterCounter;
16 /
Function created.
SQL>
SQL> DECLARE
2 v_MYTEXT VARCHAR2(20) := "THIS IS A TEST";
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("Total count is " || letterCounter(v_MYTEXT));
5 END;
6 /
Total count is 11
PL/SQL procedure successfully completed.
SQL>
SQL></source>

== Nested Blocks==



<source lang="sql">
SQL>
SQL> --PL/SQL blocks can be nested, one inside the other.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 error_flag BOOLEAN := false;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("We are going to count from 100 to 1000.");
5 DECLARE
6 hundreds_counter NUMBER(1,-2);
7 BEGIN
8 hundreds_counter := 100;
9 LOOP
10 DBMS_OUTPUT.PUT_LINE(hundreds_counter);
11 hundreds_counter := hundreds_counter + 100;
12 IF hundreds_counter > 1000 THEN
13 EXIT;
14 END IF;
15 END LOOP;
16 EXCEPTION
17 WHEN OTHERS THEN
18 error_flag := true;
19 END;
20 IF error_flag THEN
21 DBMS_OUTPUT.PUT_LINE("Sorry, I cannot count that high.");
22 ELSE
23 DBMS_OUTPUT.PUT_LINE("Done.");
24 END IF;
25 END;
26 /
We are going to count from 100 to 1000.
100
200
300
400
500
600
700
800
900
Sorry, I cannot count that high.
PL/SQL procedure successfully completed.
SQL></source>

== Nesting Functions and Procedures==



<source lang="sql">
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 temp NUMBER;
3
4 FUNCTION iifn(boolean_expression IN BOOLEAN,
5 true_number IN NUMBER,
6 false_number IN NUMBER)
7 RETURN NUMBER IS
8 BEGIN
9 IF boolean_expression THEN
10 RETURN true_number;
11 ELSIF NOT boolean_expression THEN
12 RETURN false_number;
13 ELSE
14 --nulls propagate, i.e. null input yields null output.
15 RETURN NULL;
16 END IF;
17 END;
18 BEGIN
19 DBMS_OUTPUT.PUT_LINE(iifn(2 > 1,1,0));
20 DBMS_OUTPUT.PUT_LINE(iifn(2 > 3,1,0));
21
22 temp := iifn(null,1,0);
23 IF temp IS NULL THEN
24 DBMS_OUTPUT.PUT_LINE("NULL");
25 ELSE
26 DBMS_OUTPUT.PUT_LINE(temp);
27 END IF;
28 END;
29 /
1
0
NULL
PL/SQL procedure successfully completed.
SQL>
SQL></source>

== This is an example of nested anonymous blocks==



<source lang="sql">
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 error_flag BOOLEAN := false;
3
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE("We are going to count from 100 to 1000.");
6
7 DECLARE
8 hundreds_counter NUMBER(1,-2);
9 BEGIN
10 hundreds_counter := 100;
11 LOOP
12 DBMS_OUTPUT.PUT_LINE(hundreds_counter);
13 hundreds_counter := hundreds_counter + 100;
14 IF hundreds_counter > 1000 THEN
15 EXIT;
16 END IF;
17 END LOOP;
18 EXCEPTION
19 WHEN OTHERS THEN
20 error_flag := true;
21 END;
22
23 IF error_flag THEN
24 DBMS_OUTPUT.PUT_LINE("Sorry, I cannot count that high.");
25 ELSE
26 DBMS_OUTPUT.PUT_LINE("Done.");
27 END IF;
28 END;
29 /
We are going to count from 100 to 1000.
100
200
300
400
500
600
700
800
900
Sorry, I cannot count that high.
PL/SQL procedure successfully completed.
SQL>
SQL></source>

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

Oracle PL/SQL Tutorial/PL SQL Statements/Nested Block - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия