oracle SQL高级使用总结

=====================================================================
正则表达式函数
---------------------------------------------------------------------------
元字符    字符含义
^    匹配字符串的开始位置(在[]中使用，此时它表示不接受该字符集合。
-    当使用在a-m表示范围；
当使用在第一个字符时表示
连字符串，如[-abc]
$    匹配字符结束位置
.    匹配除换行符 n之外的任何单字符。
?    匹配前面的子表达式零次或一次
*    匹配前面的子表达式零次或多次
+    匹配前面的子表达式一次或多次
()    标记一个子表达式的开始和结束位置
[]    标记一个中括号表达式
{m,n}    m= <出现次数 <=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。
|    表示或者的关系。指明两项之间的一个选择
字符簇    字符含义
[[:alpha:]]     任何字母。
[[:digit:]]     [[:digit:]] 任何数字。
[[:alnum:]]     任何字母和数
[[:space:]]     任何白字符。
[[:upper:]]    任何大写字母。
[[:lower:]]     任何小写字母。
[[:punct:]]     任何标点符号。
[[:xdigit:]]     任何16进制的数字，相当于[0-9a-fA-F]。
---------------------------------------------------------------------------------
名称    语法    备注
REGEXP_LIKE    REGEXP_LIKE
(source_string,
pattern
[, match_parameter]
)    source_string：
源字符串
Pattern：
正则表达式
match_parameter：
匹配模式(i:不区分大小写；c:区分大小写；n:允许使用可以匹配任意字符串的操作符；m:将x作为一个包含多行的字符串。
REGEXP_REPLACE    REGEXP_REPLACE
(source_string,
pattern
[,replace_string]
[,position]
[,occurtence]
[,match_parameter]
)    replace_string：
用于替换的字符串
Position：
开始搜索的起始位置
occurtence
指定替换第n次出现字符串
其他同上。
REGEXP_SUBSTR    REGEXP_SUBSTR
(source_string, pattern
[,position
[,occurrence
[,match_parameter]]]
)    Position：
指定在字符串中准确位置，默认为1
Occurrence：
指定在源字符串匹配过程中相对其他字符串，哪个字符串应该匹配。例如
select regexp_substr('The zip code
80831 is for falcon, co',
'[[:alpha:]]{3,}', 1, 3)
from dual;
结果选择的是code而非The或zip。
REGEXP_INSTR    REGEXP_INSTR
(source_string,
pattern
[,start_position
[,occurrence
[,return_option
[,match_parameter]]]]
)    start_position：
开始搜索位置
Occurrence：
第n次出现pattern，默认为1
return_option：
0：pattern的起始位置
1：pattern下一个字符起始位置
默认为0
REGEXP_COUNT    REGEXP_COUNT
(source_string,
pattern
[[,start_position]
[,match_parameter]])    11g新增的函数，表示pattern在原字符串中出现的次数
start_position：
开始搜索的位置
----------------------------------------------------------------------------------------

select REGEXP_REPLACE('abc123dsdfo456dfaj78dsl','[^0-9]','') from dual
12345678

select REGEXP_REPLACE('abc123dAdfo456dfaj78dsl','[a-z|A-Z]','') from dual
12345678

select REGEXP_REPLACE('abc123dAdfo456dfaj78dsl','[0-9]','') from dual
abcdAdfodfajdsl

select regexp_count('w20a14as', '[^0-9]', 5) from dual
2

select regexp_substr('The zip code 80831 is for falcon, co', '[[:alpha:]]{3,}', 1, 4) from dual;
for

Regexp_substr('first filed, second filed, third filed', ', [^,]*,')    , second filed,

regexp_replace('aa bb cc','(.*) (.*) (.*)','\3 \2 \1')

regexp_replace('Jone   smith','( ){2,}',' ')

regexp_substr('the final test is is the implention','([[:alnum:]]+)([[:space:]]+)\1')   is  is

========================================================================================
窗口函数（oracle称分析函数）

select deptno,sal,row_number() over(partition by deptno order by t.sal desc) from scott.emp t;
=====================================================================================================
时间和字符串函数
select trunc(to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd'),'month') from dual;
=======================================================================================================

connect by 字句

select * from emp start with empno=7839 connect by  prior empno=mgr;
select * from emp  connect by  prior empno=mgr start with empno=7839;

select * from emp start with empno=7566 connect by  prior empno=mgr;
select level,t.* from emp t start with empno=7566 connect by   empno= prior mgr;
select level,t.* from emp t connect by  prior mgr=empno start with empno=7934;

SELECT t.empno EMPNO,RPAD(' ',LEVEL*3) ||ENAME EMPLOYEE,MGR
FROM EMP t
CONNECT BY PRIOR EMPNO=mgr
START WITH ENAME='KING';
 
select count(*) from
(SELECT level,LEVEL + to_date('2014' || '0101', 'yyyymmdd') - 1 daycd
                                           FROM   dual
                                           CONNECT BY LEVEL BETWEEN 0 AND (to_date('2015' || '0101', 'yyyymmdd') + 6 -
                                                      to_date('2014' || '0101', 'yyyymmdd')))
==============================================================================================================================
model 字句

select ArrValue,soeji
from (select 'abcdefghijklmn' as ArrValue,
        1 as soeji from dual)
model
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[1] = 'Hello World');

model model语句的关键字，必须。
dimension by　dimension维度的意思，可以理解为数组的索引，必须。
measures 指定作为数组的列
rules　对数组进行各种操作的描述。
                                                      
select ArrValue,soeji
from (select 'abcdefghijklmn' as ArrValue,
1 as soeji from dual)
model
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[1] = 'Hello World',
ArrValue[2] = 'Hello model');

rules的缺省行为是存在就更新，不存在则追加
model语句里面，索引可以是不连续的。

select ArrValue,soeji
  from (select 'abcdefghijklmn' as ArrValue,
        1 as soeji from dual)
 model return updated rows
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[4] = 'Hello CodeZine');
结果是：
ArrValue       soeji    
Hello CodeZine    4
　使用model return updated rows的话，被rules（可省略）更新或者插入的行才显示，没有更新过的行不再作为SQL的结果。

