CREATE or REPLACE PROCEDURE INSCUSTOMER AS /* 変数定義 */ VCUSTOMER_NO NUMBER; VTEL_NO CHAR(17); VSEX_CD NUMBER; VBIRTHDAY DATE; VDELIVERY_CD NUMBER; VAREA_CD CHAR(4); VOCUPTON_CD NUMBER; VMARRIED_CD NUMBER; VLISTTYPE_CD NUMBER; VDM_PBLSH_FLG NUMBER; VNEW_ODR_DAY DATE; VDUMMY NUMBER; /* カーソル定義 */ CURSOR customer_cur is select CUSTOMER_NO, TEL_NO, SEX_CD, BIRTHDAY, DELIVERY_CD, AREA_CD, OCUPTON_CD, MARRIED_CD, LISTTYPE_CD, DM_PBLSH_FLG, NEW_ODR_DAY from W_CUSTOMER where DISCRIM_FLG = 1; /* 処理開始 */ BEGIN OPEN customer_cur; LOOP FETCH customer_cur INTO VCUSTOMER_NO, VTEL_NO, VSEX_CD, VBIRTHDAY, VDELIVERY_CD, VAREA_CD, VOCUPTON_CD, VMARRIED_CD, VLISTTYPE_CD, VDM_PBLSH_FLG, VNEW_ODR_DAY; EXIT WHEN customer_cur%NOTFOUND; BEGIN select CUSTOMER_NO into VDUMMY from CUSTOMER where CUSTOMER_NO = VCUSTOMER_NO and TEL_NO = VTEL_NO and LISTTYPE_CD = VLISTTYPE_CD; EXCEPTION WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN NULL; END; IF SQL%NOTFOUND THEN INSERT INTO CUSTOMER values( VCUSTOMER_NO, VTEL_NO, VSEX_CD, VBIRTHDAY, VDELIVERY_CD, VAREA_CD, VOCUPTON_CD, VMARRIED_CD, VLISTTYPE_CD, VDM_PBLSH_FLG, VNEW_ODR_DAY ); ELSE NULL; END IF; COMMIT; END LOOP; CLOSE customer_cur; COMMIT; END; |
|