1.CASE语句:
SELECT CASE SIGN(5 - 5) WHEN 1 THEN 'Is Positive' WHEN -1 THEN 'Is Negative' ELSE 'Is Zero' ENDFROM DUAL;
后台实现:
if (SIGN(5 – 5) = 1) {'Is Positive';} else if (SIGN(5 – 5) = 2 ) {'Is Negative';}else {‘Is Zero’}
2. Decode函数:
SELECT DECODE(SIGN(5 – 5), 1, 'Is Positive', -1, 'Is Negative', ‘Is Zero’)FROM DUAL
后台实现:
switch ( SIGN(5 – 5) ) {case 1 : 'Is Positive'; break; case 2 : 'Is Negative'; break; default : ‘Is Zero’}
在上面的例子中,2者似乎都可以实现。但是,在遇到特殊的问题时Decode()要实现起来就相当复杂了。
例如:
SELECT CASE X-FIELD WHEN X-FIELD < 40 THEN ‘X-FIELD < 40’ WHEN X-FIELD < 50 THEN ‘X-FIELD < 50’ WHEN X-FIELD < 60 THEN ‘X-FIELD < 60’ ELSE ‘UNBEKNOWN’ENDFROM DUAL
SELECT CASE SIGN(5 - 5) WHEN 1 THEN 'Is Positive' WHEN -1 THEN 'Is Negative' ELSE 'Is Zero' ENDFROM DUAL;
后台实现:
if (SIGN(5 – 5) = 1) {'Is Positive';} else if (SIGN(5 – 5) = 2 ) {'Is Negative';}else {‘Is Zero’}
2. Decode函数:
SELECT DECODE(SIGN(5 – 5), 1, 'Is Positive', -1, 'Is Negative', ‘Is Zero’)FROM DUAL
后台实现:
switch ( SIGN(5 – 5) ) {case 1 : 'Is Positive'; break; case 2 : 'Is Negative'; break; default : ‘Is Zero’}
在上面的例子中,2者似乎都可以实现。但是,在遇到特殊的问题时Decode()要实现起来就相当复杂了。
例如:
SELECT CASE X-FIELD WHEN X-FIELD < 40 THEN ‘X-FIELD < 40’ WHEN X-FIELD < 50 THEN ‘X-FIELD < 50’ WHEN X-FIELD < 60 THEN ‘X-FIELD < 60’ ELSE ‘UNBEKNOWN’ENDFROM DUAL