游標

使用SELECT語句可以返回符合指定條件的結(jié)果集（虛擬表），但沒有辦法對結(jié)果集中的數(shù)據(jù)進行單獨的處理。例如，使用SELECT語句查詢出多條員工信息的結(jié)果集后，無法獲取結(jié)果集中的單條記錄。為此，MySQL提供了游標機制，利用游標可以對結(jié)果集中的數(shù)據(jù)進行單獨處理。

游標的操作流程

1. 定義游標

DECLARE 游標名稱 CURSON FOR SELECT語句

特點：游標名稱必須唯一，因為在存儲過程和函數(shù)中可以存儲多個游標，而游標名就是區(qū)分不同游標的唯一標志。另外SELECT語句中不能含有INTO關(guān)鍵字。需要注意的是，變量、錯誤觸發(fā)條件、錯誤處理程序和游標都是通過DECLARE定義的，但他們的定義是有先后順序要求的。變量和錯誤觸發(fā)條件必須在最前面聲明，然后是游標的聲明，最后才是錯誤處理程序的聲明。

2.打開游標

OPEN游標名稱

3.利用游標檢索數(shù)據(jù)

FETCH 游標名稱 INTO 變量名1 [,變量名2]...

每執(zhí)行一次FETCH語句就在結(jié)果集中獲取一行記錄，F(xiàn)ETCH語句獲取記錄后，游標的內(nèi)部指針就會向前移動一步，指向下一條記錄。并將獲取到的記錄存入對應(yīng)的變量中，其中變量名的個數(shù)要和SELECT語句查詢出來的結(jié)果集一致。

FETCH語句一般和循環(huán)語句一起完成數(shù)據(jù)的檢索，它通常和REPEAT循環(huán)語句一起使用。因為無法直接判斷哪條記錄是結(jié)果集中的最后一條記錄，當利用游標從結(jié)果集中檢索出最后一條記錄后，再次執(zhí)行FETCH語句，將產(chǎn)生ERROR 1329 （02000）:No data to FETCH錯誤信息。因此，使用游標時通常自定義錯誤處理程序處理該錯誤，從而結(jié)束游標的循環(huán)。

4.關(guān)閉游標

CLOSE 游標名稱

在程序內(nèi)，如果使用CLOSE關(guān)閉了游標，則不能再通過FETCH使用該游標。如果想要再次利用游標檢索數(shù)據(jù)，只需要使用OPEN打開游標即可，而不用重新定義游標。如果沒有使用CLOSE關(guān)閉游標，那么它將在被打開的BEGIN...END語句塊的末尾關(guān)閉。

例題

技術(shù)人員想將員工表emp中獎金為NULL的員工信息存放在一個新的數(shù)據(jù)表emp_comm中，數(shù)據(jù)表emp_comm的結(jié)構(gòu)和員工表保持一致

原表如下

創(chuàng)建一個存儲過程，實現(xiàn)將獎金為NULL的員工信息添加到數(shù)據(jù)表emp_comm

然后定義存儲過程如下

DELIMITER // -- 修改MySQL語句默認結(jié)束符號為//
CREATE PROCEDURE proc_emp_comm() -- 創(chuàng)建名為proc_emp_comm()的存儲過程
BEGIN -- 開始存儲過程
DECLARE mark INT DEFAULT 0; -- 定義了變量mark用于存儲游標結(jié)束循環(huán)的標識
# 定義變量用來存儲select語句查詢出來的8個字段數(shù)據(jù)
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)，移動指針獲取虛擬結(jié)果集中的數(shù)據(jù)，存儲到定義的變量中
REPEAT -- 開啟循環(huán)
FETCH cur INTO emp_no,emp_name,emp_job,emp_mgr,emp_hiredate,emp_sal,emp_comm,emp_deptno; -- 使用上面定義的8個字段去接收查詢語句查詢出來的數(shù)據(jù)
IF mark!=1 THEN -- 只要mark值不為1，說明結(jié)果集中還有數(shù)據(jù)，就將數(shù)據(jù)添加到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; -- 結(jié)束if語句
UNTIL mark=1 END REPEAT; -- 結(jié)束repeat循環(huán)語句
CLOSE cur; -- 關(guān)閉游標
END // -- 結(jié)束存儲過程
DELIMITER ; -- 設(shè)置MySQL命令結(jié)束符號為;
 

調(diào)用存儲過程

CALL proc_emp_comm();

