Orcale 储存过程循环插入

最新推荐文章于 2024-08-13 17:46:45 发布

木子木啊

最新推荐文章于 2024-08-13 17:46:45 发布

阅读量795

点赞数

分类专栏： SQL

本文链接：https://blog.youkuaiyun.com/xiao_chengzj/article/details/23769689

版权

SQL 专栏收录该内容

3 篇文章

订阅专栏

create or replace procedure proc_xop_test(v_supplier_id       in number,
                                  v_updatesupplier_id in number) as

  V_ERRCODE    NUMBER(20);
  V_ERRMSG     VARCHAR2(200);

  
  V_SQL        VARCHAR2(4000);
  v_temp_tbale xiu_mop.X_MOP_GOODS%rowtype;
   v_aq_seq   NUMBER;
   v_img_seq   NUMBER;
  cursor c_myucursor is
  
    select *
      from xiu_mop.X_MOP_GOODS t
     where t.supplier_id = v_supplier_id
     and t.del_flag != 1;

begin

  open c_myucursor;
  loop
  
    begin
      fetch c_myucursor
      
      
       into v_temp_tbale.GOODS_ID,
   
     v_aq_seq := X_XOP_GOODS_SEQ.nextval;
     v_img_seq:= X_XOP_GOODS_IMG_SEQ.nextval;
    
      exit when c_myucursor%notfound;
    
      ----insert
      insert into x_xop_goods
        (GOODS_ID,
         XIU_CODE,
       
         SALECHANNEL,
          SUBMIT_STATUS)
      values
        (
        v_aq_seq,
         v_temp_tbale. XIU_CODE,
         v_temp_tbale. GOODS_CODE, 
         (case when v_temp_tbale.GOODS_STATUS = 0 then   0 else null   end));
        
 
      commit;
      EXCEPTION
  --事物回滚后继续执行
  WHEN OTHERS THEN
    ROLLBACK;
    V_ERRCODE := SQLCODE;
    V_ERRMSG  := SUBSTR(SQLERRM, 1, 200);
    INSERT INTO QUERY_ERR_LOG
      (OPDATE, FUNCNAME, ERRCODE, ERRMSG, ERRSQL)
    VALUES
      (SYSDATE, 'proc_xop', V_ERRCODE, V_ERRMSG, V_SQL);
    COMMIT;
    
    end;
  
  end loop;

  close c_myucursor;

end proc_xop_test;