mysql 預存程序詳解_MySQL

mysql/>MySQL預存程序

 14.1.1 建立預存程序

MySQL中，建立預存程序的基本形式如下：

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) 
        [characteristic ...] routine_body

其中，sp_name參數是預存程序的名稱；proc_parameter表示預存程序的參數清單；characteristic參數指定預存程序的特性；routine_body參數是SQL程式碼的內容，可以用BEGIN…END來標誌SQL程式碼的開始和結束。

proc_parameter中的每個參數由3部分組成。這3部分分別是輸入輸出類型、參數名稱、參數類型。其形式如下：

[ IN | OUT | INOUT ] param_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部分對預存程序進行簡單的註釋，以便日後在閱讀預存程序的程式碼時更加方便。

【範例14-1】 下面建立一個名為num_from_employee的預存程序。程式碼如下：

CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT ) 
     READS SQL DATA 
     BEGIN 
       SELECT COUNT(*) INTO count_num 
       FROM employee 
       WHERE d_id=emp_id ; 
     END 

上述程式碼中，預存程序名稱為num_from_employee；輸入變數為emp_id；輸出變數為count_num。 SELECT語句從employee表查詢d_id值等於emp_id的記錄，並用COUNT(*)計算d_id值相同的記錄的條數，最後將計算結果存入count_num。程式碼的執行結果如下：

mysql> DELIMITER && 
mysql> CREATE PROCEDURE num_from_employee
(IN emp_id INT, OUT count_num INT ) 
  -> READS SQL DATA 
  -> BEGIN 
  -> SELECT COUNT(*) INTO count_num 
  -> FROM employee 
  -> WHERE d_id=emp_id ; 
  -> END && 
Query OK, 0 rows affected (0.09 sec) 
mysql> DELIMITER ; 

程式碼執行完畢後，沒有報出任何出錯訊息就表示儲存函數已經建立成功。以後就可以呼叫這個預存程序，資料庫中會執行預存過程中的SQL語句。

說明：MySQL中預設的語句結束符為分號（;）。儲存過程中的SQL語句需要分號來    結束。為了避免衝突，首先用"DELIMITER &&"將MySQL的結束符號設定為&&。最後再用"DELIMITER ;"來將結束符號恢復成分號。這與創建觸發器時是一樣的。

14.1.2 建立儲存函數

在MySQL中，建立儲存函數的基本形式如下：

CREATE FUNCTION sp_name ([func_parameter[,...]]) 
    RETURNS type 
    [characteristic ...] routine_body 

其中，sp_name參數是儲存函數的名稱；func_parameter表示儲存函數的參數清單；RETURNS type指定傳回值的型別；characteristic參數指定儲存函數的特性，該參數的取值與儲存程序中的值是一樣的，請讀者參考14.1.1小節的內容；routine_body參數是SQL程式碼的內容，可以用BEGIN…END來標示SQL程式碼的開始與結束。

func_parameter可以由多個參數組成，其中每個參數由參數名稱和參數類型組成，其形式如下：

param_name type
其中，param_name參數是儲存函數的參數名稱；type參數指定儲存函數的參數類型，該類型可以是MySQL資料庫的任意資料類型。

【範例14-2】 下面建立一個名為name_from_employee的儲存函數。程式碼如下：

CREATE FUNCTION name_from_employee (emp_id INT ) 
     RETURNS VARCHAR(20) 
     BEGIN 
       RETURN (SELECT name 
       FROM employee 
       WHERE num=emp_id ); 
     END 

上述程式碼中，儲存函數的名稱為name_from_employee；此函數的參數為emp_id；傳回值為VARCHAR型別。 SELECT語句從employee表查詢num值等於emp_id的記錄，並將該記錄的name欄位的值傳回。程式碼的執行結果如下：

mysql> DELIMITER && 
mysql> CREATE FUNCTION name_from_employee (emp_id INT ) 
  -> RETURNS VARCHAR(20) 
  -> BEGIN 
  -> RETURN (SELECT name 
  -> FROM employee 
  -> WHERE num=emp_id ); 
  -> END&& 
Query OK, 0 rows affected (0.00 sec) 
mysql> DELIMITER ; 

結果顯示，儲存函數已經建立成功。此函數的使用和MySQL內部函數的使用方法一樣。

14.1.3  變項的使用

在存储过程和函数中，可以定义和使用变量。用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中。本小节将讲解如何定义变量和为变量赋值。

1．定义变量

MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：

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

其中， DECLARE关键字是用来声明变量的；var_name参数是变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。

【示例14-3】 下面定义变量my_sql，数据类型为INT型，默认值为10。代码如下：

DECLARE  my_sql  INT  DEFAULT 10 ;

2．为变量赋值

MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下：

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

其中，SET关键字是用来为变量赋值的；var_name参数是变量的名称；expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。

【示例14-4】 下面为变量my_sql赋值为30。代码如下：

SET  my_sql = 30 ;

MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下：

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

其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。

【示例14-5】 下面从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql。代码如下：

SELECT  d_id  INTO  my_sql 
        FROM  employee  WEHRE  id=2 ; 

14.1.4  定义条件和处理程序

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。MySQL中都是通过DECLARE关键字来定义条件和处理程序。本小节中将详细讲解如何定义条件和处理程序。

1．定义条件

MySQL中可以使用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。

【示例14-6】 下面定义"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 ; 

2．定义处理程序

MySQL中可以使用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表示一些存储过程或函数的执行语句。

【示例14-7】 下面是定义处理程序的几种方式。代码如下：

//方法一：捕获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"資訊。

感謝閱讀此文，希望能幫助大家，謝謝大家對本站的支持！