oracle10G学习笔记(SQL开发指南)

1.0 BINARY_FLOAT和BINARY_DOUBLE
BINARY_FLOAT可以存储一个单精度的32位浮点数；
BINARY_DOUBLE可以存储一个双精度的64位浮点数；
f和d分别用于表明一个数字是BINARY_FLOAT和BINARY_DOUBLE类型的。
eg.35.5f,65.8d
2.0 DATE关键字
DATE关键字向数据库提供一个日期文本字符串，此日期字符串格式为YYYY-MM-DD。可以将该文本字符串转换为日期型，相当于TO_DATE(文本字符串,'YYYY-MM-DD');

3.0 ANY和ALL
ANY和ALL是比较操作符。
ANY操作符将一个值与列表中的任何值进行比较，此时必须在ANY前添加一个= 、<>、>、<、>=、<=操作符。
ALL操作符将一个值与列表中的所有值进行比较，此时必须在ANY前添加一个= 、<>、>、<、>=、<=操作符。

4.0 ESCAPE
ESCAPE关键字说明其指定的字符是一个转义字符，其指定字符后的通配符在进行匹配时，作为普通字符进行文本匹配，而不当作通配符。
eg. SELECT first_name FROM cus_inf WHERE first_name LIKE '%a/_product%' ESCAPE '/';

5.0 连接条件和连接类型
连接条件可以分为：等连接和不等连接。
等连接在连接中使用等于操作符；不等连接在连接中使用除等号之外的其他操作符，如：BETWEEN AND；

连接类型可分为内连接，外连接和自连接。自连接是一种特殊的内连接，笛卡尔积是一种不带条件的内连接；

左外连接和右外连接
左外连接中，外连接符在等于操作符的右边，右外连接中，外连接符在等于操作符的左边。
在进行外连接时，先将符合条件的记录列出，然后再列出外连接符相反一边的连接键字段的值，如果在外连接符一边的连接表中没有匹配记录，则可对应空值；

6.0 ANSI SQL/92连接语法。
内连接：FROM table1 INNER JOIN table2 ON condition;
外连接：FROM table1 LEFT|RIGHT|FULL OUTER JOIN table2 ON condition;
笛卡尔积：FROM table1 CROSS JOIN table2 ;
用USING简化连接：如果table1和table2中连接键字段名相同，而且连接是等连接，就可以使用USING来取代ON 。
USING(连接关键字);
eg.ON table1.column1 = table2.column1 AND table1.column2=table2.column2可以简写为：
USING(column1,column2);
注意：USING中的连接关键字，不得使用别名和表名。

7.0 MEDIAN(X):取中间值

8.0 NOT IN 和 NOT EXISTS
当一个值列表包含一个空值时，NOT EXISTS返回TRUE，而NOT IN 则返回FALSE。

9.0 集合操作符
UNION　ALL:返回各个查询检索出的所有行，包括重复行；(并)
UNION： 返回各个查询检索出的所有行，不包括重复行；(并)
INTERSECT：返回各个查询检索出的共有行；(交)
MINUS ：返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的记录(差)

10.0 TRANSLATE(x,from_string,to_string)函数
在X中查询from_string字符，并将其转换成to_string中对应的字符。

