create procedure "demoadm".wt_i_btkini(
psiWasTun SMALLINT DEFAULT NULL, -- LetDefault 0
psiDebugFlag SMALLINT DEFAULT 0,
pcDebugFile CHAR(40) DEFAULT NULL
) RETURNING INTEGER ;
DEFINE riAnz INTEGER;
DEFINE iDBSqlError, iDBIsamError INTEGER;
DEFINE cDBErrMsg CHAR(72);
IF psiDebugFlag > 0 THEN
IF pcDebugFile = '' OR pcDebugFile IS NULL THEN
LET pcDebugFile = '/var/tmp/wt_i_btkini.trc';
END IF
IF MOD(psiDebugFlag,2) = 1 THEN
SET DEBUG FILE TO pcDebugFile WITH APPEND ;
ELSE SET DEBUG FILE TO pcDebugFile ;
END IF
TRACE ON;
END IF
IF psiWasTun IS NULL THEN LET psiWasTun = 0 ; END IF
-- CALL "informix".n_v_error( 'wt_i_btkini: Versinr erforderlich!' );
CALL "informix".nt_v_beginwork();
lock table "demoadm".btrkonto in exclusive mode;
create temp table t_btr ( ibtrkto integer, fibukto char(7) ) with no log;
set pdqpriority 20;
insert into t_btr ( ibtrkto, fibukto )
select mod(psk_pkto,1000000), psk_pkto from psk
where psk_pkto between 7000000 and 7099999 and psk_mandant = '01'
group by 1, 2 ;
LET riAnz = DBINFO('sqlca.sqlerrd2');
delete from "demoadm".btrkonto
where btk_ibtrkto not in ( select ibtrkto from t_btr ) ;
delete from t_btr
where ibtrkto in ( select btk_ibtrkto from "demoadm".btrkonto ) ;
insert into "demoadm".btrkonto (btk_pkto,btk_ibtrkto,btk_btrkto,btk_fibukto)
select fibukto, ibtrkto, fibukto[2,7], fibukto from t_btr ;
set pdqpriority 0;
drop table t_btr ;
BEGIN
ON EXCEPTION END EXCEPTION WITH RESUME -- wegen Rechten, sollte gehen
update statistics high for table "demoadm".btrkonto resolution 1;
update statistics low for table "demoadm".btrkonto ;
END
CALL "informix".nt_v_commitwork();
RETURN riAnz ;
end procedure
grant execute on "demoadm".wt_i_btkini to "dbadmin" as "demoadm"; grant execute on "demoadm".wt_i_btkini to "dvabt" as "demoadm"; grant execute on "demoadm".wt_i_btkini to "public" as "demoadm";
HTML-Autor: Rohloff, Datenbank: demodb, Datenstand: 31.05.00 17:07