--企业基本业务信息,更新有效业务号字段,成链
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;
ORACLE PROCEDURE存储过程学习2
最新推荐文章于 2021-09-22 09:19:22 发布