Oracle 相关使用-记录

**1.**临时存储过程执行部分逻辑比较多的业务sql,循环赋值，按字段走不同逻辑等均可采用

DECLARE
--密明变量、游标:
grpcode varchar2(5);
entcode varchar2(5);
actdate Date;
itmlevel number(2);
countNum number(1);
BEGIN
grpcode:='JS';
entcode:='JS';
actdate:= date '2022-2-18';
itmlevel:=4;
while itmlevel > 1 loop
--执行语句
for item in (SELECT * FROM table) loop
DBMS_OUTPUT.put_line(item.字段名 || '---' || itmlevel);
--insert操作
select count(1)into countNum from table t where GL_TYPE='D';
if countNum > 0 then
--update
DBMS OUTPUT.put line('');
else
--insert
end if;
end loop;
itmlevel:=itmlevel-1;
end loop;
END;

**2.**字段重新赋值问题
场景说明：金融类系统，有些账务类字段需要保证字段序号的一致性，但是业务场景又存在中间数据撤销的操作，所以没法max累加处理，然后就有了下面这种业务数据重排的操作

update table t1
set t1.needNo = (select ROW_NO from (select ROW_NUMBER() OVER (order by t2.needNo) as ROW_NO,
											needNo,
											otherCloum
										from table t2
										where 1=1) t3
										where t1.needNo = t3.needNo and 1=1)
where 1=1;
--table 是同一个表，重新取相同数据的记录数通过行号来重排
--原字段比较复杂，固定账套，按日前缀等，拼接原字段的一部分，在按固定位数补0重新得到ID
set t1.needNo = concat(substr(t1.needNo,0,13), 
LPAD('000' + (select ROW_NO from (select ROW_NUMBER() OVER (order by t2.needNo) as ROW_NO,
											needNo,
											otherCloum
										from table t2
										where 1=1) t3
										where t1.needNo = t3.needNo and 1=1) , 7, '0'))
where 1=1;

**3.**Oracle 分组取值问题
例如：取各科分数前三的学生

-- 分组聚合 over PARTITION by
select * from (select sub,
					name,
					score,
					ROW_NUMBER() over (PARTITION by sub order by score desc) as num
					from table_score) tt where tt.num <=3 order by sub;