程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> 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"信息。

感謝閱讀此文,希望能幫助到大家,謝謝大家對本站的支持!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved