事務與存儲過程

2024-09-12 20:30:15

字體：大中小

來源：轉載

供稿：網友

事務與存儲過程：

　　1.事務管理

　　（1）概念：事務指邏輯上的一組操作，組成這組操作的各個單元，要不全部成功，要不全部不成功。

　　（2）MySQL默認就自帶事務，但是MySQL自帶的事務是一條語句獨占一個事務

　　（3）也可以自己控制事務：**

　　star transcation; --開啟事務，在這條語句之后的sql將處在同一個事務中
　　...........
　　...........#語句
　　commit; #提交事務，讓這個事務中的sql對數據庫的影響立即發生
　　rollback; #回滾事務，測回

　　create table account(
　　id int primary key auto_increment,
　　name varchar(40),
　　money double

　　);

　　insert into account values(null,'賴澤銨',2000),(null,'侯文澤',1000);

　　**（4）
　　原子性：事務時一組不可分割的單位，要么同時成功要么同時不成功。

　　一致性：事務前后的數據完整性應該保持一致

　　隔離性：多個用戶并發訪問數據庫時，一個用戶的事務不能被其他用戶事務干擾。

　　持久性：一旦提交，數據的改變將是永久性

　　隔離性：本質就是多個線程操作同一個資源造成的多線程并發安全問題，加鎖可以保證隔離性，但是造成數據庫性能下降

　　如果兩個事務并發的修改：必須隔離
　　如果兩個事務并發查詢：不用隔離
　　如果一個事務修改一個查詢：臟讀#中途撤銷，不可重復讀#中途修改，虛讀#新增內容**

　　四大隔離：
　　read uncommitted #不隔離
　　read committed #可以防止臟讀
　　Repeatable read #不能防止虛讀，只能讀到開始時間事務的數據，想查看之后時間的數據只能終止事務才能看到
　　Serializable #數據庫運行在串行化未實現，性能低，直接鎖住，對方不能修改，待事務結束。

　　默認Repeatable read

　　設置語句：

　　SET SESSION TRANSCATION ISOLATION LEVEL 隔離等級;

　　查詢語句：

　　select @@tx_isolation; #上面語句執行成功后

　　2.存儲過程的創建

　　重復使用某一功能的情況，減少工作量

　　（1) 語法：

　　CREATE PROCEDURE sp_name([proc_parameter])
　　[characteristics...] routine_body

　　~proc_parameter#參數列表
　　形式：[IN | OUT | INOUT]param_name#參數名稱 type#參數類型

　　~characteristics#存儲特性

　　LANGUAGE SQL:說明routine_body部分由SQL語句組成

　　[NOT]DETERMINISTIC:指明存儲過程執行的結果是否確定。默認NOT

　　{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}:分別是包含SQL語句但不包含讀寫數據語句，不包含SQL語句，讀寫數據語句，寫數據語句。
　　默認CONTAINS SQL

　　SQL SECURITY {DEFINER | INVOKER}:指明誰有權限來執行，DEFINER 表示只有定義者才能執行，INVOKER 表示擁有權限的調用者可以執行。
　　默認 DEFINER

　　COMMENT'string‘：注釋信息，可以用來描述存儲過程

　　~routine_body:SQL語句，
　　DELIMITER // #將結束符定義為//
　　BEGIN
　　.....
　　......
　　END

　　delimiter //

　　create procedure nbaf()
　　begin
　　select from team left join star on team.id = star.team_id
　　union
　　select from team right join star on team.id = star.team_id;
　　end//

　　delimiter ;

　　call nbaf();

　　(2)在存儲過程中定義一個變量
　　變量的聲明一定要在存儲過程的BEGIN和END之間，作用范圍是當前的存儲范圍
　　DECLARE var_name [,varname]...data_type [DEFAULT value];

　　修改變量值1：
　　SET var_name = expr[,var_name = expr]#表達式賦予給.....;

　　修改變量值2：
　　SELECT col_name [...]#數據 into#復制 var_name [....]#變量 table_expr#查詢條件;

　　delimiter //

　　create procedure pf(in p_id int)
　　begin
　　select from team left join star on team.id = star.team_id where team.id = p_id
　　union
　　select from team right join star on team.id = star.team_id where team.id = p_id;
　　end//

　　delimiter ;

　　call pf();

　　3.定義條件和處理程序

　　（1）定義條件：是事先定義程序執行過程中遇到的問題，處理程序定義了在遇到這些問題時應當采取的處理方式，并且保證存儲過程中遇到警告或錯誤時能繼續執行。
　　DECLARE condition_name CONDITION FOR [condition_type];

　　condition_type 的兩種形式：
　　[condition_type]：
　　SQLSTATE[VALUE] sqlstate_value | mysql_error_code

　　sqlstate_value：是長度為5的字符串類型錯誤代碼，
　　mysql_error_code：為數值類型的錯誤代碼
　　例如：ERROR1142(42000) ,sqlstate_value:42000,mysql_error_code:1142

