declare
CURSOR CUR_CACC_CNC_GRP IS
select CACC_OID,CACC_CNC_GRP_OID from CACC_CNC_GRP where
effective_date<to_timestamp('01-02-2015 00:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff')
and (expiry_date is null or expiry_date>to_timestamp('01-02-2015 00:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff')) and
charge_group_oid in (select charge_group_oid from charge_group where regexp_substr(charge_group_code,'[[:digit:]]+\.?[[:digit:]]+')<'0.0025') ;
V_CACC_OID CLIENT_ACCOUNT_2.CACC_OID%type;
V_CACC_CNC_GRP_OID CACC_CNC_GRP.CACC_CNC_GRP_OID%type;
BEGIN
OPEN CUR_CACC_CNC_GRP;
LOOP
FETCH CUR_CACC_CNC_GRP INTO V_CACC_OID ,V_CACC_CNC_GRP_OID ;
EXIT WHEN CUR_CACC_CNC_GRP%NOTFOUND;
--UPDATE cacc_cnc_grp
update CACC_CNC_GRP set expiry_date=to_timestamp('28-02-2015 00:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff') WHERE CACC_OID=V_CACC_OID AND CACC_CNC_GRP_OID=V_CACC_CNC_GRP_OID;
--Insert a new record to table cacc_cnc_grp
insert into cacc_cnc_grp values ((select value from oid_value where name = 'DO_UID')+1,V_CACC_OID,(select charge_group_oid from charge_group where charge_group_code='C0.250_150'),NULL,to_timestamp('01-03-2015 00:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), NULL, 1,'A',NULL,sysdate,sysdate,'SYSADMALL');
--Increment OID_VALUE
update OID_VALUE set value = value +1 where name='DO_UID';
COMMIT;
END LOOP;
CLOSE CUR_CACC_CNC_GRP;
EXCEPTION
WHEN OTHERS THEN
IF CUR_CACC_CNC_GRP%ISOPEN THEN
CLOSE CUR_CACC_CNC_GRP;
END IF;
END;
/
转载于:https://blog.51cto.com/tomluo/1605024