oracle常用sql

1，保留两位小数

 

（1）ROUND(A/B,2)：将结果四舍五入

 

 

（2）TRUNC(A/B,2)：结果不四舍五入

 

 

（3）TO_CHAR(A/B,‘FM99990.99’)：控制展示格式

带0：

 

不带0：

2，按月份分组

select to_char(exportDate,'yyyy-mm'),sum(amount) from table1

group by to_char(exportDate,'yyyy-mm')

order by to_char(exportDate,'yyyy-mm');

链接：https://www.cnblogs.com/ymj126/p/4501061.html

 

3，分区排序 row_number() rank() dense_rank()

链接：https://www.cnblogs.com/qiuting/p/7880500.html

 

4，截取
instr( ','||t_xg_zg_rz.xslbdm||',', ','||T_XG_JBXX.xslbdm||',')> 0

5，插入带查询
INSERT INTO T_GG_ZTJ_SHRZ(
WID , YWZJ , YWFLMC , YHID , YHMC ,
YWZLMC , DJMC , APPID , LCDM , SLDM ,
SHZT , SHR , SHSJ , SHYJ , SHPX ,
LX , CGBZ , NR , FSSJ , SBYY ,
TBRQ ,TBLX ,CZRQ , CZZ , CZZXM , SHLX
)
SELECT
SYS_GUID() , t_xg_zdxs_yjb.yjbbh , ? , ? ,
? , ? , NULL , ? , ? , ? , ? , ? ,
TO_CHAR(sysdate,'yy-mm-dd hh24:mi:ss') , ? , NULL ,
NULL , NULL , NULL , TO_CHAR(SYSDATE,'yyyy-MM-dd HH24:MI:SS') , NULL , NULL ,
NULL , SYSDATE , ? , ? , ?
FROM t_xg_zdxs_yjb

5，修改带select
--需求：床位号字段为空，插入床位
update t_ss_zs_ssap a
set a.cwh = (
select cwpx.px from (
select t.ssdm,t.xsbh,
dense_rank() over(partition by t.ssdm order by t.xsbh desc)as px
from t_ss_zs_ssap t
group by t.ssdm,t.xsbh
) cwpx
where a.ssdm = cwpx.ssdm and a.xsbh = cwpx.xsbh
)

--需求：T_SS_ZS_DDSQ_TEMP手机号如果不为空，则修改T_XG_JBXX手机号字段
UPDATE T_XG_JBXX
SET T_XG_JBXX.SJH =
(SELECT TEMP.SJH
FROM T_SS_ZS_DDSQ_TEMP TEMP
WHERE TEMP.XH = T_XG_JBXX.XH
AND TEMP.XM = T_XG_JBXX.XM
AND TEMP.CZZ = ?
AND TEMP.SJH IS NOT NULL--此处的用法目的：T_SS_ZS_DDSQ_TEMP表中SJH为空，则不执行update；实际上还是执行了
)

UPDATE T_XG_JBXX
SET T_XG_JBXX.SJH =
(SELECT TEMP.SJH
FROM (SELECT NVL(T_SS_ZS_DDSQ_TEMP.sjh, T_XG_JBXX.SJH) SJH,--修改后的方案
T_SS_ZS_DDSQ_TEMP.XM,
T_SS_ZS_DDSQ_TEMP.XH,
T_SS_ZS_DDSQ_TEMP.CZZ
FROM T_SS_ZS_DDSQ_TEMP
LEFT JOIN t_xg_jbxx
ON T_SS_ZS_DDSQ_TEMP.XH = T_XG_JBXX.XH) TEMP
WHERE TEMP.XH = T_XG_JBXX.XH
AND TEMP.XM = T_XG_JBXX.XM
AND TEMP.CZZ = ?)

--查询当前时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

--正则
SELECT '-100' FROM dual WHERE NOT REGEXP_LIKE('-1.2','(^\d{0,}\.?\d{0,}$)');

--修改日期格式
select to_char(to_date('2019-12-25 21:41:32','YYYY-MM-DD HH24:MI:SS'),'yyyy"年"MM"月"dd"日"')from dual
TO_CHAR(TO_DATE(TJZT.Blsj, 'YYYY-MM-DD HH24:MI:SS'), 'DD') || '日 '）

