跨越Oracle和MSSQL关系数据库开发――01开关语句
热
【字体：小 大】
跨越Oracle和MSSQL关系数据库开发――01开关语句
作者：- 文章来源：- 点击数：643 更新时间：2007-8-10 
1. 概述
作为本系列的第一篇文章，我想从一个简单的专题开始。技术的文章通常都比较枯燥乏味，我尽量写的通俗易懂，并使用一些有趣的例子。
类似于高级语言，Oracle和MSSQL都提供了开关语句来处理多分枝逻辑。本文将讲述两种数据库开关语句的相同和不同之处，并介绍一些使用经验。
2. 使用条件语句表达多重选择
2.1 PL/SQL的条件语句
在PL/SQL中，可以使用IF…THEN…ELSIF…END IF来表示多重选择。
以下是测试例子：
IF v_grade = 'A' THEN
v_result := 'Excellent';
ELSIF v_grade = 'B' THEN
v_result := 'Very Good';
ELSIF v_grade = 'C' THEN
v_result := 'Good';
ELSIF v_grade = 'D' THEN
v_result := 'Fair';
ELSIF v_grade = 'F' THEN
v_result := 'Poor';
ELSE
v_result := 'No such grade';
END IF;
上面的代码按照变量v_grade的值计算出不同的等级。PL/SQL的ELSIF是专门设计成支持多重选择的，避免多层选择。
2.2 T-SQL的条件语句
T-SQL提供了类似于C语言的多层条件语句。上一小节的例子可以表达为：
IF @grade = 'A'
BEGIN
@result := 'Excellent';
END
ELSE IF @grade = 'B'
BEGIN
@result := 'Very Good';
END
ELSE IF @grade = 'C'
BEGIN
@result := 'Good'
END
ELSE IF @grade = 'D'
BEGIN
@result := 'Fair'
END
ELSE IF @grade = 'F'
BEGIN
@result := 'Poor'
END
ELSE
BEGIN
@result := 'No such grade'
END
上述语句虽然看起来和PL/SQL的ELSIF差不多，但实际上是使用多层选择。
2. 使用开关语句表达多重选择
开关语句就是专门为简化多重选择而设计的，所以，如果可能，尽量使用之。
2.1 Oracle的开关语句
2.1.1 DECODE函数
在9i以前的版本中，Oracle只提供了DECODE一个开关函数，并且该函数只能在SQL语句中使用，不能直接在PL/SQL的其他类型语句中使用。
DECODE的语法为：
DECODE(expr,search,result[,search,result]...[,default])
例如，上一小节中的例子可以用DECODE表达为：
select DECODE(v_grade, 'A', 'Excellent',
'B', 'Very Good',
'C', 'Good',
'D', 'Fair',
'F', 'Poor',
'No such grade')
into v_result
from dual;
这里假设v_grade和v_result均为PL/SQL中的变量。
2.1.2 CASE语句
为了简化PL/SQL的控制流程，从9i开发，PL/SQL提供了类似于C语言SWITCH语句的CASE语句。
从表现形式来看，CASE语句有两种语法。
语法一：
searched_case_statement ::=
[ <
CASE { WHEN boolean_expression THEN {statement;} ... }...
[ ELSE {statement;}... ]
END CASE [ label_name ];
语法二：
simple_case_statement ::=
[ <
CASE case_operand
{ WHEN when_operand THEN {statement;} ... }...
[ ELSE {statement;}... ]
END CASE [ label_name ];
这里的label_name是作为标签使用的，可忽略。
DECODE的功能相当于第二种语法的功能。
我们用两种不同的语法分别来表达前面的例子。
语法一表达：
CASE
WHEN v_grade = 'A' THEN
v_result := 'Excellent';
WHEN v_grade = 'B' THEN
v_result := 'Very Good';
WHEN v_grade = 'C' THEN
v_result := 'Good';
WHEN v_grade = 'D' THEN
v_result := 'Fair';
WHEN v_grade = 'F' THEN
v_result := 'Poor';
ELSE
v_result := 'No such grade';
END CASE;
语法二表达：
CASE v_grade
WHEN 'A' THEN
v_result := 'Excellent';
WHEN 'B' THEN
v_result := 'Very Good';
WHEN 'C' THEN
v_result := 'Good';
WHEN 'D' THEN
v_result := 'Fair';
WHEN 'F' THEN
v_result := 'Poor';
ELSE
v_result := 'No such grade';
END CASE;
可以看出，语法一的表达能力要强于语法二，因为语法二是单值比较，而语法一是表达式计算。通常，凡是语法二能表达的，语法一均能表达，反之则不然。例如，语法一可以方便的判断大于或者小于等关系，语法二则只能判断等于。
注意，Oracle并没有提供在SQL语句中使用的CASE函数。
2.1.3 用DECODE来计算复杂逻辑
前面讲到，CASE语句的语法二的表达能力有限。由于DECODE的功能相当于CASE语句的语法二，因此DECODE的表达能力也是有限的。对于9i之前版本的用户，有时面临着表达能力不够的问题。特别是从MSSQL迁移时，由于MSSQL提供了CASE语句，造成表达能力的不一致。
这里为读者提供一种技巧，使用DECODE来表达复杂的关于，例如大于逻辑。
假定有一张证券交易表fsjour，其中的列stock_code表示证券代码，我们使用DECODE来计算出stock_code所属的股票种类。
其规则为：当stock_code在600000和699999之间为“沪A”，在000000和099999之间为“深A”，否则为“其他”。
select DECODE(1, (select 1 from dual
where stock_code between '600000' and '699999'), '沪A',
(select 1 from dual
where stock_code between '000000' and '099999'), '深A',
'其他')
from fsjour;
这里的技巧是将复杂的条件转移到子语句的WHERE部分。
如果两个子语句的结果相同，那么前面一个的结果将被返回。
运用这种技巧，可以用DECODE实现CASE语句语法一的功能，但是语句会显得相当复杂。
2.2 MSSQL的开关语句
如同PL/SQL一样，T-SQL支持的CASE语句也有雷同的两种格式。
语法一：
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END
语法二：
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END
与PL/SQL不同的是，T-SQL只允许在SQL语句中使用CASE，如同Oracle的DECODE函数那样。
例如，2.1.1节中的例子可以表达为：
select CASE @grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
WHEN 'D' THEN 'Fair'
WHEN 'F' THEN 'Poor'
ELSE 'No such grade'
END