存储过程

create or replace
PROCEDURE SP_RESV_REMOVEPRODUCT(
V_RESERVATION_CODE in varchar2 ,
V_FLOW_PRO_ID in varchar2 ,
V_PRODUCT_IDS IN VARCHAR2 ,
V_HL_CENTER_ID IN VARCHAR2 )
AS
S_PACKAGECOUNT int ; --套餐
S_PACKAGEPRODUCTID VARCHAR2(36);
CURSOR CSR_ITEM
is
SELECT ITEM.ID,ITEM.RESERVATION_CODE,ITEM.PRODUCT_ID,ITEM.FLOW_PRO_ID,ITEM.AMOUNT,ITEM.ACTUAL_AMOUNT,ITEM.ORG_AMOUNT
from FLOW_RESERVATION_ITEM ITEM
WHERE ITEM.PRODUCT_ID IN (V_PRODUCT_IDS)
and ITEM.HL_CENTER_ID =V_HL_CENTER_ID
AND ITEM.FLOW_PRO_ID 　　　=V_FLOW_PRO_ID
AND ITEM.RESERVATION_CODE=V_RESERVATION_CODE;
ITEMINFO CSR_ITEM%ROWTYPE;
BEGIN
FOR ITEMINFO IN CSR_ITEM
LOOP
UPDATE FLOW_RESERVATION_ITEM
SET FLOW_RESERVATION_ITEM.IS_ADDITIONAL='2'
WHERE ID =ITEMINFO.ID;

SELECT PRO.PRODUCT_ID INTO S_PACKAGEPRODUCTID FROM FLOW_RESERVATION_PRO PRO WHERE PRO.ID=ITEMINFO.FLOW_PRO_ID;
SELECT COUNT(*)
INTO S_PACKAGECOUNT
from EXAM_PACKAGE
WHERE EXAM_PACKAGE.PRODUCT_ID=S_PACKAGEPRODUCTID;
--是否是套餐
IF S_PACKAGECOUNT>0 THEN
--1.改金额
UPDATE FLOW_RESERVATION_PRO
SET FLOW_RESERVATION_PRO.ORG_AMOUNT =FLOW_RESERVATION_PRO.ORG_AMOUNT-ITEMINFO.ORG_AMOUNT,
FLOW_RESERVATION_PRO.ACTUAL_AMOUNT = FLOW_RESERVATION_PRO.ACTUAL_AMOUNT -ITEMINFO.ACTUAL_AMOUNT ,
FLOW_RESERVATION_PRO.DISCOUNT_AMOUNT=FLOW_RESERVATION_PRO.DISCOUNT_AMOUNT-(ITEMINFO.ORG_AMOUNT-ITEMINFO.ACTUAL_AMOUNT)
where FLOW_RESERVATION_PRO.id =ITEMINFO.FLOW_PRO_ID;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
END SP_RESV_REMOVEPRODUCT;