發表者討論內容

冷日
(冷日) 發表時間：2016/10/26 16:12

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]Oracle / PLSQL: CASE Statement

Oracle / PLSQL: CASE Statement

This Oracle tutorial explains how to use the Oracle/PLSQL CASE statement with syntax and examples.
Description

The Oracle/PLSQL CASE statement has the functionality of an IF-THEN-ELSE statement. Starting in Oracle 9i, you can use the CASE statement within a SQL statement.
Syntax

The syntax for the CASE statement in Oracle/PLSQL is:

CASE [ expression ]

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END

Parameters or Arguments

expression
Optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)
condition_1, condition_2, ... condition_n
The conditions that must all be the same datatype. The conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further.
result_1, result_2, ... result_n
Results that must all be the same datatype. This is the value returned once a condition is found to be true.

Note

If no condition is found to be true, then the CASE statement will return the value in the ELSE clause.
If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.
You can have up to 255 comparisons in a CASE statement. Each WHEN ... THEN clause is considered 2 comparisons.

Applies To

The CASE statement can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Example

The CASE statement can be used in Oracle/PLSQL.

You could use the CASE statement in a SQL statement as follows: (includes the expression clause)

SELECT table_name,
CASE owner
  WHEN 'SYS' THEN 'The owner is SYS'
  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
FROM all_tables;

Or you could write the SQL statement using the CASE statement like this: (omits the expression clause)

SELECT table_name,
CASE
  WHEN owner='SYS' THEN 'The owner is SYS'
  WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
FROM all_tables;

The above two CASE statements are equivalent to the following IF-THEN-ELSE statement:

IF owner = 'SYS' THEN
   result := 'The owner is SYS';

ELSIF owner = 'SYSTEM' THEN
   result := 'The owner is SYSTEM'';

ELSE
   result := 'The owner is another value';

END IF;

The CASE statement will compare each owner value, one by one.

One thing to note is that the ELSE clause within the CASE statement is optional. You could have omitted it. Let's look at the SQL statement above with the ELSE clause omitted.

Your SQL statement would look as follows:

SELECT table_name,
CASE owner
  WHEN 'SYS' THEN 'The owner is SYS'
  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
END
FROM all_tables;

With the ELSE clause omitted, if no condition was found to be true, the CASE statement would return NULL.
Comparing 2 Conditions

Here is an example that demonstrates how to use the CASE statement to compare different conditions:

SELECT
CASE
  WHEN a < b THEN 'hello'
  WHEN d < e THEN 'goodbye'
END
FROM suppliers;

Frequently Asked Questions

Question: Can you create a CASE statement that evaluates two different fields? I want to return a value based on the combinations in two different fields.

Answer: Yes, below is an example of a case statement that evaluates two different fields.

SELECT supplier_id,
CASE
  WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
  WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
FROM suppliers;

So if supplier_name field is IBM and the supplier_type field is Hardware, then the CASE statement will return North office. If the supplier_name field is IBM and the supplier_type is Software, the CASE statement will return South office.

原文出處：Oracle / PLSQL: CASE Statement

回覆

冷日
(冷日) 發表時間：2016/10/26 16:15

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]Oracle Select 在欄位上使用條件 IF(when case)

CASE 是 SQL 用來做為 if-then-else 之類邏輯的關鍵字。 CASE 的語法如下：

SELECT CASE ("欄位名")
WHEN "條件1" THEN "結果1"
WHEN "條件2" THEN "結果2"
...
[ELSE "結果N"]
END
FROM "表格名"

"條件" 可以是一個數值或是公式。 ELSE 子句則並不是必須的。

在我們的 Store_Information 中

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

若我們要將 'Los Angeles' 的 Sales 數值乘以2，以及將 'San Diego' 的 Sales 數值乘以1.5，我們就鍵入以下的 SQL：

SELECT store_name, CASE store_name
WHEN 'Los Angeles' THEN Sales * 2
WHEN 'San Diego' THEN Sales * 1.5
ELSE Sales
END
"New Sales",
Date
FROM Store_Information

"New Sales" 是用到 CASE 那個欄位的欄位名。

結果:

store_name	New Sales	Date
Los Angeles	$3000	Jan-05-1999
San Diego	$375	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

原文出處：Oracle Select 在欄位上使用條件 IF(when case) @ 羽澈.Net :: 隨意窩 Xuite日誌

回覆

主題一覽的下方

討論串

前一個主題 | 下一個主題 | 頁首 |

| |

發表新主題

DB研討會 : [轉貼]Oracle / PLSQL: CASE Statement