使用位置标记或符号标记之间有一个区别需要了解，即它们处理维度中空值的方式不同。
例如，sales_amount[null,2003]返回月份为空值、年份为2003的销量，
而sales_amount[month=null,year=2004]则不会访问任何有效的数据单元，因为null=null的返回值总是false。

BETWEEN和AND关键字可用于访问一段范围内的数据单元。例如，下面这个表达式将2004年1月的销量设置为2003年1月至3月销量的平均值取整：
Sales_amount[1,2004]=ROUND(AVG(sales_amount)[month between 1 and 3,2003],2)

可以用ANY和IS ANY谓词访问数组中所有的数据单元。ANY和位置标记合用，IS ANY和符号标记合用。
例如，下面这个表达式将2004年1月的销量设置为所有年份月份的销量之和取整：
Sales_amount[1,2004]=ROUND(SUM(sales_amount)[ANY,year IS ANY],2)

CURRENTV()函数用于获得某个维度的当前值。
例如，下面的表达式将2004年第一个月的销量设置为2003年同月销量的1.25倍。注意此处用CURRENTV()获得当前月份，其值为1
Sales_amount[1,2004]=ROUND(sales_amount[CURRENTV(),2003]*1.25,2)

可以通过FOR循环访问数据单元。
例如，下面这个表达式将2004年前三个月的销量设置为2003年相应月份销量的1.25倍。
注意其中使用了FOR循环，还通过INCREMENT关键字定义每一次循环迭代中month的增量：
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=ROUND(sales_amount[CURRENTV(),2003]*1.25,2)

当数据单元指定的记录在MODEL子句执行之前存在，则IS PRESENT返回TRUE。例如：
Sales_amount[CURRENTV(),2003] IS PRESENT
如果Sales_amount[CURRENTV(),2003]存在，则返回TRUE。
下面的表达式将2004年前三个月的销量设置为2003年同期销量的1.25倍：
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
CASE WHEN Sales_amount[CURRENTV(),2003] IS PRESENT THEN
ROUND(sales_amount[CURRENTV(),2003]*1.25,2) ELSE 0 END


如果cell引用的记录在MODEL子句执行以前就存在，那么PRESENTV(cell,expr1,expr2)返回表达式expr1。如果这条记录不存在，则返回表达式expr2。例如：
PRESENTV(sales_amount[CURRENTV(),2003],ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
如果sales_amount[CURRENTV(),2003]存在，上面的表达式返回取整后的销量；否则，返回0.下面这个查询展示了上述表达式的用法：
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 (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
PRESENTV(sales_amount[CURRENTV(),2003],ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
)
Order by prd_type_id,year,month;


使用IGNORE NAV和KEEP NAV
IGNORE NAV的返回值如下：
空值或缺失数字值时返回0。
空值或缺失字符串值时返回空字符串。
空值或缺失日期值时返回01-JAN-2000。
其他所有数据库类型时返回空值。
KEEP NAV对空值或缺失数字值返回空值。注意默认条件下使用KEEP NAV。
下面这个查询展示了IGNORE NAV的用法：
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 (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
Order by prd_type_id,year,month;


默认情况下，如果表达式左端的引用单元存在，则更新该单元。如果该单元不存在，就在数组中创建一条新的记录。
可以用RULES UPDATE改变这种默认的行为，指出在单元不存在的情况下不创建新纪录。
======================================================================================================
游标
where current of cursor_name


===============================
提取时间
select extract(day from to_date('20131231','yyyymmdd')) from dual;
select to_char(sysdate,'yyyy') from dual;

++++++++++++++++++++++++++++++
疑问
select name,type,source_size,parsed_size,code_size
from user_object_size
where code_size > &&1 * 1024
order by code_size desc