ORACLE PROCEDURE存储过程学习二

ORACLE PROCEDURE存储过程学习2

 --企业基本业务信息，更新有效业务号字段，成链
  PROCEDURE SJBL_GXYXYWH(v_PRIPID IN VARCHAR2) AS

    TYPE T_DS IS TABLE OF VARCHAR2(24);
    r_ZCDJYWSEQ_DS T_DS;

    -- 动态seq
    v_SQL VARCHAR2(200);
  BEGIN
    
    --v_SQL := 'SELECT ZCDJYWSEQ FROM T_REG_QYJGJBXXYW WHERE PRIPID = :v_PRIPID ORDER BY APPRDATE';

    v_SQL := 'SELECT ZCDJYWSEQ FROM T_REG_SCZTZCDJYW WHERE APPRCONC = 1 AND DJYWLXSEQ IN (2001,2002,2003,2005,2006,2007,2008,2009,2011,2012) AND PRIPID = :v_PRIPID ORDER BY APPRDATE';
   
    EXECUTE IMMEDIATE v_SQL BULK COLLECT INTO r_ZCDJYWSEQ_DS USING v_PRIPID;

    FOR i IN 1..r_ZCDJYWSEQ_DS.COUNT LOOP

      IF i <> 1 THEN
      --更新企业基本信息中的有效业务号字段
       UPDATE T_REG_QYJGJBXXYW SET YXZCDJYWSEQ = r_ZCDJYWSEQ_DS(i-1) WHERE ZCDJYWSEQ = r_ZCDJYWSEQ_DS(i);
      /*现在系统没有将设立登记业务数据的有效业务号字段赋值*/
      --ELSE

      --  UPDATE T_REG_QYJGJBXXYW SET YXZCDJYWSEQ = r_ZCDJYWSEQ_DS(i) WHERE ZCDJYWSEQ = r_ZCDJYWSEQ_DS(i);
      
      --更新个体工商户基本信息中的有效业务号字段
      UPDATE T_REG_GTGSHJBXXYW SET YXZCDJYWSEQ = r_ZCDJYWSEQ_DS(i-1) WHERE ZCDJYWSEQ = r_ZCDJYWSEQ_DS(i);
      
      --更新集团基本信息中的有效业务号字段
      UPDATE T_REG_JTJBXXYW SET YXZCDJYWSEQ = r_ZCDJYWSEQ_DS(i-1) WHERE ZCDJYWSEQ = r_ZCDJYWSEQ_DS(i);
      END IF;

    END LOOP;

  END SJBL_GXYXYWH;