11.0 CONNECT BY 和START WITH
SELECT [LEVEL],column,expression,.... FROM table [WHERE where_clause] [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
LEVEL是伪列，代表位于树的第几层。对于根节点而言，LEVEL为1；
start_condition定义了层次化查询的起点。编写层次化查询时必须指定START WITH；
prior_condition定义了父行和子行之间的关系。编写层次化查询时必须定义CONNECT BY PRIOR；
START WITH子句可以使用子查询。
通过交换你节点与子节点在CONNECT BY PRIOR中的顺序可以实现从下向上遍历；
通过START WITH 子句，可以从任何节点开始遍历；
可以通过WHERE 子句从查询树中除去某个特点的节点；
如果要将准备除去节点的分支也去掉，则在CONNECT BY PRIOR子句中增加相应条件；

12.0 GROUP BY扩展
ROLLUP的用法：GROUP BY ROLLUP(column1,column2,....columnN)
为每一个分组返回一条小计记录，并为全部分组返回总计。
先列出按column1,column2,....columnN进行分组的结果，并在每组后列出column1分组的结果，最后列出所有分组的结果；

CUBE的用法：GROUP BY CUBE(column1,column2,....columnN)
返回所列组合的小计信息，同时在最后显示总计信息。
先列出按column1,column2,....columnN进行分组的结果，并在每组后列出column1分组的结果，然后分别列出按column2,....columnN分组的结果，最后列出所有分组的结果；

GROUPING()函数可以接受一列，返回0或1。如果列值为空，则返回1，否则返回0；GROUPING()函数只能在CUBE或ROLLUP中使用。

GROUPING SETS子句：可以只返回小计记录。
GROUP BY GROUPING SETS(column1,column2,...columnN);
分别列出按column1,column2,......columnN进行分组的小计值。

GROUPING_ID()函数:借助HAVING子句对记录进行过滤，将不包含小计或者总计的记录去掉。
GROUPING_ID()函数可以接受一列或多列，返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是将按照顺序对每一列调用GROUPING()函数的结果组合起来,然后转换成十进制数。
HAVING GROUPING_ID(column1,column2,....columnN)>0就可以将不包含小计或总计的记录除去。

GROUP_ID()函数：用于消除GROUP BY子句返回的重复记录。GROUP_ID函数不接受任何参数。某个某个特定分组重复出现n次，则该函数返回从0至n-1之间的一个整数。因此，可以在HAVING子句中消除重复记录，只返回GROUP_ID()的值为0的记录。


13.0 分析函数：
A.评级函数
RANK():返回数据项在分组中的排名。在排名相等的情况下会在名次中留下空位；
DENSE_RANK():返回数据项在分组中的排名。在排名相等的情况下不会在名次中留下空位；
CUME_DIST():返回特定值相对于一组值的位置；
PERCENT_RANK()：返回某个值对于一组值的百分比排名；
NTILE()：返回n分片后的值；
ROW_NUMBER():为每一条分组记录返回一个数字,从1开始
用法：分析函数 OVER (ORDER BY DESC|ASC)
OVER子句中，使用DESC时将空值指定为最高排名，使用ASC时将空值指定为最低排名；在OVER子句中可使用NULLS FIRST和NULLS LAST子句来显示的控制将空值是作为最高排名还是最低排名。
eg.RANK() OVER (ORDER BY SUM(account_sum) DESC NULLS FIRST);
使用PARTITION BY子句将分组划分为子分组：
RANK() OVER (PARTITION BY add_date ORDER BY SUM(account_sum) DESC)
与ROLLUP，CUBE，GROUPING SETS的结合使用。
反百分点函数：
PERCENTILE_DISC(X):在每一个分组中检查累积分布的数值，直到找到大于或等于X的值；
PERCENTILE_CONT(X):在每一个分组中检查百分比排名的值，直到找到大于或等于X的值；
用法：
eg.SELECT PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY sum(account_sum) DESC) FROM all_sales GROUP BY month;

