[MySQL光速入門]021 聊聊遊標

貓哥的技術部落格發表於2019-04-18

遊標

有點像迭代器, 一次向前推進一步, 所以如果想全部讀取內容, 需要使用迴圈, 同時需要監聽結束, 以便跳出迴圈

那什麼時候回使用遊標呢?

需要一條一條修改資料的時候...

drop table if exists teacher;
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `salary` int(11) NOT NULL,
  `sex` tinyint(1) NOT NULL COMMENT '1 for male, 2 for female',
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `teacher`(`id`, `salary`, `sex`, `name`) VALUES (1, 4500, 1, '教師1');
INSERT INTO `teacher`(`id`, `salary`, `sex`, `name`) VALUES (2, 5500, 2, '教師2');
INSERT INTO `teacher`(`id`, `salary`, `sex`, `name`) VALUES (3, 6500, 2, '教師3');
INSERT INTO `teacher`(`id`, `salary`, `sex`, `name`) VALUES (4, 7500, 1, '教師4');
INSERT INTO `teacher`(`id`, `salary`, `sex`, `name`) VALUES (5, 8500, 2, '教師5');
複製程式碼

image.png

假如有一張教師表, 工資有高有低, 為了平均收入, 拉低貧富差距, 校長決定, 工資大於等於5000的老師, 工資減一千, 工資低於5000的老師, 每人工資加一千

應該怎麼做呢? 像這樣?

update teacher set salary = salary - 1000 where salary >= 5000;
update teacher set salary = salary + 1000 where salary < 5000;
複製程式碼

恐怕不行, 如果一個老師原來的工資是5500, 那麼第一句之後, 他的工資會變成4500; 再執行第二句的時候, 他的工資又變回了5500...

所以不能全部update, 需要一條一條的修改, 這就需要遊標了...

drop PROCEDURE if exists change_salary;
create PROCEDURE change_salary() begin 
    declare tid int default 0;
    declare tsalary int default 0;
    declare done int default 0;
    declare cur cursor for select id,salary from teacher;
    declare continue handler for not found set done = 1;
    open cur;
        myloop:loop
            fetch cur into tid,tsalary;
            if tsalary >= 5000 then
                update teacher set salary = tsalary - 1000 where id  = tid;
            else
                update teacher set salary = tsalary + 1000 where id  = tid;
            end if;
            if done then leave myloop;
            end if;
        end loop;
    close cur;
end;
call change_salary();
複製程式碼

中文版

drop PROCEDURE if exists 修改工資;
create PROCEDURE 修改工資() begin 
    declare 教師id int default 0;
    declare 教師工資 int default 0;
    declare 遊標是否結束 int default 0;
    declare 遊標 cursor for select id,salary from 教師表;
    declare continue handler for not found set 遊標是否結束 = 1;
    open 遊標;
        自定義迴圈名稱:loop
            fetch 遊標 into 教師id,教師工資;
            if 教師工資 >= 5000 then
                update 教師表 set salary = 教師工資 - 1000 where id  = 教師id;
            else
                update 教師表 set salary = 教師工資 + 1000 where id  = 教師id;
            end if;
            if 遊標是否結束 then leave 自定義迴圈名稱;
            end if;
        end loop;
    close 遊標;
end;
call 修改工資();
複製程式碼

比較一下兩者的結果差異

image.png

image.png

image.png

最後這個結果, 才是我們想要的

快速跳轉

相關文章