游標

使用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ù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！