条件控制：IF语句和CASE语句

1.IF-THEN-ELSE

DECLARE

    v_total NUMBER;
BEGIN
    SELECT COUNT(*)
      INTO v_total
      FROM enrollment e
      JOIN section s
     USING (section_id)
     WHERE s.course_no = 25
       AND s.section_no = 1;
    --check if section 1 of course 25 is full
    IF v_total >= 15
    THEN
        dbms_output.put_line('Section 1 of course 25 is full');
    ELSE
        dbms_output.put_line('Section 1 of course 25 if not full');
        --control resumes here
    END IF;

END;

替换成用户输入变量：

DECLARE
    v_total      NUMBER;
    v_course_no  CHAR(6) := '&sv_course_no';
    v_section_no NUMBER := &sv_section_no;
BEGIN
    SELECT COUNT(*)
      INTO v_total
      FROM enrollment e
      JOIN section s
     USING (section_id)
     WHERE s.course_no = v_course_no
       AND s.section_no = v_section_no;
    --check if section 1 of course 25 is full
    IF v_total >= 15
    THEN
        dbms_output.put_line('Section 1 of course 25 is full');
    ELSE
        dbms_output.put_line('Section 1 of course 25 if not full');
        --control resumes here
    END IF;
END;

DECLARE
    v_total    NUMBER;
    v_students NUMBER;
BEGIN
    SELECT COUNT(*)
      INTO v_total
      FROM enrollment e
      JOIN section s
     USING (section_id)
     WHERE s.course_no = 25
       AND s.section_no = 1;
    --check if section 1 of course 25 is full
    IF v_total >= 15
    THEN
        dbms_output.put_line('Section 1 of course 25 is full');
    ELSE
        v_students := 15 - v_total;
        dbms_output.put_line(v_students ||
                             'students can still enroll into section 1' ||
                             'of course 25');
        --control resumes here
    END IF;
END;

使用elsif语句：

DECLARE
    v_student_id   NUMBER := 102;
    v_section_id   NUMBER := 89;
    v_final_grade  NUMBER;
    v_letter_grade CHAR(1);
BEGIN
    SELECT final_grade
      INTO v_final_grade
      FROM enrollment
     WHERE student_id = v_student_id
       AND section_id = v_section_id;
    IF v_final_grade BETWEEN 90 AND 120
    THEN
        v_letter_grade := 'A';
    ELSIF v_final_grade BETWEEN 80 AND 89
    THEN
        v_letter_grade := 'B';
    ELSIF v_final_grade BETWEEN 70 AND 79
    THEN
        v_letter_grade := 'C';
    ELSIF v_final_grade BETWEEN 60 AND 69
    THEN
        v_letter_grade := 'D';
    ELSE
        v_letter_grade := 'F';
    END IF;
    --control resumes here
    dbms_output.put_line('Letter grade is: ' || v_letter_grade);
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('Theres is no such student or section');
END;

DECLARE
    v_student_id   NUMBER := &sv_student_id;
    v_section_id   NUMBER := &sv_section_id;
    v_final_grade  NUMBER;
    v_letter_grade CHAR(1);
BEGIN
    SELECT final_grade
      INTO v_final_grade
      FROM enrollment
     WHERE student_id = v_student_id
       AND section_id = v_section_id;
    IF v_final_grade BETWEEN 90 AND 120
    THEN
        v_letter_grade := 'A';
    ELSIF v_final_grade BETWEEN 80 AND 89
    THEN
        v_letter_grade := 'B';
    ELSIF v_final_grade BETWEEN 70 AND 79
    THEN
        v_letter_grade := 'C';
    ELSIF v_final_grade BETWEEN 60 AND 69
    THEN
        v_letter_grade := 'D';
    ELSE
        v_letter_grade := 'F';
    END IF;
    --control resumes here
    dbms_output.put_line('Letter grade is: ' || v_letter_grade);
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('Theres is no such student or section');
END; 

用搜索式CASE语句改写：

DECLARE
    v_student_id   NUMBER := 102;
    v_section_id   NUMBER := 89;
    v_final_grade  NUMBER;
    v_letter_grade CHAR(1);
BEGIN
    SELECT final_grade
      INTO v_final_grade
      FROM enrollment
     WHERE student_id = v_student_id
       AND section_id = v_section_id;
    CASE
        WHEN v_final_grade >= 90 THEN
            v_letter_grade := 'A';
        WHEN v_final_grade >= 80 THEN
            v_letter_grade := 'B';
        WHEN v_final_grade >= 70 THEN
            v_letter_grade := 'C';
        WHEN v_final_grade >= 6 THEN
            v_letter_grade := 'D';
        ELSE
            v_letter_grade := 'F';
    END CASE;
    dbms_output.put_line('Letter grade is: ' || v_letter_grade);
    EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('Theres is no such student or section');
END;

DECLARE
    v_student_id   NUMBER := 102;
    v_section_id   NUMBER := 89;
    v_final_grade  NUMBER;
    v_letter_grade CHAR(1);
