PL/SQL学习笔记[5]-流程控制语句
1 条件分支语句
1.1 IF语句
语法
DECLAREv_age NUMBER(3);BEGINSELECT age INTO v_age FROM userWHERE lower(name) = lower('&&name');IF v_age < 20 THENUPDATE 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');ELSEUPDATE user SET age = v_age/2 WHERE lower(name) = lower('&&name');END IF;END;??
1.2 CASE语句
语法
?
DECLAREv_uid user.uid%TYPE;BEGINv_uid := &id;CASE v_uidWHEN 1 THEN UPDATE user SET age = 20 WHERE uid = v_uid;WHEN 2 THEN UPDATE user SET age = 40 WHERE uid = v_uid;ELSEdbms_out.put_line('不存在该用户');END CASE;END;DECLAREv_uid user.uid%TYPE;BEGINv_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;ELSEdbms_out.put_line('不存在该用户');END CASE;END;??
2 循环语句2.1 LOOP 循环
语法
?
DELCAREi INT :=1;BEGINLOOPUPDATE user SET createDate = SYSDATE WHERE uid = i;EXIT WHEN i = 10 ;i := i+1;END LOOP;COMMIT;END;?
2.2?WHILE 循环
语法
?
DECLARE i INT := 1;BEGINWHILE i <= 10 LOOPUPDATE user SET createDate = SYSDATE WHERE uid = i;i := i+1;END LOOP;COMMIT;END;??
?
2.3 FOR 循环
语法
?
BEGINFOR i IN 1..10 LOOPUPDATE user SET createDate = SYSDATE WHERE uid = i;END LOOP;COMMIT;END;??
2.4 嵌套循环和标号
?
-- 该示例中的<<waibu>>、<<neibu>>为标号，该名称可以自定义。DECLAREresult INT;BEGIN<<waibu>>FOR i IN 1..10 LOOP<<neibu>>FOR j IN 1..10 LOOPresult := 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 语句
语法
?
DECLAREi INT := 1; BEGIN LOOPIF i = 10 THENGOTO 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程序的可读性
?
DECLAREv_uid user.uid%TYPE := &di;v_age user.age%TYPE;BEGINSELECT age INTO v_age FROM user WHERE uid = v_uid;IF v_age < 20 THENUPDATE user SET age = 100 WHERE uid = v_uid;commit;ELSENULL;END IF;END;?
?