# 《oracle pl/sql programming》 第四章 条件与顺序控制 第五章 迭代与循环

1. IF
IF THEN END IF;
IF salary > 40000
THEN
give_bonus (employee_id,500);
END IF;
IF THEN ELSE END IF;
IF salary <= 40000
THEN
give_bonus (employee_id, 0);
ELSE
give_bonus (employee_id, 500);
END IF;
IF THEN ELSIF ELSE END IF;
IF salary BETWEEN 10000 AND 20000
THEN
give_bonus(employee_id, 1500);
ELSIF salary BETWEEN 20000 AND 40000
THEN
give_bonus(employee_id, 1000);
ELSIF salary > 40000
THEN
give_bonus(employee_id, 500);
ELSE
give_bonus(employee_id, 0);
END IF;

IF嵌套
IF condition1
THEN
IF condition2
THEN
statements2
ELSE
IF condition3
THEN
statements3
ELSIF condition4
THEN
statements4
END IF;
END IF;
END IF;

2.CASE

CASE employee_type
WHEN 'S' THEN
award_salary_bonus(employee_id);
WHEN 'H' THEN
award_hourly_bonus(employee_id);
WHEN 'C' THEN
award_commissioned_bonus(employee_id);
ELSE
RAISE invalid_employee_type;
END CASE;

CASE TRUE
WHEN salary >= 10000 AND salary <=20000 THEN
give_bonus(employee_id, 1500);
WHEN salary > 20000 AND salary <= 40000
give_bonus(employee_id, 1000);
WHEN salary > 40000
give_bonus(employee_id, 500);
ELSE
give_bonus(employee_id, 0);
END CASE;

CASE
WHEN salary >= 10000 AND salary <=20000 THEN
give_bonus(employee_id, 1500);
WHEN salary > 20000 AND salary <= 40000 THEN
give_bonus(employee_id, 1000);
WHEN salary > 40000 THEN
give_bonus(employee_id, 500);
ELSE
give_bonus(employee_id, 0);
END CASE;

CASE嵌套
CASE
WHEN salary >= 10000 THEN
CASE
WHEN salary <= 20000 THEN
give_bonus(employee_id, 1500);
WHEN salary > 40000 THEN
give_bonus(employee_id, 500);
WHEN salary > 20000 THEN
give_bonus(employee_id, 1000);
END CASE
;
WHEN salary < 10000 THEN
give_bonus(employee_id,0);
END CASE;

CASE表达式
CASE表达是的一种常见用法是用于SQL语句中它有两种方式
Simple_Case_Expression :=
CASE expression
WHEN result1 THEN
result_expression1
WHEN result2 THEN
result_expression2
...
ELSE
result_expression_else
END;

Searched_Case_Expression :=
CASE
WHEN expression1 THEN
result_expression1
WHEN expression2 THEN
result_expression2
...
ELSE
result_expression_else
END;

DECLARE
boolean_true BOOLEAN := TRUE;
boolean_false BOOLEAN := FALSE;
boolean_null BOOLEAN;

FUNCTION boolean_to_varchar2 (flag IN BOOLEAN) RETURN VARCHAR2 IS
BEGIN
RETURN
CASE flag
WHEN TRUE THEN 'True'
WHEN FALSE THEN 'False'
ELSE 'NULL' END;
END;

BEGIN
DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_true));
DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_false));
DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_null));
END;

DECLARE
salary NUMBER := 20000;
employee_id NUMBER := 36325;

