原始出处:
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 ('
|| C ASE 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;
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 ('
|| C ASE 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中的所有表达式都是相同的类型。