oracle 笔记
Oracle DUAL
如果你想查看一下数据库的时间该怎么办呢？你需要执行一个 SQL 语句，但是 SQL 语句语法规定需要指定一个表，为此 Oracle 设计了一个只有一行一列的表 DUAL，我们可以使用这个表来执行一些不需要表的 SQL 语句。
SELECT CURRENT_DATE FROM DUAL;Oracle DATE
DATE 由以下部分组成
组成部分 默认值
year 当前年
month 当前月
day 01
hour 0
minute 0
second 0怎么会有默认值呢？看了下面你就知道了。
CREATE TABLE TEST
(
COL DATE
);
-- 语法1：日期必须是环境变量 NLS_DATE_FORMAT 指定的格式
-- 查询：SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';
INSERT INTO TEST VALUES ('13-12月-14');
-- 语法2：DATE 'YYYY-MM-DD'
INSERT INTO TEST VALUES (DATE '2014-12-13');
-- 语法3：TO_DATE 方法
INSERT INTO TEST VALUES (TO_DATE('2014-12-13', 'YYYY-MM-DD'));
INSERT INTO TEST VALUES (TO_DATE('2014-12-13 20:30:18', 'YYYY-MM-DD HH24:MI:SS'));
-- SYSDATE 返回当前系统时间
INSERT INTO TEST VALUES (SYSDATE);
-- CURRENT_DATE 返回当前会话时区的时间
INSERT INTO TEST VALUES (CURRENT_DATE);
-- 变态的 Oracle 颠覆了我们的观念，DATE 怎么能包含时分秒呢？这将给查询带来问题
SELECT * FROM TEST WHERE COL = DATE '2014-12-13';
-- 为了确保查询没有问题，我们不得不把 DATE 条件转成范围扫描
SELECT * FROM TEST WHERE COL >= DATE '2014-12-13' AND COL < DATE '2014-12-14';
-- 我们也可以使用 TRUNC 去除时，分，秒，不过这将使索引失效
SELECT * FROM TEST WHERE TRUNC(COL) = DATE '2014-12-13';其他数据类型
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECONDOracle ROWNUM
首先，我们准备一下测试数据。
CREATE TABLE TEST
(
NAME VARCHAR2(20),
BIRTHDAY DATE
);
INSERT INTO TEST VALUES ('张三', DATE '2014-12-14');
INSERT INTO TEST VALUES ('李四', DATE '2013-12-14');
INSERT INTO TEST VALUES ('王五', DATE '2012-12-14');
INSERT INTO TEST VALUES ('赵六', DATE '2011-12-14');Oracle 在查询数据的时候会为每一行赋一个行号，这个行号会存储在一个叫做 ROWNUM 伪列中，我们可以通过这个伪列来限定返回的结果集。下面的 SQL 返回两条数据。
SELECT * FROM TEST WHERE ROWNUM < 3;值得注意的是 Oracle 并不是先查询数据，后赋行号，而是查到一条赋一条，为什么这么说呢？下面让我们查询一下年龄最大的两个人是谁？
SELECT * FROM TEST WHERE ROWNUM <= 2 ORDER BY BIRTHDAY;
结果如下：
NAME BIRTHDAY
李四 14-12月-13
张三 14-12月-14很明显，这不是我们想要的结果，它是先返回两条数据，后对这两条数据排序。那这个 SQL 应该怎么写呢？答案是子查询。
SELECT * FROM (SELECT * FROM TEST ORDER BY BIRTHDAY) WHERE ROWNUM <= 2;
结果如下：
NAME BIRTHDAY
赵六 14-12月-11
王五 14-12月-12Oracle BETWEEN 临界
好多人搞不清楚 BETWEEN 条件到底包不包含临界值,呵呵,今天我负责任的告诉你,下面的语句是等价的.
x BETWEEN 1 AND 10
x >=1 AND x<=10Oracle 尽量避免在 SQL语句的WHERE子句中使用函数
在 WHERE 子句中应该尽量避免在列上使用函数，因为这样做会使该列上的索引失效，影响SQL 语句的性能。即使该列上没有索引，也应该避免在列上使用函数。
现在要求你把2009-9-24 注册的用户都查出来，怎么办？可能有人会这么写：
SELECT * FROM EMPLOYEE WHERE TO_DATE(REGISTERDATE) = DATE '2009-09-24';上述语句完全正确，但是假如 REGISTERDATE 列上有索引，那么会使索引失效，即使没有索引，也不应该这么做。那么到底如何处理呢？答案是将它转化为范围扫描，如下：
SELECT * FROM EMPLOYEE WHERE REGISTERDATE >= TIMESTAMP '2009-9-24 00:00:00.0' AND REGISTERDATE < TIMESTAMP '2009-9-25 00:00:00.0';Oracle 尽量避免在 SQL语句中使用 LIKE
前面，我们介绍了尽量避免在SQL语句的WHERE子句中使用函数，因为这样做会使该字段上的索引失效，影响SQL 语句的性能。基于同样的道理，我们也应该避免使用LIKE。
现在要求你把身份证号码开头是2102（大连人）查出来，怎么办？我们很自然的会这么写：
SELECT * FROM PEOPLE WHERE MYNUMBER LIKE '2102%';上述语句完全正确，只可惜性能不好，那么到底如何处理呢？答案是将它转化为范围扫描，如下：
SELECT * FROM PEOPLE WHERE MYNUMBER>='210200000000000000' AND MYNUMBER<'210300000000000000';Oracle Exists 和 IN
exists表示()内子查询语句返回结果不为空说明where条件成立就会执行主sql语句，如果为空就表示where条件不成立，sql语句就不会执行。 not exists和exists相反，子查询语句结果为空，则表示where条件成立，执行sql语句，否则不执行。
create table test_table_A(
A_id number(10) primary key,
A_name varchar2(10) not null
);
create table test_table_B(
B_id number(10) primary key,
owner number(10),
B_name varchar2(10) not null
);
insert into test_table_A values(1001,'tom');
insert into test_table_A values(1002,'jack');
insert into test_table_A values(1003,'jenny');
insert into test_table_B values(2001,1002,'cup');
insert into test_table_B values(2002,1001,'car');
insert into test_table_B values(2003,1002,'computer');先来看看 exists 操作
SELECT *
FROM TEST_TABLE_A A
WHERE EXISTS(SELECT 1 FROM TEST_TABLE_B B WHERE B.OWNER = A.A_id);执行结果：
# A_ID A_NAME
1 1001 tom
2 1002 jack再来看看 in 操作
SELECT *
FROM TEST_TABLE_A A
WHERE A.A_id IN (SELECT B.OWNER FROM TEST_TABLE_B B);执行结果是一样的。
exists与in的效率问题
使用EXISTS，会首先检查主查询，然后运行子查询，当子查询找到第一个匹配项时即开始下一次操作。 使用IN，会先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中，再执行主查询与临时表运算。
结论：
- select * from T1 where exists(select 1 from T2 where T1.a=T2.a);
- select * from T1 where T1.a in (select T2.a from T2); 当T1数据量小而T2数据量非常大时（T1 < T2），即子查询更耗费时间时，exists的查询效率更高。 当T1数据量非常大而T2数据量小时（T1 > T2），即主查询更耗费时间时，in 的查询效率高。
Oracle NULL 和 NOT IN
NULL 是一个很神奇的值，它颠覆了二值逻辑结构（即：真和假），出现了三值逻辑结构（即：真、假和未知）。由于null，我们的 SQL 语句很有可能出现意想不到的结果，试一试下面的语句吧。
CREATE TABLE test(
ID NUMBER(10,0),
NAME VARCHAR2(10)
);
INSERT INTO test values (1, '张三');
INSERT INTO test values (2, '李四');
SELECT * FROM test WHERE ID NOT IN (1, NULL);你可能认为我们应该查询出李四，但是结果却是什么也查不出来。也许你认为不会有人那么傻，在 NOT IN 里写个 NULL，但是如果 NOT IN 里是一个子查询，而子查询的结果集里有 NULL 值呢？
Oracle 将null值转化为其他值
我个人认为数据库中不应该有null值，因为他颠覆了二值逻辑结构（即：真和假），出现了三值逻辑结构（即：真、假和未知）。由于null，我们的SQL语句很有可能出现意想不到的结果。此外null值和其他值进行数值运算的时候也会带来问题。但是，有时候有些事情并不是我们能够控制和改变的，作为一名真正的程序员，应该敢于面对最垃圾的数据库设计。下面给大家介绍如何将null值转化为其他值。
CREATE TABLE EMPLOYEE
(
NAME VARCHAR2(20) NOT NULL, -- 姓名
SALARY NUMBER, -- 基本工资
BONUS NUMBER -- 奖金
);
INSERT INTO EMPLOYEE VALUES ('张三', 3000.0, NULL);
INSERT INTO EMPLOYEE VALUES ('李四', 4000.0, 0.0);
INSERT INTO EMPLOYEE VALUES ('王五', 5000.0, 1000.0);
-- 下面几条语句是等价的
SELECT CASE WHEN BONUS IS NULL THEN 0.0 ELSE BONUS END FROM EMPLOYEE;
SELECT COALESCE(BONUS, 0.0) FROM EMPLOYEE;
SELECT NVL(BONUS, 0.0) FROM EMPLOYEE;
-- 下面两条语句是等价的
SELECT CASE WHEN BONUS IS NOT NULL THEN BONUS ELSE 0.0 END FROM EMPLOYEE;
SELECT NVL2(BONUS, BONUS, 0.0) FROM EMPLOYEE;假设你要查找总工资(基本工资+奖金)大于等于3000元的员工，我们很自然的会写出下面的语句：
SELECT * FROM EMPLOYEE WHERE SALARY + BONUS >= 3000.0;但是很不幸，这条语句并不是永远正确，当SALARY或BONUS有一个值是null的时候，我们很可能会漏掉部分数据（会漏掉哪些数据呢？朋友们自己思考一下），这就是我认为数据库中不应该有null值的原因之一，如果你不是决策者，无法改变数据库设计，我们可以这样写：
SELECT * FROM EMPLOYEE WHERE NVL(SALARY, 0.0) + NVL(BONUS, 0.0) >= 3000.0;有没有更简单的办法呢？答案是肯定的。
-- 如果条件是假或未知，LNNVL 函数返回真
SELECT * FROM EMPLOYEE WHERE LNNVL(SALARY + BONUS < 3000.0);知道了如何将 null 转换成其他值，现在让你把其他值转成 null 该怎么办呢？呵呵试一试下面的语句吧。
SELECT NULLIF(BONUS,0.0) FROM EMPLOYEE;
SELECT CASE WHEN BONUS = 0.0 THEN NULL ELSE BONUS END FROM EMPLOYEE;还有一个函数用来将 BINARY_FLOAT_NAN 和 BINARY_DOUBLE_NAN 转成其他值。
SELECT NANVL(BINARY_FLOAT_NAN, 0.0) FROM DUAL;
SELECT NANVL(BINARY_DOUBLE_NAN, 0.0) FROM DUAL;Oracle COUNT
CREATE TABLE STUDENT
(
ID NUMBER(10, 0) NOT NULL, -- 学号
NAME VARCHAR(20) NOT NULL, -- 姓名
BIRTHDAY DATE -- 生日
);
INSERT INTO STUDENT (ID,NAME,BIRTHDAY) VALUES (1, '张三', DATE '1991-1-1');
INSERT INTO STUDENT (ID,NAME,BIRTHDAY) VALUES (2, '李四', DATE '1991-1-1');
INSERT INTO STUDENT (ID,NAME,BIRTHDAY) VALUES (3, '王五', NULL);现在让你统计以下这个表有多少条数据，怎么办？很简单，我们有下面三种方法。
-- 结果 3
SELECT COUNT(*) FROM STUDENT;
-- 结果 3
SELECT COUNT(1) FROM STUDENT;
-- 结果 2
SELECT COUNT(BIRTHDAY) FROM STUDENT;遗憾的是方法3统计出的数据并不总是正确的，那是因为 COUNT 函数会忽略 NULL 值。 所以，千万不要统计可以为 NULL 的列。
Oracle ROLLUP 和 CUBE
不知道大家听没听说过小计，总计等会计词语，如果你做过报表，一定不会陌生。
CREATE TABLE employee
(
name NVARCHAR2(10),
gender NCHAR(1),
country NVARCHAR2(10),
department NVARCHAR2(10),
salary NUMBER(10)
);
INSERT INTO employee VALUES ('张三','男','中国','市场部',4000);
INSERT INTO employee VALUES ('李四','男','中国','市场部',5000);
INSERT INTO employee VALUES ('王五','女','美国','市场部',3000);
INSERT INTO employee VALUES ('赵红','男','中国','技术部',2000);
INSERT INTO employee VALUES ('李白','女','中国','技术部',5000);
INSERT INTO employee VALUES ('王蓝','男','美国','技术部',4000);通常我们是在生成报表时算小计或总计的，其实通过 SQL 就可以实现。
SELECT
country,
department,
round(avg(salary), 2) AVG
FROM
employee
GROUP BY ROLLUP(country, department);结果如下：
country department AVG
中国 市场部 4500
中国 技术部 3500
中国 null 4000
美国 市场部 3000
美国 技术部 4000
美国 null 3500
null null 3833.33上面的 null 是不是让人看着很不爽，要是能返回有意义的值就更好了，为此 Oralce 提供几个函数实现这个功能。
SELECT
DECODE(GROUPING(country), 1, '总计', country) AS country,
DECODE(GROUPING(department), 1, '小计', department) AS department,
round(avg(salary), 2) AVG
FROM
employee
GROUP BY ROLLUP(country, department);结果如下：
country department AVG
中国 市场部 4500
中国 技术部 3500
中国 小计 4000
美国 市场部 3000
美国 技术部 4000
美国 小计 3500
总计 小计 3833.33还有个 CUBE 关键字，它比 ROLLUP 语句返回更多的内容，以下是将上面语句的 ROLLUP 替换为 CUBE 后得到的结果：
SELECT
DECODE(GROUPING(country), 1, '总计', country) AS country,
DECODE(GROUPING(department), 1, '小计', department) AS department,
round(avg(salary), 2) AVG
FROM
employee
GROUP BY CUBE(country, department);结果如下：
country department AVG
总计 小计 3833.33
总计 市场部 4000
总计 技术部 3666.67
中国 小计 4000
中国 市场部 4500
中国 技术部 3500
美国 小计 3500
美国 市场部 3000
美国 技术部 4000Oracle 正则表达式
大家对 LIKE 语句应该都很熟悉，它是用来做模糊查询的。Oracle 还提供了 REGEXP_LIKE，让我们可以通过正则表达式来做模糊查询，下面是一个简单的例子。
CREATE TABLE EMPLOYEE
(
ID NUMBER(10),
NAME VARCHAR2(20),
EMAIL VARCHAR2(60)
);
INSERT INTO EMPLOYEE VALUES (1, 'zhang san', 'san.zhang@163.com;san.zhang@qq.com');
INSERT INTO EMPLOYEE VALUES (2, 'wang mazi', 'mazi.wang@163.com');假设让你查找一下姓 zhang 的员工，注意 zhang 不区分大小写，怎么办？我们可以使用 LIKE， 也可以使用 REGEXP_LIKE。
--- 查找一下姓 zhang 的员工，注意 zhang 不区分大小写
SELECT * FROM EMPLOYEE WHERE UPPER(NAME) LIKE 'ZHANG%';
SELECT * FROM EMPLOYEE WHERE REGEXP_LIKE(NAME, '^zhang.*$', 'i');如果你还不熟悉正则表达式，请参考本人另一系列文章“正则表达式精萃”。上面的 i 指的是匹配模式，Oracle 支持如下的匹配模式。可以指定多个匹配模式的哦。
i 表示不区分大小写
c 表示区分大小写
n 单行模式,也叫点号通配模式
m 多行模式，又称增强的行锚点模式
x 注释模式除此之外，Oracle 还提供了以下几个支持正则表达式的函数，用来对字符串进行操作。
REGEXP_SUBSTR 通过正则表达式查找子字符串
REGEXP_INSTR 通过正则表达式查找子字符串的位置
REGEXP_COUNT 通过正则表达式查找子字符串的数量
REGEXP_REPLACE 通过正则表达式替换下面是一个简单的例子。
-- 查找第一个 EMAIL 地址
SELECT NAME, REGEXP_SUBSTR(EMAIL,'^[^;]+;?') FROM EMPLOYEE;
-- 查找第一个 EMAIL 地址
SELECT NAME, REGEXP_REPLACE(EMAIL,';.*$') FROM EMPLOYEE;
-- 查找 ; 的位置
SELECT NAME, REGEXP_INSTR(EMAIL,';') FROM EMPLOYEE;
-- 查找 @ 的数量
SELECT NAME, REGEXP_COUNT(EMAIL,'@') FROM EMPLOYEE;Oracle 采集样本数据
我们经常会遇到这样的情况，想看看某个表中的若干条数据，如10 条。我们可以这么写：
SELECT * FROM ZT_CYCLE_JL WHERE ROWNUM <= 10;不知道你注意到没有，以上这条语句无论你执行多少遍，结果集是不变的。那么我想每次随机的查询10 条记录看看，该怎么处理呢？可以使用下面的SQL：
--- 随机查询10条记录
SELECT *
FROM (
SELECT *
FROM ZT_CYCLE_JL
ORDER BY DBMS_RANDOM.RANDOM()
)
WHERE ROWNUM <= 10;上面是最简单的采集样本数据的方法，更为专业的是使用 SAMPLE 采集样本数据。那么，为什么要采集样本数据呢？主要原因是当我们对海量数据进行分组统计时，即费时又费力，这时候，我们可以采集样本数据，然后对样本数据进行统计，以预测整体趋势。
-- 采集 10% 的样本数据
SELECT * FROM ZT_CYCLE_JL SAMPLE (10);
-- 指定 SEED 后，多次执行下面的语句，结果保持不变
SELECT * FROM ZT_CYCLE_JL SAMPLE (10) SEED (1);Oracle 行转列
在网上看到这样一个问题
班级 科目 分数
1 语文 8800
1 数学 8420
1 英语 7812
2 语文 8715
2 数学 8511
2 英语 8512要求转换成下面这样的结果
班级 语文 数学 英语
1 8800 8420 7812
2 8715 8511 8512这是一个非常经典的 4 属性的表设计模式，顾名思义，这样的表一般有四列，分别是：entity_id, attribute_name, attribute_type, attribute_value ，这样的设计使我们添加字段非常容易，如：我们想添加一个物理成绩是非常简单的，我们只要向表中插入一条记录即可。但是，这样的设计有一个非常严重的问题，那就是：查询难度增加，查询效率非常差。要想实现上面的查询有一个原则，那就是：通过 case 语句创造虚拟字段，使结果集成为二维数组，然后应用聚合函数返回单一记录。
create table entity
(
entity_id number(9,0),
attribute_name varchar2(10),
attribute_type varchar2(10),
attribute_value varchar2(10)
);
insert into entity values (1, 'Chinese', 'NUMBER', '8800');
insert into entity values (1, 'Math', 'NUMBER', '8420');
insert into entity values (1, 'English', 'NUMBER', '7812');
insert into entity values (2, 'Chinese', 'NUMBER', '8715');
insert into entity values (2, 'Math', 'NUMBER', '8511');
insert into entity values (2, 'English', 'NUMBER', '8512');
select
Class,
max(Chinese) Chinese,
max(Math) Math,
max(English) English
from (
select
entity_id class,
case attribute_name when 'Chinese' then to_number(attribute_value) else 0 end Chinese,
case attribute_name when 'Math' then to_number(attribute_value) else 0 end Math,
case attribute_name when 'English' then to_number(attribute_value) else 0 end English
from entity
) tt
group by tt.class
order by 1;结果如下：
# CLASS CHINESE MATH ENGLISH
1 1 8800 8420 7812
2 2 8715 8511 8512再来看看子查询
select
entity_id class,
case attribute_name when 'Chinese' then to_number(attribute_value) else 0 end Chinese,
case attribute_name when 'Math' then to_number(attribute_value) else 0 end Math,
case attribute_name when 'English' then to_number(attribute_value) else 0 end English
from entity结果如下：
# CLASS CHINESE MATH ENGLISH
1 1 8800 0 0
2 1 0 8420 0
3 1 0 0 7812
4 2 8715 0 0
5 2 0 8511 0
6 2 0 0 8512以上思想来自 <SQL 语言艺术> 的第11 章，想了解更全面的信息，大家可以参考。
Oracle 集合操作符
Oracle 支持如下几个集合操作符。
UNION 用来求两个集合的并集，并去掉重复值
UNION ALL 用来求两个集合的并集
INTERSECT 用来求两个集合的交集，并去掉重复值
MINUS 用来求在第一个集合中存在，而在第二个集合中不存在的记录，并去掉重复值下面我们先来准备测试数据。
CREATE TABLE TEST_LEFT
(
COL CHAR
);
INSERT INTO TEST_LEFT VALUES ('A');
INSERT INTO TEST_LEFT VALUES ('A');
INSERT INTO TEST_LEFT VALUES ('B');
INSERT INTO TEST_LEFT VALUES ('B');
INSERT INTO TEST_LEFT VALUES ('C');
CREATE TABLE TEST_RIGHT
(
COL CHAR
);
INSERT INTO TEST_RIGHT VALUES ('A');
INSERT INTO TEST_RIGHT VALUES ('B');
INSERT INTO TEST_RIGHT VALUES ('B');
INSERT INTO TEST_RIGHT VALUES ('D');
INSERT INTO TEST_RIGHT VALUES ('E');下面我们通过一个例子来对比一下它们直接的不同。
---UNION
SELECT * FROM TEST_LEFT
UNION
SELECT * FROM TEST_RIGHT;
---结果
A
B
C
D
E
---UNION ALL
SELECT * FROM TEST_LEFT
UNION ALL
SELECT * FROM TEST_RIGHT;
---结果
A
A
B
B
C
A
B
B
D
E
---INTERSECT
SELECT * FROM TEST_LEFT
INTERSECT
SELECT * FROM TEST_RIGHT;
---结果
A
B
---MINUS
SELECT * FROM TEST_LEFT
MINUS
SELECT * FROM TEST_RIGHT;
---结果
C大家对比一下它们之间的结果就可以看出它们之间的区别，不过有个问题需要注意:
UNION 和 INTERSECT 两别集合可以互换的，但是 MINUS 互换将有不同的结果，如下：
---语句1
SELECT * FROM TEST_LEFT
MINUS
SELECT * FROM TEST_RIGHT;
---结果
C
---语句2
SELECT * FROM TEST_RIGHT
MINUS
SELECT * FROM TEST_LEFT;
---结果
D
EOracle CASE
大家对 IF ELSE 语句应该都很熟悉吧，它是用来对过程进行控制的。在 SQL 的世界中 CASE 语句有类似的效果。下面简单的介绍 CASE 语句的用法。考虑下面的情况，假设有个 USER_INFO 表，定义如下：
CREATE TABLE USER_INFO
(
NAME VARCHAR2(20) NOT NULL, ---姓名
GENDER NUMBER(1,0), ---性别（1、男 2、女）
BIRTHDAY DATE ---生日
);
INSERT INTO USER_INFO VALUES ('张三', 1, DATE '2014-12-27');
INSERT INTO USER_INFO VALUES ('李四', 2, DATE '2014-12-27');CASE使用案例 1
把 USER_INFO 表导出生成一个文件，要求性别为男或女，而不是1和2，怎么办？我们可以用如下的语句处理：
SELECT
NAME,
CASE GENDER
WHEN 1 THEN '男'
ELSE '女'
END AS GENDER,
BIRTHDAY
FROM USER_INFO;CASE使用案例 2
假设 USER_INFO 目前没有值，然后你往 USER_INFO 导入了一批数据，但是很不幸，错把男设置成为2，而把女设置成为1，现在要求你变换过来，怎么办？
方法1
使用三条语句，先把2更新成3，接着把1更新成2，最后把3更新成1，是不是很麻烦？
UPDATE USER_INFO SET GENDER=3 WHERE GENDER=2;
UPDATE USER_INFO SET GENDER=1 WHERE GENDER=3;
UPDATE USER_INFO SET GENDER=2 WHERE GENDER=1;方法2
使用CASE语句
UPDATE USER_INFO SET GENDER=
(
CASE GENDER
WHEN 1 THEN 2
WHEN 2 THEN 1
ELSE GENDER
END
);细心的朋友可能已经发现了，上面的方法1 的三条语句的执行顺序有问题，没错，是我故意那些写的，仅仅是把1变成2，把2变成1就那么麻烦，而且很容易出错，想象一下，如果有很多这样的值需要变换，那是一种什么样的情况。还好，我们有CASE语句，有好多这样的值需要变换，CASE语句也不会存在问题。
CASE使用案例 3
假设让你把张三的生日更新成1949-10-1，李四的生日更新成1997-7-1等，类似这样的更新有很多。该怎么办呢？非常简单，大多数人会这么做。
UPDATE USER_INFO SET BIRTHDAY = DATE '1949-10-1' WHERE NAME = '张三';
UPDATE USER_INFO SET BIRTHDAY = DATE '1997-7-1' WHERE NAME = '李四';当 USER_INFO 表的数据量非常大，而 NAME 字段上又没有索引时，每条语句都要进行全表扫描，如果这样的语句有很多，效率会非常差，这时候我们可以用 CASE 语句，如下：
UPDATE USER_INFO SET BIRTHDAY =
(
CASE NAME
WHEN '张三' THEN DATE '1949-10-1'
WHEN '李四' THEN DATE '1997-7-1'
ELSE BIRTHDAY
END
)
WHERE NAME in ('张三','李四');CASE 语句的形式
事实上，CASE 语句有两种形式。
SELECT
-- 简单 CASE 语句（Simple CASE）
CASE GENDER
WHEN 1 THEN '男'
ELSE '女'
END AS GENDER,
-- 查询 CASE 语句（Searched CASE）
CASE
WHEN GENDER = 1 THEN '男'
ELSE '女'
END AS GENDER
FROM USER_INFO;DECODE 函数
此外， Oracle 还提供了一个函数来达到和 CASE 语句相同的效果。
SELECT DECODE(GENDER, 1, '男', 2, '女', '未知') FROM USER_INFO;Oracle 递归查询
递归查询的一个典型的例子是对树状结构的表进行查询，考虑如下的情况：
论坛首页
--数据库开发
----DB2
------DB2 文章1
--------DB2 文章1 的评论1
--------DB2 文章1 的评论2
------DB2 文章2
----Oracle
--Java 技术以上是一个论坛的典型例子，下面我们新建一个表来存储以上信息。
CREATE TABLE BBS
(
PARENT_ID NUMBER(9, 0) NOT NULL,
ID NUMBER(9, 0) NOT NULL,
NAME VARCHAR2(200) NOT NULL -- 板块、文章、评论等。
);
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (0,0,'论坛首页');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (0,1,'数据库开发');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (1,11,'DB2');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (11,111,'DB2 文章1');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (111,1111,'DB2 文章1 的评论1');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (111,1112,'DB2 文章1 的评论2');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (11,112,'DB2 文章2');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (1,12,'Oracle');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (0,2,'Java 技术');现在万事兼备了，我们开始查询吧。假设现在让你查询一下‘DB2’的所有文章，有人说，这还不简单，如下这样就可以了。
SELECT * FROM BBS WHERE PARENT_ID=(SELECT ID FROM BBS WHERE NAME='DB2');答案完全正确。那么，现在让你查询一下DB2 的所有文章及评论，怎么办？传统的方法就很难查询了，这时候递归查询就派上用场了，如下：
SELECT
-- LEVEL 是一个伪列，表示当前行所属层次，从 1 开始
LEVEL,
-- CONNECT_BY_ISLEAF 表示当前行是否是叶子节点。1 表示叶子节点，0 表示非叶子节点。
CONNECT_BY_ISLEAF,
-- CONNECT_BY_ROOT 是一个操作符，用来修饰列，表示该列的值是根节点的值。
CONNECT_BY_ROOT NAME,
-- SYS_CONNECT_BY_PATH 是一个函数，返回层次路径
SYS_CONNECT_BY_PATH(NAME, '/')
FROM
BBS
-- START WITH 用来表示起始行
START WITH NAME = 'DB2'
-- CONNECT BY 用来指定父子连接条件
-- PRIOR 是一个操作符，用来修饰列，表示该列是父行中的列
CONNECT BY PRIOR ID = PARENT_ID
-- ORDER SIBLINGS BY 对同一层次中的行进行排序
ORDER SIBLINGS BY NAME结果如下：
# LEVEL CONNECT_BY_ISLEAF CONNECT_BY_ROOTNAME SYS_CONNECT_BY_PATH(NAME,'/')
1 1 0 DB2 /DB2
2 2 0 DB2 /DB2/DB2 文章1
3 3 1 DB2 /DB2/DB2 文章1/DB2 文章1 的评论1
4 3 1 DB2 /DB2/DB2 文章1/DB2 文章1 的评论2
5 2 1 DB2 /DB2/DB2 文章2其实递归查询还有好多其他用途，如： 你想生成从 1 到 100 的数字，怎么办？看看下面的 SQL 吧。
SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 100;再如：你想生成从 1 到 100 的数字，但是要求它们用逗号分割。
SELECT
LISTAGG(R, ',') WITHIN GROUP (ORDER BY R)
FROM
(SELECT ROWNUM R FROM DUAL CONNECT BY ROWNUM <= 100)
GROUP BY 1Oracle Merge 案例
假设现在有下面这个表用来存放股票的价格，我们需要保存一个月的历史记录。
CREATE TABLE price_history
(
security_id NUMBER(10, 0),
price NUMBER(10, 4),
price_date DATE,
rank NUMBER(2, 0)
);由于这个表的数据量非常大，导致我们想取最新的价格非常慢，于是我们有下面这个表存放最新价格。
CREATE TABLE price
(
security_id NUMBER(10, 0),
price NUMBER(10, 4)
);现在问题来了，怎么把 price_history 的最新价格搞到 price 表里呢？也许你想说根据 price_date，但是 price_date 并不一定是当前日期，而且我们还需要根据 rank 排序，怎么办？实在不行，一条一条处理？来吧，是时候展现 Oracle 强大功能的时候了。
MERGE INTO price p
USING (
SELECT security_id, price FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY security_id ORDER BY price_date DESC, rank DESC) AS ROW_ID,
security_id,
price
FROM
price_history
) WHERE ROW_ID = 1
) ph
ON (p.security_id = ph.security_id)
-- 匹配时更新
WHEN MATCHED THEN UPDATE SET p.price = ph.price
-- 不匹配时新增
WHEN NOT MATCHED THEN INSERT VALUES (ph.security_id, ph.price);