mysql 存儲過程和遊标

CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`()
BEGIN
    #Routine body goes here...
        DECLARE  itemId varchar(64);   -- id
        DECLARE  AA varchar(64);   -- 省
    DECLARE  BB  varchar(64); -- 市
    DECLARE  CC  varchar(64); -- 區
        DECLARE  new_province varchar(64);   -- 省
    DECLARE  new_city  varchar(64); -- 市
    DECLARE  new_area  varchar(64); -- 區

    -- 周遊資料結束标志
    DECLARE done INT DEFAULT FALSE;
    -- 遊标
    DECLARE cur_account CURSOR FOR select id,province,city,area from startup_project_copy;
    -- 将結束标志綁定到遊标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 打開遊标
    OPEN  cur_account;     
    -- 周遊
    read_loop: LOOP
            -- 取值 取多個字段
        FETCH  NEXT from cur_account INTO itemId,AA,BB,CC;

            IF done THEN
                LEAVE read_loop;
            END IF;
                SET new_province = (SELECT Impcode FROM sys_area WHERE AreaID = AA);
                SET new_city = (SELECT Impcode FROM sys_area WHERE AreaID = BB);
                SET new_area = (SELECT Impcode FROM sys_area WHERE AreaID = CC);
-- 你自己想做的操作
                --                  INSERT INTO test_city VALUE(new_province, new_city, new_area, itemId);
                 -- 執行更新
                 UPDATE startup_project_copy SET province = new_province, city= CONCAT(new_city,'000000'), area = CONCAT(new_area,'000000') WHERE id = itemId;
--                  SET @UPDATE = CONCAT('UPDATE startup_project_copy SET province =', new_province,', city=', new_city, ', area =', new_area, ' WHERE id = ',id);
--                 PREPARE stm FROM @UPDATE;
--                 EXECUTE stm;
--                 DEALLOCATE PREPARE stm;
                 COMMIT;-- 送出
    END LOOP;
 
    CLOSE cur_account;
    

END      

轉載于:https://www.cnblogs.com/joyny/p/10153941.html