ORACLE开发：Oracle数据库之PL/SQL使用流程控制语句

IF <布尔表达式> THEN
PL/SQL 和 SQL语句
END IF;

IF <布尔表达式> THEN
PL/SQL 和 SQL语句
ELSE
其它语句
END IF;

IF <布尔表达式> THEN
PL/SQL 和 SQL语句
ELSIF < 其它布尔表达式> THEN
其它语句
ELSIF < 其它布尔表达式> THEN
其它语句
ELSE
其它语句
END IF;

DECLARE
v_first_name VARCHAR2(20);
v_hire_date DATE;
v_bonus NUMBER(6,2);
BEGIN
SELECT first_name, hire_date INTO v_first_name, v_hire_date FROM employees
WHERE employee_id = &emp_id;
IF v_hire_date > TO_DATE(’01-1月-90′) THEN
v_bonus := 800;
ELSIF v_hire_date > TO_DATE(’01-1月-88′) THEN
v_bonus := 1600;
ELSE
v_bonus := 2400;
END IF;
DBMS_OUTPUT.PUT_LINE(v_first_name||’雇员的雇佣日期是’||v_hire_date ||’、奖金是’||v_bonus);
END;

CASE
WHEN 条件表达式1 THEN
语句段1
WHEN 条件表达式2 THEN
语句段2
……
WHEN 条件表达式n THEN
语句段n
[ELSE 语句段]
END;

DECLARE
V_grade char(1) := UPPER(‘&p_grade’);
V_appraisal VARCHAR2(20);
BEGIN
V_appraisal :=
CASE v_grade
WHEN ‘A’ THEN ‘Excellent’
WHEN ‘B’ THEN ‘Very Good’
WHEN ‘C’ THEN ‘Good’
ELSE ‘No such grade’
END;
DBMS_OUTPUT.PUT_LINE(‘Grade:’||v_grade||’ Appraisal: ‘|| v_appraisal);
END;

CASE 条件表达式
WHEN 条件表达式结果1 THEN
语句段1
WHEN 条件表达式结果2 THEN
语句段2
……
WHEN 条件表达式结果n THEN
语句段n
[ELSE 条件表达式结果]
END;

DECLARE
v_first_name employees.first_name%TYPE;
v_job_id employees.job_id%TYPE;
v_salary employees.salary%TYPE;
v_sal_raise NUMBER(3,2);
BEGIN
SELECT first_name, job_id, salary INTO
v_first_name, v_job_id, v_salary
FROM employees WHERE employee_id = &emp_id;
CASE
WHEN v_job_id = ‘PU_CLERK’ THEN
IF v_salary < 3000 THEN v_sal_raise := .08;
ELSE v_sal_raise := .07;
END IF;
WHEN v_job_id = ‘SH_CLERK’ THEN
IF v_salary < 4000 THEN v_sal_raise := .06;
ELSE v_sal_raise := .05;
END IF;
WHEN v_job_id = ‘ST_CLERK’ THEN
IF v_salary < 3500 THEN v_sal_raise := .04;
ELSE v_sal_raise := .03;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(‘该岗位不涨工资: ‘||v_job_id);
END CASE;
DBMS_OUTPUT.PUT_LINE(v_first_name||’的岗位是’||v_job_id ||’、的工资是’||v_salary ||’、工资涨幅是’||v_sal_raise);
END;

LOOP
要执行的语句;
EXIT WHEN <条件语句> –条件满足，退出循环语句
END LOOP;

DECLARE
int NUMBER(2) :=0;
BEGIN
LOOP
int := int + 1;
DBMS_OUTPUT.PUT_LINE(‘int 的当前值为:’||int);
EXIT WHEN int =10;
END LOOP;
END;

WHILE <布尔表达式> LOOP
要执行的语句;
END LOOP;

DECLARE
x NUMBER :=1;
BEGIN
WHILE x<=10 LOOP
DBMS_OUTPUT.PUT_LINE(‘X的当前值为:’||x);
x:= x+1;
END LOOP;
END;

[<<循环标签>>]
FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
要执行的语句;
END LOOP [循环标签];

BEGIN
FOR int in 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(‘int 的当前值为: ‘||int);
END LOOP;
END;

declare
result int:=0;
begin
<<outer>>
for i in 1..20 loop
<<inner>>
for j in 1..10 loop
result:=i*j;
dbms_output.put_line(‘循环时,i=’||i||’,j=’||j||’,result的值:’||result);
–当外层循环result值达到200时，跳出整个外层循环
exit outer when result = 200;
–当内层循环result值达到100时，跳出内层循环
exit when result = 100;
end loop inner;
dbms_output.put_line(‘end inner ,result的值:’||result);
end loop outer;
dbms_output.put_line(‘end outter ,result的值:’||result);
end;

GOTO label;
……
<<label>> /*标号是用<<　>>括起来的标识符 */

DECLARE
V_counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(‘V_counter的当前值为:’||V_counter);
V_counter := v_counter + 1;
IF v_counter > 10 THEN
GOTO labelOffLOOP;
END IF;
END LOOP;
<<labelOffLOOP>>
DBMS_OUTPUT.PUT_LINE(‘V_counter的当前值为:’||V_counter);
END;

DECLARE
v_emp_id employees.employee_id%TYPE;
v_first_name employees.first_name%TYPE;
v_salary employees.salary%TYPE;
v_sal_raise NUMBER(3,2);
BEGIN
v_emp_id := &emp_id;
SELECT first_name, salary INTO v_first_name, v_salary
FROM employees WHERE employee_id = v_emp_id;
IF v_salary <= 3000 THEN
v_sal_raise := .10;
DBMS_OUTPUT.PUT_LINE(v_first_name||’的工资是’||v_salary||’、工资涨幅是’||v_sal_raise);
ELSE
NULL;
END IF;
END;