跨越Oracle和MSSQL关系数据库开发——01开关语句

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 ::=

[ <<label_name>> ]

CASE { WHEN boolean_expression THEN {statement;} ... }...

[ ELSE {statement;}... ]

END CASE [ label_name ];

 

　　语法二：

simple_case_statement ::=

[ <<label_name>> ]

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