事務與存儲過程

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;