第四章 控制结构

一、控制结构的作用和类型

1.作用：在PL/SQL块内部使用控制结构来更改程序的逻辑

2.类型：条件IF语句、CASE语句、循环语句

①条件控制语句

类型：

·简单条件判断（IF-THEN）

语句：

IF condition THEN

   Statements

END IF;

实例：

SQL> DECLARE

  2    myage NUMBER :=10;

  3  BEGIN

  4    IF myage<11 THEN

  5      dbms_output.put_line('I am a child');

  6    END IF;

  7  END;

  8  /

I am a child

PL/SQL procedure successfully completed

·二重条件判断（IF-THEN-ELSE）

语法：

IF condition THEN

  Statements；

ELSE

  Statements；

END IF;

实例：

SQL> DECLARE

  2    myage NUMBER :=18;

  3  BEGIN

  4    IF myage<11 THEN

  5      dbms_output.put_line('I am a child');

  6    ELSE

  7      dbms_output.put_line('I am not a child');

  8    END IF;

  9  END;

 10  /

I am not a child

PL/SQL procedure successfully completed

·多重条件判断（IF-THEN-ELSIF）

语法：

IF condition THEN

   Statements;

ELSIF condition THEN

   Statements;

ELSE

   Statements;

END IF;

实例：

SQL> DECLARE

  2    myage NUMBER :=18;

  3  BEGIN

  4    IF myage<11 THEN

  5      dbms_output.put_line('I am a child');

  6    ELSIF myage<20 THEN

  7      dbms_output.put_line('I am not a child');

  8    ELSE

  9      dbms_output.put_line('I am always young');

 10    END IF;

 11  END;

 12  /

I am not a child

PL/SQL procedure successfully completed

 

二、CASE 语句

①作用： CASE表达式返回基于一个或多个备选项的结果

②语法：

CASE  selector

  WHEN exp1 THEN res1

  WHEN exp2 THEN res2

  WHEN exp3 THEN res3

……

  [ELSE resN]

END;

实例：

SQL> DECLARE

  2    v_mygrade CHAR(1) :='A';

  3    v_res  VARCHAR2(20);

  4  BEGIN

  5    v_res :=CASE v_mygrade

  6         WHEN 'A' THEN

  7           'The mark is 90-100'

  8         WHEN 'B' THEN

  9         'The mark is 80-90'

 10          WHEN 'C' THEN

 11            'The mark is 70-80'

 12            WHEN'D' THEN

 13            'The mark is 60-70'

 14            WHEN 'E' THEN

 15            'The mark is 0-60'

 16            END;

 17       dbms_output.put_line(v_res);

 18   END;

 19  /

The mark is 90-100

PL/SQL procedure successfully completed

 

三、NULL值处理

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

 

 

 

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

 

NOT
TRUE	FALSE
FALSE	TRUE
NULL	NULL

 

四、循环语句

·循环是指多次循环处理一条语句或一段逻辑

·循环原则：

①如果循环内部必须执行一次，则使用基本循环

②如果必须在每次循环开始时判断条件，则使用WHILE循环

③如果知道循环次数，则使用FOR循环

·循环类型：

—基本循环

语法：

LOOP

   Statement;

   ……

   EXIT [WHEN condition];

END LOOP

实例：

SQL> DECLARE

  2    i NUMBER :=0;

  3  BEGIN

  4    LOOP

  5      dbms_output.put_line(i);

  6      i :=i+1;

  7      EXIT WHEN i=10;

  8    END LOOP;

  9  END;

 10  /

0

1

2

3

4

5

6

7

8

9

PL/SQL procedure successfully completed

—FOR循环

语法：

FOR  counter IN [REVERSE] lower_bound..upper_bound

LOOP

Statement1;

Statement2;

…

END LOOP;

实例：

SQL> DECLARE

  2   v_count NUMBER;

  3  BEGIN

  4    FOR i IN 1..10

  5      LOOP

  6        INSERT INTO t1 VALUES(i,'tom'||i,'ok');

  7      END LOOP;

  8      COMMIT;

  9      SELECT COUNT(*) INTO v_count FROM t1;

 10      dbms_output.put_line('T1 rows is:' ||v_count);

 11   END;

 12  /

T1 rows is:10

PL/SQL procedure successfully completed

 

SQL> select * from t1;

                                     ID NAME DSC

--------------------------------------- ---------- --------------------

                                      2 tom2   ok

                                      1 tom1   ok

                                      3 tom3   ok

                                      4 tom4   ok

                                      5 tom5   ok

                                      6 tom6   ok

                                      7 tom7   ok

                                      8 tom8   ok

                                      9 tom9   ok

                                     10 tom10 ok

10 rows selected

 

—WHILE循环

语法：

WHILE condition LOOP

   Statement1;

   Statement2;

   ……

END LOOP;

实例：

SQL> DECLARE

  2    v_num NUMBER :=1;

  3    v_count NUMBER;

  4  BEGIN

  5    WHILE v_num <=10 LOOP

  6      INSERT INTO t1 VALUES(v_num,'tom'||v_num,'ok');

  7      v_num :=v_num+1;

  8     END LOOP;

  9     COMMIT;

 10     SELECT COUNT(*) INTO v_count FROM t1;

 11     dbms_output.put_line('T1 rows is :'||v_count);

 12   END;

 13  /

T1 rows is :10

PL/SQL procedure successfully completed

 

SQL> select * from t1;

                                     ID NAME DSC

--------------------------------------- ---------- --------------------

                                      1 tom1   ok

                                      2 tom2   ok

                                      3 tom3   ok

                                      4 tom4   ok

                                      5 tom5   ok

                                      6 tom6   ok

                                      7 tom7   ok

                                      8 tom8   ok

                                      9 tom9   ok

                                     10 tom10 ok

10 rows selected

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31393890/viewspace-2129127/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31393890/viewspace-2129127/