Incrementation automatique lors d'un update
Bonjour a tous,
Voila, novice en sql et newbie en oracle,
j'ai un petit soucis avec ce bloque pl
ca doit etre tres simple mais ....
Voila, lors d'un update (voir commentaire dans le code), je dois incrementer un id pour chaque ligne updated, puis le remettre a 1 pour recommencer l'incrementation au prochain update.
J'ai essaye avec une sequence en la recreant a chaque fois mais c'est pas tres elegant
Voici le code
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
| DECLARE
--variable to update
vNewOrderId ph2_item.order_id%TYPE;
vNewItemId ph2_item.item_id%TYPE := 0;
--Values of the current row of cursor to be compqred
vCrOrderid ph2_item.order_id%TYPE;
vCrCustomerid ph2_item.customer_id%TYPE;
vCrShipdate ph2_item.ship_date%TYPE;
--Values of precedent row to be compared
vCkOrderid ph2_item.order_id%TYPE;
vCkCustomerid ph2_item.customer_id%TYPE;
vCkShipdate ph2_item.ship_date%TYPE;
CURSOR cr_consolid
IS
SELECT
order_id,
customer_id,
ship_date
FROM
ph2_item
GROUP BY
order_id,
customer_id,
order_date,
ship_date
ORDER BY
order_date;
BEGIN
SELECT (MAX(order_id)+1) INTO vNewOrderid FROM sales_order;
vCkOrderid := 1;
vCkCustomerid := 1;
vCkShipdate := '01-JAN-11';
OPEN cr_consolid;
LOOP
FETCH cr_consolid INTO vCrOrderid, vCrCustomerid, vCrShipdate;
EXIT WHEN cr_consolid%NOTFOUND;
IF(vCkCustomerid = vCrCustomerid) AND (vCkShipdate = vCrShipdate) THEN
UPDATE ph2_item
SET order_id = vNewOrderId, item_id = vNewItemId-- ici je dois incrementer l'id pour
WHERE customer_id = vCrCustomerid --chaque ligne qui sera 'updated'
AND ship_date = vCrShipdate;
vNewItemId := vNewItemId +1;
vCkOrderid := vCrOrderid;
vCkCustomerid := vCrCustomerid;
vCkShipdate := vCrShipdate;
ELSE
UPDATE ph2_item
SET order_id = vNewOrderId,
WHERE customer_id = vCrCustomerid
AND ship_date = vCrShipdate;
vNewOrderId := vNewOrderId +1;
vNewItemId :=1;
vCkOrderid := vCrOrderid;
vCkCustomerid := vCrCustomerid;
vCkShipdate := vCrShipdate;
END IF;
END LOOP;
CLOSE cr_consolid;
END;
/ |
Merci a tous :ccool: