选读SQL经典实例笔记20_Oracle语法示例
1.选读SQL经典实例笔记23_读后总结与感想兼导读2.选读SQL经典实例笔记01_检索和排序3.选读SQL经典实例笔记02_多表查询4.选读SQL经典实例笔记03_DML和元数据5.选读SQL经典实例笔记04_日期运算(上)6.选读SQL经典实例笔记05_日期运算(下)7.选读SQL经典实例笔记06_日期处理(上)8.选读SQL经典实例笔记07_日期处理(下)9.选读SQL经典实例笔记08_区间查询10.选读SQL经典实例笔记09_数值处理11.选读SQL经典实例笔记10_高级查询12.选读SQL经典实例笔记11_结果集变换13.选读SQL经典实例笔记12_桶、图和小计14.选读SQL经典实例笔记13_case与聚合15.选读SQL经典实例笔记14_层次查询16.选读SQL经典实例笔记15_窗口函数17.选读SQL经典实例笔记16_逻辑否定18.选读SQL经典实例笔记17_最多和最少19.选读SQL经典实例笔记18_Exactly20.选读SQL经典实例笔记19_Any和All
21.选读SQL经典实例笔记20_Oracle语法示例
22.选读SQL经典实例笔记21_字符串处理23.选读SQL经典实例笔记22_2版增补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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业