Quellcode Prozedur p_01hier_kst

Das DB-Objekt selbst

CREATE DBA PROCEDURE "demoadm".p_01hier_kst(
  a_hier        char(5)  -- hierarchy name (short)
)
  DEFINE er_msg varchar(100,0);
  DEFINE h_nr   int;  -- kst_hier:nr
  DEFINE h_bez  char(30); -- kst_hier:ext_bez
  DEFINE i      int;
  DEFINE l_nr   int;
  DEFINE l_id   char(15);
  DEFINE l_bez  char(30);
  DEFINE vv     int;
  DEFINE v0     char(15);
  DEFINE v1     char(15);
  DEFINE v2     char(15);
  DEFINE v3     char(15);
  DEFINE v4     char(15);
  DEFINE v5     char(15);
  DEFINE v6     char(15);
  DEFINE v7     char(15);
  DEFINE v8     char(15);
  DEFINE v9     char(15);
  DEFINE vbez0  char(30);
  DEFINE vbez1  char(30);
  DEFINE vbez2  char(30);
  DEFINE vbez3  char(30);
  DEFINE vbez4  char(30);
  DEFINE vbez5  char(30);
  DEFINE vbez6  char(30);
  DEFINE vbez7  char(30);
  DEFINE vbez8  char(30);
  DEFINE vbez9  char(30);

  LET i=0;

  DELETE FROM hier_kst_hn WHERE (hier_nr = a_hier) AND (mandant = '01');
  DELETE FROM hier_kst_10 WHERE (hier_nr = a_hier) AND (mandant = '01');

  FOREACH SELECT nr,ext_bez INTO h_nr,h_bez FROM demo01:kst_hier
  WHERE (ext_nr = a_hier) AND (pruef = 1)
    LET i = i + 1;
  END FOREACH
  IF i <> 1 THEN -- Hierarchy does not exist or is inconsistent
    LET er_msg = 'Hierarchy: ' || a_hier || ' not found or inconsistent';
    RAISE EXCEPTION -90,0,er_msg;
  END IF

  -- OK Hierarchy can be processed

  INSERT INTO hier_kst_hn (mandant,hier_nr,hier_name) VALUES ('01',a_hier,h_bez);

  FOREACH SELECT nr, ext_nr, ext_bez, vater INTO l_nr, l_id, l_bez, vv
  FROM demo01:ksth_k
  WHERE (hier_nr = h_nr) AND (kst = 1)
    -- We have a leaf now we must trace path up to the root
    LET vbez0,vbez1,vbez2,vbez3,vbez4,vbez5,vbez6,vbez7,vbez8 = ' ' , ' ' , ' ' , ' ' , ' ' ,
  ' ' , ' ' , ' ' , ' ' ;
    LET vbez9 = ' ' ;
    LET v0,v1,v2,v3,v4,v5,v6,v7,v8 = ' ',' ',' ',' ',' ',' ',' ',' ',' ' ;
    LET v9 = ' ' ;
    WHILE vv <> 0
    LET vbez9,vbez8,vbez7,vbez6,vbez5,vbez4,vbez3,vbez2,vbez1 = vbez8,vbez7,vbez6,vbez5,
  vbez4,vbez3,vbez2,vbez1,vbez0 ;
    LET v9,v8,v7,v6,v5,v4,v3,v2,v1 = v8,v7,v6,v5,v4,v3,v2,v1,v0 ;
    SELECT ext_nr, ext_bez, vater INTO v0, vbez0, vv
    FROM demo01:ksth_k k
    WHERE (k.nr = vv) AND (k.hier_nr = h_nr) ;
    END WHILE
    INSERT INTO hier_kst_10
    VALUES ('01',a_hier,l_id,v0,v1,v2,v3,v4,v5,v6,v7,v8,v9,
      vbez0,vbez1,vbez2,vbez3,vbez4,vbez5,vbez6,vbez7,vbez8,vbez9);
  END FOREACH
END PROCEDURE

Abhängige Objekte, Rechte