[操作疑難] SQL SCRIPT

各位師兄,請問以下呢段野可以在WINDOWS的MYSQL WORKBENCH行到嗎? 謝謝!

DROP procedure IF EXISTS `delete_orphaned_captures`;
SET GLOBAL event_scheduler = ON;

DELIMITER $$

CREATE PROCEDURE `delete_orphaned_captures`()
BLOCK1: BEGIN
DECLARE _output TEXT DEFAULT '';
declare v_Capture_id VARCHAR(36);
declare v_Count INT;
declare l_done INT;
declare v_GetID CURSOR FOR SELECT distinct(c.ID) as CAPTURE_ID
            from CAPTURE c
            left join PRESENTATION p on c.id=p.capture_id
            left join PROCESSING_TASK pt on c.id=pt.CAPTURE_ID
            where p.id is null and pt.id is not null and STATUS_ID in (select ID from CAPTURE_STATUS where NAME_KEY  like '%complete%') LIMIT 100;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done = 1;
       
SET v_Count = 0;
SET l_done =0;

OPEN v_GetID;
First_Loop: LOOP
FETCH v_GetID INTO v_Capture_id;
IF l_done= 1 THEN LEAVE First_Loop;
END IF;
       
  delete FROM PROCESSING_TASK_FAILED_DEVICE
        where PROCESSING_TASK_ID in(select ID FROM PROCESSING_TASK
        where CAPTURE_ID in(v_Capture_id));

        delete FROM PROCESSING_TASK where CAPTURE_ID in(v_Capture_id);
       
        delete FROM CAPTURE_PORT_GROUP where CAPTURE_ID in (v_Capture_id);

# LOOP2
    BLOCK2: BEGIN
        declare v_Media_id VARCHAR(36);
        declare v_mCount INT;
        declare mdone INT default 0;

        declare v_GetMediaID CURSOR FOR
        SELECT DISTINCT(MEDIA_ID)
        FROM( select media_id FROM  CAPTURE_MEDIA
              where CAPTURE_ID = v_Capture_id) as tmp;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET mdone = 1;
       
        SET v_mCount = 0;
        OPEN v_GetMediaID;
        Second_Loop : LOOP
                FETCH v_GetMediaID INTO v_Media_id;
                IF mdone=1 THEN LEAVE Second_Loop;
                END IF;
                IF v_mCount=0 THEN
                    delete FROM CAPTURE_MEDIA
                    where CAPTURE_ID = v_Capture_id;
                END IF;
            
                delete FROM MEDIA
                where ID = v_Media_id;
               
                Set v_mCount = v_mCount+1;
                SET _output = concat('Deleted ',v_mCount, '  Media ID ', v_Media_id);
               
        END LOOP Second_Loop;
        CLOSE v_GetMediaID;
        SET l_done=0;
    END BLOCK2;
       
        delete FROM CAPTURE_MEDIA
        where CAPTURE_ID = v_Capture_id;

        delete FROM CAPTURE_PRESENTER
        where CAPTURE_ID = v_Capture_id;
       
        delete from EVENT_ENTITY where alert_id in(select ID from ALERT
        where CAPTURE_ID = v_Capture_id);

        delete from ALERT
        where CAPTURE_ID = v_Capture_id;

        delete from EVENT_ENTITY
        where CAPTURE_ID = v_Capture_id;

        delete from DEVICE_SOURCE_STATUS
        where CAPTURE_ID = v_Capture_id;

        delete FROM PRESENTATION
        where Capture_ID = v_Capture_id;

        delete FROM CAPTURE
        where ID  = v_Capture_id;
       
        Set v_Count = v_Count+1;
        SET _output =  concat('Current count ',v_Count, '  Capture ID ', v_Capture_id);
       
END LOOP First_Loop;
CLOSE v_GetID;
SELECT _output;
END BLOCK1
$$

DELIMITER $$

CREATE
        EVENT `Purge_Orphaned_Capture`
        ON SCHEDULE EVERY 1 DAY
        STARTS
   TIMESTAMP(CURRENT_DATE+0,'00:00:00')
   DO BEGIN
                Call delete_orphaned_captures();
   END
$$

大大不如講吓有乜 error message!

TOP

TOP

回覆 2# toylet

師姐,我唔知點樣在MYSQLBENCH 行呢條SCRIPT......

TOP

回覆  toylet
師姐,...
lookfrance 發表於 20/11/2014 11:23

小人其實想提提大大尊稱別人應該**中性**一點....  須然好少女 programmers!
不過小人做過成 team 呀嬸的 programming team ....
我唔知點樣在MYSQLBENCH 行呢條SCRIPT......

小人太耐無寫 MySQL, 不太記得:
http://dev.mysql.com/doc/refman/5.5/en/mysql.html

TOP