查看emp_comm表數(shù)據(jù)

SELECT * FROM emp_comm;

觸發(fā)器

在實際開發(fā)項目時，如果需要在數(shù)據(jù)表發(fā)生更改時自動進行一些處理，這時就可以使用觸發(fā)器。

例如，刪除一條數(shù)據(jù)時，需要在數(shù)據(jù)庫中保留一個備份副本，這種情況下可以創(chuàng)建一個觸發(fā)器對象，每當刪除一條數(shù)據(jù)時，就執(zhí)行一次備份操作。

觸發(fā)器可以看成一種特殊的存儲過程，它不用CALL語句調(diào)用，而是在預選定義好的操作自動調(diào)用（INSERT,DELETE)等

觸發(fā)器具有以下優(yōu)點：

當觸發(fā)器相關(guān)聯(lián)的數(shù)據(jù)表中的數(shù)據(jù)發(fā)生修改時，觸發(fā)器中定義的語句會自動執(zhí)行。

觸發(fā)器對數(shù)據(jù)進行安全校驗，保障數(shù)據(jù)安全。

通過和觸發(fā)器相關(guān)聯(lián)的表，可以實現(xiàn)表數(shù)據(jù)的級聯(lián)更改，在一定程度上保證數(shù)據(jù)的完整性。

觸發(fā)器的基本操作

1.創(chuàng)建觸發(fā)器

CREATE TRIGGER 觸發(fā)器名稱 觸發(fā)時機 觸發(fā)事件 ON 數(shù)據(jù)表名 FOR EACH ROW 觸發(fā)程序

• 觸發(fā)器名稱：必須在當前數(shù)據(jù)庫中唯一。如果要在指定的數(shù)據(jù)庫中創(chuàng)建觸發(fā)器，觸發(fā)器名稱前面應(yīng)該加上數(shù)據(jù)庫的名稱。
• 觸發(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作為語句的開始和結(jié)束。觸發(fā)程序中可以使用NEW和OLD分別表示新記錄和舊記錄。例如，當需要訪問數(shù)新插入記錄的字段值時，可以使用“NEW.字段名”方式訪問；當修改數(shù)據(jù)表的某條記錄時，可以使用“OLD.字段名”訪問修改之前的字段值。

2.查看觸發(fā)器

SHOW TRIGGERS;

利用SELECT語句查看數(shù)據(jù)庫information_schema下數(shù)據(jù)表trigges中的觸發(fā)器數(shù)據(jù)

SELECT * FROM information_schema.triggers [WHERE trigger_name = '觸發(fā)器名稱'];

3. 觸發(fā)觸發(fā)器

根據(jù)定義的觸發(fā)器知道，執(zhí)行刪除操作時，會觸發(fā)觸發(fā)器的執(zhí)行(下面的例題)
DELETE FROM emp WHERE empno=8888;

4. 刪除觸發(fā)器

DROP TRIGGER [IF EXISTS] [數(shù)據(jù)庫名.]觸發(fā)器名;-- DELETE一般刪除表中數(shù)據(jù),其他為DROP

DROP TRIGGER IF EXISTS trig_emp;（下面例題）

例題

技術(shù)人員想要在刪除員工信息后，自動將刪除的員工信息添加在其他數(shù)據(jù)表，以防后續(xù)需要查詢被刪除的員工信息

 首先創(chuàng)建一個新的表，用來存儲刪除的數(shù)據(jù)，這個表的字段和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ā)器。當
刪除員工表的數(shù)據(jù)后，觸發(fā)該觸發(fā)器，
并且在觸發(fā)器的觸發(fā)程序中將被刪除的員工信息添加到數(shù)據(jù)表emp_del

# CREATE TRIGGER 觸發(fā)器名稱 觸發(fā)時機 觸發(fā)事件 ON 數(shù)據(jù)表名 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 ;

接著根據(jù)定義的觸發(fā)器知道，執(zhí)行刪除操作時，會觸發(fā)觸發(fā)器的執(zhí)行

-- 刪除員工編號為 8888 的記錄
DELETE FROM emp WHERE empno = 8888;
-- 刪除員工編號為 7369 的記錄
DELETE FROM emp WHERE empno = 7369;

查看觸發(fā)器

SHOW TRIGGERS;

到此這篇關(guān)于MySQL游標和觸發(fā)器的操作流程的文章就介紹到這了,更多相關(guān)mysql游標和觸發(fā)器內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論