:: 글답변 :: 이 름 패스워드 이메일 홈페이지 옵 션 html  분 류 선택하세요 Html CSS Script JQuery ASP PHP GNU .NET MySQL MsSQL DNS SEO ENG IONIC REACT 제 목 > > > 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 ; > 링크 #1 링크 #2 파일첨부   왼쪽의 글자를 입력하세요.