작성일 : 23-12-17 23:43
|
[MySQL] 프로시저 예
|
 |
|
글쓴이 :
최고관리자
 조회 : 4,292
|
DELIMITER ;;
CREATE PROCEDURE Proc_Insert_AND_Update_Data_From_Sap_Evstation()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE sap_KUNNR VARCHAR(255);
DECLARE sap_NAME1 VARCHAR(255);
DECLARE sap_STCD1 VARCHAR(255);
DECLARE sap_STRAS VARCHAR(255);
DECLARE sap_J_1KFREPRE VARCHAR(255);
DECLARE sap_TELF1 VARCHAR(255);
DECLARE sap_SORTL VARCHAR(255);
DECLARE cur CURSOR FOR SELECT KUNNR, NAME1, STCD1, STRAS, J_1KFREPRE, TELF1, SORTL FROM sap_evstation_tb;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO sap_KUNNR, sap_NAME1, sap_STCD1, sap_STRAS, sap_J_1KFREPRE, sap_TELF1, sap_SORTL;
IF done THEN
LEAVE read_loop;
END IF;
IF EXISTS (SELECT 1 FROM Orgs WHERE erp = sap_KUNNR LIMIT 0, 1) THEN
UPDATE Orgs SET erp = sap_KUNNR, fullname = sap_NAME1, name = sap_NAME1, bizRegNo = sap_STCD1, contactName = sap_J_1KFREPRE, contactPhoneNo = sap_TELF1,
category = CASE
WHEN sap_KUNNR LIKE '499%' THEN 'STT_DIR'
WHEN sap_KUNNR LIKE '495%' THEN 'EV_DIV'
ELSE 'STT_FRN'
END
WHERE erp = sap_KUNNR;
ELSE
INSERT INTO Orgs (
erp,
fullname,
bizRegNo,
address,
contactName,
contactPhoneNo,
name,
contactEmail,
deductType,
discountPrice,
staticUnitPrice,
payMethodId,
isPayLater,
isLocked,
billingDate,
closed,
area,
branch,
haveCarWash,
haveCVS,
STN_STN_SEQ,
STN_STN_ID,
STN_STN_GUBUN,
STN_CUST_NO,
STN_ASSGN_AREA_GUBUN,
STN_COST_CT,
STN_PAL_CT,
STN_STN_SHORT_NM,
createdAt,
updatedAt,
category
)
VALUES (
sap_KUNNR,
sap_NAME1,
sap_STCD1,
sap_STRAS,
sap_J_1KFREPRE,
sap_TELF1,
sap_NAME1,
'',
'NONE',
0,
0,
null,
0,
0,
null,
0,
null,
null,
'N',
'N',
'',
'',
'',
'',
'',
'',
'',
'',
NOW(),
NOW(),
CASE
WHEN sap_KUNNR LIKE '499%' THEN 'STT_DIR'
WHEN sap_KUNNR LIKE '495%' THEN 'EV_DIV'
ELSE 'STT_FRN'
END);
END IF;
IF EXISTS (SELECT 1 FROM sb_charging_stations WHERE orgId = (SELECT id FROM Orgs WHERE erp = sap_KUNNR)) THEN
UPDATE sb_charging_stations SET chgs_name = sap_SORTL
WHERE orgId = (SELECT id FROM Orgs WHERE erp = sap_KUNNR LIMIT 0, 1);
ELSE
INSERT INTO sb_charging_stations (
chgs_station_id,
status,
chgs_name,
coordinate,
chrgStartTime,
chrgEndTime,
washStartTime,
washEndTime,
chgs_kepco_meter_no,
isUse,
chgs_car_wash_yn,
chgs_aff_only,
chgs_field_desc,
area_code_id,
activeStationYN,
createdAt,
updatedAt,
orgId
)
VALUES (
null,
'ACTIVE',
sap_SORTL,
null,
null,
null,
null,
null,
null,
"Y",
'N',
null,
null,
null,
'Y',
NOW(),
NOW(),
(SELECT id FROM Orgs WHERE erp = sap_KUNNR LIMIT 0, 1));
END IF;
END LOOP;
CLOSE cur;
END;;
DELIMITER ;
|
|
1297