　　（2）定義處理程序
　　DECLARE handler_type HANDER FOR condition_value[,...] sp_statement

　　handler_type：CONTINUE | EXIT | UNDO#遇到錯誤撤回之前的操作，但是MySQL不支持

　　condition_value:

　　SQLSTATE[VALUE] sqlstate_value：包含5個字符的字符串錯誤值
　　condition_name ：錯誤條件名稱
　　SQLWARNING ：匹配所有以01開頭的SQLSTATE錯誤代碼
　　NOT FOUND ：匹配所有以02開頭的SQLSTATE錯誤代碼
　　SQLEXCEPTION ：匹配所有除01，02開頭外的SQLSTATE錯誤代碼
　　mysql_error_code ：匹配數值類型的錯誤代碼

　　定義處理程序的幾種方式

　　declare continue handler for SQLSTATE '42S02' set @info= 'NO_SUCH_TABLE' ; #info輸出

　　declare continue handler for 1146 set @info= 'NO_SUCH_TABLE' ; #1146，捕獲mysql_error_code

　　declare no_such_table condition for 1146;
　　declare continue handler for NO_SUCH_TABLE set @info= 'ERROR' ; #先定義條件，然后調用

　　declare exit handler for SQLWARNING set @info= 'ERROR' ;

　　declare exit handler for NOT FOUND set @info= 'NO_SUCH_TABLE' ;

　　declare exit handler for SQLEXCEPTION set @info= 'ERROR' ;

　　4.光標的使用：數據量非常大時使用光標逐條查詢

　　（1）光標的聲明：在聲明變量、條件后，聲明處理程序之后

　　DECLARE cursor_name CURSOR FOR select_statement

　　(2)光標的使用
　　打開光標：
　　OPEN cursor_name;
　　FETCH cursor_name INTO var_name [,var_name]....
　　關閉光標：
　　CLOSE curse_name

　　5.控制流程的使用：在編寫存儲過程中

　　（1）IF語句：
　　IF expr_condition THEN statement_list
　　[ELSEIF expr_contidion THEN statement_list]
　　[ELSE statement_list]
　　END IF
　　//expr_condition 判斷語句 statement_list SQL語句

　　（2）CASE語句：
　　CASE case_expr
　　WHEN when_value THEN statement_list
　　[ WHEN when_value THEN statement_list]
　　............
　　[ELSE statement_list]
　　END CASE;

　　(3) LOOP語句：
　　[loop_table:] LOOP
　　statement_list
　　END LOOP [loop_tabel];

　　                    //loop_table表示標注名稱，可以省略

　　----------------------------------------------------------------------------------------------------------------------------------
　　delimiter //

　　create procedure east()
　　begin
　　declare ep1 int default 0;
　　declare ep2 int default 7;
　　east_p:LOOP
　　set ep1 = ep1 + 1;
　　if ep1< 4 then select * from team left join star on team.id = star.team_id where team.id = ep1
　　     union
　　     select * from team right join star on team.id = star.team_id where team.id = ep1;

　　else leave loop;
　　end if;
　　end LOOP esat_p;

　　end//

　　delimiter ;

　　call east();
　　//不會用
　　------------------------------------------------------------

　　（4）LEAVE

　　LEAVE label #退出循環

　　（5）ITERATE
　　ITERATE label #再次循環，回到開頭

　　（6）REPEAT

　　[repeat_lable:] REPEAT
　　     statement_list
　　UNTIL expr_condition                  #直到判斷語句為真退出
　　END REPEAT[repeat_lable]
　　（7）WHILE

　　[while_lable:] WHEIL expr_condition DO
　　statement_list
　　END WHILE [while_lable]

　　6.調用存儲過程

　　（1）執行存儲過程

　　CALL sp_name([parameter[.....]])

　　7.查看存儲過程

　　（1）SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

　　show procedure status;

　　show procedure status like 'nbaf';

　　show procedure status like '%f'/G #查看以f結尾的存儲過程

　　8.修改存儲過程

　　ALTER {PROCEDURE | FUNCTION } sp_name [characteristic....]
　　#characteristic表示要修改的存儲過程的哪個部分，取值如下
　　~CONTAINS SQL
　　~NO SQL
　　~READS SQL DATA #讀數據
　　~MODIFIES SQL DATA #寫數據
　　~SQL SECURITY { DEFINER | INVOKER}
　　~COMMENT'string' #注釋

　　目前MySQL還不提供對已經存在的存儲過程代碼進行修改，要修改先刪除

　　9.刪除存儲過程

　　DROP {PROCEDURE | FUNCTION } [IF EXISTS] sp_name;

（編輯：武林網）

上一篇：基于xtrabackup來實現InnoDB存儲引擎的備份恢復

下一篇：mybatis if test非空判斷數字0為什么是false