1、实现行列动态转换,常用于主从表关联时的特殊需求
select rwbm,psqh,
max(decode(xh1,1,yy))JKYL1,
max(decode(xh1,2,yy))JKYL2,
max(decode(xh1,3,yy))JKYL3,
max(decode(xh1,4,yy))JKYL4,
max(decode(xh1,5,yy))JKYL5,
max(decode(xh1,1,sz))RZSL1,
max(decode(xh1,2,sz))RZSL2,
max(decode(xh1,3,sz))RZSL3,
max(decode(xh1,4,sz))RZSL4,
max(decode(xh1,5,sz))RZSL5
from(
select a.*,row_number()over(partition by rwbm,psqh order by xh)xh1
from JC_CSD_SJCSCTZK a
where pz is not null
)group by rwbm,psqh
2、过程编写时进行分类处理
create or replace procedure [过程名]([参数1] in [类型],[参数2] in [类型]) is
CN NUMBER(1):=0; --是否已经存在
begin
FOR X IN ([待导出数据查询SQL])
LOOP
FOR Y IN (select count(*) TS from [判断导入表对应数据存在])
LOOP
CN := Y.TS; --已存在数据条数
END LOOP;
IF CN>0 THEN --更新记录
[update数据];
ELSE --添加记录
[insert数据];
END IF;
CN := 0;
END LOOP;
commit;
exception when others then rollback;
end;