Oracle SQL记录 - 《SQL数据库笔记》

基本函数:

基本函数:

NVL函数:

/*
返回值类型：
字符型、日期型、日期时间型、数值型、货币型、逻辑型或 null 值
说明：
在不支持 null 值或 null 值无关紧要的情况下，可以使用 NVL( ) 来移去计算或操作中的 null 值。
*/
select nvl(a.name,'空值') as name from student a join school b on a.ID=b.ID;
SELECT  
        T.D_FDATE,
        T.VC_ZHCODE,
        NVL(SUM(T.F_FZQSZ), 0) f_price_b,
        NVL(SUM(T.F_FZQCB), 0) f_cost_b,
        NVL(SUM(T.F_FGZ_ZZ), 0) f_gz_b,
        NVL(SUM(T.F_FYZQSZ), 0) f_price_Y,
        NVL(SUM(T.F_FYZQCB), 0) f_cost_Y,
        NVL(SUM(T.F_FYGZ_ZZ), 0) f_gz_Y,
        T.VC_SOURCE,
        SYSDATE d_updatetime
        FROM GZ_FUND_GZB T;

Case…when…then…end函数

/*
Case具有两种格式。简单Case函数和Case搜索函数。
*/
-- 基本语法
select
       LX_D_, XSLX.ID_ xslx_id_,ZR_ID_,LXNF_,LXPC.ID_,LXPC.PCMC_,LXSX.ID_ LXSXID_,LXPCSX.ID_ LXPCSXID_,LXSX.LXSXMC_,
       XH_,XM_,XBM_D_,(decode(XBM_D_,'0','男','女'))XBM_,PYCCM_D_,PYCCT.NAME_ PYCC_M_,DQSZJ_,JX01ND.ID_ JX01ND_ID_,JX01ND.ZYMC_,
       XS0701.JW_XX0301_ID_,XX0301.DWMC_,XX04.ID_,XX04.BJ_,SXBL.ID_ SXBLID_,BLZT_D_,TSSL.ID_ SSLID_,
       (case when BLZT_D_ is null then '未办理' when BLZT_D_='0' then '撤销' when BLZT_D_='1' then '已办理' end )BLZT_M_
        from T_XG_LXGL_PCSX_ZRR ZRR
         LEFT JOIN T_XG_LXGL_LXPCSX LXPCSX on ZRR.XG_LXGL_LXPCSX_ID_ = LXPCSX.ID_
         inner join T_XG_LXGL_XSLX XSLX on XSLX.XG_LXGL_LXPC_ID_ = LXPCSX.XG_LXGL_LXPC_ID_
         LEFT JOIN T_XG_LXGL_LXPC LXPC on LXPC.ID_ = LXPCSX.XG_LXGL_LXPC_ID_
         left join T_XG_LXGL_LXSX LXSX on LXSX.ID_ = LXPCSX.XG_LXGL_LXSX_ID_
         LEFT JOIN T_JW_XS0101 XS0101 on XS0101.ID_ = XSLX.JW_XS0101_ID_
         LEFT JOIN T_JW_XS0701 XS0701 on XS0701.JW_XS0101_ID_ = XS0101.ID_
         LEFT JOIN T_XG_LXGL_SXBL SXBL on SXBL.XG_LXGL_LXSX_ID_ = LXSX.ID_ and SXBL.XG_LXGL_XSLX_ID_ = XSLX.ID_
         Left Join T_JW_JX01ND JX01ND on JX01ND.ID_=JW_JX01ND_ID_
         Left Join T_JW_XX04 XX04 on XX04.ID_=JW_XX04_ID_
         Left Join T_JW_XX0301 XX0301  on XS0701.JW_XX0301_ID_=XX0301.ID_
        Left Join (select SSL.ID_,XSRZ.T_JW_XS0101_ID_
                   from T_XG_SSGL_SSL SSL
                            left join T_XG_SSGL_SSFJ SSFJ on SSFJ.T_XG_SSGL_SSL_ID_=SSL.ID_
                            left join T_XG_SSGL_XSRZ XSRZ on XSRZ.T_XG_SSGL_SSFJ_ID_=SSFJ.ID_ where ISDEL_='0') TSSL on TSSL.T_JW_XS0101_ID_=XS0101.ID_--宿舍楼关联表
         Left Join (select * from IBPS_CAT_DIC where TYPE_ID_ = '723218685921067008') PYCCT on PYCCT.KEY_=PYCCM_D_
        where ZRR.JW_JG0101_ID_='1253017' and
        (case when LXPCSX.LX_D_='yx' then XS0701.JW_XX0301_ID_ when LXPCSX.LX_D_='ssl' then TSSL.ID_ when LXPCSX.LX_D_='xx' then '1' end) = (decode(ZRR.ZR_ID_,'','1',ZRR.ZR_ID_));

DECODE()函数

