|
|
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 NULL;
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;
| |
|
|
|
|
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
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
);
TOO_MANY_ROWS THEN NULL;
END;
COMMIT;
END LOOP;
CLOSE customer_cur;
COMMIT;
END;
| |
|
|
|