mysql的儲存過程

標籤：

儲存過程可以簡單理解為一條或多條SQL語句的集合。

儲存過程的操作包括建立儲存過程，查看儲存過程，更新儲存過程和刪除儲存過程。

1. 建立儲存過程

1.1 儲存過程文法結構

在MySQL中建立預存程序通過SQL語句CREATE PROCEDURE來實現，其文法形式如下：

CREATE PROCEDURE procedure_name([procedure_paramter[,…]])    [characteristic…] routine_body

procedure_name參數表示所要建立的儲存過程的名字，procedure_paramter表示儲存過程的參數,characteristic表示儲存過程的特性,routine_body表示儲存過程的SQL語句代碼，
可以使用BEGIN...END來標誌SQL語句的開始和結束。

procedure_name中每個參數的文法形式如下:

[IN|OUT|INOUT] procedure_name type

每個參數由三部分組成,分別為輸入/輸出類型,參數名和參數類型。其中輸入/輸出類型有三種類型，分別是IN表示輸入參數；OUT表示輸出參數； INOUT表示既可以是輸入，也可以是輸出； param_name參數是預存程序的參數名稱；type參數指定預存程序的參數類型，該類型可以是MySQL資料庫的任意資料類型。

characteristic參數有多個取值。其取值說明如下：

LANGUAGE SQL：說明routine_body部分是由SQL語言的語句組成，這也是資料庫系統預設的語言。

[NOT] DETERMINISTIC：指明預存程序的執行結果是否是確定的。DETERMINISTIC表示結果是確定的。每次執行預存程序時，相同的輸入會得到 相同的輸出。NOT DETERMINISTIC表示結果是非確定的，相同的輸入可能得到不同的輸出。預設情況下，結果是非確定的。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程式使用SQL語句的限制。CONTAINS SQL表示子程式包含SQL語句，但不包含讀或寫資料的語句；NO SQL表示子程式中不包含SQL語句；READS SQL DATA表示子程式中包含讀資料的語句；MODIFIES SQL DATA表示子程式中包含寫資料的語句。預設情況下，系統會指定為CONTAINS SQL。

SQL SECURITY { DEFINER | INVOKER }：指明誰有許可權來執行。DEFINER表示只有定義者自己才能夠執行；INVOKER表示調用者可以執行。預設情況下，系統指定的許可權是DEFINER。

COMMENT ‘string‘：注釋資訊。

技巧：建立預存程序時，系統預設指定CONTAINS SQL，表示預存程序中使用了SQL語句。但是，如果預存程序中沒有使用SQL語句，最好設定為NO SQL。而且，預存程序中最好在COMMENT部分對預存程序進行簡單的注釋，以便以後在閱讀預存程序的代碼時更加方便。

樣本如下:

DELIMITER $$CREATE PROCEDURE proce_employee_sal ()COMMENT‘查詢所有僱員的工資‘BEGIN    SELECT sal    FROM t_employee;END$$DELIMITER ;

1.2 關於儲存過程的運算式

運算式和其他進階語言的運算式一樣,由變數,運算子和流程式控制制來構成。

1.2.1  操作變數

a. 定義變數

使用DECLARE關鍵字來定義變數。定義變數的基本文法如下：

DECLARE  var_name[,...]  type  [DEFAULT value] 

DECLARE關鍵字是用來聲明變數的；var_name參數是變數的名稱，這裡可以同時定義多個變數；type參數用來指定變數的類型；DEFAULT value子句將變數預設值設定為value，沒有使用DEFAULT子句時，預設值為NULL。

樣本如下:

DECLARE  my_sql  INT  DEFAULT 10 ; 

b. 賦值變數

使用SET關鍵字來為變數賦值。SET語句的基本文法如下：

SET  var_name = expr [, var_name = expr] ... 

SET關鍵字是用來為變數賦值的；var_name參數是變數的名稱；expr參數是賦值運算式。一個SET語句可以同時為多個變數賦值，各個變數的指派陳述式之間用逗號隔開。

樣本如下:

SET  my_sql = 30 ; 

還可以使用SELECT…INTO語句為變數賦值。其基本文法如下：

SELECT  col_name[,…]  INTO  var_name[,…]   FROM  table_name  WEHRE  condition 

其中，col_name參數表示查詢的欄位名稱；var_name參數是變數的名稱；table_name參數指表的名稱；condition參數指查詢條件。

需要注意的是當將查詢結果賦值給變數時,該查詢語句的返回結果只能是單行。

樣本如下:

SELECT  d_id  INTO  my_sql  FROM  employee  WEHRE  id=2 ; 

1.2.2 操作條件

定義條件和處理常式是事先定義程式執行過程中可能遇到的問題。並且可以在處理常式中定義解決這些問題的辦法。這種方式可以提前預測可能出現的問題，並提出 解決辦法。這樣可以增強程式處理問題的能力，避免程式異常停止。MySQL中都是通過DECLARE關鍵字來定義條件和處理常式。

a. 定義條件

使用DECLARE關鍵字來定義條件。其基本文法如下：

DECLARE  condition_name  CONDITION  FOR  condition_value  condition_value:  SQLSTATE [VALUE] sqlstate_value | mysql_error_code 

其中，condition_name參數表示條件的名稱；condition_value參數表示條件的類型；sqlstate_value參數和mysql_error_code參數都可以表示MySQL的錯誤。例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。

樣本如下：

