一、控制结构的作用和类型
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