PL/SQL学习札记[5]-流程控制语句

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;

?

?