MySQL<事務與預存程序>

標籤：層級   exist   back   where   行資料   procedure   案例   varname   並發控制   

事務與預存程序 交易管理

　　事務的概念

　　　　謂的事務就是針對資料庫的一組操作，它可以由一條或多條SQL語句組成，同一個事務的操作具備同步的特點，即事務中的語句要麼都執行，要麼都不執行。

　　事務的使用

　　　　開啟事務START TRANSACTION;

　　　　執行SQL語句

　　　　提交事務COMMIT;

　　　　取消事務(復原)

　　事務的提交

　　　　事務中的動作陳述式都需要使用COMMIT語句手動提交，只有事務提交後其中的操作才會生效。

　　事務的復原

　　　　如果不想提交當前事務，可使用ROLLBACK語句取消當前事務。

　　　　ROLLBACK語句只能針對未提交的事務執行復原操作，已提交的事務是不能復原的。

　　事務的隔離等級

　　　　REPEATABLE READ(可重複讀)

　　　　READ UNCOMMITTED(讀未提交)

　　　　READ COMMITTED(讀提交)

　　　　SERIALIZABLE(可序列化)

事務的定義特性

　　原子性

    　　原子性是指一個事務必須被視為一個不可分割的最小工作單元，只有事務中所有的資料庫操作都執行成功，才算整個事務執行成功

　　一致性

   　　 一致性是指事務將資料庫從一種狀態轉變為下一種一致的狀態。

　　隔離性

   　　 隔離性還可以稱為並發控制、可序列化、鎖等，當多個使用者並發訪問資料庫時，資料庫為每一個使用者開啟的事務，不能被其他事務的操作資料所幹擾，多個並發事務之間要相互隔離。　　

　　持久性

    　　事務一旦提交，其所做的修改就會永久儲存到資料庫中，即使資料庫發生故障也不應該對其有任何影響。

    　　事務的持久性不能做到100%的持久，只能從事務本身的角度來保證永久性，而一些外部原因導致資料庫發生故障，如硬碟損壞，那麼所有提交的資料可能都會丟失。

預存程序的建立 　　什麼是預存程序

　　　　預存程序就是一條或多條SQL語句的集合，當對資料庫進行一系列複雜操作時，預存程序可以將這些複雜操作封裝成一個代碼塊，以便重複使用，大大減少資料庫開發人員的工作量。

　　建立預存程序

　　　　CREATE PROCEDURE sp_name([proc_parameter])

　　　　[characteristics…]routine_body

 

　　　　CREATE PROCEDURE：為用來建立預存程序的關鍵字。

　　　　sp_name：為預存程序的名稱。

　　　　proc_parameter：為指定預存程序的參數列表。

　　　　characteristics：用於指定預存程序的特性。

　　變數的使用

　　　　定義

　　　　　　在MySQL中，變數可以在子程式中聲明，用於儲存資料處理過程中的值，這些變數的作用範圍在BEGIN…END程式中。

　　　　　　DECLARE var_name[,varname]…date_type[DEFAULT value];

　　　　　　var_name：為局部變數的名稱。

　　　　　　DEFAULT value：子句給變數提供一個預設值，該值可以被聲明為一個常數或一個運算式。如果沒有DEFAULT子句，變數的初始值為NULL。

　　　　使用SET語句為變數賦值

　　　　　　SET var_name =

　　　　　　expr[,var_name = expr]…;

 

　　　　使用SELECT…INTO為一個或多個變數賦值

　　　　　　SELECT col_name[…]

　　　　　　INTO var_name[…] table_expr;

 

　　定義條件和處理常式

　　　　定義條件是指事先定義程式執行過程中遇到的問題

　　　　　　DECLARE condition_name CONDITION FOR [condition_type];

　　　　　　// condition_type的兩種形式：

　　　　　　[condition_type]:

　　　　　　SQLSTATE[VALUE] sqlstate_value|mysql_error_code

　　　　處理常式定義了在程式執行過程中遇到問題時應當採取的處理方式，並且保證預存程序在遇到警告或錯誤時能繼續執行處理過程使用DECLARE語句定義

