oracle 每日一题-case表达式

原始出处：
http://www.plsqlchallenge.com/

作者：Steven Feuerstein 

运行环境：SQLPLUS, SERVEROUTPUT已打开

我们正在创建一个应用，用户可以选择他们的考题难度。他们可以选择某种特定难度，也可以指定一个范围。我们用整数值来代表这些选项：

1 Beginner only
2 Beginner -> Intermediate
3 Intermediate
4 Intermediate -> Advanced
5 Advanced
6 Any


我们需要在SELECT语句中引用这些范围。所以我执行了下列语句：

CREATE OR REPLACE PACKAGE plch_pkg
IS
   c_beginner       CONSTANT INTEGER := 1;
   c_intermediate   CONSTANT INTEGER := 2;
   c_advanced       CONSTANT INTEGER := 3;
END;
/

哪些选项在执行这个代码块之后：
BEGIN
   DBMS_OUTPUT.put_line (plch_difficulty_where_clause (6));
END;
/

会导致下列文本被显示：

difficulty_number IN (1,2,3)


(A) 
CREATE OR REPLACE FUNCTION plch_difficulty_where_clause (
   difficulty_range_in   IN INTEGER)
   RETURN VARCHAR2
IS

BEGIN
   RETURN    'difficulty_number IN ('
          || CASE difficulty_range_in
                WHEN 1
                THEN
                   plch_pkg.c_beginner
                WHEN 2
                THEN
                   plch_pkg.c_beginner || ',' || plch_pkg.c_intermediate
                WHEN 3
                THEN
                   plch_pkg.c_intermediate
                WHEN 4
                THEN
                   plch_pkg.c_intermediate || ',' || plch_pkg.c_advanced
                WHEN 5
                THEN
                   plch_pkg.c_advanced
                WHEN 6
                THEN
                      plch_pkg.c_beginner
                   || ','
                   || plch_pkg.c_intermediate
                   || ','
                   || plch_pkg.c_advanced
             END
          || ')';
END;
/

(B) 
CREATE OR REPLACE FUNCTION plch_difficulty_where_clause (
   difficulty_range_in   IN INTEGER)
   RETURN VARCHAR2
IS
BEGIN
   RETURN    'difficulty_number IN ('
          || CASE difficulty_range_in
                WHEN 1
                THEN
                   TO_CHAR (plch_pkg.c_beginner)
                WHEN 2
                THEN
                   plch_pkg.c_beginner || ',' || plch_pkg.c_intermediate
                WHEN 3
                THEN
                   plch_pkg.c_intermediate
                WHEN 4
                THEN
                   plch_pkg.c_intermediate || ',' || plch_pkg.c_advanced
                WHEN 5
                THEN
                   plch_pkg.c_advanced
                WHEN 6
                THEN
                      plch_pkg.c_beginner
                   || ','
                   || plch_pkg.c_intermediate
                   || ','
                   || plch_pkg.c_advanced
             END
          || ')';
END;
/

(C) 
CREATE OR REPLACE FUNCTION plch_difficulty_where_clause (
   difficulty_range_in   IN INTEGER)
   RETURN VARCHAR2
IS
BEGIN
   RETURN    'difficulty_number IN ('
          || CASE difficulty_range_in
                WHEN 1
                THEN
                   TO_CHAR (plch_pkg.c_beginner)
                WHEN 2
                THEN
                   plch_pkg.c_beginner || ',' || plch_pkg.c_intermediate
                WHEN 3
                THEN
                   TO_CHAR (plch_pkg.c_intermediate)
                WHEN 4
                THEN
                   plch_pkg.c_intermediate || ',' || plch_pkg.c_advanced
                WHEN 5
                THEN
                   TO_CHAR (plch_pkg.c_advanced)
                WHEN 6
                THEN
                      plch_pkg.c_beginner
                   || ','
                   || plch_pkg.c_intermediate
                   || ','
                   || plch_pkg.c_advanced
             END
          || ')';
END;
/


(D) 
CREATE OR REPLACE PACKAGE plch_pkg
IS
   c_beginner       CONSTANT VARCHAR2 (1) := 1;
   c_intermediate   CONSTANT VARCHAR2 (1) := 2;
   c_advanced       CONSTANT VARCHAR2 (1) := 3;
END;
/

CREATE OR REPLACE FUNCTION plch_difficulty_where_clause (
   difficulty_range_in   IN INTEGER)
   RETURN VARCHAR2
IS
BEGIN
   RETURN    'difficulty_number IN ('
          || CASE difficulty_range_in
                WHEN 1
                THEN
                   plch_pkg.c_beginner
                WHEN 2
                THEN
                   plch_pkg.c_beginner || ',' || plch_pkg.c_intermediate
                WHEN 3
                THEN
                   plch_pkg.c_intermediate
                WHEN 4
                THEN
                   plch_pkg.c_intermediate || ',' || plch_pkg.c_advanced
                WHEN 5
                THEN
                   plch_pkg.c_advanced
                WHEN 6
                THEN
                      plch_pkg.c_beginner
                   || ','
                   || plch_pkg.c_intermediate
                   || ','
                   || plch_pkg.c_advanced
             END
          || ')';
END;

/

AB: 这个选项会报 ORA-06502 错误。
问题在于有些表达式（不带拼接的那些）返回一个整数，有些返回一个字符串（那些带拼接的，它们依赖于隐式转换）。
如果WHEN子句中有任何一个是整数，那么PL/SQL就会要求每个都是整数，因此拼接会导致错误——它们无法被转换成整数。
C: 现在，WHEN子句中每一个整数都被显式转换成字符串，所以CASE表达式可以完成而不出错。
D: 现在，包把所有三个常量声明为字符串，因此CASE中的所有表达式都是相同的类型。