最新要闻

广告

手机

顺络电子：董事长部分股权办理股票质押业务

顺络电子：董事长部分股权办理股票质押业务

 深圳7月二手住宅成交2259套，中介称近期咨询客户开始增加

深圳7月二手住宅成交2259套，中介称近期咨询客户开始增加

家电

  1. 首页
  2. 移动通信
  3. 内容

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

来源：博客园


(资料图片仅供参考)

1.计算一年有多少天

1.1.Oraclesql语句实例

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.Oracle 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 Oracle语句实例

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

关键词：