选读SQL经典实例笔记20_Oracle语法示例

 

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 dual
REPORT
-----------------
Days in 2005: 365
Days in 2004: 366

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

2.1. sql

with v as (
select 'ClassSummary' strings from dual union
select '3453430278'           from dual union
select 'findRow 55'           from dual union
select '1010 switch'          from dual union
select '333'                  from dual union
select '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 1100100000
ALLEN       1600 11001000000
WARD        1250 10011100010
JONES       2975 101110011111
MARTIN      1250 10011100010
BLAKE       2850 101100100010
CLARK       2450 100110010010
SCOTT       3000 101110111000
KING        5000 1001110001000
TURNER      1500 10111011100
ADAMS       1100 10001001100
JAMES        950 1110110110
FORD        3000 101110111000
MILLER      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
        ) x
DEPTNO 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
    as
select 'entry:stewiegriffin:lois:brian:' strings
  from dual
 union all
select 'entry:moe::sizlack:'
  from dual
 union all
select 'entry:petergriffin:meg:chris:'
  from dual
 union all
select 'entry:willie:'
  from dual
 union all
select 'entry:quagmire:mayorwest:cleveland:'
  from dual
 union all
select 'entry:::flanders:'
  from dual
 union all
select 'entry:robo:tchi:ken:'
  from dual

5.2. sql

 VAL1            VAL2            VAL3
--------------- --------------- ---------------
moe                         sizlack
petergriffin    meg         chris
quagmire        mayorwest   cleveland
robo            tchi        ken
stewiegriffin   lois        brian
willie
                           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_REPORT

6.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 10g

7.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