PROCEDURE give_bonus (emp_id IN NUMBER, bonus_amt IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(emp_id);
DBMS_OUTPUT.PUT_LINE(bonus_amt);
END;

BEGIN
give_bonus(employee_id,
CASE
WHEN salary >= 10000 AND salary <=20000 THEN 1500
WHEN salary > 20000 AND salary <= 40000 THEN 1000
WHEN salary > 40000 THEN 500
ELSE 0
END);
END;

DECLARE
salary NUMBER := 20000;
employee_id NUMBER := 36325;
bonus_amount NUMBER;
BEGIN
bonus_amount :=
CASE
WHEN salary >= 10000 AND salary <=20000 THEN 1500
WHEN salary > 20000 AND salary <= 40000 THEN 1000
WHEN salary > 40000 THEN 500
ELSE 0
END * 10;

DBMS_OUTPUT.PUT_LINE(bonus_amount);
END;

SELECT
CASE
WHEN DUMMY='X' THEN 'Dual is OK'
ELSE 'Dual is messed up'
END
FROM DUAL;

3. GOTO
BEGIN
GOTO second_output;

DBMS_OUTPUT.PUT_LINE('This line will never execute.');

<<second_output>>
DBMS_OUTPUT.PUT_LINE('We are here!');
END;

4.null语句
NULL语句不执行任何操作一般用来提高程序代码的可读性。
PROCEDURE calc_avg_sales
BEGIN
:sales.avg := :sales.month1 / :sales.total;
EXCEPTION
WHEN ZERO_DIVIDE
THEN
:sales.avg := 0;
RAISE FORM_TRIGGER_FAILURE;
WHEN OTHERS
THEN
NULL;
END;

PROCEDURE process_data (data_in IN orders%ROWTYPE,
data_action IN VARCHAR2) IS
BEGIN
-- First in series of validations.
IF data_in.ship_date IS NOT NULL
THEN
status := validate_shipdate (data_in.ship_date);
IF status != 0 THEN GOTO end_of_procedure;
END IF;

-- Second in series of validations.
IF data_in.order_date IS NOT NULL
THEN
status := validate_orderdate (data_in.order_date);
IF status != 0 THEN GOTO end_of_procedure;
END IF;

... more validations ...

<< end_of_procedure >>
NULL;
END;

1.循环基础

PROCEDURE display_multiple_years (

start_year_in IN PLS_INTEGER

,end_year_in IN PLS_INTEGER

)

IS

l_current_year PLS_INTEGER := start_year_in;

BEGIN

LOOP

EXIT WHEN l_current_year > end_year_in;

display_total_sales (l_current_year);

l_current_year :=  l_current_year + 1;

END LOOP;

END display_multiple_years;

FOR循环

PROCEDURE display_multiple_years (

start_year_in IN PLS_INTEGER

,end_year_in IN PLS_INTEGER

)

IS

BEGIN

FOR l_current_year IN start_year_in .. end_year_in

LOOP

display_total_sales (l_current_year);

END LOOP;

END display_multiple_years;

WHILE循环

PROCEDURE display_multiple_years (

start_year_in IN PLS_INTEGER

,end_year_in IN PLS_INTEGER

)

IS

l_current_year PLS_INTEGER := start_year_in;

BEGIN

WHILE (l_current_year <= end_year_in)

LOOP

display_total_sales (l_current_year);

l_current_year :=  l_current_year + 1;

END LOOP;

END display_multiple_years;

2. 游标FOR循环

FOR record_index IN { cursor_name | (explicit SELECT statement) }

LOOP

executable statement(s)

END LOOP;

FOR book_cur IN (SELECT * FROM books)

LOOP

show_usage;

END LOOP;

1   DECLARE

2      CURSOR occupancy_cur IS

3         SELECT pet_id, room_number

4           FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);

5      occupancy_rec occupancy_cur%ROWTYPE;

6   BEGIN

7      OPEN occupancy_cur;

8      LOOP

9         FETCH occupancy_cur INTO occupancy_rec;

10         EXIT WHEN occupancy_cur%NOTFOUND;

11         update_bill

12             (occupancy_rec.pet_id, occupancy_rec.room_number);

13       END LOOP;

14       CLOSE occupancy_cur;

15    END;

DECLARE

CURSOR occupancy_cur IS

SELECT pet_id, room_number

FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);

BEGIN

FOR occupancy_rec IN occupancy_cur

LOOP

update_bill (occupancy_rec.pet_id, occupancy_rec.room_number);

END LOOP;

END;

3.标签跳转

BEGIN

<<outer_loop>>

LOOP

LOOP

EXIT outer_loop;

END LOOP;

some_statement ;

END LOOP;

END;

• 本文已收录于以下专栏：

举报原因： 您举报文章：《oracle pl/sql programming》 第四章 条件与顺序控制 第五章 迭代与循环 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)