工作中用到的oracle总结（持续更新）

1

表里有三个记录人数的字段，分别是 本单位人数，局级人数，党政干部数对应字段如下（UNIT_NUM/BUREAU_LEVEL_NUM/PARTY_CADRES_NUM））

但是在页面上要合起来显示，格式如下 15（10/5/0），相关sql函数如下

SELECT
 (case when D.GROUP_TYPE in ('01','03','04' )then
	     NVL(D.GROUP_TOTAL_NUM,0) || '(' || NVL(D.UNIT_NUM,0) || '/' || NVL(D.BUREAU_LEVEL_NUM,0) || '/' || NVL(D.PARTY_CADRES_NUM,0) || ')' 
	     when D.GROUP_TYPE='02' then
	     NVL(D.UNIT_NUM,0) || '(' || NVL(D.UNIT_NUM,0) || '/' || NVL(D.BUREAU_LEVEL_NUM,0) || '/' || NVL(D.PARTY_CADRES_NUM,0) || ')' 
	     end) AS  Num
FROM TABLE

2

有两个字段分别是部门状态：dept_status（两位数），部门id：deptId（六位数）。

dept_status里有多个用逗号隔开的数据和deptId一一对应

现在给出某个部门id，查出对应位置的部门状态

1、substr函数

参考Oracle中的substr()函数 详解及应用 - DSHORE - 博客园 (cnblogs.com)

格式1： substr(string string, int a, int b);
        1、string 需要截取的字符串
        2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
        3、b 要截取的字符串的长度

 格式2：substr(string string, int a) ;
        1、string 需要截取的字符串
        2、a 可以理解为从第a个字符开始截取后面所有的字符串。

2instr（）函数

参考 Oracle中的instr()函数 详解及应用_方圆几里的博客-CSDN博客

格式：instr( string1, string2 ) ，string2 的值要在string1中查找

instr()-1：获取目标字符串前面的位置，也就是上一组数据结束的位置

/7：部门id为6位数，加上逗号是7位，计算出是第几组

*3：状态字段加上逗号是3位数，计算的是状态字段里第n组结束的字符的位置

+1：下一个组数据开始的位置，即要截取的字段的起始位置

substr(dept_status, (instr(zb_dept_id, '${deptId}') - 1) / 7 * 3 + 1, 2) 

 例2，根据承办人userId的找到对应的部门id

userId 长度32为，

假设要查找的userId在字段里的位置是1，查出的instr（）值为1，

instr(ZB_DEPT_PEOPLE_ID,'4028825d4e8bea2a014e8c3578d80609')-1)/33 * 7 +1 为1，

在部门id中从第一个字符开始查找。

假如要查的userId在字段里的位置为3，前面的userId由逗号分割，加上逗号共33位数，

计算出的数字是instruction（）是2，部门id加上逗号共7位数，2*7+1=15，从第15位开始截取

--4028825d4e8bea2a014e8c3578d80609,
4028825d4e8bea2a014e8c3588330702,4028825d4e8bea2a014e8c3581ec0696
-- 001003,001006,001005
select substr(ZB_DEPT_ID,
(instr(ZB_DEPT_PEOPLE_ID,'4028825d4e8bea2a014e8c3578d80609')-1)/33 * 7 +1
 ,6) from ssf_supervise 
where title = ' 1736重要'

-- like双边模糊查询会放弃使用索引，这两种查询结果是一样的
select * from ssf_supervise a where title like '%6重%';
select * from ssf_supervise a where instr(title,'6重')>0;

in 和 not in 优化事项:

select * from t1 where id not in (select id from t2);
select * from t1 where id  in (select id from t2);
-- 优化为:
select * from t1 where not EXISTS(select id from t2 where t1.id = t2.id)
select * from t1 where EXISTS (select id from t2 where t1.id = t2.id)

 delete from SSF_SW_DOC_SEND
	    <where>
	        id in
	       <foreach collection="sendList" item="item" index="index" open="(" close=")" separator=",">
	               #{item}
	       </foreach>
	       <if test="baseId!=null and baseId!=''">
	            and base_id = #{baseId}
	       </if>
	    </where>