MySQL游標和觸發(fā)器的操作流程
游標
使用SELECT語句可以返回符合指定條件的結果集(虛擬表),但沒有辦法對結果集中的數據進行單獨的處理。例如,使用SELECT語句查詢出多條員工信息的結果集后,無法獲取結果集中的單條記錄。為此,MySQL提供了游標機制,利用游標可以對結果集中的數據進行單獨處理。
游標的操作流程
1. 定義游標
DECLARE 游標名稱 CURSON FOR SELECT語句
特點:游標名稱必須唯一,因為在存儲過程和函數中可以存儲多個游標,而游標名就是區(qū)分不同游標的唯一標志。另外SELECT語句中不能含有INTO關鍵字。需要注意的是,變量、錯誤觸發(fā)條件、錯誤處理程序和游標都是通過DECLARE定義的,但他們的定義是有先后順序要求的。變量和錯誤觸發(fā)條件必須在最前面聲明,然后是游標的聲明,最后才是錯誤處理程序的聲明。
2.打開游標
OPEN游標名稱
3.利用游標檢索數據
FETCH 游標名稱 INTO 變量名1 [,變量名2]...
每執(zhí)行一次FETCH語句就在結果集中獲取一行記錄,FETCH語句獲取記錄后,游標的內部指針就會向前移動一步,指向下一條記錄。并將獲取到的記錄存入對應的變量中,其中變量名的個數要和SELECT語句查詢出來的結果集一致。
FETCH語句一般和循環(huán)語句一起完成數據的檢索,它通常和REPEAT循環(huán)語句一起使用。因為無法直接判斷哪條記錄是結果集中的最后一條記錄,當利用游標從結果集中檢索出最后一條記錄后,再次執(zhí)行FETCH語句,將產生ERROR 1329 (02000):No data to FETCH錯誤信息。因此,使用游標時通常自定義錯誤處理程序處理該錯誤,從而結束游標的循環(huán)。
4.關閉游標
CLOSE 游標名稱
在程序內,如果使用CLOSE關閉了游標,則不能再通過FETCH使用該游標。如果想要再次利用游標檢索數據,只需要使用OPEN打開游標即可,而不用重新定義游標。如果沒有使用CLOSE關閉游標,那么它將在被打開的BEGIN...END語句塊的末尾關閉。
例題
技術人員想將員工表emp中獎金為NULL的員工信息存放在一個新的數據表emp_comm中,數據表emp_comm的結構和員工表保持一致
原表如下

創(chuàng)建一個存儲過程,實現將獎金為NULL的員工信息添加到數據表emp_comm
然后定義存儲過程如下
DELIMITER // -- 修改MySQL語句默認結束符號為// CREATE PROCEDURE proc_emp_comm() -- 創(chuàng)建名為proc_emp_comm()的存儲過程 BEGIN -- 開始存儲過程 DECLARE mark INT DEFAULT 0; -- 定義了變量mark用于存儲游標結束循環(huán)的標識 # 定義變量用來存儲select語句查詢出來的8個字段數據 DECLARE emp_no INT; DECLARE emp_name VARCHAR(20); DECLARE emp_job VARCHAR(20); DECLARE emp_mgr INT; DECLARE emp_hiredate DATE; DECLARE emp_sal DECIMAL(7,2); DECLARE emp_comm DECIMAL(7,2); DECLARE emp_deptno INT; DECLARE cur CURSOR FOR SELECT * FROM emp WHERE comm IS NULL; -- # 定義游標 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' -- 定義錯誤程序及處理方式 SET mark=1; # 打開游標 OPEN cur; # 借助repeat循環(huán),移動指針獲取虛擬結果集中的數據,存儲到定義的變量中 REPEAT -- 開啟循環(huán) FETCH cur INTO emp_no,emp_name,emp_job,emp_mgr,emp_hiredate,emp_sal,emp_comm,emp_deptno; -- 使用上面定義的8個字段去接收查詢語句查詢出來的數據 IF mark!=1 THEN -- 只要mark值不為1,說明結果集中還有數據,就將數據添加到emp_comm表中 INSERT INTO emp_comm VALUES(emp_no,emp_name,emp_job,emp_mgr,emp_hiredate,emp_sal,emp_comm,emp_deptno); END IF; -- 結束if語句 UNTIL mark=1 END REPEAT; -- 結束repeat循環(huán)語句 CLOSE cur; -- 關閉游標 END // -- 結束存儲過程 DELIMITER ; -- 設置MySQL命令結束符號為;
調用存儲過程
CALL proc_emp_comm();
查看emp_comm表數據
SELECT * FROM emp_comm;

