- 浏览: 442881 次
- 性别:
- 来自: 上海
文章分类
最新评论
-
鱼里的yeol:
正在学习EJB 感觉有些吃力
Ejb3学习之二----Ejb3的Ejb Bean类型介绍 -
phoenix5870:
默认就是singleton的。
Spring中的Singleton模式和Java中的Singleton模式 -
jhys7s8jd:
pdf打印机下载 无水印http://www.onlinedo ...
PDFCreate工具的使用 -
wang371134086:
:e vil:
浅谈Struts2中的拦截器实现责任链模式 -
liu765023051:
亲,local与remote有什么区别呢
EJB学习之三---Local和Remote接口
Oracle 中条件分歧总结:
* Decode
* IF--THEN--ELSIF---END IF
* CASE WHEN---END CASE
1.DECODE介绍
In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.
The syntax for the decode function is:
decode( expression , search , result [, search , result]... [, default] )
expression is the value to compare.
search is the value that is compared against expression.result is the value returned, if expression is equal to search.default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).
Applies To:
Oracle 9i, Oracle 10g, Oracle 11g
For example:
You could use the decode function in an SQL statement as follows:
SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;
The above decode statement is equivalent to the following IF-THEN-ELSE statement:
IF supplier_id = 10000 THEN
result := 'IBM';
ELSIF supplier_id = 10001 THEN
result := 'Microsoft';
ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';
ELSE
result := 'Gateway';
END IF;
Refer to:http://www.techonthenet.com/oracle/functions/index.php
2. IF-THEN-ELSE Statement
There are three different syntaxes for these types of statements.
Syntax #1: IF-THEN
IF condition THEN
{...statements...}
END IF;
Syntax #2: IF-THEN-ELSE
IF condition THEN
{...statements...}
ELSE
{...statements...}
END IF;
Syntax #3: IF-THEN-ELSIF
IF condition THEN
{...statements...}
ELSIF condition THEN
{...statements...}
ELSE
{...statements...}
END IF;
Here is an example of a function that uses the IF-THEN-ELSE statement:
CREATE OR REPLACE Function IncomeLevel
( name_in IN varchar2 )
RETURN varchar2
IS
monthly_value number(6);
ILevel varchar2(20);
cursor c1 is
select monthly_income
from employees
where name = name_in;
BEGIN
open c1;
fetch c1 into monthly_value;
close c1;
IF monthly_value <= 4000 THEN
ILevel := 'Low Income';
ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN
ILevel := 'Avg Income';
ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN
ILevel := 'Moderate Income';
ELSE
ILevel := 'High Income';
END IF;
RETURN ILevel;
END;
3.Case Statement
Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.
The syntax for the case statement is:
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)
condition_1 to condition_n must all be the same datatype. 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 to result_n 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:
Oracle 9i, Oracle 10g, Oracle 11g
For example:
You could use the case statement in an 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 take a 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.
For Example:
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;
* Decode
* IF--THEN--ELSIF---END IF
* CASE WHEN---END CASE
1.DECODE介绍
In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.
The syntax for the decode function is:
decode( expression , search , result [, search , result]... [, default] )
expression is the value to compare.
search is the value that is compared against expression.result is the value returned, if expression is equal to search.default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).
Applies To:
Oracle 9i, Oracle 10g, Oracle 11g
For example:
You could use the decode function in an SQL statement as follows:
SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;
The above decode statement is equivalent to the following IF-THEN-ELSE statement:
IF supplier_id = 10000 THEN
result := 'IBM';
ELSIF supplier_id = 10001 THEN
result := 'Microsoft';
ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';
ELSE
result := 'Gateway';
END IF;
Refer to:http://www.techonthenet.com/oracle/functions/index.php
2. IF-THEN-ELSE Statement
There are three different syntaxes for these types of statements.
Syntax #1: IF-THEN
IF condition THEN
{...statements...}
END IF;
Syntax #2: IF-THEN-ELSE
IF condition THEN
{...statements...}
ELSE
{...statements...}
END IF;
Syntax #3: IF-THEN-ELSIF
IF condition THEN
{...statements...}
ELSIF condition THEN
{...statements...}
ELSE
{...statements...}
END IF;
Here is an example of a function that uses the IF-THEN-ELSE statement:
CREATE OR REPLACE Function IncomeLevel
( name_in IN varchar2 )
RETURN varchar2
IS
monthly_value number(6);
ILevel varchar2(20);
cursor c1 is
select monthly_income
from employees
where name = name_in;
BEGIN
open c1;
fetch c1 into monthly_value;
close c1;
IF monthly_value <= 4000 THEN
ILevel := 'Low Income';
ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN
ILevel := 'Avg Income';
ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN
ILevel := 'Moderate Income';
ELSE
ILevel := 'High Income';
END IF;
RETURN ILevel;
END;
3.Case Statement
Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.
The syntax for the case statement is:
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)
condition_1 to condition_n must all be the same datatype. 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 to result_n 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:
Oracle 9i, Oracle 10g, Oracle 11g
For example:
You could use the case statement in an 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 take a 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.
For Example:
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;
发表评论
-
Oracle的Hash Join之探究整理
2012-04-08 15:48 11324Hash join算法原理 自从or ... -
Oracle Event 10046
2012-03-29 23:17 1599下面是一个10046的例子,可以用来分析具体的一些sql执行计 ... -
使用SQL_TRACE进行数据库诊断
2012-03-29 23:08 970From:http://www.eygle.com/archi ... -
Oracle索引之B-Tree和Bitmap索引对比
2012-03-23 18:32 2330B树索引是所有大型关系 ... -
Oracle性能优化五大工具介绍
2012-03-23 15:55 1540本文介绍了Oracle性能优化工具Oracle数据库在线数据字 ... -
Oracle事物处理中回滚段容量的问题
2012-03-23 15:33 1598在执行大事务时,有时oracle会报出如下的错误: ORA- ... -
ORACLE CASE WHEN 及 SELECT CASE WHEN的用法
2011-06-01 23:07 2279转载:http://blog.csdn.net/songsen ... -
View的作用
2010-11-11 00:53 3134这篇文章重要讲述下关 ... -
Oracle For Update 行锁
2010-11-08 23:43 1928转自:http://hi.baidu.com/mcj0127/ ... -
Mysql的存储引擎:InnoDB和MyISAM区别
2010-10-05 02:25 1339InnoDB和MyISAM是许多人在 ... -
SSMAを活用してMySQL/AccessからSQL Server/Azureへマイグレーション
2010-09-10 23:03 2302作者 Abel Avram , 翻訳者 (株)ネクストスケープ ... -
Oracle 嵌套事务与自治事务思考
2010-04-22 21:35 6209关键字 嵌套事务和自治事务的概念 嵌套事务的使用 ... -
Oracle中Cursor介绍
2010-04-21 22:09 1734关键字 概念 类型 异常处理 一 概念 游标是SQL ... -
Oracle 10g 中动态性能视图
2010-04-16 19:18 2023动态性能视图用于记录当前例程的活动。启动例程时,oracle会 ... -
Oracle 和 Sql Server中日期的显示问题
2010-03-10 02:01 2826在日常的项目中,经常遇见User需要显示不同的日期格式。当然, ... -
Oracle中RowNum的用法
2010-02-08 22:05 1776ROWNUM,是一种伪列,它根据特定记录返回一个序列化的数字。 ... -
ORACLE 分析函数解析
2010-01-17 21:29 1312分析函数是oracle 8.1.6中就引入的一个全新的概念,为 ... -
ORACLE LOB大对象处理
2010-01-16 21:42 2423ORACLE LOB大对象处理 主要是用来存储大量数据的数据库 ... -
DUMP用法
2010-01-16 09:13 1819一 DUMP():查看表中列在datafile中的存储内容,它 ... -
全角,半角互换
2010-01-12 12:08 2005对于全角和半角互换,oracle 提供了两个函数to_mult ...
相关推荐
oracle游标的总结oracle游标的总结oracle游标的总结oracle游标的总结oracle游标的总结oracle游标的总结oracle游标的总结oracle游标的总结oracle游标的总结
oracle学习心得和总结,本人看过,感觉超好
Oracle中的日期处理总结Oracle中的日期处理总结Oracle中的日期处理总结Oracle中的日期处理总结Oracle中的日期处理总结Oracle中的日期处理总结
ORACLE 中ROWNUM用法总结 整理版本,详细明了
文档中的内容是从网上资料和自己在Oracle的日常巡检中总结的Oracle日常巡检项。
Oracle数据库的学习总结一Oracle数据库的学习总结一
oracle中的函数总结 总结oracle中的常用函数。。希望可以帮助到你 。
ORACLE经典常用语句总结 ORACLE经典常用语句总结
oracle中基础知识总结,快速入门参考
Oracle复习总结Oracle复习总结Oracle复习总结Oracle复习总结Oracle复习总结Oracle复习总结
Oracle DBA常用命令总结 , 包含 复杂权限控制等等
oracle RMAN 备份恢复总结 oracle RMAN 备份恢复总结 oracle RMAN 备份恢复总结
这是本人在实践中总结的关于oracle中job的一些总结。希望能帮助大家在理论上和实际中解决一些问题。
可以帮助初级程序员更好学习oracle,同时本资源也有不足之处,望大家不吝赐教!
中软工作总结一:oracle
oracle分区表总结oracle分区表总结oracle分oracle分区表总结区表总结oracle分区表总结
本人自己总结的尚学堂oracle笔记,希望能派上用场,本资源不收资源分,大家可以随意下载
Oracle数据库备份与恢复总结
Oracle知识点总结,适合初学者,平时多联系,放在手机上,当做电子书来看。
oracle知识点总结.txt 个人对oracle的一些总结