如何做好網(wǎng)站管理工作深圳網(wǎng)絡推廣代運營
目錄
- 1. 存儲函數(shù)
- 2. 存儲函數(shù)的應用
- 3. 錯誤處理
- 4. 拋出異常
- 5. 事務處理
- 6. 事務隔離級
- 7. 應用實例
- 參考書籍
1. 存儲函數(shù)
-
要 創(chuàng)建 存儲函數(shù),需要用到
CREATE
語句:CREATE FUNCTION 存儲函數(shù)名([參數(shù)名 類型, ...])RETURNS 類型[存儲函數(shù)體]
注意:存儲過程名和存儲函數(shù)名不能相同!
-
要 調用 存儲函數(shù),語法格式如下:
存儲函數(shù)名([參數(shù), ...])
注:存儲過程只能采用
CALL
語句直接調用,而存儲函數(shù)則可以出現(xiàn)在各種語句中。 -
要 修改 存儲函數(shù),需要用到
ALTER
語句:ALTER FUNCTION 存儲函數(shù)名 [COMMENT ‘string’| LANGUAGE SQL| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER } }
注:上述修改語句只能修改存儲函數(shù)的屬性,不能修改功能!修改存儲函數(shù)的功能,與修改存儲過程功能一樣也只能采取先刪除后重新定義的方式實現(xiàn)。
-
要 刪除 存儲函數(shù),需要用到
DROP
語句:DROP FUNCTION [IF EXISTS] 存儲函數(shù)名
-
注意在創(chuàng)建自定義存儲函數(shù)的時候,需要提供權限:
set GLOBAL log_bin_trust_function_creators = 1; # 創(chuàng)建自定義函數(shù)需要給權限
注:上述語句只需要在開啟數(shù)據(jù)庫時輸入一次即可。
2. 存儲函數(shù)的應用
【例 1】在數(shù)據(jù)庫 score
中創(chuàng)建存儲函數(shù),要求通過給定的學號輸出相對應的姓名。
drop FUNCTION if exists f1;
delimiter $
create FUNCTION f1(stu_id char(20))
returns varchar(20) # 返回學生姓名
begindeclare sname varchar(20);select stu.`name` into snamefrom stuwhere stu.id = stu_id;return sname;
end$
delimiter ;
select f1('20191001') as Name;
【例 2】在數(shù)據(jù)庫 score
中創(chuàng)建存儲函數(shù),要求輸出每位學生的所修學分總數(shù)。
drop FUNCTION if exists getxf;
delimiter $
create FUNCTION getxf(stu_id char(20))
returns decimal(5,1) # 返回學生總學分
begindeclare s decimal(5,1) default 0; # 因為之后要求和,所以給個缺省值0select sum(if(score.score >= 60, lesson.xf, 0)) into sfrom score join lesson on score.LessonId = lesson.lessonidwhere score.stuId = stu_id;return s;
end$
delimiter ;
select stu.id, stu.`name`, IFNULL(getxf(stu.id),0) as 學分
from stu;
3. 錯誤處理
在執(zhí)行 SQL
語句后,可能有時會出錯,那么大家可以根據(jù)報錯編號在 MySQL
手冊里面查找出錯原因。
錯誤處理語句:
DECLARE 處理動作 HANDLER FOR 條件值 ,...處理語句過程體
4. 拋出異常
我們還可以自定義錯誤代碼以及拋出異常值:
SIGNAL SQLSTATE 錯誤編號(自定義,不能與系統(tǒng)已有編號重復)
SET message_text = 錯誤提示信息;
示例如下:
SIGNAL SQLSTATE '12345'
set message_text = '證件號不存在';
5. 事務處理
-
關閉自動提交
自動提交常用語銀行系統(tǒng)中,比如小鄧給森林轉了1
元的寫博客助力費,那么這個動作應該是同步的,即小鄧銀行賬戶扣除1
元,與此同時,森林銀行賬戶增加1
元(假設小鄧銀行賬戶剛好有1
元,足夠扣除)。不能說小鄧銀行賬戶扣除1
元,但森林銀行賬戶余額不變(比如轉錢信號傳輸中受到干擾導致信號未正確傳輸)。這時候 關閉自動提交 就可以實現(xiàn)解決這種情況。當完成一系列操作后,只有手動提交事務,才能算是實現(xiàn)了之前的所有操作!
SET @@AUTOCOMMIT = 0;
注:默認是開啟自動提交的,即
SET @@AUTOCOMMIT = 0;
。 -
開始事務
START TRANSACTIONSQL語句
-
結束事務
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
-
撤銷事務
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
-
回滾事務到指定點
回滾前需要先設置一個保存點:SAVEPOINT 保存點名
然后才可以回滾事務:
ROLLBACK [WORK] TO SAVEPOINT 保存點名
6. 事務隔離級
- 臟讀(dirty reads):一個事務讀取了另一個未提交的并行事務寫的數(shù)據(jù)。
- 不可重復讀(non-repeatable reads):一個事務重新讀取前面讀取過的數(shù)據(jù), 發(fā)現(xiàn)該數(shù)據(jù)已經(jīng)被另一個已提交的事務修改過。
- 幻讀(phantom read):一個事務重新執(zhí)行一個查詢,返回一套符合查詢條件的行, 發(fā)現(xiàn)這些行因為其他最近提交的事務而發(fā)生了改變。
以上 3
種情況是我們不想看見的。
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVELSERIALIZABLE //可序列化| REPEATABLE READ //可重復讀| READ COMMITTED //提交讀| READ UNCOMMITTED //未提交讀
如果指定GLOBAL,那么定義的隔離級將適用于所有的SQL用戶;如果指定SESSION,則隔離級只適用于當前運行的會話和連接。
基于 ANSI/ISO SQL
規(guī)范,MySQL
提供了 4
種隔離級:序列化、可重復讀、提交讀和未提交讀。
查看當前事務隔離級:
SELECT @@TRANSACTION_ISOLATION;
7. 應用實例
請自行創(chuàng)建數(shù)據(jù)庫 bank
,并在里面新建一個表 account
,表內容如下:
下面完成轉賬操作:
set @@autocommit = 0; # 關閉自動提交功能select * from account;update account
set ck = ck - 500
where id = '001';SAVEPOINT a; # 保存點(rollback時只撤銷后面的操作)update account
set ck = ck + 500
where id = '002';select * from account;
此時還未提交事務,大家可以在可視化界面刷新表 account
,發(fā)現(xiàn)數(shù)據(jù)并沒有更新。這是因為我們關閉了自動提交,因此要手動提交事務:
COMMIT;
參考書籍
《MySQL實用教程(第4版)》
上一篇文章:【數(shù)據(jù)庫——MySQL】(12)過程式對象程序設計——存儲過程