1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
|
CREATE OR REPLACE PROCEDURE DividendCalcul IS
CURSOR data_cur IS SELECT r1.label, r1.DIVIDEND_VALUE-r2.DIVIDEND_VALUE as fdiv
FROM (SELECT HISTO_INDEX.DIVIDEND_VALUE, INDEX_TABLE.LABEL, INDEX_TABLE.INDEX_ID
FROM HISTO_INDEX, INDEX_TABLE
WHERE HISTO_INDEX.INDEX_ID=INDEX_TABLE.INDEX_ID
AND HISTO_INDEX.DATE_ID=(SELECT MAX(DATE_ID) FROM HISTO_INDEX)
AND INDEX_TABLE.TYPE='Index') r1,
(SELECT HISTO_INDEX.DIVIDEND_VALUE, INDEX_TABLE.LABEL, INDEX_TABLE.INDEX_ID
FROM HISTO_INDEX, INDEX_TABLE
WHERE HISTO_INDEX.INDEX_ID=INDEX_TABLE.INDEX_ID
AND DATE_ID=(SELECT MAX(DATE_ID) FROM HISTO_INDEX WHERE HISTO_INDEX.DATE_ID NOT IN (SELECT MAX(DATE_ID) FROM HISTO_INDEX))
AND INDEX_TABLE.TYPE='Index') r2
WHERE r1.INDEX_ID=r2.INDEX_ID;
BEGIN
for c in data_cur loop
UPDATE DIFF_TABLE SET DIFF_VALUE=c.fdiv WHERE LABEL=c.label;
end loop;
END; |
Partager