# 编写控制结构

IF condition THEN

statements;

[ELSIF condition THEN

statements;]

[ELSE

statements;]

END IF;

(1)    简单条件判断

DECLARE

v_sal NUMBER(6,2);

BEGIN

SELECT sal INTO v_sal FROM emp

WHERE LOWER(ename) = LOWER('&&name');

IF v_sal < 2000 THEN

UPDATE emp SET sal = v_sal + 200

WHERE LOWER(ename) = LOWER('&name');

END IF;

END;

PL/SQL

(2)    二重条件分支

DECLARE

v_comm NUMBER(6,2);

BEGIN

SELECT comm INTO v_comm FROM emp

WHERE empno = &&no;

IF v_comm <> 0 THEN

UPDATE emp SET comm = v_comm + 100

WHERE empno = &no;

ELSE

UPDATE emp SET comm = 200

WHERE empno = &no;

END IF;

END;

PL/SQL

(3)    多重条件分支

undefine no

DECLARE

v_job VARCHAR2(10);

v_sal NUMBER(6,2);

BEGIN

SELECT job,sal INTO v_job,v_sal

FROM emp WHERE empno = &&no;

IF v_job = 'PRESIDENT' THEN

UPDATE emp SET sal = v_sal + 1000 WHERE empno = &no;

ELSIF v_job = 'MANAGER' THEN

UPDATE emp SET sal = v_sal + 500 WHERE empno = &no;

ELSE

UPDATE emp SET sal = v_sal + 200 WHERE empno = &no;

END IF;

END;

PL/SQL

(1)    CASE语句中使用单一选择符进行等值比较

CASE selector

WHEN expression1 THEN sequence_of_statements1;

WHEN expression2 THEN sequence_of_statements2;

WHEN expression_of_statementsN;

[ELSE sequence_of_statementsN+1;]

END CASE;

l         selector：用于指定条件选择符

l         expression：用于指定条件值的表达式

l         sequence_of_statement：用于指定要执行的条件操作

DECLARE

v_deptno emp.deptno%TYPE;

BEGIN

v_deptno := &no;

CASE v_deptno

WHEN 10 THEN

UPDATE emp SET comm = 100 WHERE deptno = v_deptno;

WHEN 20 THEN

UPDATE emp SET comm = 80 WHERE deptno = v_deptno;

WHEN 30 THEN

UPDATE emp SET comm = 50 WHERE deptno = v_deptno;

ELSE

dbms_output.put_line('不存在该部门');

END CASE;

END;

PL/SQL

(2)    CASE语句中使用多种条件比较

CASE

WHEN search_condition1 THEN sequence_of_statements1;

WHEN search_condition2 THEN sequence_of_statements2;

WHEN search_conditionN THEN sequence_of_statementsN;

[ELSE sequence_of_statementsN+1;]

END CASE;

l         search_condition：用于指定不同比较条件

l         sequence_of_statement：用于指定当满足特定条件时要执行的操作

DECLARE

v_sal emp.sal%TYPE;

v_ename emp.ename%TYPE;

BEGIN

SELECT ename,sal INTO v_ename,v_sal

FROM emp WHERE empno = &no;

CASE

WHEN v_sal < 1000 THEN

UPDATE emp SET comm = 100 WHERE ename = v_ename;

WHEN v_sal < 2000 THEN

UPDATE emp SET comm = 80 WHERE ename = v_ename;

WHEN v_sal < 6000 THEN

UPDATE emp SET comm = 50 WHERE ename = v_ename;

END CASE;

END;

PL/SQL

(1)    基本循环

PL/SQL中最简单格式的循环语句是基本循环语句，这种循环语句以LOOP开始，以END LOOP结束。

LOOP

statement1;

EXIT [WHEN condition];

END LOOP;

CREATE TABLE temp(cola INT);

DECLARE

i INT := 1;

BEGIN

LOOP

INSERT INTO temp VALUES(i);

EXIT WHEN i = 10;

i := i + 1;

END LOOP;

END;

PL/SQL 过程已成功完成。

(2)    WHILE循环

WHILE condition LOOP

statement1;

statement2;

END LOOP;

conditionTRUE时，PL/SQL执行器会执行循环体内的语句；而当conditionFALSENULL时，会退出循环，并执行END LOOP后的语句。

DECLARE

i INT := 1;

BEGIN

WHILE i <= 10 LOOP

DELETE temp WHERE cola = i;

i := i + 1;

END LOOP;

END;

PL/SQL 过程已成功完成。

(3)    FOR循环

FOR counter in [PEVERSE]

lower_bound .. upper_bound LOOP

statement1;

satement2;

END LOOP;

l         counter：是循环控制变量，并且该变量由Oracle隐含定义，不需要显式定义

l         lower_boundupper_bound：分别对应于循环控制变量的下界值和上界值。

BEGIN

FOR i IN 1..10 LOOP

INSERT INTO temp VALUES(i);

END LOOP;

END;

PL/SQL 过程已成功完成。

(4)    嵌套循环和标号

DECLARE

result INT;

BEGIN

<<outer>>

FOR i IN 1..100 LOOP

<<inter>>

FOR j IN 1..100 LOOP

result := i * j;

EXIT outer WHEN result = 1000;

EXIT WHEN result = 500;

END LOOP inner;

dbms_output.put_line(result);

END LOOP outer;

dbms_output.put_line(result);

END;

PL/SQL不仅提供了条件分支语句和循环控制语句，而且还提供了顺序控制语句GOTONULL。但与IFCASELOOP语句不同，GOTO语句和NULL语句，一般情况下不要使用。

(1)    GOTO

GOTO语句用于跳转到特定标号处去执行语句。

GOTO label_name;

l         label_name：是已经定义的标号名。

DECLARE

i INT := 1;

BEGIN

LOOP

INSERT INTO temp VALUES(i);

IF i = 10 THEN

GOTO end_loop;

END IF;

i := i + 1;

END LOOP;

<<end_loop>>

dbms_output.put_line('循环结束');

END;

PL/SQL

(2)    NULL

NULL语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用NULL语句的主要好处是可以提高PL/SQL程序的可读性。

DECLARE

v_sal emp.sal%TYPE;

v_ename emp.ename%TYPE;

BEGIN

SELECT ename,sal INTO v_ename,v_sal

FROM emp WHERE empno = &no;

IF v_sal < 3000 THEN

UPDATE emp SET comm = sal*0.1 WHERE ename = v_ename;

ELSE

NULL;

END IF;

END;

PL/SQL