B.窗口函数
1.计算累计和：
SELECT month,SUM(account_sum),
SUM(SUM(account_sum)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM all_sales
WHERE year=2003
GROUP BY month;
查询2003年各月销量及各月的累计销量。
SUM(SUM(account_sum)):计算累计销量。
ORDER BY month：按照月份对查询的记录进行排序
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：定义窗口的起点和终点。起点由UNBOUNDED PRECEDING 指定查询所读取的所有行，CURRENT ROW表示终点是当前行，这是默认值，可省略.

2.计算移动本均值
SELECT month,SUM(account_sum),
AVG(SUM(account_sum)) OVER (ORDER BY month ROWS BETWEEN n PRECEDING AND CURRENT ROW)
FROM all_sales
WHERE year=2003
GROUP BY month;
查询2003年每月与其前n个月之间销量的移动平均值;

3.计算中心平均值
SELECT month,SUM(account_sum),
AVG(SUM(account_sum)) OVER (ORDER BY month ROWS BETWEEN n PRECEDING AND m FOLLOWING)
FROM all_sales
WHERE year=2003
GROUP BY month;
查询2003年每月与其前n个月、后m个月之间销量的移动平均值;

4.FIRST_VALUE()和LAST_VALUE()获取当前窗口函数中的第一条和最后一条记录
eg.
SELECT month,SUM(account_sum),
FIRST_VALUE(SUM(account_sum)) OVER (ORDER BY month ROWS BETWEEN n PRECEDING AND m FOLLOWING) as pre_month_account_sum,
LAST_VALUE(SUM(account_sum)) OVER (ORDER BY month ROWS BETWEEN n PRECEDING AND m FOLLOWING) as next_month_account_sum
FROM all_sales
WHERE year=2003
GROUP BY month;
查询2003年每月前一月和后月的销量。

C.报表函数
1.总计报表
SELECT month,prd_type_id,SUM(SUM(account_sum)) OVER (PARTITION BY month),SUM(SUM(account_sum)) OVER (PARTITION BY prd_type_id)
FROM all_sales
WHERE year =2003
AND month <=3 GROUP BY month,prd_type_id;
计算2003年前三个月每个月的销量总和和所有产品类型销量的总和
先用SUM(account_sum)求出每个月每类产品的销量，再用OVER (PARTITION BY month)对所有产品销量总计按月分组求每个月的总计；
先用SUM(account_sum)求出每个月每类产品的销量，再用OVER (PARTITION BY prd_type_id)对所有产品销量总计按产品类型分组求每种类型的总计；

2.RATIO_TO_REPORT()函数：用来计算某个值在一组值的总和中所占的比率。
SELECT month,prd_type_id,SUM(account_sum),RATIO_TO_REPORT(SUM(account_sum)) OVER (PARTITION BY month)
WHERE year =2003
AND month <=3 GROUP BY month,prd_type_id;
查询2003年前三个月每种类型产品每个月的销量及该类产品销量占整月销量的比率。

D.LAG()和LEAD()函数
用来获得位于距当前记录指定距离处的那条记录。

SELECT month,SUM(account_sum),
LAG(SUM(account_sum),1) OVER (ORDER BY month),
LEAD(SUM(account_sum),1) OVER (ORDER BY month)
FROM all_sales
WHERE year =2003
GROUP BY month;
LAG(SUM(account_sum),1) OVER (ORDER BY month):获得前一月的销量；按month字段进行排序，并返回当前记录之前一条记录的sum(account_sum)值
LEAD(SUM(account_sum),1) OVER (ORDER BY month):获得后一月的销量；按month字段进行排序，并返回当前记录之后一条记录的sum(account_sum)值

14.0 MODEL子句的用法：用于行间计算。允许像访问数组中的元素那样访问记录中的某个列。
SELECT prd_type_id,year,month,sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id = 21
MODEL
PARTITION BY(prd_type_id)
DIMENSION BY(month,year)
MEASURES (account_sum sales_amount)(
sales_amount[1,2004] = sales_amount[1,2003],
sales_amount[2,2004] = sales_amount[2,2003] + sales_amount[3,2003],
sales_amount[3,2004] = ROUND(sales_amount[3,2003] * 1.5,2)
)
ORDER BY prd_type_id,year,month;
根据2003年工号为21的员工完成的产品类型为1，2的销量预测2004年1，2，3月份的销量。
解释：
PARTITION BY(prd_type_id) 指定结果是根据prd_type_id进行分区；
DIMENSION BY(month,year)定义数组的维数是month和year；
MEASURES (account_sum sales_amount)用来指定包含数量的数组中的任何一个单元，数组名为sales_amount,account_sum为指定的列；
sales_amount[1,2004] = sales_amount[1,2003],
sales_amount[2,2004] = sales_amount[2,2003] + sales_amount[3,2003],
sales_amount[3,2004] = ROUND(sales_amount[3,2003] * 1.5,2)
用于计算数组单元具体的值。以上是使用位置标记，也可用符号标记访问数据单元。
sales_amount[month=1,year=2004] = sales_amount[month=1,year=2003],
sales_amount[month=2,year=2004] = sales_amount[month=2,year=2003] + sales_amount[month=3,year=2003],
sales_amount[month=3,year=2004] = ROUND(sales_amount[month=3,year=2003] * 1.5,2)
用位置标记和用符号标记访问数据单元的区别：
sales_amount[null,2003]表示访问年份为2003年，月份为空的数据单元；
sales_amount[month=null,year=2003]不能访问任何有效数据单元。
可以在数组的访问方法中加上BETWEEN AND ，用于返回特定范围内的数据单元
eg:sales_amount[1,2004] = ROUND(AVG((sales_amount)[month BETWEEN 1 AND 3,2003],2)
可以用ANY和IS ANY访问所有的数据单元,ANY和位置标记合用，IS ANY和符号标记合用;
eg:sales_amount[1,2004] = ROUND(SUM((sales_amount)[ANY ,year IS ANY],2)
将2004年1月的销量设置为所有年份月份销量之和取整。
用CURRENTV()获取某个维度的当前值
eg:sales_amount[1,2004] = ROUND(sales_amount[CURRENTV ,2003] * 1.5,2)
表示将2004年1月的销量设置为2003年同月份销量的1.5倍取整。
用FOR循环来访问数据单元
eg:sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004] = ROUND(sales_amount[CURRENTV ,2003] * 1.5,2)
表示将2004年1-3月销量设置为2003年同月份销量的1.5倍取整
INCREMENT用来表示循环递增量。
处理空值和缺失值
IS PRESENT：当数据单元指定的记录在MODEL执行之前就存在，则返回TRUE。
sales_amount[CURRENTV(),2003] IS PRESENT
PRESENTV(cell,expr1,expr2)：如果cell引用的记录在MODEL子句执行之前就存在，则返回expr1，否则返回expr2。
PRESENTNNV(cell,expr1,expr2)：如果cell引用的单元在MODEL执行之前就存在，并且该单元的值不为空，则返回expr1，如果单元不存在或单元值为空值，则返回expr2.
IGNORE NAV和KEEP NAV
IGNORE NAV返回值如下：
空值或缺失数字值时返回0；
空值或缺失字符串值时返回空字符串；
空值或缺失日期值时返回01-JAN-2000；
KEEP NAV对空值或缺失数字值返回空值。此为默认条件
eg:MODEL IGNORE NAV

默认情况下，如果表达式左端的引用单元存在，则更新该单元。不存在，就在数组中创建一条新的记录。可用RULES UPDATE改变这种默认行为即在单元不存在的情况下不创建新记录。
eg:
SELECT prd_type_id,year,month,sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id = 21
MODEL IGNORE NAV
PARTITION BY(prd_type_id)
DIMENSION BY(month,year)
MEASURES (account_sum sales_amount)
RULES UPDATE
(
sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004] = ROUND(sales_amount[CURRENTV ,2003] * 1.5,2)
)
ORDER BY prd_type_id,year,month;

15.0 UPDATE用法补充
在ORACLE10G中可以使用RETURNING子句返回使用聚合函数计算的结果。
eg:
VARIABLE average_product_prices;

UPDATE products
SET price = price * 1.25
RETURNING AVG(price) INTO :average_product_prices;
1)声明变量average_product_prices
2)增加price列25%,并将平均值存在average_product_prices中；(更新前的平均值还是更新后的平均值?)

