小知识点小结

--取随机的数据
select empno,ename from ( select a.*,rownum rn from emp a order by dbms_random.value ) where rn<2;


--coalesce 将空转为0  --推荐用此方法 返回第一个不为空的表达式，如果都为空则返回空值。
select a.*,coalesce(comm,0)comm from emp a where empno=7369 ;  
select coalesce(null,3+5,4+6) value from dual; 
select a.*,nvl(comm,0)comm from emp a where empno=7369 ;  



--like查询下划线开头的字符
update emp set ename='_'||job where empno=7788;


select * from emp where ename like '\_%' escape '\';


--从右向左截取
select a.*,substr(job,-2) from emp a where empno=7369;    --从右边第一位开始，截取2 位
select a.*,substr(job,-2,1) from emp a where empno=7369;  --从右边第二位开始，截取 1位


--order by 中用 case when 
select empno,sal,case when sal>=3000 then 1 else 2 end as rn from emp order by 3 ,2 desc; 
select empno,sal  from emp order by case when sal>=3000 then 1 else 2 end,2 desc; 




--translate 函数  translate(字段,from_string,to_string) 如果 from_string 比to_string 长
-- from_string中有而 to_string没有的，这些没有的字符将从字段中被替换为空，也就是删除的意思，9i,10g 11g 都适用
select prid,praddr,translate(praddr,'a 0123456789','a') from prov; 


select translate('afdfadf你好','1你好','1') from dual; 


-----------------------------------------------------------------------------------------------------------------------
--正则表达式用法示例
-- regexp_replace  如果字符中既有数字也有汉字，可以用如下方式提取数字和汉字

--提取汉字
select prid,praddr,regexp_replace(praddr,'[0-9]')rn from prov where prid=100252;


--提取数字
select prid,praddr,regexp_replace(praddr,'[^0-9]')rn from prov where prid=100252;




--regexp_like  查找 ename 中有 A 字符的
select * from emp where regexp_like(ename,'A');
select * from emp where regexp_like(ename,'a','i');  --i 不区分大小写 ，c 区分大小写，默认为区分大小写


--查找 以A开头的
select * from emp where regexp_like(ename,'^A');


--查找 以 N 结尾的
select * from emp where regexp_like(ename,'N$');


--查找包含下划线的
select * from emp where regexp_like(ename,'_');


---regexp_substr
select a.*,regexp_substr(ename,'A',1,1,'i')rn from emp a where empno=7499; 


 --以一个或以上逗号开头，从第一位开始，第一次出现的位置，下面截取左边的 34 
SELECT REGEXP_SUBSTR('34,56,-23','[^,]+',1,1,'i') AS STR FROM DUAL; 




--分段截取数字
SELECT REGEXP_SUBSTR('34,56,-23', '[^,]+', 1, LEVEL, 'i') AS STR
  FROM DUAL  
CONNECT BY LEVEL <=  
           LENGTH('34,56,-23') - LENGTH(REGEXP_REPLACE('34,56,-23', ',', ''))+1;  






--regexp_instr 某个字符串出现的位置
Select instr('avdsdabasdab','a',1,2)rn FROM DUAL; 
select regexp_instr('avdsdabasdab','a',1,3)rn  from dual; --从第一位开始，查找a第三次出现的位置


--regexp_count  某个字符串出现的次数
select regexp_count ('The pro-niece was born today, so exciting!', 'o', 23, 'i')rn from dual;




-----------------------------------------------------------------------------------------------------------------------------------------------


--闪回版本查询及 闪回事物查询
select * from emp; 
update scott.emp set comm=1000


--查所有时间闪回版本查询
select versions_starttime, versions_endtime, versions_xid, 
versions_operation, empno 
from scott.emp versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME


--按时间段查
select empno, versions_starttime, versions_endtime,versions_xid
from scott.emp versions
between timestamp 
to_date('2015-03-23 16:15:00','yyyy-mm-dd hh24:mi:ss')
and to_date('2015-03-23 16:19:00','yyyy-mm-dd hh24:mi:ss')


--闪回事物查询
alter database add supplemental log data;  --启用最小补充日志
select *  from flashback_transaction_query  where table_owner='SCOTT' and xid='08000D0014030000'