Quellcode Prozedur b

Das DB-Objekt selbst

CREATE PROCEDURE "demoadm".b_journal()
  DEFINE dw_oper varchar(50,0);
  DEFINE err_id  int;
  DEFINE err_sql int;
  DEFINE err_isam int;
  DEFINE err_txt varchar(150,0);
  DEFINE err_msg varchar(250,0);
  DEFINE i  int;
  DEFINE pv_action char(1);
  DEFINE sv_mandant char(2);

  DEFINE sv_skto_nr char(15);
  DEFINE sv_jahr decimal(4,0);
  DEFINE sv_periode decimal(2,0);
  DEFINE sv_soll decimal(13,2);
  DEFINE sv_haben decimal(13,2);

  LET dw_oper = 'Start';
  LET err_id = 0;
  BEGIN WORK;
  SET LOCK MODE TO WAIT 10;
  BEGIN
    ON EXCEPTION
    SET err_sql, err_isam, err_txt
    LET err_msg = 'ERR[' ||err_sql|| ',' ||err_isam|| ',' ||err_id|| ']:' ||err_txt||
  ' # ' ||dw_oper;
    SYSTEM 'echo "B_journal: '||err_msg||'" >> /var/tmp/log.dwb';
    END EXCEPTION;
    FOREACH dw_curs1 FOR SELECT
    l.er_id,
    l.lv_action,
    l.lv_mandant,
    l.lv_skto_nr,
    l.lv_jahr,
    l.lv_periode,
    l.lv_soll,
    l.lv_haben
    INTO
    err_id,
    pv_action,
    sv_mandant,
    sv_skto_nr,
    sv_jahr,
    sv_periode,
    sv_soll,
    sv_haben
    FROM DWE_journal l
    ON EXCEPTION
      UPDATE DWE_journal SET er_retry = er_retry + 1 WHERE er_id = err_id ;
    END EXCEPTION;

    UPDATE sr_sh_skto SET
      soll  = soll  + sv_soll,
      haben = haben + sv_haben
    WHERE (skto_nr = sv_skto_nr)
      AND (mandant = sv_mandant)
      AND (jahr    = sv_jahr)
      AND (periode = sv_periode)
    ;
    IF DBINFO('sqlca.sqlerrd2') < 1 THEN
      INSERT INTO sr_sh_skto (
      skto_nr,
      mandant,
      jahr,
      periode,
      soll,
      haben
      )
      VALUES (
      sv_skto_nr,
      sv_mandant,
      sv_jahr,
      sv_periode,
      sv_soll,
      sv_haben
      );
    END IF

    LET dw_oper = 'Deleting from DWE_journal';

    DELETE FROM DWE_journal WHERE CURRENT OF dw_curs1;
    END FOREACH
  END
  SET LOCK MODE TO NOT WAIT;
  COMMIT WORK;

END PROCEDURE
Quellcode Prozedur b_journal

Das DB-Objekt selbst

Abhängige Objekte, Rechte