16.0 MERGE用法
MERGE INTO table1
USING table2 ON ( conditition )
WHEN MATCHED THEN
UPDATE
SET table1.column1 = table2.column1,.....table1.columnN = table2.columnN
WHEN NOT MATCHED THEN
INSERT (
table1.column1,table1.column2,.....table1.columnN
)
VALUES (
table2.column1,table2.column2,.....table2.columnN
);

17.0 设置事务隔离性级别
1)幻读：事务1读取记录时事务2增加了记录并提交，事务1再次读取时可以看到事务2新增的记录；
2)不可重复读取：事务1读取记录时，事务2更新了记录并提交，事务1再次读取时可以看到事务2修改后的记录；
3)脏读：事务1更新了记录，但没有提交，事务2读取了更新后的行，然后事务T1回滚，现在T2读取无效。
事务隔离级别描述：
READ UNCOMMITTED：幻读，不可重复读和脏读均允许；
READ COMMITTED：允许幻读和不可重复读，但不允许脏读；
REPEATABLE READ：允许幻读，但不允许不可重复读和脏读；
SERIALIZABLE:幻读，不可重复读和脏读都不允许
ORACLE默认的是 READ COMMITTED
设置语法：
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE|READ COMMITTED|READ UNCOMMITTED|REPEATABLE READ；

18.0 查询闪回
查询闪回的授权
GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO user1;
查询闪回的使用
将数据库闪回到一个特定的时间。下例中是10分钟前
EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE - 10/1440);
禁用闪回
EXECUTE DBMS_FLASHBACK.DISABLE();
在再次启用闪回操作之前，必须先将其禁用。
通过系统变更号(SCN)进行查询闪回
取得当前的SCN
VARIABLE current_scn number;
EXECUTE :current_scn :=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
通过SCN闪回
EXECUTE DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:current_scn);
禁用闪回
EXECUTE DBMS_FLASHBACK.DISABLE();