　　　　　　DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement

　　　　　　handler_type:

　　　　　　CONTINUE|EXIT|UNDO

　　　　　　condition_value:

　　　　　　|condition_name

　　　　　　|SQLWARNING

　　　　　　|NOT FOUND

　　　　　　|SQLEXCEPTION

　　　　　　|mysql_error_code

　　　　　　handler_type：為錯誤處理方式，參數取值

　　　　　　有3個：CONTINUE、EXIT和UNDO。

  　　　　　CONTINUE：表示遇到錯誤不處理，繼續執行；

  　　　　　EXIT：遇到錯誤馬上退出。

  　　　　　UNDO：表示遇到錯誤後撤回之前的操作，MySQL中暫時不支援這樣的操作。sp_statement：參數為程式語句段，表示在遇到定義的錯誤時，需要執行的預存程序。

　　　　　　condition_value：表示錯誤類型。

　　游標的使用

　　　　在編寫預存程序時，查詢語句可能會返回多條記錄，如果資料量非常大，則需要使用游標來逐條讀取查詢結果集中的記錄。游標是一種用於輕鬆處理多行資料的機制。

　　　　聲明

　　　　　　文法：DECLARE cursor_name CURSOR FOR select_statement

　　　　　　樣本：DECLARE cursor_student CURSOR FOR select s_name,s_gender FROM student;

　　　　使用

　　　　　　文法：OPEN cursor_name FETCH cursor_name INTO var_name[,var_name]…

　　　　　　樣本：FETCH cursor_student INTO s_name, s_gender;

　　　　關閉

　　　　　　CLOSE cursor_name

流程式控制制的使用

　　預存程序中的流程式控制制語句用於將多個SQL語句劃分或組合成符合商務邏輯的代碼塊，MySQL中的流程式控制制語句有7個

　　1、 IF語句

　　　　文法格式如下：

　　　　IF expr_condition THEN statement_list

　　　　[ELSEIF expr_condition THEN statement_list]

　　　　[ELSE statement_list]

　　　　END IF

　　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_label:]LOOP

　　　　statement_list

　　　　END LOOP [loop_label]

    4、LEAVE語句

　　　　文法格式如下：

　　　　LEAVE lable

　　5、ITERATE語句

　　　　文法格式如下：

　　　　ITERATE lable

    6、REPEAT語句

　　　　文法格式如下：

　　　　[repeat_lable:] REPEAT

　　　　statement_list

　　　　UNTIL expr_condition

　　　　END REPEAT[repeat_lable]

　　7、WHILE語句

　　　　文法格式如下：

　　　　[[while_lable:] WHILE expr_condition DO

　　　　Statement_list

　　　　END WHILE [while_lable]

預存程序的使用 　　調用預存程序

　　　　CALL sp_name([parameter[,…]])

　　　　CALL：為調用預存程序的關鍵字。

　　　　sp_name：為預存程序的名稱。

　　　　Parameter：為預存程序的參數。

　　查看預存程序

　　　　1、使用SHOW STATUS語句

　　　　　　CALL sp_name([parameter[,…]])

 

　　　　2、使用SHOW CREATE語句

　　　　　　SHOW CREATE{PROCEDURE|FUNCTION} sp_name

　　　　3、information_schema.Routines表中查看

　　　　　　SELECT * FROM  information_schema.Routines

　　　　　　WHERE ROUTINE_NAME=‘CountProc1‘

　　　　　　AND ROUTINE_TYPE=‘PROCEDURE‘\G

　　修改預存程序

　　　　ALTER {PROCEDURE|FUNCTION} sp_name[characteristic…]

　　　　sp_name：表示預存程序或函數的名稱。characteristic：表示要修改預存程序的哪個部分， characteristic 的取值分為8部分。

　　刪除預存程序

　　　　DROP{ PROCEDURE|FUNCTION }[IF EXISTS] sp_name

　　　　綜合案例--預存程序應用

MySQL<事務與預存程序>