select
(decode(XBM_D_,'0','男','女'))XBM_,
(case when BLZT_D_ is null then '未办理' when BLZT_D_='0' then '撤销' when BLZT_D_='1' then '已办理' end )BLZT_M_
from T_XG_LXGL_PCSX_ZRR ZRR
         LEFT JOIN T_XG_LXGL_LXPCSX LXPCSX on ZRR.XG_LXGL_LXPCSX_ID_ = LXPCSX.ID_
         inner join T_XG_LXGL_XSLX XSLX on XSLX.XG_LXGL_LXPC_ID_ = LXPCSX.XG_LXGL_LXPC_ID_
         LEFT JOIN T_XG_LXGL_LXPC LXPC on LXPC.ID_ = LXPCSX.XG_LXGL_LXPC_ID_
         left join T_XG_LXGL_LXSX LXSX on LXSX.ID_ = LXPCSX.XG_LXGL_LXSX_ID_
         LEFT JOIN T_JW_XS0101 XS0101 on XS0101.ID_ = XSLX.JW_XS0101_ID_
         LEFT JOIN T_JW_XS0701 XS0701 on XS0701.JW_XS0101_ID_ = XS0101.ID_
         LEFT JOIN T_XG_LXGL_SXBL SXBL on SXBL.XG_LXGL_LXSX_ID_ = LXSX.ID_ and SXBL.XG_LXGL_XSLX_ID_ = XSLX.ID_
         Left Join T_JW_JX01ND JX01ND on JX01ND.ID_=JW_JX01ND_ID_
         Left Join T_JW_XX04 XX04 on XX04.ID_=JW_XX04_ID_
         Left Join T_JW_XX0301 XX0301  on XS0701.JW_XX0301_ID_=XX0301.ID_
        Left Join (select SSL.ID_,XSRZ.T_JW_XS0101_ID_
                   from T_XG_SSGL_SSL SSL
                            left join T_XG_SSGL_SSFJ SSFJ on SSFJ.T_XG_SSGL_SSL_ID_=SSL.ID_
                            left join T_XG_SSGL_XSRZ XSRZ on XSRZ.T_XG_SSGL_SSFJ_ID_=SSFJ.ID_ where ISDEL_='0') TSSL on TSSL.T_JW_XS0101_ID_=XS0101.ID_--宿舍楼关联表
         Left Join (select * from IBPS_CAT_DIC where TYPE_ID_ = '723218685921067008') PYCCT on PYCCT.KEY_=PYCCM_D_
        where ZRR.JW_JG0101_ID_='1253017' and
        (case when LXPCSX.LX_D_='yx' then XS0701.JW_XX0301_ID_ when LXPCSX.LX_D_='ssl' then TSSL.ID_ when LXPCSX.LX_D_='xx' then '1' end) = (decode(ZRR.ZR_ID_,'','1',ZRR.ZR_ID_));

时间函数

select sysdate from DUAL;
-- 获取当前时间
select current_date from DUAL;
-- 获取当前时间
select to_date(sysdate) from DUAL;-- todate：str转成date

WM_CONCAT行数

wm_concat()函数是oracle中独有的,mysql中有一个group_concat()函数。效果一致

-- 就是合并列,将列合成一行，此时你这个查询也只可以展示一个列
select WM_CONCAT(XS0101.ID_) from T_JW_XS0101 XS0101 where ID_ in ('201541904301','201541904302');

INSTR函数

-- INSTR（默认从一开始计数）
select instr('helloworld','l') from dual; --返回结果：3    默认第一次出现“l”的位置
select instr('helloworld','lo') from dual; --返回结果：4    即“lo”同时出现，第一个字母“l”出现的位置

select instr('helloword','l',2,2) from dual;  --返回结果：4    也就是说：在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',4,2) from dual;  --返回结果：9    也就是说：在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置

-- 和WM_CONCAT()连用
-- 数据长这样
-- 778620964408655872,,192.168.6.227,1,2020-11-18 14:01:43,1,2020-11-30 13:42:25,缴费,,"778620847152693248,778617124347183104",1,1,1,1,,,,,
-- 778620847152693248,,192.168.6.227,1,2020-11-18 14:01:16,1,2020-11-30 13:30:35,退宿,,"778617124347183104,781906948814536704",1,1,1,1,,,,,

select
LXSX.ID_,LXSX.LXSXMC_,
(select WM_CONCAT(a.LXSXMC_) from T_XG_LXGL_LXSX a where INSTR(LXSX.XG_LXGL_LXSX_IDS_,a.ID_)>0) QZSXMC_
from T_XG_LXGL_LXSX LXSX;

listagg(~,’分隔符’) within group(order by ~)

select listagg(XS0101.ID_,'==') within group ( order by ID_) from T_JW_XS0101 XS0101 where ID_ in ('201541904301','201541904302');

-- 和INSTR连用
-- 数据长这样
-- 778620964408655872,,192.168.6.227,1,2020-11-18 14:01:43,1,2020-11-30 13:42:25,缴费,,"778620847152693248,778617124347183104",1,1,1,1,,,,,
-- 778620847152693248,,192.168.6.227,1,2020-11-18 14:01:16,1,2020-11-30 13:30:35,退宿,,"778617124347183104,781906948814536704",1,1,1,1,,,,,

select
LXSX.ID_,LXSX.LXSXMC_,
(select listagg(a.LXSXMC_,',') within group ( order by LXSX.ID_) from T_XG_LXGL_LXSX a where INSTR(LXSX.XG_LXGL_LXSX_IDS_,a.ID_)>0) QZSXMC_
from T_XG_LXGL_LXSX LXSX;

upper函数、lower函数

select  upper(DUMMY) from DUAL;-- 大写
select  lower(DUMMY) from DUAL;-- 小写

正则表达式(10g之后的版本才有)

regexp_like

regexp_substr

regexp_instr
regexp_replace