postgresql - 在 postgres 中使用光标批量收集

问题描述

我们正在从 Oracle 迁移到 Postgres。我有下一个代码。我被批量收集光标困住了。我尝试使用 insert into .. select * from.. 但是当这段代码检查某些特定列时，我很困惑。
创建或替换函数 usmqa.pk_vpdata_load_load_vdrarmhlink（输出 err_code bigint，输出 vdrarmhlink_errmsg 文本）返回记录语言“plpgsql”
$BODY$ 声明
V_COUNT_VDRARMHLINK bigint;

CUR_GET_INT_VDRARMHLINK CURSOR FOR
  SELECT VENLINKID,
         VENARINTNO,
         VENMVINTNO,
         VENLINKEFFDT,
         VENLINKTRMDT,
         VENACT,
         VENRUNSTS,
         VENPROCID,
         VENERRCODE
    FROM INT_VDRARMHLINK
   WHERE VENRUNSTS = -1
     AND coalesce(VENERRMSG::text, '') = ''
     AND coalesce(VENERRCODE::text, '') = '';

        -- TYPE TYP_GET_INT_VDRARMHLINK IS TABLE OF RECORD INDEX BY integer;    Oracle code
       --REC_GET_INT_VDRARMHLINK TYP_GET_INT_VDRARMHLINK;    --- Oracle 
     REC_GET_INT_VDRARMHLINK int[];
      I int;

BEGIN

glo_error_var:= 0;
UPDATE INT_VDRARMHLINK SET VENRUNSTS = -1 WHERE VENRUNSTS = 0;


glo_error_var:= 1;
UPDATE INT_VDRARMHLINK
   SET VENERRMSG  = 'VENDOR INTERNAL NUMBER DOES NOT EXISTS VDREXT TABLE',
       VENERRCODE = -1,
       VENRUNSTS  = -2
 WHERE NOT EXISTS (SELECT 1 FROM VDREXT WHERE VDXINTNO = VENARINTNO)
    OR NOT EXISTS (SELECT 1 FROM VDREXT WHERE VDXINTNO = VENMVINTNO);
COMMIT;


  glo_error_var:= 2;
OPEN CUR_GET_INT_VDRARMHLINK;
LOOP
 -- FETCH CUR_GET_INT_VDRARMHLINK BULK COLLECT ---- Oracle code
   -- INTO REC_GET_INT_VDRARMHLINK LIMIT 1000; ---- Oracle code

 -- FOR I IN 1 .. REC_GET_INT_VDRARMHLINK.COUNT LOOP   -- Oracle code 

SELECT arrat_agg(CUR_GET_INT_VDRARMHLINK)
into REC_GET_INT_VDRARMHLINK
FROM CUR_GET_INT_VDRARMHLINK;

