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

来源：互联网发布：游戏美工是做什么的编辑：程序博客网时间：2024/04/30 03:07

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