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

这篇具有很好参考价值的文章主要介绍了选读SQL经典实例笔记20_Oracle语法示例。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

选读SQL经典实例笔记20_Oracle语法示例文章来源地址https://www.toymoban.com/news/detail-634384.html

1. 计算一年有多少天

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

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

到了这里,关于选读SQL经典实例笔记20_Oracle语法示例的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包