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