Using PL/SQL Control Structures
以下为控制结构需要注意的:
在条件判断比较多的情况下,推荐使用CASE语句代替IF/ELSE控制
The value of a Boolean expression can be assigned directly to a Boolean variable. You
can replace the first IF statement with a simple assignment:
overdrawn := new_balance < minimum_balance;
case语句:
The ELSE clause is
optional. However, if you omit the ELSE clause, PL/SQL adds the following implicit
ELSE clause:
ELSE RAISE CASE_NOT_FOUND;
There is always a default action, even when you omit the ELSE clause. If the CASE
statement does not match any of the WHEN clauses and you omit the ELSE clause,
PL/SQL raises the predefined exception CASE_NOT_FOUND.
如果忽略了ELSE语句,系统会默认的添加一条ESLE语句,当找不到匹配的条件,就会报出异常信息。
case语句的两种结构:
1 [<<label_name>>]
2 CASE selector
3 WHEN expression1 THEN sequence_of_statements1;
4 WHEN expression2 THEN sequence_of_statements2;
5 ...
6 WHEN expressionN THEN sequence_of_statementsN;
7 [ELSE sequence_of_statementsN+1;]
8 Testing Conditions: IF and CASE Statements
9 END CASE [label_name]
搜索结构的CASE语句:
1 [<<label_name>>]
2 CASE
3 WHEN search_condition1 THEN sequence_of_statements1;
4 WHEN search_condition2 THEN sequence_of_statements2;
5 ...
6 WHEN search_conditionN THEN sequence_of_statementsN;
7 [ELSE sequence_of_statementsN+1;]
8 END CASE [label_name];
LOOP
在循环中有两种类型的EXIT可以使用
1、EXIT
1 LOOP
2 IF credit_rating < 3 THEN
3 EXIT; -- exit loop immediately
4 END IF;
5 END LOOP;
2、EXIT WHEN
1 LOOP
2 FETCH c1 INTO ...
3 EXIT WHEN c1%NOTFOUND; -- exit loop if condition is true
4 ...
5 END LOOP;
6 CLOSE c1;
FOR LOOP循环注意事项:
The bounds of a loop range can be literals, variables, or expressions but must evaluate
to numbers. Otherwise, PL/SQL raises the predefined exception VALUE_ERROR.
循环的范围可以是文本,变量或者表达式,但是他必须可以证明为数字类型,否则PL/SQL将会抛出值错误的异常信息
当需要进行FOR LOOP的逆向循环的时候,需要使用关键字 REVERSE.
1 BEGIN
2 FOR i IN REVERSE 1..3 LOOP
3 dbms_output.put_line(i);
4 END LOOP;
5 END;
输出的结果为3、2、1