oracle sql编写,Oracle中SQL编写集锦

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 [类型]) isCN 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;