發現了一些新的用法 & Function

From 網路文章

http://www.orafaq.com/faq/how_does_one_implement_if_then_else_logic_in_a_select_statement

 


How does one implement IF-THEN-ELSE logic in a SELECT statement?

Oracle SQL supports several methods of coding conditional IF-THEN-ELSE logic in SQL statements. Here are some:

CASE Expressions

From Oracle 8i one can use CASE statements in SQL. Look at this example:

SELECT ename, CASE WHEN sal = 1000 THEN 'Minimum wage'
WHEN sal > 1000 THEN 'Over paid'
ELSE 'Under paid'
END AS "Salary Status"
FROM emp;

DECODE() Function

The Oracle decode function acts like a procedural statement inside an
SQL statement to return different values or columns based on the values of
other columns in the select statement. Examples:

select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
from employees;
select a, b, decode( abs(a-b), a-b, 'a > b',
0, 'a = b',
'a < b') from tableX;

Note: The decode function is not ANSI SQL and is rarely implemented
in other RDBMS offerings. It is one of the good things about Oracle,
but use it sparingly if portability is required.


 

GREATEST() and LEAST() Functions

select decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B', 
'B is greater than A')...

 

select decode( GREATEST(A,B), 
A, decode(A, B, 'A NOT GREATER THAN B', 'A GREATER THAN B'),
'A NOT GREATER THAN B')...

NVL() and NVL2() Functions

NVL and NVL2 can be used to test for NULL values.

NVL(a,b) == if 'a' is null then return 'b'.

SELECT nvl(ename, 'No Name') 
FROM emp;

NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.

SELECT nvl2(ename, 'Do have a name', 'No Name') 
FROM emp;

COALESCE() Function

COALESCE() returns the first expression that is not null. Example:

SELECT 'Dear '||COALESCE(preferred_name, first_name, 'Sir or Madam')
FROM emp2;

NULLIF() Function

NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:

SELECT NULLIF(ename, ename)
FROM emp;
創作者介紹
創作者 羅伯特 的頭像
羅伯特

Thinking in Robert

羅伯特 發表在 痞客邦 留言(0) 人氣()