FOREACH I SLICE 1 in ARRAY REC_GET_INT_VDRARMHLINK
LOOP

    BEGIN
      SELECT COUNT(1)
        INTO STRICT V_COUNT_VDRARMHLINK
        FROM VDRARMHLINK
       WHERE  VENARINTNO = REC_GET_INT_VDRARMHLINK[I].VENARINTNO
         AND VENMVINTNO = REC_GET_INT_VDRARMHLINK[I].VENMVINTNO;
    END;

    --INSERT
    IF ( V_COUNT_VDRARMHLINK = 0 AND
        REC_GET_INT_VDRARMHLINK[I].VENRUNSTS = -1) THEN


    SELECT VENLINKID,
         VENARINTNO,
         VENMVINTNO,
         VENLINKEFFDT,
         VENLINKTRMDT,
         VENACT,
         VENRUNSTS,
         VENPROCID,
         VENERRCODE
    FROM INT_VDRARMHLINK
   WHERE VENRUNSTS = -1
     AND coalesce(VENERRMSG::text, '') = ''
     AND coalesce(VENERRCODE::text, '') = '';

      BEGIN
        INSERT INTO VDRARMHLINK
        (VENLINKID,
           VENARINTNO,
           VENMVINTNO,
           VENLINKEFFDT,
           VENLINKTRMDT)        
        VALUES (REC_GET_INT_VDRARMHLINK[I].VENLINKID,
           REC_GET_INT_VDRARMHLINK[I].VENARINTNO,
           REC_GET_INT_VDRARMHLINK[I].VENMVINTNO,
           REC_GET_INT_VDRARMHLINK[I].VENLINKEFFDT,
           REC_GET_INT_VDRARMHLINK[I].VENLINKTRMDT);

        UPDATE INT_VDRARMHLINK
           SET VENRUNSTS = 1
         WHERE 
        VENPROCID = REC_GET_INT_VDRARMHLINK[I].VENPROCID
           AND VENERRCODE = NULL
           AND VENERRMSG = NULL;

      END;

    END IF;


    IF (V_COUNT_VDRARMHLINK = 1) THEN

      BEGIN

        UPDATE VDRARMHLINK
           SET VENLINKEFFDT = REC_GET_INT_VDRARMHLINK[I].VENLINKEFFDT,
               VENLINKTRMDT = REC_GET_INT_VDRARMHLINK[I].VENLINKTRMDT
         WHERE VENLINKID = REC_GET_INT_VDRARMHLINK[I].VENLINKID
           AND VENARINTNO = REC_GET_INT_VDRARMHLINK[I].VENARINTNO
           AND VENMVINTNO = REC_GET_INT_VDRARMHLINK[I].VENMVINTNO;

        UPDATE INT_VDRARMHLINK
           SET VENRUNSTS = 1
         WHERE  VENPROCID = REC_GET_INT_VDRARMHLINK[I]
        .VENPROCID
           AND VENLINKID = REC_GET_INT_VDRARMHLINK[I]
        .VENLINKID
           AND VENARINTNO = REC_GET_INT_VDRARMHLINK[I]
        .VENARINTNO
           AND VENMVINTNO = REC_GET_INT_VDRARMHLINK[I]
        .VENMVINTNO
           AND VENERRCODE = NULL
           AND VENERRMSG = NULL;

      END;

    END IF;

    --DELETE
    IF (REC_GET_INT_VDRARMHLINK[I].VENACT = 2 AND V_COUNT_VDRARMHLINK = 1) THEN

      BEGIN

        UPDATE VDRARMHLINK
           SET VENLINKEFFDT = REC_GET_INT_VDRARMHLINK[I].VENLINKEFFDT,
               VENLINKTRMDT = clock_timestamp()
         WHERE VENLINKID = REC_GET_INT_VDRARMHLINK[I]
        .VENLINKID
           AND VENARINTNO = REC_GET_INT_VDRARMHLINK[I]
        .VENARINTNO
           AND VENMVINTNO = REC_GET_INT_VDRARMHLINK[I].VENMVINTNO;

        UPDATE INT_VDRARMHLINK
           SET VENRUNSTS = 1
         WHERE VENACT = 2
           AND VENPROCID = REC_GET_INT_VDRARMHLINK[I]
        .VENPROCID
           AND VENLINKID = REC_GET_INT_VDRARMHLINK[I]
        .VENLINKID
           AND VENARINTNO = REC_GET_INT_VDRARMHLINK[I]
        .VENARINTNO
           AND VENMVINTNO = REC_GET_INT_VDRARMHLINK[I]
        .VENMVINTNO
           AND VENERRCODE = NULL
           AND VENERRMSG = NULL;


    ELSIF (REC_GET_INT_VDRARMHLINK[I]
          .VENACT = 2 AND V_COUNT_VDRARMHLINK = 0) THEN

      UPDATE INT_VDRARMHLINK
         SET VENRUNSTS  = -3,
             VENERRCODE = -300,
             VENERRMSG  = 'THERE IS NO VENODR AR MERCHANDISE IN VP TO DELETE'
       WHERE VENACT = 2
         AND VENPROCID = REC_GET_INT_VDRARMHLINK[I]
      .VENPROCID
         AND VENERRCODE = NULL
         AND VENERRMSG = NULL;
    END IF;


  END LOOP;
  EXIT WHEN NOT FOUND; /* apply on CUR_GET_INT_VDRARMHLINK */
END LOOP;
CLOSE CUR_GET_INT_VDRARMHLINK;

   END;

   $BODY$;
标签： postgresql
postgresql - 在 postgres 中使用光标批量收集

问题描述

解决方案

推荐阅读