--添加字段
ALTER TABLE T_SS_RC_WJ_TEMP_DR ADD FJH VARCHAR2(40)
comment on column T_SS_RC_WJ_TEMP_DR.FJH is '房间号'

--更换主键：先删后加
alter table T_SS_ZS_YQZS_EK drop constraint XSBH cascade
alter table T_SS_ZS_YQZS_EK add constraint T_SS_ZS_YQZS_EK_PK primary key (WID)

--创建视图
CREATE OR REPLACE VIEW V_PSXG_TSG_JY AS
SELECT WID,TSTM,TSMC,SSH,DZTM,SFRZH,DZXM,YHRQ,JSRQ,XJBS,XJRQ,JSJSR,DJH FROM T_PSXG_TSG_JYLS
UNION
SELECT WID,TSTM,TSMC,SSH,DZTM,SFRZH,DZXM,YHRQ,JSRQ,XJBS,XJRQ,JSJSR,DJH FROM T_PSXG_TSG_JYXX

--循环插入
--添加数据
declare i number;
--用for实现
begin
for i in 0 .. 500 loop
insert into cust(custsn,type,code,pwd,provincecode,citycode,partycodetype,partycodevalue,partycitycode,identtype,identnum,name,brand,servicelevel,address)
values(sys_guid(),'01','360123198904010001'+i,'123456','86su','025','100200','100200300','su025','03','3206211992','陈霏','A',1,'中山路328号');
end loop;
end;

--导入校验时间格式
update T_XG_ZNCQ_LSBJXS_DR_EK
set type='F',
sbyy=sbyy||' '||'日期格式不正确(YYYY-MM-DD)'
where (not regexp_like(lskssj,'^\d{4}-([0]\d|[1][0,1,2])-([0,1,2]\d|[3][0,1])$')
or not regexp_like(lsjssj,'^\d{4}-([0]\d|[1][0,1,2])-([0,1,2]\d|[3][0,1])$')
)
and czz=?

--for update 和 for update nowait 的区别

for update:一直等待commit

for update nowait：会抛出异常：ORA-00054 资源正忙

 

 to_char(100.00, 'FM999,990.00')

FM999 其9代表：如果存在数字则显示数字，不存在则显示空，其0代表：如果存在数字则显示数字，不存在则显示0，即占位符。其FM代表：删除如果是因9带来的空格，则删除之

 

row_number() over 的order by 后的东西必须和最下面order by后的一致，否则会导致row_num字段错误

SELECT T_PSXG_TZGG_GG.GGBT,
T_PSXG_TZGG_GG.GGDM,
T_PSXG_TZGG_GG.FBSJ,
CASE
WHEN T_PSXG_TZGG_GG.SFZD = '1' AND
T_PSXG_TZGG_GG.GGZT = '1' AND
to_date(T_PSXG_TZGG_GG.ZDSJZ,
'yyyy-mm-dd hh24:mi:ss') >= sysdate then
1
else
0
end ZDZT,
T_PSXG_TZGG_GG.NRLX,
T_PSXG_TZGG_GG.PCURL,
T_PSXG_TZGG_GG.YDURL,
row_number() OVER(order by 
CASE
WHEN T_PSXG_TZGG_GG.SFZD = '1' AND
T_PSXG_TZGG_GG.GGZT = '1' AND
to_date(T_PSXG_TZGG_GG.ZDSJZ,
'yyyy-mm-dd hh24:mi:ss') >= sysdate then
1
else
0
end ZDZT desc,
T_PSXG_TZGG_GG.px,
T_PSXG_TZGG_GG.FBSJ) as row_num
FROM T_PSXG_TZGG_GG
LEFT JOIN T_PSXG_TZGG_LM
ON T_PSXG_TZGG_LM.LMDM = T_PSXG_TZGG_GG.LMDM
ORDER BY ZDZT DESC,
T_PSXG_TZGG_GG.PX,
T_PSXG_TZGG_GG.FBSJ DESC

 

 FCNR：varchar2转clob 

1，先新增temp字段

2，fcnr值赋给temp

3，删除fcnr字段

4，新增fcnr字段，type=clob

5，temp的值赋给fcnr

6，删除temp