yth.
Pada Package dan Package Bodies mempunyai tabel dgn nama yg sama tetapi
scriptnya beda dalam satu object schema.
Mohon bantuan dari show error script terlampir dgn message error sbb:
All of LG_UPDATES_NUMENTRIES, TABLE_UPDATETS must be declared
Terima kasih
Syam
____________________________________________________________________________________
Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how.
http://overview.mail.yahoo.com/
----------
IS
-- PL/SQL Block
FUNCTION F_SUM
(
lng_TIME_SERIES_PK_Basis IN NUMBER,
lng_TIME_SERIES_PK IN NUMBER,
str_Action IN VARCHAR2,
lng_VALUE IN NUMBER,
date_TIME IN DATE,
lng_OPERATION_PK IN NUMBER
) RETURN BOOLEAN
IS
bool_MASK BOOLEAN := TRUE;
lng_VALUE_Old NUMBER;
lng_VALUE_New NUMBER;
lng_DATA_RESULT_PK NUMBER;
dbl_AVAILABLE_COUNT NUMBER;
date_TIME_FROM DATE;
date_TIME_TO DATE;
BEGIN
DBMS_OUTPUT.PUT_LINE('IN SUMME');
DBMS_OUTPUT.PUT_LINE(str_Action);
IF str_Action='DELETE' or str_Action='INSERT' THEN
SELECT DATA_RESULT_PK, VALUE, TIME_FROM, TIME_TO
INTO lng_DATA_RESULT_PK, lng_VALUE_Old,
date_TIME_FROM,date_TIME_TO
FROM DATA_RESULT
WHERE TIME_SERIES_PK = lng_TIME_SERIES_PK
AND date_TIME >= TIME_FROM
AND date_TIME <TIME_TO;
IF NOT lng_DATA_RESULT_PK IS NULL THEN
-- the Case: DATA_RESULT.VALUE IS NULL and str_Action
is not possible, because
-- there are no data, that can be deleted.
IF lng_VALUE_Old IS NULL THEN
lng_VALUE_Old := 0;
END IF;
IF str_Action = 'INSERT' THEN
lng_VALUE_New := lng_VALUE_Old + lng_VALUE;
ELSIF str_Action = 'DELETE' THEN
lng_VALUE_New := lng_VALUE_Old - lng_VALUE;
IF lng_VALUE_New = 0 Then
DATA_TRIGGER.lng_UpdateTS_NUMENTRIES :=
DATA_TRIGGER.lng_UpdateTS_NUMENTRIES + 1;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).TIME_SERIES_PK:=lng_TIME_SERIES_PK_Basis;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).DATA_RESULT_PK:=
lng_DATA_RESULT_PK;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).TIME_FROM:=
date_TIME_FROM;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).TIME_TO:=
date_TIME_TO;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).OPERATION_PK:=lng_OPERATION_PK;
END IF;
END IF;
UPDATE DATA_RESULT
SET VALUE = lng_VALUE_New
WHERE DATA_RESULT_PK = lng_DATA_RESULT_PK;
DBMS_OUTPUT.PUT_LINE('NEUE SUMME: ' || lng_VALUE_New );
bool_MASK := FALSE;
END IF;
END IF;
RETURN bool_MASK;
EXCEPTION
WHEN NO_DATA_FOUND THEN
bool_MASK := TRUE;
RETURN bool_MASK;
END F_SUM;
FUNCTION F_AVERAGE
(
lng_TIME_SERIES_PK_Basis IN NUMBER,
lng_TIME_SERIES_PK IN NUMBER,
str_Action IN VARCHAR2,
lng_VALUE IN NUMBER,
date_TIME IN DATE
) RETURN BOOLEAN
IS
bool_Mask BOOLEAN := TRUE;
lng_VALUE_Old NUMBER;
lng_VALUE_New NUMBER;
lng_DATA_RESULT_PK NUMBER;
lng_DATA_RES_COUNT NUMBER;
BEGIN
IF str_Action='INSERT' or str_Action='DELETE' THEN
SELECT DATA_RESULT_PK,
VALUE,
DATA_RES_COUNT
INTO lng_DATA_RESULT_PK,
lng_VALUE_Old,
lng_DATA_RES_COUNT
FROM DATA_RESULT
WHERE TIME_SERIES_PK = lng_TIME_SERIES_PK
AND date_TIME >= TIME_FROM
AND date_TIME < TIME_TO;
IF NOT lng_DATA_RESULT_PK IS NULL THEN
IF lng_VALUE_Old IS NULL THEN
lng_VALUE_Old := 0;
lng_DATA_RES_COUNT := 0;
END IF;
IF str_Action='INSERT' THEN
lng_VALUE_New := (lng_VALUE_Old *
lng_DATA_RES_COUNT + lng_Value) / (lng_DATA_RES_COUNT +1);
lng_DATA_RES_COUNT := lng_DATA_RES_COUNT + 1;
UPDATE DATA_RESULT
SET VALUE = lng_VALUE_New,
DATA_RES_COUNT =
lng_DATA_RES_COUNT
WHERE DATA_RESULT_PK = lng_DATA_RESULT_PK;
bool_MASK := FALSE;
-- DBMS_OUTPUT.PUT_LINE('ALTER AVG: ' || lng_VALUE_New );
ELSIF str_Action = 'DELETE' THEN
--In this case, DATA_RES_COUNT cannot be NULL
IF lng_DATA_RES_COUNT > 1 THEN
lng_VALUE_New := (lng_VALUE_Old *
lng_DATA_RES_COUNT - lng_Value) / (lng_DATA_RES_COUNT - 1);
ELSE
lng_VALUE_New := NULL;
END IF;
UPDATE DATA_RESULT
SET VALUE = lng_VALUE_New,
DATA_RES_COUNT =
DATA_RES_COUNT - 1
WHERE DATA_RESULT_PK = lng_DATA_RESULT_PK;
bool_MASK := FALSE;
END IF;
DBMS_OUTPUT.PUT_LINE('NEUER AVG: ' ||
lng_VALUE_New );
END IF;
END IF;
RETURN bool_MASK;
EXCEPTION
WHEN NO_DATA_FOUND THEN
bool_MASK := TRUE;
RETURN bool_MASK;
END F_AVERAGE;
FUNCTION F_MIN
(
lng_TIME_SERIES_PK_Basis IN NUMBER,
lng_TIME_SERIES_PK IN NUMBER,
str_Action IN VARCHAR2,
lng_VALUE IN NUMBER,
date_TIME IN DATE
)
RETURN BOOLEAN
IS
bool_MASK BOOLEAN:=TRUE;
lng_VALUE_Old NUMBER;
lng_DATA_RESULT_PK NUMBER;
date_TIME_FROM DATE;
date_TIME_TO DATE;
BEGIN
IF str_Action='INSERT' or str_Action ='DELETE' THEN
SELECT DATA_RESULT_PK,
VALUE,
TIME_FROM,
TIME_TO
INTO lng_DATA_RESULT_PK,
lng_VALUE_Old,
date_TIME_FROM,
date_TIME_TO
FROM DATA_RESULT
WHERE TIME_SERIES_PK = lng_TIME_SERIES_PK
AND date_TIME >= TIME_FROM
AND date_TIME < TIME_TO;
DBMS_OUTPUT.PUT_LINE('Altes Min: ' || lng_VALUE_Old);
IF NOT lng_DATA_RESULT_PK IS NULL THEN
IF str_Action='INSERT' THEN
IF lng_VALUE_Old IS NULL OR lng_VALUE <
lng_VALUE_Old THEN
UPDATE DATA_RESULT
SET VALUE = lng_VALUE
WHERE DATA_RESULT_PK = lng_DATA_RESULT_PK;
DBMS_OUTPUT.PUT_LINE('NEUES MIN: ' || lng_VALUE
);
END IF;
bool_MASK := FALSE;
ELSIF str_Action ='DELETE' THEN
IF lng_VALUE = lng_VALUE_Old THEN
-- IF the value is the mininum value of
the intervall, then it cannot be decided,
-- whats the new minimum
DBMS_OUTPUT.PUT_LINE('Basiszeitreihe: ' ||
lng_TIME_SERIES_PK_Basis);
DATA_TRIGGER.lng_UpdateTS_NUMENTRIES :=
DATA_TRIGGER.lng_UpdateTS_NUMENTRIES + 1;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).TIME_SERIES_PK:=lng_TIME_SERIES_PK_Basis;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).DATA_RESULT_PK:=
lng_DATA_RESULT_PK;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).TIME_FROM:=
date_TIME_FROM;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).TIME_TO:=
date_TIME_TO;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).OPERATION_PK:=1081987;
bool_MASK := FALSE;
ELSE
-- IF the value is not the mininum
value of the intervall, then nothing must be done
-- and the intervall must not be
calculated new.
bool_MASK := FALSE;
END IF;
END IF;
ELSE
-- IF there is no intervall for the value, then the
time series must be masked to be
-- recalculated.
bool_MASK := TRUE;
END IF;
END IF;
RETURN bool_MASK;
EXCEPTION
WHEN NO_DATA_FOUND THEN
bool_MASK := TRUE;
RETURN bool_MASK;
END F_MIN;
FUNCTION F_MAX
(
lng_TIME_SERIES_PK_Basis IN NUMBER,
lng_TIME_SERIES_PK IN NUMBER,
str_Action IN VARCHAR2,
lng_VALUE IN NUMBER,
date_TIME IN DATE
)
RETURN BOOLEAN
IS
lng_VALUE_Old NUMBER;
lng_DATA_RESULT_PK NUMBER;
date_TIME_FROM DATE;
date_TIME_TO DATE;
bool_MASK BOOLEAN := TRUE;
BEGIN
IF str_Action = 'INSERT' OR str_Action='DELETE' THEN
SELECT DATA_RESULT_PK,
VALUE,
TIME_FROM,
TIME_TO
INTO lng_DATA_RESULT_PK,
lng_VALUE_Old,
date_TIME_FROM,
date_TIME_TO
FROM DATA_RESULT
WHERE TIME_SERIES_PK = lng_TIME_SERIES_PK
AND date_TIME >= TIME_FROM
AND date_TIME < TIME_TO;
IF NOT lng_DATA_RESULT_PK IS NULL THEN
IF str_Action = 'INSERT' THEN
IF lng_VALUE_Old IS NULL OR lng_VALUE > lng_VALUE_Old
THEN
UPDATE DATA_RESULT
SET VALUE = lng_VALUE
WHERE DATA_RESULT_PK = lng_DATA_RESULT_PK;
DBMS_OUTPUT.PUT_LINE('NEUES MAX: ' || lng_VALUE
);
END IF;
bool_MASK := FALSE;
ELSIF str_Action='DELETE' THEN
IF lng_VALUE = lng_VALUE_Old THEN
DATA_TRIGGER.lng_UpdateTS_NUMENTRIES :=
DATA_TRIGGER.lng_UpdateTS_NUMENTRIES + 1;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).TIME_SERIES_PK:=lng_TIME_SERIES_PK_Basis;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).DATA_RESULT_PK:=
lng_DATA_RESULT_PK;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).TIME_FROM:=
date_TIME_FROM;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).TIME_TO:=
date_TIME_TO;
DATA_TRIGGER.Table_UpdateTS(DATA_TRIGGER.lng_UpdateTS_NUMENTRIES).OPERATION_PK:=1081988;
bool_MASK := FALSE;
ELSE
-- IF the value is not the maxinum
value of the intervall, then nothing must be done
-- and the intervall must not be
calculated new.
bool_MASK := FALSE;
END IF;
END IF;
ELSE
-- IF there is no intervall for the value, then the
time series must be masked to be
-- recalculated.
bool_MASK := TRUE;
END IF;
END IF;
RETURN bool_MASK;
EXCEPTION
WHEN NO_DATA_FOUND THEN
bool_MASK := TRUE;
RETURN bool_MASK;
END F_MAX;
PROCEDURE P_LOAD_CURSOR
(
dbl_TIME_SERIES_PK IN NUMBER
)
IS
CURSOR c_Parent_TS IS
SELECT TIME_SERIES_PK_PARENT AS TIME_SERIES_PK,
OPERATION_PK,
DATE_CHANGED_MIN,
DATE_CHANGED_MAX,
DATE_CHANGED_MIN AS DATE_CHANGED_MIN_MASK,
DATE_CHANGED_MAX AS DATE_CHANGED_MAX_MASK,
PRECALCULATE
FROM TS_TS,
V_TIME_SERIES
WHERE TS_TS.TIME_SERIES_PK_CHILD = dbl_TIME_SERIES_PK
AND
TS_TS.TIME_SERIES_PK_PARENT = V_TIME_SERIES.TIME_SERIES_PK;
BEGIN
DATA_TRIGGER.lng_DerivedTS_NUMENTRIES:=0;
FOR v_Parent IN c_Parent_TS LOOP
DATA_TRIGGER.lng_DerivedTS_NUMENTRIES
:= DATA_TRIGGER.lng_DerivedTS_NUMENTRIES + 1;
DATA_TRIGGER.Table_DerivedTS(DATA_TRIGGER.lng_DerivedTS_NUMENTRIES) := v_Parent;
-- DBMS_OUTPUT.PUT_LINE('Intervall ');
-- DBMS_OUTPUT.PUT_LINE(v_Parent.DATE_CHANGED_MIN_MASK);
-- DBMS_OUTPUT.PUT_LINE(v_Parent.DATE_CHANGED_MAX_MASK);
END LOOP;
DATA_TRIGGER.dbl_TIME_SERIES_PK_BASIC:= dbl_TIME_SERIES_PK;
END P_LOAD_CURSOR;
PROCEDURE P_CHANGE_CHANGE_INTERVALL
(
i IN NUMBER,
date_TIME IN DATE
)
IS
BEGIN
-- DBMS_OUTPUT.PUT_LINE('In Change Intervall');
IF DATA_TRIGGER.Table_DerivedTS(i).DATE_CHANGED_MIN_MASK IS NULL OR
DATA_TRIGGER.Table_DerivedTS(i).DATE_CHANGED_MAX_MASK IS NULL THEN
DATA_TRIGGER.Table_DerivedTS(i).DATE_CHANGED_MIN_MASK := date_TIME;
DATA_TRIGGER.Table_DerivedTS(i).DATE_CHANGED_MAX_MASK :=
date_TIME;
ELSE
IF date_TIME <
DATA_TRIGGER.Table_DerivedTS(i).DATE_CHANGED_MIN_MASK THEN
DATA_TRIGGER.Table_DerivedTS(i).DATE_CHANGED_MIN_MASK
:= date_TIME;
END IF;
IF date_TIME >
DATA_TRIGGER.Table_DerivedTS(i).DATE_CHANGED_MAX_MASK THEN
DATA_TRIGGER.Table_DerivedTS(i).DATE_CHANGED_MAX_MASK :=
date_TIME;
END IF;
END IF;
END P_CHANGE_CHANGE_INTERVALL;
PROCEDURE P_TIME_SERIES_LEVEL2
(
lng_TIME_SERIES_PK IN NUMBER,
str_Action IN VARCHAR2,
lng_VALUE IN NUMBER,
date_TIME IN DATE
)
IS
lng_OPERATION_PK NUMBER;
date_DATE_CHANGED_MIN DATE;
date_DATE_CHANGED_MAX DATE;
date_DATE_AVAILABLE_FROM DATE;
date_DATE_AVAILABLE_TO DATE;
lng_DATE_AVAILABLE_COUNT NUMBER;
bool_MASK BOOLEAN
:= TRUE;
bool_UPDATE_DATA_RESULT BOOLEAN := TRUE;
Table_DerivedTS
DATA_TRIGGER.Type_Table_DerivedTS;
lng_DerivedTS_NUMENTRIES BINARY_INTEGER := 0;
BEGIN
-- DBMS_OUTPUT.PUT_LINE('Basiszeitreihe ' || lng_TIME_SERIES_PK);
-- DBMS_OUTPUT.PUT_LINE(str_Action || ' ' || date_TIME );
IF DATA_TRIGGER.dbl_TIME_SERIES_PK_BASIC = -1 OR
DATA_TRIGGER.dbl_TIME_SERIES_PK_BASIC <> lng_TIME_SERIES_PK THEN
P_LOAD_CURSOR(lng_TIME_SERIES_PK);
END IF;
Table_DerivedTS:=DATA_TRIGGER.Table_DerivedTS;
lng_DerivedTS_NUMENTRIES:=DATA_TRIGGER.lng_DerivedTS_NUMENTRIES;
FOR i IN 1..lng_DerivedTS_NUMENTRIES LOOP
-- If the Time Series should not be precalculated, then
DATA_RESULT must not be
-- updated. But the Time Series has to be masked.
IF Table_DerivedTS(i).PRECALCULATE <> - 1 Then
bool_UPDATE_DATA_RESULT := FALSE;
ELSE
bool_UPDATE_DATA_RESULT:= TRUE;
END IF;
-- If the Time Series has already been masked and the time of
the data point is
-- in the change intervall, then neither DATA_RESULT must
be updated nor the
-- Change Intervall has to be adapted.
bool_MASK := TRUE;
IF Table_DerivedTS(i).DATE_CHANGED_MIN_MASK IS NOT NULL THEN
IF date_TIME >=
Table_DerivedTS(i).DATE_CHANGED_MIN_MASK AND
date_TIME <=
Table_DerivedTS(i).DATE_CHANGED_MAX_MASK THEN
bool_UPDATE_DATA_RESULT := FALSE;
bool_MASK := FALSE;
END iF;
END IF;
IF bool_UPDATE_DATA_RESULT THEN
IF Table_DerivedTS(i).OPERATION_PK = 1081985 THEN
bool_MASK:=F_SUM(lng_TIME_SERIES_PK,
Table_DerivedTS(i).TIME_SERIES_PK,
str_Action,
lng_VALUE,
date_TIME,
1081985);
ELSIF Table_DerivedTS(i).OPERATION_PK = 1081992 THEN
-- COUNT(VALUE) liegt vor - gleiche Fall, wie SUM, mit Value_Old =
1 und Value_New = 1
bool_MASK:=F_SUM(lng_TIME_SERIES_PK,
Table_DerivedTS(i).TIME_SERIES_PK,
str_Action,
1,
date_TIME,
1081992);
ELSIF Table_DerivedTS(i).OPERATION_PK = 1081991 THEN
bool_MASK:=F_AVERAGE(lng_TIME_SERIES_PK,
Table_DerivedTS(i).TIME_SERIES_PK,
str_Action,
lng_VALUE,
date_TIME);
ELSIF Table_DerivedTS(i).OPERATION_PK = 1081987 THEN
bool_MASK:=F_MIN(lng_TIME_SERIES_PK,
Table_DerivedTS(i).TIME_SERIES_PK,
str_Action,
lng_VALUE,
date_TIME);
ELSIF Table_DerivedTS(i).OPERATION_PK = 1081988 THEN
bool_MASK:=F_MAX(lng_TIME_SERIES_PK,
Table_DerivedTS(i).TIME_SERIES_PK,
str_Action,
lng_VALUE,
date_TIME);
END IF;
END IF;
IF bool_MASK THEN
P_CHANGE_CHANGE_INTERVALL(i,
date_TIME);
END IF;
END LOOP;
END P_TIME_SERIES_LEVEL2;
END TIME_SERIES_MASK;
[Non-text portions of this message have been removed]
