关闭

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

标签: oracleintegernulldateexceptionoutput
1571人阅读 评论(0) 收藏 举报
分类:
第四章 条件与顺序控制
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
    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.循环基础

 

简单循环 (REPEAT...UNTIL...)

    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;

可替换成FOR

    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;

 

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:232364次
    • 积分:3688
    • 等级:
    • 排名:第10822名
    • 原创:102篇
    • 转载:4篇
    • 译文:0篇
    • 评论:52条
    最新评论