觸發(fā)器
在實際開發(fā)項目時,如果需要在數據表發(fā)生更改時自動進行一些處理,這時就可以使用觸發(fā)器。
例如,刪除一條數據時,需要在數據庫中保留一個備份副本,這種情況下可以創(chuàng)建一個觸發(fā)器對象,每當刪除一條數據時,就執(zhí)行一次備份操作。
觸發(fā)器可以看成一種特殊的存儲過程,它不用CALL語句調用,而是在預選定義好的操作自動調用(INSERT,DELETE)等
觸發(fā)器具有以下優(yōu)點:
當觸發(fā)器相關聯的數據表中的數據發(fā)生修改時,觸發(fā)器中定義的語句會自動執(zhí)行。
觸發(fā)器對數據進行安全校驗,保障數據安全。
通過和觸發(fā)器相關聯的表,可以實現表數據的級聯更改,在一定程度上保證數據的完整性。
觸發(fā)器的基本操作
1.創(chuàng)建觸發(fā)器
CREATE TRIGGER 觸發(fā)器名稱 觸發(fā)時機 觸發(fā)事件 ON 數據表名 FOR EACH ROW 觸發(fā)程序
- 觸發(fā)器名稱:必須在當前數據庫中唯一。如果要在指定的數據庫中創(chuàng)建觸發(fā)器,觸發(fā)器名稱前面應該加上數據庫的名稱。
- 觸發(fā)時機:指觸發(fā)程序執(zhí)行的時間,可選值有BEFORE和AFTER;其中BEFORE表示在觸發(fā)事件之前執(zhí)行觸發(fā)小程序,AFTER表示在觸發(fā)事件之后執(zhí)行觸發(fā)程序。
- 觸發(fā)事件:表示激活觸發(fā)器的操作類型,可選值有INSERT、UPDATE和DELETE;其中INSERT表示將新紀錄插入表時激活觸發(fā)器中的觸發(fā)程序,UPDATE表示更改表中某一條記錄時激活觸發(fā)器中的觸發(fā)程序,DELETE表示刪除表中某一行記錄時激活觸發(fā)器中的觸發(fā)程序。
- 觸發(fā)程序:指的是觸發(fā)器執(zhí)行的SQL語句,如果要執(zhí)行多條語句,可使用BEGIN...END作為語句的開始和結束。觸發(fā)程序中可以使用NEW和OLD分別表示新記錄和舊記錄。例如,當需要訪問數新插入記錄的字段值時,可以使用“NEW.字段名”方式訪問;當修改數據表的某條記錄時,可以使用“OLD.字段名”訪問修改之前的字段值。
2.查看觸發(fā)器
SHOW TRIGGERS;
利用SELECT語句查看數據庫information_schema下數據表trigges中的觸發(fā)器數據
SELECT * FROM information_schema.triggers [WHERE trigger_name = '觸發(fā)器名稱'];
3. 觸發(fā)觸發(fā)器
根據定義的觸發(fā)器知道,執(zhí)行刪除操作時,會觸發(fā)觸發(fā)器的執(zhí)行(下面的例題)
DELETE FROM emp WHERE empno=8888;

4. 刪除觸發(fā)器
DROP TRIGGER [IF EXISTS] [數據庫名.]觸發(fā)器名;-- DELETE一般刪除表中數據,其他為DROP
DROP TRIGGER IF EXISTS trig_emp;(下面例題)
例題
技術人員想要在刪除員工信息后,自動將刪除的員工信息添加在其他數據表,以防后續(xù)需要查詢被刪除的員工信息
首先創(chuàng)建一個新的表,用來存儲刪除的數據,這個表的字段和emp表的字段一樣
CREATE TABLE `emp_del` ( `empno` INT DEFAULT NULL, `ename` VARCHAR(50) DEFAULT NULL, `job` VARCHAR(50) DEFAULT NULL, `mgr` INT DEFAULT NULL, `hiredate` DATE DEFAULT NULL, `sal` DECIMAL(7,2) DEFAULT NULL, `comm` DECIMAL(7,2) DEFAULT NULL, `deptno` INT DEFAULT NULL );
接著在員工表emp中創(chuàng)建觸發(fā)器。當
刪除員工表的數據后,觸發(fā)該觸發(fā)器,
并且在觸發(fā)器的觸發(fā)程序中將被刪除的員工信息添加到數據表emp_del
# CREATE TRIGGER 觸發(fā)器名稱 觸發(fā)時機 觸發(fā)事件 ON 數據表名 FOR EACH ROW 觸發(fā)程序
DELIMITER //
CREATE TRIGGER trig_emp
AFTER DELETE ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp_del(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(OLD.empno, OLD.ename, OLD.job, OLD.mgr, OLD.hiredate, OLD.sal, OLD.comm, OLD.deptno);
END //
DELIMITER ;接著根據定義的觸發(fā)器知道,執(zhí)行刪除操作時,會觸發(fā)觸發(fā)器的執(zhí)行
-- 刪除員工編號為 8888 的記錄 DELETE FROM emp WHERE empno = 8888; -- 刪除員工編號為 7369 的記錄 DELETE FROM emp WHERE empno = 7369;

查看觸發(fā)器
SHOW TRIGGERS;

到此這篇關于MySQL游標和觸發(fā)器的操作流程的文章就介紹到這了,更多相關mysql游標和觸發(fā)器內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
MySQL數據恢復工具:ibd2sql離線解析.ibd文件轉換為SQL
ibd2sql是純Python3工具,用于離線解析MySQL?InnoDB?IBD文件生成SQL語句,支持多版本和復雜結構,可恢復誤刪數據,優(yōu)點包括無依賴、靈活輸出,但存在性能慢、數據完整性風險及資源消耗大的缺點,適用于數據恢復、遷移和審計場景2025-06-06

