游標(biāo)

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

游標(biāo)的操作流程

1. 定義游標(biāo)

DECLARE 游標(biāo)名稱 CURSON FOR SELECT語句

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

2.打開游標(biāo)

OPEN游標(biāo)名稱

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

FETCH 游標(biāo)名稱 INTO 變量名1 [,變量名2]...

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

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

4.關(guān)閉游標(biāo)

CLOSE 游標(biāo)名稱

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

例題

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

原表如下

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

然后定義存儲(chǔ)過程如下

DELIMITER // -- 修改MySQL語句默認(rèn)結(jié)束符號(hào)為//
CREATE PROCEDURE proc_emp_comm() -- 創(chuàng)建名為proc_emp_comm()的存儲(chǔ)過程
BEGIN -- 開始存儲(chǔ)過程
DECLARE mark INT DEFAULT 0; -- 定義了變量mark用于存儲(chǔ)游標(biāo)結(jié)束循環(huán)的標(biāo)識(shí)
# 定義變量用來存儲(chǔ)select語句查詢出來的8個(gè)字段數(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; -- # 定義游標(biāo)
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' -- 定義錯(cuò)誤程序及處理方式
 SET mark=1;
# 打開游標(biāo)
OPEN cur;
# 借助repeat循環(huán)，移動(dòng)指針獲取虛擬結(jié)果集中的數(shù)據(jù)，存儲(chǔ)到定義的變量中
REPEAT -- 開啟循環(huán)
FETCH cur INTO emp_no,emp_name,emp_job,emp_mgr,emp_hiredate,emp_sal,emp_comm,emp_deptno; -- 使用上面定義的8個(gè)字段去接收查詢語句查詢出來的數(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)閉游標(biāo)
END // -- 結(jié)束存儲(chǔ)過程
DELIMITER ; -- 設(shè)置MySQL命令結(jié)束符號(hào)為;
 

調(diào)用存儲(chǔ)過程

CALL proc_emp_comm();

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

SELECT * FROM emp_comm;

觸發(fā)器

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

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

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

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

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

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

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

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

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

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

• 觸發(fā)器名稱：必須在當(dāng)前數(shù)據(jù)庫中唯一。如果要在指定的數(shù)據(jù)庫中創(chuàng)建觸發(fā)器，觸發(fā)器名稱前面應(yīng)該加上數(shù)據(jù)庫的名稱。
• 觸發(fā)時(shí)機(jī)：指觸發(fā)程序執(zhí)行的時(shí)間，可選值有BEFORE和AFTER；其中BEFORE表示在觸發(fā)事件之前執(zhí)行觸發(fā)小程序，AFTER表示在觸發(fā)事件之后執(zhí)行觸發(fā)程序。
• 觸發(fā)事件：表示激活觸發(fā)器的操作類型，可選值有INSERT、UPDATE和DELETE；其中INSERT表示將新紀(jì)錄插入表時(shí)激活觸發(fā)器中的觸發(fā)程序，UPDATE表示更改表中某一條記錄時(shí)激活觸發(fā)器中的觸發(fā)程序，DELETE表示刪除表中某一行記錄時(shí)激活觸發(fā)器中的觸發(fā)程序。
• 觸發(fā)程序：指的是觸發(fā)器執(zhí)行的SQL語句，如果要執(zhí)行多條語句，可使用BEGIN...END作為語句的開始和結(jié)束。觸發(fā)程序中可以使用NEW和OLD分別表示新記錄和舊記錄。例如，當(dāng)需要訪問數(shù)新插入記錄的字段值時(shí)，可以使用“NEW.字段名”方式訪問；當(dāng)修改數(shù)據(jù)表的某條記錄時(shí)，可以使用“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í)行刪除操作時(shí)，會(huì)觸發(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ù)人員想要在刪除員工信息后，自動(dòng)將刪除的員工信息添加在其他數(shù)據(jù)表，以防后續(xù)需要查詢被刪除的員工信息

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

# CREATE TRIGGER 觸發(fā)器名稱 觸發(fā)時(shí)機(jī) 觸發(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í)行刪除操作時(shí)，會(huì)觸發(fā)觸發(fā)器的執(zhí)行

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

查看觸發(fā)器

SHOW TRIGGERS;

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

最新評(píng)論