定義"ERROR 1146 (42S02)"這個錯誤，名稱為can_not_find。可以用兩種不同的方法來定義，代碼如下：

 //方法一：使用sqlstate_value     DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  ‘42S02‘ ;  //方法二：使用mysql_error_code     DECLARE  can_not_find  CONDITION  FOR  1146 ; 

b. 定義處理常式

使用DECLARE關鍵字來定義處理常式。其基本文法如下：

DECLARE handler_type HANDLER FOR     condition_value[,...] sp_statement      handler_type:          CONTINUE | EXIT | UNDO      condition_value:          SQLSTATE [VALUE] sqlstate_value |    condition_name  | SQLWARNING        | NOT FOUND  | SQLEXCEPTION  | mysql_error_code 

其中，handler_type參數指明錯誤的處理方式，該參數有3個取值。這3個取值分別是CONTINUE、EXIT和UNDO。CONTINUE表 示遇到錯誤不進行處理，繼續向下執行；EXIT表示遇到錯誤後馬上退出；UNDO表示遇到錯誤後撤回之前的操作，MySQL中暫時還不支援這種處理方式。

注意：通常情況下，執行過程中遇到錯誤應該立刻停止執行下面的語句，並且撤回前面的操作。但是，MySQL中現在還不能支援UNDO操作。因此，遇到錯誤時最好執行EXIT操作。如果事先能夠預測錯誤類型，並且進行相應的處理，那麼可以執行CONTINUE操作。

condition_value參數指明錯誤類型，該參數有6個取值。sqlstate_value和mysql_error_code與條件定義中的是 同一個意思。condition_name是DECLARE定義的條件名稱。SQLWARNING表示所有以01開頭的sqlstate_value值。 NOT FOUND表示所有以02開頭的sqlstate_value值。SQLEXCEPTION表示所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值。sp_statement表示一些預存程序或函數的執行語句。

樣本如下：

下面是定義處理常式的幾種方式。代碼如下：

    //方法一：捕獲sqlstate_value      DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02‘    SET @info=‘CAN NOT FIND‘;      //方法二：捕獲mysql_error_code      DECLARE CONTINUE HANDLER FOR 1146 SET @info=‘CAN NOT FIND‘;      //方法三：先定義條件，然後調用      DECLARE  can_not_find  CONDITION  FOR  1146 ;      DECLARE CONTINUE HANDLER FOR can_not_find SET     @info=‘CAN NOT FIND‘;      //方法四：使用SQLWARNING      DECLARE EXIT HANDLER FOR SQLWARNING SET @info=‘ERROR‘;      //方法五：使用NOT FOUND      DECLARE EXIT HANDLER FOR NOT FOUND SET @info=‘CAN NOT FIND‘;      //方法六：使用SQLEXCEPTION      DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=‘ERROR‘; 

上述代碼是6種定義處理常式的方法。第一種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為42S02，執行 CONTINUE操作，並且輸出"CAN NOT FIND"資訊。第二種方法是捕獲mysql_error_code值。如果遇到mysql_error_code值為1146，執行CONTINUE操 作，並且輸出"CAN NOT FIND"資訊。第三種方法是先定義條件，然後再調用條件。這裡先定義can_not_find條件，遇到1146錯誤就執行CONTINUE操作。第四 種方法是使用SQLWARNING。SQLWARNING捕獲所有以01開頭的sqlstate_value值，然後執行EXIT操作，並且輸 出"ERROR"資訊。第五種方法是使用NOT FOUND。NOT FOUND捕獲所有以02開頭的sqlstate_value值，然後執行EXIT操作，並且輸出"CAN NOT FIND"資訊。第六種方法是使用SQLEXCEPTION。SQLEXCEPTION捕獲所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值，然後執行EXIT操作，並且輸出"ERROR"資訊。

1.3 使用遊標

遊標可以看做一種資料類型,可以用來遍曆結果集,相當於指標,或者是數組中的下標。處理結果集的方法可以通過遊標定位到結果集的某一行,從當前結果集的位置搜尋一行或一部分行或者對結果集中的當前行進行資料修改。

1.3.1 聲明遊標

DECLARE cursor_name CURSOR FOR select_statement;

1.3.2 開啟遊標

OPEN cursor_name

1.3.3 使用遊標

FETCH cursor_name INTO var_name [,var_name] …

在上述語句中,將參數遊標cursor_name中SELECT語句的執行結果儲存到變數參數var_name中。變數參數var_name必須在遊標使用之前定義。使用遊標類似進階語言中的數組遍曆,當第一次使用遊標時,此時遊標指向結果集中的第一條記錄。

1.3.4 關閉遊標

CLOSE cursor_name

完整樣本如下所示:

DROP PROCEDURE IF EXISTS emplayee_count;DELIMITER $#建立預存程序CREATE PROCEDURE emplayee_count (OUT NUM INTEGER)BEGIN#聲明變數DECLARE   emplayee_sal INTEGER;DECLARE   flag INTEGER;#聲明遊標DECLARE cursor_emplayee     CURSOR FOR SELECT sal FROM t_employee;DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;#設定結束標誌SET flag=0;SET NUM=0;‘#開啟遊標OPEN cursor_emplayee;#遍曆遊標指向的結果集FETCH cursor_emplayee INTO emplayee_sal;WHILE flag<>1 DOIF emplayee_sal >999 THENSET num=num+1;END IF;FETCH cursor_emplayee INTO emplayee_sal;END WHILE;#關閉遊標CLOSE cursor_emplayee;END$DELIMITER ;

2. 查看儲存過程

3. 更新儲存過程

4. 刪除儲存過程

mysql的儲存過程