1 条件分支语句
1.1 IF语句
语法
?写道
IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;
?statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;
?
示例
?
DECLARE v_age NUMBER(3);BEGIN SELECT age INTO v_age FROM user WHERE lower(name) = lower('&&name'); IF v_age < 20 THEN UPDATE user SET age = v_age*2 WHERE lower(name) = lower('&&name'); ELSIF v_age < 40 THEN UPDATE user SET age = v_age*3 WHERE lower(name) = lower('&&name'); ELSE UPDATE user SET age = v_age/2 WHERE lower(name) = lower('&&name'); END IF;END;??
1.2 CASE语句
语法
?
?写道
[单一条件]
CASE selector
WHEN expression1 THEN sequence_of_statements1;
WHEN expression1 THEN sequence_of_statements2;
...
WHEN expression1 THEN sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE;
[多条件]
CASE
WHEN selector_condition1 THEN sequence_of_statements1;
WHEN selector_condition2 THEN sequence_of_statements2;
...
WHEN selector_conditionN THEN sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE;
?CASE selector
WHEN expression1 THEN sequence_of_statements1;
WHEN expression1 THEN sequence_of_statements2;
...
WHEN expression1 THEN sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE;
[多条件]
CASE
WHEN selector_condition1 THEN sequence_of_statements1;
WHEN selector_condition2 THEN sequence_of_statements2;
...
WHEN selector_conditionN THEN sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE;
?
示例
?
DECLARE v_uid user.uid%TYPE;BEGIN v_uid := &id; CASE v_uid WHEN 1 THEN UPDATE user SET age = 20 WHERE uid = v_uid; WHEN 2 THEN UPDATE user SET age = 40 WHERE uid = v_uid; ELSE dbms_out.put_line('不存在该用户'); END CASE;END;DECLARE v_uid user.uid%TYPE;BEGIN v_uid := &id; CASE WHEN v_uid == 1 THEN UPDATE user SET age = 20 WHERE uid = v_uid; WHEN v_uid == 2 THEN UPDATE user SET age = 40 WHERE uid = v_uid; ELSE dbms_out.put_line('不存在该用户'); END CASE;END;??
2 循环语句
2.1 LOOP 循环
语法
?
?写道
LOOP
statement1;
EXIT [WHEN condition];
END LOOP;
statement1;
EXIT [WHEN condition];
END LOOP;
使用该语句statement1至少会被执行一次。相当于do...while
?
示例
?
DELCARE i INT :=1;BEGIN LOOP UPDATE user SET createDate = SYSDATE WHERE uid = i; EXIT WHEN i = 10 ; i := i+1; END LOOP; COMMIT;END;
?
2.2?WHILE 循环
语法
?
?写道
WHILE condition LOOP
statement1;
statement2;
...
END LOOP;
?statement1;
statement2;
...
END LOOP;
?
示例
?
DECLARE i INT := 1;BEGIN WHILE i <= 10 LOOP UPDATE user SET createDate = SYSDATE WHERE uid = i; i := i+1; END LOOP; COMMIT; END;?
?
?
2.3 FOR 循环
语法
?
?写道
FOR counter IN [REVERSE]
min_bound..upper_bound LOOP
statement1;
statement1;
...
END LOOP;
min_bound..upper_bound LOOP
statement1;
statement1;
...
END LOOP;
?
counter : 循环控制变量，由Oracle隐含定义，不需要显示定义
min_bound、upper_bound ：循环控制变量的上下界；
默认情况下FOR循环在每执行一次后，控制变量会自增一；如果指定REVERSE选项，则会减一
?
示例
?
BEGIN FOR i IN 1..10 LOOP UPDATE user SET createDate = SYSDATE WHERE uid = i; END LOOP; COMMIT;END;?
?
2.4 嵌套循环和标号
?
-- 该示例中的<<waibu>>、<<neibu>>为标号，该名称可以自定义。DECLARE result INT;BEGIN <<waibu>> FOR i IN 1..10 LOOP <<neibu>> FOR j IN 1..10 LOOP result := i * j; dbms_output.put_line(result); EXIT waibu WHEN result = 10; -- 当 result=10 时，退出外部循环，本例中是当i=5时退出。相当于调用break语句 EXIT WHEN j = 2; -- 当 j = 2 时，退出内部循环。相当于调用break语句 END LOOP neibu; END LOOP waibu; dbms_output.put_line(result);END;?
?
3 顺序控制语句
3.1 GOTO 语句
语法
?
?写道
GOTO label_name;
?
?GOTO语句用于跳转到特定标号处。一般不建议使用
?
示例
?
DECLARE i INT := 1; BEGIN LOOP IF i = 10 THEN GOTO jump_loop; END IF; EXIT WHEN i > 11; dbms_output.put_line('i-->'||i); -- 这条语句是不会被执行的！ i := i+1; END LOOP; <<jump_loop>> dbms_output.put_line('i == 10 ! ');END;??
3.2 NULL 语句
NULL 语句不执行任何操作，并且直接将控制传递到下一条语句。使用NULL可以提高PL/SQL程序的可读性
?
DECLARE v_uid user.uid%TYPE := &di; v_age user.age%TYPE;BEGIN SELECT age INTO v_age FROM user WHERE uid = v_uid; IF v_age < 20 THEN UPDATE user SET age = 100 WHERE uid = v_uid; commit; ELSE NULL; END IF;END;?
?