在sql函数或者存储过程编程中,对数据一行一行地进行操作,游标的操作自然离不了,下面为学习的游标练习
CREATE OR REPLACE PROCEDURE ECC_test.SP_TST AS
VV_A VARCHAR(300);
VV_B VARCHAR(300);
BEGIN
FOR I_CUR IN (SELECT * FROM ecc_test.special_com_imagine_TST T)
LOOP
VV_A := I_CUR.ORDER_ID;
VV_B:= I_CUR.SPECIAL_COM;
begin
INSERT INTO ecc_test.special_com_imagine_TST1
(
ORDER_ID ,
PRODFAM_CODE ,
PRODFAM_TEXT ,
CFGITEM_CODE ,
SPECIAL_COM ,
CREATE_DATE ,
USEFUL_DATE ,
CFGITEM_TEXT ,
ACTIVE_FLAG ,
GUID ,
KEY_MATERIAL ,
KEY_MATERIAL_DES ,
PAY_TIME ,
PAY_NOT_SATISFY_
)
VALUES(
I_CUR.ORDER_ID ,
I_CUR.PRODFAM_CODE ,
I_CUR.PRODFAM_TEXT ,
I_CUR.CFGITEM_CODE ,
I_CUR.SPECIAL_COM ,
CASE WHEN I_CUR.CREATE_DATE IS NOT NULL THEN TO_DATE(TRIM(I_CUR.CREATE_DATE),'yyyy-mm-DD') END ,
CASE WHEN I_CUR.USEFUL_DATE IS NOT NULL THEN TO_DATE(TRIM(I_CUR.USEFUL_DATE),'yyyy-mm-DD') END ,
I_CUR.CFGITEM_TEXT ,
I_CUR.ACTIVE_FLAG ,
I_CUR.GUID ,
I_CUR.KEY_MATERIAL ,
I_CUR.KEY_MATERIAL_DES ,
I_CUR.PAY_TIME ,
I_CUR.PAY_NOT_SATISFY_
);
COMMIT;
exception
WHEN OTHERS THEN
insert into ecc_test.tb_tst_123
(ORDER_ID,
SPECIAL_COMM)
values(VV_A,VV_B);
commit;
end ;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
insert into ecc_test.tb_tst_123
(ORDER_ID,
SPECIAL_COMM)
values(VV_A,VV_B);
commit;
END;