CREATE PROCEDURE YHS.HYDKZG1 (  )

------------------------------------------------------------------------

-- SQL 存储过程

------------------------------------------------------------------------

P1: BEGIN

    -- 声明变量

      --at_end表示游标是否到尾部标志  ， rowcount表示更新的行数

       DECLARE                          rowcount  INT  DEFAULT  0 ;

       --声明SQLCODE ,S_SQLCODE

       DECLARE                          SQLCODE,S_SQLCODE   INT  DEFAULT  0 ;

       --中间源表各个列对应的变量

        DECLARE      S_HYZGID                          BIGINT;

        DECLARE      S_NSRSH                           VARCHAR(25);

        DECLARE      S_NSRMC                           VARCHAR(100);

        DECLARE      S_SHWDJZH                         VARCHAR(50);

        DECLARE      S_CZYID                           VARCHAR(10);

        DECLARE      S_FJID                            VARCHAR(30);

        DECLARE      S_NSRFJID                         VARCHAR(30);

        DECLARE      S_SQRQ                            TIMESTAMP;

        DECLARE      S_DQZT                            CHARACTER(1);

       -- 增量标志，I代表增加、U代表更新、D代表删除

       DECLARE                              S_OPERATION CHARACTER(1);

       --声明游标

       -- 本例以（3707    潍坊市地税局    G0 ）为例  ，中间源表为yhs.tgtfp_hydkzg ，中间目的表为YHS.FPGL_HYDKZG

       --实际部署的时候中间目的表模式名要改成各个地市前缀 ，根据性能的不同，FETCH的行数可以根据实际需要调整，建议不要超过10000

 FOR  V1  AS

        SELECT   HYZGID,

          case when NSRSH like 'g0%' then insert(NSRSH,1,2,'') else NSRSH end   NSRSH1  ,

           NSRMC,SHWDJZH,CZYID,FJID,NSRFJID,SQRQ,DQZT,IBMSNAP_OPERATION

         FROM   yhs.tgtfp_hydkzg

          WHERE   NSRFJID  LIKE  '3707%'

           ORDER BY IBMSNAP_LOGMARKER FETCH FIRST 100  ROWS ONLY

     DO

         SET S_HYZGID=V1.HYZGID;

                   SET S_NSRSH=V1.NSRSH1;

                   SET S_NSRMC=V1.NSRMC;

                   SET S_SHWDJZH=V1.SHWDJZH;

                   SET S_CZYID=V1.CZYID;

                   SET S_FJID=V1.FJID;

                   SET S_NSRFJID=V1.NSRFJID;

                   SET S_SQRQ=V1.SQRQ;

                   SET S_DQZT=V1.DQZT;

                   SET S_OPERATION=V1.IBMSNAP_OPERATION;                    

--主键列不能为空

IF  (S_HYZGID   IS  NULL   )      THEN

    SIGNAL   SQLSTATE '98000'

           SET  MESSAGE_TEXT  =   'THE PRIMARY KEY IS NOT PERMIT NULL :HYZGID'    ;

END IF ;

--对表进行转换，当增量标志为'I'的时候，只对目标表进行插入 ；当增量标志为‘D’的时候，对目标表相应行进行删除操作；当增量标志为‘U’的时候，先执行删除再执行插

IF    (S_OPERATION='I' )  THEN

  Insert into YHS.FPGL_HYDKZG(HYZGID,NSRSH,NSRMC,SHWDJZH,CZYID,FJID,NSRFJID,SQRQ,DQZT)

values(S_HYZGID,S_NSRSH,S_NSRMC,S_SHWDJZH,S_CZYID,S_FJID,S_NSRFJID,S_SQRQ,S_DQZT) ;

  SET S_SQLCODE=SQLCODE;

ELSEIF     (S_OPERATION='D' )  THEN

     delete from   YHS.FPGL_HYDKZG  where   HYZGID =S_HYZGID;

     SET S_SQLCODE=SQLCODE;

ELSEIF     (S_OPERATION='U' )  THEN

      delete from   YHS.FPGL_HYDKZG  where  HYZGID =S_HYZGID;

  Insert into YHS.FPGL_HYDKZG(HYZGID,NSRSH,NSRMC,SHWDJZH,CZYID,FJID,NSRFJID,SQRQ,DQZT)

values(S_HYZGID,S_NSRSH,S_NSRMC,S_SHWDJZH,S_CZYID,S_FJID,S_NSRFJID,S_SQRQ,S_DQZT) ;

  SET S_SQLCODE=SQLCODE;

END IF ;

--判断以上sql语句是否执行成功

IF(S_SQLCODE>=0)    THEN

--删除中间源表的相应行

delete from   yhs.tgtfp_hydkzg  where    HYZGID =S_HYZGID;

--操作行数加1

SET  rowcount=rowcount+1;

END IF;

END FOR;

        RETURN           rowcount;

END P1

image010.gif