1. 计算一年有多少天1.1. sql

select 'Days in 2005: '||        to_char(add_months(trunc(sysdate,'y'),12)-1,'DDD')        as report   from dual union all select 'Days in 2004: '||        to_char(add_months(trunc(                     to_date('01-SEP-2004'),'y'),12)-1,'DDD')   from dualREPORT-----------------Days in 2005: 365Days in 2004: 366

2. 查找含有数字和字母的字符串2.1. sql

with v as (select 'ClassSummary' strings from dual unionselect '3453430278'           from dual unionselect 'findRow 55'           from dual unionselect '1010 switch'          from dual unionselect '333'                  from dual unionselect 'threes'               from dual)select strings  from (select strings,       translate(         strings,         'abcdefghijklmnopqrstuvwxyz0123456789',         rpad('#',26,'#')||rpad('*',10,'*')) translated  from v       ) x where instr(translated,'#') ＞ 0   and instr(translated,'*') ＞ 0

3. 把整数转换成二进制3.1. sql

ENAME        SAL SAL_BINARY---------- ----- --------------------SMITH        800 1100100000ALLEN       1600 11001000000WARD        1250 10011100010JONES       2975 101110011111MARTIN      1250 10011100010BLAKE       2850 101100100010CLARK       2450 100110010010SCOTT       3000 101110111000KING        5000 1001110001000TURNER      1500 10111011100ADAMS       1100 10001001100JAMES        950 1110110110FORD        3000 101110111000MILLER      1300 10100010100

3.2. sql

select ename,       sal,       (       select bin         from dual        model        dimension by ( 0 attr )        measures ( sal num,                   cast(null as varchar2(30)) bin,                   '0123456789ABCDEF' hex                 )        rules iterate (10000) until (num[0] ＜= 0) (          bin[0] = substr(hex[cv()],mod(num[cv()],2)+1,1)||bin[cv()],          num[0] = trunc(num[cv()]/2)        )       ) sal_binary  from emp

4. 标量子查询转换为复合子查询4.1. sql

select e.deptno,       e.ename,       e.sal,       (select d.dname,d.loc,sysdate today          from dept d         where e.deptno=d.deptno)  from emp e

4.2. SELECT列表里的子查询只允许返回一个值4.3. sql

create type generic_obj    as object (    val1 varchar2(10),    val2 varchar2(10),    val3 date);

4.3.1. 对象类型4.4. sql

select x.deptno,        x.ename,        x.multival.val1 dname,        x.multival.val2 loc,        x.multival.val3 today   from ( select e.deptno,        e.ename,        e.sal,        (select generic_obj(d.dname,d.loc,sysdate+1)           from dept d          where e.deptno=d.deptno) multival   from emp e        ) xDEPTNO ENAME      DNAME      LOC        TODAY------ ---------- ---------- ---------- -----------    20 SMITH      RESEARCH   DALLAS     12-SEP-2005    30 ALLEN      SALES      CHICAGO    12-SEP-2005    30 WARD       SALES      CHICAGO    12-SEP-2005    20 JONES      RESEARCH   DALLAS     12-SEP-2005    30 MARTIN     SALES      CHICAGO    12-SEP-2005    30 BLAKE      SALES      CHICAGO    12-SEP-2005    10 CLARK      ACCOUNTING NEW YORK   12-SEP-2005    20 SCOTT      RESEARCH   DALLAS     12-SEP-2005    10 KING       ACCOUNTING NEW YORK   12-SEP-2005    30 TURNER     SALES      CHICAGO    12-SEP-2005    20 ADAMS      RESEARCH   DALLAS     12-SEP-2005    30 JAMES      SALES      CHICAGO    12-SEP-2005    20 FORD       RESEARCH   DALLAS     12-SEP-2005    10 MILLER     ACCOUNTING NEW YORK   12-SEP-2005

4.5. 对象本身是一个标量值，它并不会违反标量子查询的规则5. 解析串行化的数据5.1. sql

 STRINGS-----------------------------------entry:stewiegriffin:lois:brian:entry:moe::sizlack:entry:petergriffin:meg:chris:entry:willie:entry:quagmire:mayorwest:cleveland:entry:::flanders:Entry:robo:tchi:ken:

5.1.1. sql

create view V    asselect 'entry:stewiegriffin:lois:brian:' strings  from dual union allselect 'entry:moe::sizlack:'  from dual union allselect 'entry:petergriffin:meg:chris:'  from dual union allselect 'entry:willie:'  from dual union allselect 'entry:quagmire:mayorwest:cleveland:'  from dual union allselect 'entry:::flanders:'  from dual union allselect 'entry:robo:tchi:ken:'  from dual

5.2. sql

 VAL1            VAL2            VAL3--------------- --------------- ---------------moe                         sizlackpetergriffin    meg         chrisquagmire        mayorwest   clevelandrobo            tchi        kenstewiegriffin   lois        brianwillie                           flanders

5.2.1. sql

with cartesian as ( select level id   from dual connect by level ＜= 100 ) select max(decode(id,1,substr(strings,p1+1,p2-1))) val1,        max(decode(id,2,substr(strings,p1+1,p2-1))) val2,        max(decode(id,3,substr(strings,p1+1,p2-1))) val3   from ( select v.strings,        c.id,        instr(v.strings,':',1,c.id) p1,        instr(v.strings,':',1,c.id+1)-instr(v.strings,':',1,c.id) p2   from v, cartesian c  where c.id ＜= (length(v.strings)-length(replace(v.strings,':')))-1        )  group by strings  order by 1

6. 计算比重6.1. Oracle支持内置函数RATIO_TO_REPORT6.2. sql

select job,num_emps,sum(round(pct)) pct_of_all_salaries  from (select job,       count(*)over(partition by job) num_emps,       ratio_to_report(sal)over()*100 pct  from emp        ) group by job,num_emps

7. 正则表达式功能7.1. Oracle Database 10g7.2. sql

select emp_id, text  from employee_comment where regexp_like(text, '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')   and regexp_like(          regexp_replace(text,            '[0-9]{3}([-. ])[0-9]{3}\1[0-9]{4}',''),          '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')   EMP_ID TEXT---------- ----------------------------------------------------------      7369 126 Varnum, Edmore MI 48829, 989 313-5351      7844 989-387.5359      9999 906-387-1698, 313-535.8886