BEGIN
    SELECT final_grade
      INTO v_final_grade
      FROM enrollment
     WHERE student_id = v_student_id
       AND section_id = v_section_id;
    CASE --outer case
        WHEN v_final_grade IS NULL THEN
            dbms_output.put_line('There is no final grade.');
        ELSE
            CASE --inner case
                WHEN v_final_grade >= 90 THEN
                    v_letter_grade := 'A';
                WHEN v_final_grade >= 80 THEN
                    v_letter_grade := 'B';
                WHEN v_final_grade >= 70 THEN
                    v_letter_grade := 'C';
                WHEN v_final_grade >= 6 THEN
                    v_letter_grade := 'D';
                ELSE
                    v_letter_grade := 'F';
            END CASE;--inner case
            dbms_output.put_line('Letter grade is: ' || v_letter_grade);
    END CASE; --outer case
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('Theres is no such student or section');
END;

CASE表达式：

DECLARE
    v_course_no   NUMBER;
    v_description VARCHAR2(50);
    v_prereq      VARCHAR2(35);
BEGIN
    SELECT course_no,
           description,
           CASE
               WHEN prerequisite IS NULL THEN
                'No prerequisite course required'
               ELSE
                to_char(prerequisite)
           END prerequisite --没有分号
      INTO v_course_no, v_description, v_prereq
      FROM course
     WHERE course_no = 20;
    dbms_output.put_line('Course: ' || v_course_no);
    dbms_output.put_line('Description: ' || v_description);
    dbms_output.put_line('Prerequisite: ' || v_course_no);
END;

把case表达式的值赋给变量：

DECLARE
    v_student_id   NUMBER := 102;
    v_section_id   NUMBER := 89;
    v_final_grade  NUMBER;
    v_letter_grade CHAR(1);
BEGIN
    SELECT final_grade
      INTO v_final_grade
      FROM enrollment
     WHERE student_id = v_student_id
       AND section_id = v_section_id;
    v_letter_grade := CASE
                          WHEN v_final_grade >= 90 THEN
                           'A'
                          WHEN v_final_grade >= 80 THEN
                           'B'
                          WHEN v_final_grade >= 70 THEN
                           'C'
                          WHEN v_final_grade >= 6 THEN
                           'D'
                          ELSE
                           'F'
                      END;
    dbms_output.put_line('Letter grade is: ' || v_letter_grade);
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('Theres is no such student or section');
END;

把case表达式的结果通过select into赋值给变量：

DECLARE
    v_student_id   NUMBER := 102;
    v_section_id   NUMBER := 89;
    v_letter_grade CHAR(1);
BEGIN
    SELECT CASE
               WHEN final_grade >= 90 THEN
                'A'
               WHEN final_grade >= 80 THEN
                'B'
               WHEN final_grade >= 70 THEN
                'C'
               WHEN final_grade >= 6 THEN
                'D'
               ELSE
                'F'
           END
      INTO v_letter_grade
      FROM enrollment
     WHERE student_id = v_student_id
       AND section_id = v_section_id;
    dbms_output.put_line('Letter grade is: ' || v_letter_grade);
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('Theres is no such student or section');
END;

coalesce函数：

SELECT e.student_id,
       e.section_id,
       e.final_grade,
       g.numeric_grade,
       coalesce(e.final_grade, g.numeric_grade, 0)
  FROM enrollment e, grade g
 WHERE e.student_id = g.student_id
   AND e.section_id = g.section_id
   AND e.student_id = 102
   AND g.grade_type_code = 'FI';

GRADE的值等于第一行中NUMBERIC_GRADE的值。COALESCE函数会比较FINAL_GRADE的值是否等于NULL。如果FINAL_GRADE的值等于NULL，则NUMBERIC_GRADE的值会与NULL比较。由于NUMBERIC_GRADE的值不是NULL,COALESCE返回NUMBERIC_GRADE的值。

NULLIF 函数：

DECLARE
    v_final_grade NUMBER;
BEGIN
    SELECT CASE
               WHEN e.final_grade = g.numeric_grade THEN 
                NULL
               ELSE
                g.numeric_grade
           END
      INTO v_final_grade
      FROM enrollment e
      JOIN grade g
        ON (e.student_id = g.student_id)
       AND e.section_id = g.section_id
     WHERE e.student_id = 102
       AND e.section_id = 86
       AND g.grade_type_code = 'FI';
    dbms_output.put_line('Final grade: ' || v_final_grade);
END;

保存于FINAL_GARDE 的值会与保存于列NUMBERIC_GRADE的值进行比较。如果值相等，NULL会被赋予V_FINAL_GRADE;否则的话，保存于列NUMBERIC_GRADE的值会被赋予变量v_final_grade；

用NULLIF函数改写：

DECLARE
    v_final_grade NUMBER;
BEGIN
    SELECT nullif(g.numeric_grade,e.final_grade)
      INTO v_final_grade
      FROM enrollment e
      JOIN grade g
        ON (e.student_id = g.student_id)
       AND e.section_id = g.section_id
     WHERE e.student_id = 102
       AND e.section_id = 86
       AND g.grade_type_code = 'FI';
    dbms_output.put_line('Final grade: ' || v_final_grade);
END;