Вот собственно код процедуры, где "клинит" кассовый модуль:
Код:
PROCEDURE CREATEUKMCLICLASS( PRICETYPE IN INTEGER )
IS
TRANSSIZE PLS_INTEGER := 10000;
TRANSROW INTEGER := 0;
VIDSTART INTEGER;
VIDEND INTEGER;
VIDMIN INTEGER;
VIDMAX INTEGER;
VIDBETWEEN INTEGER := 2000;
BEGIN
EXECUTE IMMEDIATE 'truncate table TTUkmCliClass';
INSERT
INTO TTUKMCLICLASS
( TREE, BARCODE, PERCENT )
SELECT
X.TREE, '!' || T.PREFIX, C.PERCENT
FROM TTDISCTYPECHANGES T, SMDISCPERSCLASS C, SACARDCLASS X
WHERE X.ID = C.IDCLASS AND C.CARDTYPE = T.ID AND C.PRICEID = PRICETYPE;
COMMIT;
SELECT
MIN( ID ), MAX( ID )
INTO VIDMIN, VIDMAX
FROM SACARDCLASS;
VIDSTART := VIDMIN;
VIDEND := VIDSTART + VIDBETWEEN;
LOOP
INSERT
INTO TTUKMCLICLASS
( TREE, BARCODE, PERCENT )
SELECT
X.TREE, T.CODE, C.PERCENT
FROM TTDISCCARDCHANGES T, SMDISCPERSCLASS C, SACARDCLASS X
WHERE C.CARDTYPE = T.CARDTYPE AND C.PRICEID = PRICETYPE AND C.IDCLASS BETWEEN VIDSTART AND VIDEND AND X.ID = C.IDCLASS AND ( T.ISCOLLECT = '0' );
COMMIT;
VIDSTART := VIDEND + 1;
EXIT WHEN ( VIDSTART > VIDMAX );
END LOOP;
FOR R IN ( SELECT
X.TREE, T.CODE, C.PERCENT
FROM TTDISCCARDCHANGES T, SMDISCCARDCLASS C, SACARDCLASS X
WHERE C.IDCLASS = X.ID AND C.CARDCODE = T.CODE AND C.PRICEID = PRICETYPE AND ( T.ISCOLLECT = '0' )
ORDER BY LENGTH( X.TREE ) ASC )
LOOP
IF R.TREE = '#' THEN
DELETE
FROM TTUKMCLICLASS C
WHERE C.BARCODE = R.CODE;
ELSE
DELETE
FROM TTUKMCLICLASS C
WHERE C.BARCODE = R.CODE AND C.TREE LIKE ( R.TREE || '%' );
END IF;
INSERT
INTO TTUKMCLICLASS
( TREE, BARCODE, PERCENT )
VALUES( R.TREE, R.CODE, R.PERCENT );
TRANSROW := TRANSROW + 1;
IF TRANSROW > TRANSSIZE THEN
COMMIT;
TRANSROW := 0;
END IF;
END LOOP;
COMMIT;
VIDSTART := VIDMIN;
VIDEND := VIDSTART + VIDBETWEEN;
LOOP
INSERT
INTO TTUKMCLICLASS
( TREE, BARCODE, PERCENT )
SELECT
X.TREE, T.CODE, C.PERCENT
FROM TTDISCCARDCHANGES T, SMCOLLECTDISCCARD C, SACARDCLASS X
WHERE C.CODE = T.CODE AND C.IDCLASS BETWEEN VIDSTART AND VIDEND AND X.ID = C.IDCLASS AND ( T.ISCOLLECT != '0' );
COMMIT;
VIDSTART := VIDEND + 1;
EXIT WHEN ( VIDSTART > VIDMAX );
END LOOP;
COMMIT;
END; А конкретнее я так понимаю не выполняется условие выхода из цикла:
Код:
LOOP
INSERT
INTO TTUKMCLICLASS
( TREE, BARCODE, PERCENT )
SELECT
X.TREE, T.CODE, C.PERCENT
FROM TTDISCCARDCHANGES T, SMDISCPERSCLASS C, SACARDCLASS X
WHERE C.CARDTYPE = T.CARDTYPE AND C.PRICEID = PRICETYPE AND C.IDCLASS BETWEEN VIDSTART AND VIDEND AND X.ID = C.IDCLASS AND ( T.ISCOLLECT = '0' );
COMMIT;
VIDSTART := VIDEND + 1;
EXIT WHEN ( VIDSTART > VIDMAX );
END LOOP; от этого и такая высокая нагрузка на Oracle при работе процедуры... Сейчас буду дальше разбираться