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

1. 计算一年有多少天

1.1. Oracle sql语句实例

1. select 'Days in 2005: '||
2.         to_char(add_months(trunc(sysdate,'y'),12)-1,'DDD')
3.         as report
4.    from dual
5. union all
6. select 'Days in 2004: '||
7.         to_char(add_months(trunc(
8.                      to_date('01-SEP-2004'),'y'),12)-1,'DDD')
9.    from dual
10. REPORT
11. -----------------
12. Days in 2005: 365
13. Days in 2004: 366

复制代码

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

2.1. Oracle sql语句实例

1. with v as (
2. select 'ClassSummary' strings from dual union
3. select '3453430278'           from dual union
4. select 'findRow 55'           from dual union
5. select '1010 switch'          from dual union
6. select '333'                  from dual union
7. select 'threes'               from dual
8. )
9. select strings
10.   from (
11. select strings,
12.        translate(
13.          strings,
14.          'abcdefghijklmnopqrstuvwxyz0123456789',
15.          rpad('#',26,'#')||rpad('*',10,'*')) translated
16.   from v
17.        ) x
18. where instr(translated,'#') ＞ 0
19.    and instr(translated,'*') ＞ 0

复制代码

3. 把整数转换成二进制

3.1. sql Oracle语句实例

1. ENAME        SAL SAL_BINARY
2. ---------- ----- --------------------
3. SMITH        800 1100100000
4. ALLEN       1600 11001000000
5. WARD        1250 10011100010
6. JONES       2975 101110011111
7. MARTIN      1250 10011100010
8. BLAKE       2850 101100100010
9. CLARK       2450 100110010010
10. SCOTT       3000 101110111000
11. KING        5000 1001110001000
12. TURNER      1500 10111011100
13. ADAMS       1100 10001001100
14. JAMES        950 1110110110
15. FORD        3000 101110111000
16. MILLER      1300 10100010100

复制代码

3.2. sql语句实例

1. select ename,
2.        sal,
3.        (
4.        select bin
5.          from dual
6.         model
7.         dimension by ( 0 attr )
8.         measures ( sal num,
9.                    cast(null as varchar2(30)) bin,
10.                    '0123456789ABCDEF' hex
11.                  )
12.         rules iterate (10000) until (num[0] ＜= 0) (
13.           bin[0] = substr(hex[cv()],mod(num[cv()],2)+1,1)||bin[cv()],
14.           num[0] = trunc(num[cv()]/2)
15.         )
16.        ) sal_binary
17.   from emp

复制代码

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

4.1. sql语句实例

1. select e.deptno,
2.        e.ename,
3.        e.sal,
4.        (select d.dname,d.loc,sysdate today
5.           from dept d
6.          where e.deptno=d.deptno)
7.   from emp e

复制代码

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

4.3. sql语句实例

1. create type generic_obj
2.     as object (
3.     val1 varchar2(10),
4.     val2 varchar2(10),
5.     val3 date
6. );

复制代码

4.3.1. 对象类型

4.4.  sql语句实例

1. select x.deptno,
2.         x.ename,
3.         x.multival.val1 dname,
4.         x.multival.val2 loc,
5.         x.multival.val3 today
6.    from (
7. select e.deptno,
8.         e.ename,
9.         e.sal,
10.         (select generic_obj(d.dname,d.loc,sysdate+1)
11.            from dept d
12.           where e.deptno=d.deptno) multival
13.    from emp e
14.         ) x
15. DEPTNO ENAME      DNAME      LOC        TODAY
16. ------ ---------- ---------- ---------- -----------
17.     20 SMITH      RESEARCH   DALLAS     12-SEP-2005
18.     30 ALLEN      SALES      CHICAGO    12-SEP-2005
19.     30 WARD       SALES      CHICAGO    12-SEP-2005
20.     20 JONES      RESEARCH   DALLAS     12-SEP-2005
21.     30 MARTIN     SALES      CHICAGO    12-SEP-2005
22.     30 BLAKE      SALES      CHICAGO    12-SEP-2005
23.     10 CLARK      ACCOUNTING NEW YORK   12-SEP-2005
24.     20 SCOTT      RESEARCH   DALLAS     12-SEP-2005
25.     10 KING       ACCOUNTING NEW YORK   12-SEP-2005
26.     30 TURNER     SALES      CHICAGO    12-SEP-2005
27.     20 ADAMS      RESEARCH   DALLAS     12-SEP-2005
28.     30 JAMES      SALES      CHICAGO    12-SEP-2005
29.     20 FORD       RESEARCH   DALLAS     12-SEP-2005
30.     10 MILLER     ACCOUNTING NEW YORK   12-SEP-2005

复制代码

4.5. 对象本身是一个标量值，它并不会违反标量子查询的规则

5. 解析串行化的数据

5.1. sql语句实例

1. STRINGS
2. -----------------------------------
3. entry:stewiegriffin:lois:brian:
4. entry:moe::sizlack:
5. entry:petergriffin:meg:chris:
6. entry:willie:
7. entry:quagmire:mayorwest:cleveland:
8. entry:::flanders:
9. Entry:robo:tchi:ken:

复制代码

5.1.1. sql语句实例

1. create view V
2.     as
3. select 'entry:stewiegriffin:lois:brian:' strings
4.   from dual
5. union all
6. select 'entry:moe::sizlack:'
7.   from dual
8. union all
9. select 'entry:petergriffin:meg:chris:'
10.   from dual
11. union all
12. select 'entry:willie:'
13.   from dual
14. union all
15. select 'entry:quagmire:mayorwest:cleveland:'
16.   from dual
17. union all
18. select 'entry:::flanders:'
19.   from dual
20. union all
21. select 'entry:robo:tchi:ken:'
22.   from dual

复制代码

5.2. sql语句实例

1. VAL1            VAL2            VAL3
2. --------------- --------------- ---------------
3. moe                         sizlack
4. petergriffin    meg         chris
5. quagmire        mayorwest   cleveland
6. robo            tchi        ken
7. stewiegriffin   lois        brian
8. willie
9.                            flanders

复制代码

5.2.1.  sql语句实例

1. with cartesian as (
2. select level id
3.    from dual
4. connect by level ＜= 100
5. )
6. select max(decode(id,1,substr(strings,p1+1,p2-1))) val1,
7.         max(decode(id,2,substr(strings,p1+1,p2-1))) val2,
8.         max(decode(id,3,substr(strings,p1+1,p2-1))) val3
9.    from (
10. select v.strings,
11.         c.id,
12.         instr(v.strings,':',1,c.id) p1,
13.         instr(v.strings,':',1,c.id+1)-instr(v.strings,':',1,c.id) p2
14.    from v, cartesian c
15.   where c.id ＜= (length(v.strings)-length(replace(v.strings,':')))-1
16.         )
17.   group by strings
18.   order by 1

复制代码

6. 计算比重

6.1. Oracle支持内置函数RATIO_TO_REPORT

6.2. sql语句实例

1. select job,num_emps,sum(round(pct)) pct_of_all_salaries
2.   from (
3. select job,
4.        count(*)over(partition by job) num_emps,
5.        ratio_to_report(sal)over()*100 pct
6.   from emp
7.         )
8. group by job,num_emps

复制代码

7. 正则表达式功能

7.1. Oracle Database 10g

7.2. sql语句实例

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

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！