oralce存储过程写法

-- 动态参数 存过
CREATE OR REPLACE PROCEDURE P_X_RPT_TRANS01R01 (v_ruler_seq in varchar2)
as
v_record_date date :=sysdate;
v_num number;

begin
  --1、批量删除数据
      p_delBigdata('X_RPT_TRANS_DETAIL','ruler_seq='''||v_ruler_seq||'''','5000');   --批量删除数据

  --2、入库不合格数据
   for cur in (
  SELECT * FROM   (SELECT  'trans01r01' ruler_seq,CP.AREACODE sharding_id ,CP.region_id region_id,CP.INT_ID id,CP.ZH_LABEL  name, cp.device_type,
  decode(sign(cp.device_type-0),-1,'非设备实体类型') ||
  decode(sign(cp.device_type-8),1,'非设备实体类型')||
  decode(cp.device_type,null,'设备实体类型为空')  notes,rownum rn
  FROM irms.VIEW_TRANS01R01 cp)  cp1 WHERE   notes is not null
  ) loop
    insert into X_RPT_TRANS_DETAIL(ruler_seq,sharding_id,region_id,id,name,notes,Record_Date)
      values(cur.ruler_seq,cur.sharding_id,cur.region_id,cur.id,cur.name,cur.notes,sysdate);
        if mod(cur.rn, 2000) = 1 then commit;
        end if;
    end loop;
   commit;

   --3、记录问题数据量，并更新仅统计表
   select count(1) into v_num from X_RPT_TRANS_DETAIL dt where dt.ruler_seq = ''||v_ruler_seq||'';
   update x_Rpt_Trans tr set tr.num = v_num,tr.RECORD_DATE =v_record_date  where tr.ruler_seq = ''||v_ruler_seq||'';
   commit;
 end P_X_RPT_TRANS01R01;