Oracle日常记录

碰到就更新：

1、排序

1.1按指定的顺序排序

order by decode(column, 'value1', 1, 'value2', 2, 'valued', 3, 'valueN', 99);

2、隐式转换

create table test(  id varchar2(32),  name varchar2(10),  age number,  varchar_date varchar2(20),  date_date Date);

1、对于INSERT和UPDATE操作，oracle会把插入值或者更新值隐式转换为字段的数据类型。

insert into test values ('1','Jack','18')   相当于:  insert into test values(,’1’,’Jack’,to_number('18'))

2、当比较字符型和数值型的值时，oracle会把字符型的值隐式转换为数值型。

select * from test where age='19'  相当于:  select * from test where age=to_number('19')
select * from text where id=1;  相当于： select * from text where to_number(id)=1
注：如果id列建有索引此时将失效

3.当比较字符型和日期型的数据时，oracle会把字符型转换为日期型

select * from test where varchar_date > sysdate  相当于： select * from test where to_date(varchar_date,’yyyy-mm-dd hh24:mi:ss’)
select * from test where date_date>'2014-02-26 00:00:00'  相当于  select * from test where date_date>to_date('2014-02-26 00:00:00’, ’yyyy-mm-dd hh24:mi:ss’)

注意：1）当末发生隐式转换时索引有效(INDEX RANGE SCAN)      2）当字段列发生隐式转换时索引将失效，将使用全表扫描(TABLE ACCESS FULL)

总结：Oracle使用数据类型的优先级来决定隐式类型转换，原则是将优先级低的转换为优先级高的（数据类型优先级为：Number>字符类型>日期类型）。隐式转换发生在字段列上时将使索引失效。

3、Oracle中的if-->Merge into

有一个表T，有两个字段a、b，我们想在表T中做Insert或Update操作，则可以使用Merge into函数：

MERGE INTO table_name alias1 USING (table|view|sub_query) alias2ON (join condition) WHEN MATCHED THEN     UPDATE table_name     SET col1 = col_val1,            col2 = col_val2 WHEN NOT MATCHED THEN     INSERT (column_list) VALUES (column_values);

用中文来解释上面的Merge语法，就是：
在alias2中Select出来的数据，每一条都跟alias1进行 ON (join condition)的比较，如果匹配，就进行更新的操作(Update),如果不匹配，就进行插入操作(Insert)。
因此，严格意义上讲，”在一个同时存在Insert和Update语法的Merge语句中，总共Insert/Update的记录数，就是Using语句中alias2的记录数。”

oracle不支持update和insert操作时关联其他表，例如下面的代码在oracle中是无法执行的，遇到这种情况也可以使用merge into。

update a set a.xxx=b.eee from a left join b on a.bid=b.id where a.xxx='xxx'

4、分析函数-->over()

分析函数

         分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

         在主查询结果的基础上进行一定的分析，如分部门汇总，分部门求均值等等。

 数据窗口

        1. Oracle 分析函数建立在所谓的数据窗口之上，数据窗口可以理解为一个数据集合。主查询的数据可以按照不同的标准分割成不同的数据集。比如partition BY xx_id按照xx_id将主查询的数据分成N(N代表有多少个不同的xx_id)个不同的数据窗口。

         2. 其次，数据窗口内部还应该与一定的顺序通过 ORDER BY 实现

over() 函数 

    是对分析函数的一种条件解释，可以理解为就是给分析函数加条件的函数

分析函数包括有(不完全)：

SUM        ：该函数计算组中表达式的累积和MIN        ：在一个组中的数据窗口中查找表达式的最小值MAX        ：在一个组中的数据窗口中查找表达式的最大值AVG        ：用于计算一个组和数据窗口内表达式的平均值。COUNT      ：对一组内发生的事情进行累积计数-------------------------------------------------------------------------------------------------RANK        ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置DENSE_RANK  ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置FIRST       ：从DENSE_RANK返回的集合中取出排在最前面的一个值的行LAST        ：从DENSE_RANK返回的集合中取出排在最后面的一个值的行FIRST_VALUE ：返回组中数据窗口的第一个值LAST_VALUE  ：返回组中数据窗口的最后一个值。LAG         ：可以访问结果集中的其它行而不用进行自连接LEAD        ：LEAD与LAG相反，LEAD可以访问组中当前行之后的行ROW_NUMBER  ：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号-------------------------------------------------------------------------------------------------STDDEV     ：计算当前行关于组的标准偏离STDDEV_POP ：该函数计算总体标准偏离，并返回总体变量的平方根STDDEV_SAMP：该函数计算累积样本标准偏离，并返回总体变量的平方根VAR_POP    ：该函数返回非空集合的总体变量（忽略null）VAR_SAMP   ：该函数返回非空集合的样本变量（忽略null）VARIANCE   ：如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMPCOVAR_POP  ：返回一对表达式的总体协方差COVAR_SAMP ：返回一对表达式的样本协方差CORR       ：返回一对表达式的相关系数-------------------------------------------------------------------------------------------------CUME_DIST   ：计算一行在组中的相对位置NTILE       ：将一个组分为"表达式"的散列表示PERCENT_RANK：和CUME_DIST（累积分配）函数类似PERCENTILE_DISC：返回一个与输入的分布百分比值相对应的数据值PERCENTILE_CONT：返回一个与输入的分布百分比值相对应的数据值RATIO_TO_REPORT：该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比REGR_ (Linear Regression) Functions：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用

5、分组排序函数-->rank/dense_rank/row_number() over()

函数的完整语句是：

select * from (select rank()/dense_rank()/row_number() over(partition by column1 order by column2 desc) rk,a.* from a) twhere t.rk<=1;

可以看出，over()函数可以包含3个子句：三个分析子句:分组(partition by), 排序(order by), 窗口(rows/range) 

贴个例子来描述rank()/dense_rank()/row_number() over()三种函数的区别：

create table students_test(id number(15,0),nm varchar2(16),course varchar2(16),score number(5,2));insert into students_test values(1, '张三', '语文', 80 );insert into students_test values(2, '张三', '数学', 80 );insert into students_test values(3, '张三', '英语', 89 );insert into students_test values(4, '张三', '物理', 68 );insert into students_test values(5, '李四', '语文', 80 );insert into students_test values(6, '李四', '数学', 70 );insert into students_test values(7, '李四', '英语', 60 );insert into students_test values(8, '李四', '物理', 65 );insert into students_test values(9, '王五', '语文', 75 );insert into students_test values(10, '王五', '数学', 58 );insert into students_test values(11, '王五', '英语', 58 );insert into students_test values(12, '王五', '物理', 90 );insert into students_test values(13, '赵六', '语文', 89 );insert into students_test values(14, '赵六', '数学', 90 );insert into students_test values(15, '赵六', '英语', 90 );insert into students_test values(16, '赵六', '物理', 89 );commit;

执行如下语句：

select t.nm,t.course,t.score,    rank() over(partition by course order by score desc) rank,    dense_rank() over(partition by course order by score desc) dense_rank,    row_number() over(partition by course order by score desc) row_numberfrom students_test t where course in ('数学','语文')

从上面的例子可以看出：

①ROW_NUMBER()：

Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。 

②DENSE_RANK()：

Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。 

③RANK()：

Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

6、Oracle中in函数的陷阱

异常：ORA-01795: maximum number of expressions in a list is 1000 

Oracle的In-list有1000个元素的限制。如果in中包含的元素个数超过1000，就会出现如上错误。

对于上述问题能够马上想到的解决办法有两个：

1、将元素拆分成多组不超过1000的组，一个in改成：in(...) or in(...) ...

2、使用exist替换

两个方法的比较：

1、改用多个的可读性强一些。但似乎也就只有这个有点，因为一般in中的元素超过1000，使用in的效率就不是很高了。

亲测的使用in(...) or in(...) ...的方式，1100个元素，耗时将近1秒。

2、对于查询表是小表的情况，exist的效率完爆in。否则还是建议使用方法1。具体为什么可以详细了解in和exist的区别。