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;