丰满少妇96av,国产精品三级av及在线观看,欧美精品色视频

主頁 > 知識庫 > MySQL中觸發器和游標的介紹與使用

MySQL中觸發器和游標的介紹與使用

觸發器簡介

觸發器是和表關聯的特殊的存儲過程，可以在插入，刪除或修改表中的數據時觸發執行，比數據庫本身標準的功能有更精細和更復雜的數據控制能力。

觸發器的優點：

安全性：可以基于數據庫的值使用戶具有操作數據庫的某種權利。例如不允許下班后和節假日修改數據庫數據；
審計：可以跟蹤用戶對數據庫的操作；
實現復雜的數據完整性規則。例如，觸發器可回退任何企圖吃進超過自己保證金的期貨；
提供了運行計劃任務的另一種方法。例如，如果公司的帳號上的資金低于 5 萬元則立即給財務人員發送警告數據。

MySQL 中使用觸發器

創建觸發器

創建觸發器的技巧就是記住觸發器的四要素：

監控地點：table；
監控事件：insert/update/delete；
觸發時間：after/before；
觸發事件：insert/update/delete。

創建觸發器的基本語法如下所示：

CREATE TRIGGER
-- trigger_name：觸發器的名稱； 
-- tirgger_time：觸發時機，為 BEFORE 或者 AFTER；
-- trigger_event：觸發事件，為 INSERT、DELETE 或者 UPDATE； 
 trigger_name trigger_time trigger_event 
 ON
 -- tb_name：表示建立觸發器的表名，在哪張表上建立觸發器；
 tb_name
 -- FOR EACH ROW 表示任何一條記錄上的操作滿足觸發事件都會觸發該觸發器。
 FOR EACH ROW
 -- trigger_stmt：觸發器的程序體，可以是一條 SQL 語句或者是用 BEGIN 和 END 包含的多條語句； 
 trigger_stmt

trigger_name：觸發器的名稱；
tirgger_time：觸發時機，為 BEFORE 或者 AFTER；
trigger_event：觸發事件，為 INSERT、DELETE 或者 UPDATE；
tb_name：表示建立觸發器的表名，在哪張表上建立觸發器；
trigger_stmt：觸發器的程序體，可以是一條 SQL 語句或者是用 BEGIN 和 END 包含的多條語句；
FOR EACH ROW 表示任何一條記錄上的操作滿足觸發事件都會觸發該觸發器。

注意：對同一個表相同觸發時間的相同觸發事件，只能定義一個觸發器。

觸發器新舊記錄

MySQL 中定義了 NEW 和 OLD，用來表示觸發器的所在表中，觸發了觸發器的那一行數據：

在 INSERT 型觸發器中，NEW 用來表示將要（BEFORE或已經（AFTER）插入的新數據；
在 UPDATE型觸發器中，OLD 用來表示將要或已經被修改的原數據，NEW 用來表示將要或已經修改為的新數據；
在 DELETE型觸發器中，OLD 用來表示將要或已經被刪除的原數據。

創建觸發器，當用戶購買商品時，同時更新對應商品庫存記錄，代碼如下所示：

-- 刪除觸發器，drop trigger 觸發器名稱
-- if exists判斷存在才會刪除
drop trigger if exists myty1;
-- 創建觸發器
create trigger mytg1-- myty1觸發器的名稱
after insert on orders-- orders在哪張表上建立觸發器；
for each row
begin
	update product set num = num-new.num where pid=new.pid;
end;
-- 往訂單表插入記錄
insert into orders values(null,2,1);
-- 查詢商品表商品庫存更新情況
select * from product;

創建觸發器，當用戶刪除訂單時，同時更新對應商品庫存記錄，代碼如下所示：

-- 創建觸發器
create trigger mytg2
after delete on orders
for each ROW
begin 
-- 對庫存進行回退，重新加上
	update product set num = num+old.num where pid=old.pid;
end;
-- 刪除訂單記錄
delete from orders where oid = 2;
-- 查詢商品表商品庫存更新情況
select * from product;

before 和 after 的區別

before 在執行語句之前after 在執行語句之后

當訂單商品數量超過庫存時，修改訂單數量為最大庫存：

-- -- 創建 before 觸發器
create trigger mytg3
before insert on orders
for each row 
begin 
	-- 定義一個變量，來接收庫存
	declare n int default 0;
	-- 查詢庫存 把num賦值給n
	select num into n from product where pid = new.pid;
	-- 判斷下單的數量是否大于庫存量
	if new.num>n then
		-- 大于修改下單庫存（庫存改為最大量）
	set new.num = n;
	end if;
	update product set num = num-new.num where pid=new.pid;
end;
-- 往訂單表插入記錄
insert into orders values(null,3,50);
-- 查詢商品表商品庫存更新情況
select * from product;
-- 查詢訂單表
select * from orders;

游標

游標簡介

游標的作用就是用于對查詢數據庫所返回的記錄進行遍歷，以便進行相應的操作。游標有下面這些特征

游標是只讀的，也就是不能更新它；
游標是不能滾動的，也就是只能在一個方向上進行遍歷，不能在記錄之間隨意進退，不能跳過某些記錄；
避免在已經打開游標的表上更新數據。

創建游標

創建游標的語法包含四個部分：

定義游標：declare 游標名 cursor for select 語句；
打開游標：open 游標名；
獲取結果：fetch游標名 into 變量名[,變量名]；
關閉游標:close 游標名；

創建一個過程 p1，使用游標返回 test 數據庫中 student 表的第一個學生信息。代碼如下所示：

-- 定義過程
create procedure p1()
begin 
	declare id int;
	declare name varchar(20);
	declare age int;
	-- 定義游標 declare 游標名 cursor for select 語句；
	declare mc cursor for select * from student;
	-- 打開游標 open 游標名；
	open mc;
	-- 獲取數據 fetch 游標名 into 變量名[,變量名]；
	fetch mc into id,name,age;
	-- 打印
	select id,name,age;
	-- 關閉游標
	close mc;
end;
-- 調用過程
call p1();

在 test 數據庫創建一個 student2 表，創建一個過程 p2，使用游標提取 student 表中所有學生信息插入到 student2 表中。代碼如下所示：

-- 定義過程
create procedure p3()
begin 
	declare id int;
	declare name varchar(20);
	declare age int;
	declare flag int default 0;
	-- 定義游標 declare 游標名 cursor for select 語句；
	declare mc cursor for select * from student;
	declare continue handler for not found set flag=1;
	-- 打開游標 open 游標名；
	open mc;
	-- 獲取數據 fetch 游標名 into 變量名[,變量名]；
	a:loop -- 循環獲取數據
	fetch mc into id,name,age;
	if flag=1 then -- 當無法fetch時觸發continue handler
	leave a;-- 終止循環
	end if;
	-- 進行遍歷，將提取的每一行數據插入到 student2 表中
	insert into student2 values(id,name,age);
	end loop;
	-- 關閉游標
	close mc;
end;
-- 調用過程
call p3();
-- 查詢 student2 表
select * from student2;