|
DROP PROCEDURE IF EXISTS sdm.AUTO_JOB_ALLOCATION_TEST1;
CREATE PROCEDURE sdm.`AUTO_JOB_ALLOCATION_TEST1`()
BEGIN
declare iCqtCount int default 0;
declare iJobcount int;
declare iJobcount1 int default 0;
declare iBuildingNo int;
declare i_distance int;
declare i_cqtBuildingNo int;
declare iIdx int;
DECLARE iCursorLoop int default FALSE;
DECLARE setting_cursor CURSOR FOR
SELECT jobcount, standardBuildingNo
FROM cqtautosetting;
DECLARE product_cursor CURSOR FOR
SELECT
( 6371 * acos(cos(radians( CAST((select buildingLat from cqtbuilding where cqtBuildingNo = iBuildingNo ) AS DECIMAL(50,5))))
* cos(radians(buildingLat))
* cos(radians(buildingLng) - radians(CAST((select buildingLng from cqtbuilding where cqtBuildingNo = iBuildingNo ) AS DECIMAL(50,5))))
+ sin(radians(CAST((select buildingLat from cqtbuilding where cqtBuildingNo = iBuildingNo ) AS DECIMAL(50,5)))) * sin(radians(buildingLat)))) AS distance,
cqtBuil.cqtBuildingNo
FROM cqtbuilding as cqtBuil
LEFT OUTER JOIN
(SELECT * FROM cqtauto
WHERE cqtauto.idx = (SELECT MAX(idx) FROM cqtauto))
AS cqtAu
ON cqtAu.cqtBuildingNo = cqtBuil.cqtBuildingNo
WHERE cqtAu.cqtAutoNo IS NULL
ORDER BY distance
LIMIT 0, iJobcount;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET iCursorLoop := TRUE;
SELECT count(*)
INTO iCqtCount
FROM cqtautosetting;
SELECT MAX(idx)+1
INTO iIdx
FROM cqtauto;
IF iCqtCount != 0 then
OPEN setting_cursor;
setting_loop: LOOP
FETCH setting_cursor INTO iJobcount, iBuildingNo;
IF iCursorLoop THEN
CLOSE setting_cursor;
LEAVE setting_loop;
END IF;
OPEN product_cursor;
FETCH product_cursor INTO i_distance, i_cqtBuildingNo;
product_loop: LOOP
IF iCursorLoop THEN
SET iCursorLoop := FALSE;
CLOSE product_cursor;
LEAVE product_loop;
END IF;
INSERT INTO cqtauto
(
cqtBuildingNo,
cqtUserNo,
regiDate,
regiId,
idx
)
VALUES
(
i_cqtBuildingNo,
1,
NOW(),
'admin',
iIdx
);
END LOOP product_loop;
END LOOP setting_loop;
END IF;
END;
----------------------------------------------------------------------
setting_cursor의 쿼리
SELECT jobcount, standardBuildingNo
FROM cqtautosetting;
의 결과가
jobcount standardBuildingNo
------------------------------
4 4
5 2
5 32
일때
iJobcount 와 iBuildingNo 를 LOOP 돌리며 하단의 product_cursor 에 맴핑하여 돌릴 생각 입니다.
product_cursor 의 쿼리 ( 위경도를 받아와 가장 가까운 곳의 빌딩을 찾는 쿼리입니다)
SELECT
( 6371 * acos(cos(radians( CAST((select buildingLat from cqtbuilding where cqtBuildingNo = iBuildingNo ) AS DECIMAL(50,5))))
* cos(radians(buildingLat))
* cos(radians(buildingLng) - radians(CAST((select buildingLng from cqtbuilding where cqtBuildingNo = iBuildingNo ) AS DECIMAL(50,5))))
+ sin(radians(CAST((select buildingLat from cqtbuilding where cqtBuildingNo = iBuildingNo ) AS DECIMAL(50,5)))) * sin(radians(buildingLat)))) AS distance,
cqtBuil.cqtBuildingNo
FROM cqtbuilding as cqtBuil
LEFT OUTER JOIN
(SELECT * FROM cqtauto
WHERE cqtauto.idx = (SELECT MAX(idx) FROM cqtauto))
AS cqtAu
ON cqtAu.cqtBuildingNo = cqtBuil.cqtBuildingNo
WHERE cqtAu.cqtAutoNo IS NULL
ORDER BY distance
LIMIT 0, iJobcount;
입니다 그런데 무한 루프가 ㅜㅜ 도네요...
도움 요청 드립니다
|
