《Mysql實(shí)例mysql 存儲(chǔ)過程詳解》要點(diǎn)：
本文介紹了Mysql實(shí)例mysql 存儲(chǔ)過程詳解，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

MySQL存儲(chǔ)過程MYSQL實(shí)例

?14.1.1 創(chuàng)建存儲(chǔ)過程MYSQL實(shí)例

MySQL中,創(chuàng)建存儲(chǔ)過程的基本形式如下：MYSQL實(shí)例

CREATE PROCEDURE sp_name ([proc_parameter[,...]])?
??????? [characteristic ...] routine_body
MYSQL實(shí)例

其中,sp_name參數(shù)是存儲(chǔ)過程的名稱；proc_parameter表示存儲(chǔ)過程的參數(shù)列表； characteristic參數(shù)指定存儲(chǔ)過程的特性；routine_body參數(shù)是SQL代碼的內(nèi)容,可以用BEGIN…END來標(biāo)志SQL代碼的開始和結(jié)束.MYSQL實(shí)例

proc_parameter中的每個(gè)參數(shù)由3部分組成.這3部分分別是輸入輸出類型、參數(shù)名稱和參數(shù)類型.其形式如下：MYSQL實(shí)例

[ IN | OUT | INOUT ] param_name type MYSQL實(shí)例

其中,IN表示輸入?yún)?shù)；OUT表示輸出參數(shù)； INOUT表示既可以是輸入,也可以是輸出； param_name參數(shù)是存儲(chǔ)過程的參數(shù)名稱；type參數(shù)指定存儲(chǔ)過程的參數(shù)類型,該類型可以是MySQL數(shù)據(jù)庫(kù)的任意數(shù)據(jù)類型.MYSQL實(shí)例

characteristic參數(shù)有多個(gè)取值.其取值說明如下：MYSQL實(shí)例

LANGUAGE SQL：說明routine_body部分是由SQL語(yǔ)言的語(yǔ)句組成,這也是數(shù)據(jù)庫(kù)系統(tǒng)默認(rèn)的語(yǔ)言.MYSQL實(shí)例

[NOT] DETERMINISTIC：指明存儲(chǔ)過程的執(zhí)行結(jié)果是否是確定的.DETERMINISTIC表示結(jié)果是確定的.每次執(zhí)行存儲(chǔ)過程時(shí),相同的輸入會(huì)得到相同的輸出.NOT DETERMINISTIC表示結(jié)果是非確定的,相同的輸入可能得到不同的輸出.默認(rèn)情況下,結(jié)果是非確定的.MYSQL實(shí)例

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL語(yǔ)句的限制.CONTAINS SQL表示子程序包含SQL語(yǔ)句,但不包含讀或?qū)憯?shù)據(jù)的語(yǔ)句；NO SQL表示子程序中不包含SQL語(yǔ)句；READS SQL DATA表示子程序中包含讀數(shù)據(jù)的語(yǔ)句；MODIFIES SQL DATA表示子程序中包含寫數(shù)據(jù)的語(yǔ)句.默認(rèn)情況下,系統(tǒng)會(huì)指定為CONTAINS SQL.MYSQL實(shí)例

SQL SECURITY { DEFINER | INVOKER }：指明誰有權(quán)限來執(zhí)行.DEFINER表示只有定義者自己才能夠執(zhí)行；INVOKER表示調(diào)用者可以執(zhí)行.默認(rèn)情況下,系統(tǒng)指定的權(quán)限是DEFINER.MYSQL實(shí)例

COMMENT 'string'：注釋信息.MYSQL實(shí)例

技巧：創(chuàng)建存儲(chǔ)過程時(shí),系統(tǒng)默認(rèn)指定CONTAINS SQL,表示存儲(chǔ)過程中使用了SQL語(yǔ)句.但是,如果存儲(chǔ)過程中沒有使用SQL語(yǔ)句,最好設(shè)置為NO SQL.而且,存儲(chǔ)過程中最好在COMMENT部分對(duì)存儲(chǔ)過程進(jìn)行簡(jiǎn)單的注釋,以便以后在閱讀存儲(chǔ)過程的代碼時(shí)更加方便.MYSQL實(shí)例

【示例14-1】 下面創(chuàng)建一個(gè)名為num_from_employee的存儲(chǔ)過程.代碼如下：MYSQL實(shí)例

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 

上述代碼中,存儲(chǔ)過程名稱為num_from_employee；輸入變量為emp_id；輸出變量為count_num.SELECT語(yǔ)句從employee表查詢d_id值等于emp_id的記錄,并用COUNT(*)計(jì)算d_id值相同的記錄的條數(shù),最后將計(jì)算結(jié)果存入count_num中.代碼的執(zhí)行結(jié)果如下：MYSQL實(shí)例

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 ; 

代碼執(zhí)行完畢后,沒有報(bào)出任何出錯(cuò)信息就表示存儲(chǔ)函數(shù)已經(jīng)創(chuàng)建成功.以后就可以調(diào)用這個(gè)存儲(chǔ)過程,數(shù)據(jù)庫(kù)中會(huì)執(zhí)行存儲(chǔ)過程中的SQL語(yǔ)句.MYSQL實(shí)例

說明：MySQL中默認(rèn)的語(yǔ)句結(jié)束符為分號(hào)(;).存儲(chǔ)過程中的SQL語(yǔ)句需要分號(hào)來??? 結(jié)束.為了避免沖突,首先用"DELIMITER &&"將MySQL的結(jié)束符設(shè)置為&&.最后再用"DELIMITER ;"來將結(jié)束符恢復(fù)成分號(hào).這與創(chuàng)建觸發(fā)器時(shí)是一樣的.MYSQL實(shí)例

14.1.2 創(chuàng)建存儲(chǔ)函數(shù)MYSQL實(shí)例

在MySQL中,創(chuàng)建存儲(chǔ)函數(shù)的基本形式如下：MYSQL實(shí)例

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

其中,sp_name參數(shù)是存儲(chǔ)函數(shù)的名稱；func_parameter表示存儲(chǔ)函數(shù)的參數(shù)列表；RETURNS type指定返回值的類型；characteristic參數(shù)指定存儲(chǔ)函數(shù)的特性,該參數(shù)的取值與存儲(chǔ)過程中的取值是一樣的,請(qǐng)讀者參照14.1.1小節(jié)的內(nèi)容；routine_body參數(shù)是SQL代碼的內(nèi)容,可以用BEGIN…END來標(biāo)志SQL代碼的開始和結(jié)束.MYSQL實(shí)例

func_parameter可以由多個(gè)參數(shù)組成,其中每個(gè)參數(shù)由參數(shù)名稱和參數(shù)類型組成,其形式如下：MYSQL實(shí)例

param_name type
其中,param_name參數(shù)是存儲(chǔ)函數(shù)的參數(shù)名稱；type參數(shù)指定存儲(chǔ)函數(shù)的參數(shù)類型,該類型可以是MySQL數(shù)據(jù)庫(kù)的任意數(shù)據(jù)類型.MYSQL實(shí)例

【示例14-2】 下面創(chuàng)建一個(gè)名為name_from_employee的存儲(chǔ)函數(shù).代碼如下：MYSQL實(shí)例

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

上述代碼中,存儲(chǔ)函數(shù)的名稱為name_from_employee；該函數(shù)的參數(shù)為emp_id；返回值是VARCHAR類型.SELECT語(yǔ)句從employee表查詢num值等于emp_id的記錄,并將該記錄的name字段的值返回.代碼的執(zhí)行結(jié)果如下：MYSQL實(shí)例

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 ; 

結(jié)果顯示,存儲(chǔ)函數(shù)已經(jīng)創(chuàng)建成功.該函數(shù)的使用和MySQL內(nèi)部函數(shù)的使用辦法一樣.MYSQL實(shí)例

14.1.3? 變量的使用MYSQL實(shí)例

在存儲(chǔ)過程和函數(shù)中,可以定義和使用變量.用戶可以使用DECLARE關(guān)鍵字來定義變量.然后可以為變量賦值.這些變量的作用范圍是BEGIN…END程序段中.本小節(jié)將講解如何定義變量和為變量賦值.MYSQL實(shí)例

1．定義變量MYSQL實(shí)例

MySQL中可以使用DECLARE關(guān)鍵字來定義變量.定義變量的基本語(yǔ)法如下：MYSQL實(shí)例

DECLARE? var_name[,...]? type? [DEFAULT value]
MYSQL實(shí)例

其中, DECLARE關(guān)鍵字是用來聲明變量的；var_name參數(shù)是變量的名稱,這里可以同時(shí)定義多個(gè)變量；type參數(shù)用來指定變量的類型；DEFAULT value子句將變量默認(rèn)值設(shè)置為value,沒有使用DEFAULT子句時(shí),默認(rèn)值為NULL.MYSQL實(shí)例

【示例14-3】 下面定義變量my_sql,數(shù)據(jù)類型為INT型,默認(rèn)值為10.代碼如下：MYSQL實(shí)例

DECLARE? my_sql? INT? DEFAULT 10 ;
MYSQL實(shí)例

2．為變量賦值MYSQL實(shí)例

MySQL中可以使用SET關(guān)鍵字來為變量賦值.SET語(yǔ)句的基本語(yǔ)法如下：MYSQL實(shí)例

SET? var_name = expr [, var_name = expr] ...
MYSQL實(shí)例

其中,SET關(guān)鍵字是用來為變量賦值的；var_name參數(shù)是變量的名稱；expr參數(shù)是賦值表達(dá)式.一個(gè)SET語(yǔ)句可以同時(shí)為多個(gè)變量賦值,各個(gè)變量的賦值語(yǔ)句之間用逗號(hào)隔開.MYSQL實(shí)例

【示例14-4】 下面為變量my_sql賦值為30.代碼如下：MYSQL實(shí)例

SET? my_sql = 30 ;
MYSQL實(shí)例

MySQL中還可以使用SELECT…INTO語(yǔ)句為變量賦值.其基本語(yǔ)法如下：MYSQL實(shí)例

SELECT? col_name[,…]? INTO? var_name[,…]?
??? FROM? table_name? WEHRE? condition
MYSQL實(shí)例

其中,col_name參數(shù)表示查詢的字段名稱；var_name參數(shù)是變量的名稱；table_name參數(shù)指表的名稱；condition參數(shù)指查詢條件.MYSQL實(shí)例

【示例14-5】 下面從employee表中查詢id為2的記錄,將該記錄的d_id值賦給變量my_sql.代碼如下：MYSQL實(shí)例

SELECT? d_id? INTO? my_sql?
??????? FROM? employee? WEHRE? id=2 ;?
MYSQL實(shí)例

14.1.4? 定義條件和處理程序MYSQL實(shí)例

定義條件和處理程序是事先定義程序執(zhí)行過程中可能遇到的問題.并且可以在處理程序中定義解決這些問題的方法.這種方式可以提前預(yù)測(cè)可能出現(xiàn)的問題,并提出解決方法.這樣可以增強(qiáng)程序處理問題的能力,避免程序異常停止.MySQL中都是通過DECLARE關(guān)鍵字來定義條件和處理程序.本小節(jié)中將詳細(xì)講解如何定義條件和處理程序.MYSQL實(shí)例

1．定義條件MYSQL實(shí)例

MySQL中可以使用DECLARE關(guān)鍵字來定義條件.其基本語(yǔ)法如下：MYSQL實(shí)例

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

其中,condition_name參數(shù)表示條件的名稱；condition_value參數(shù)表示條件的類型；sqlstate_value參數(shù)和mysql_error_code參數(shù)都可以表示MySQL的錯(cuò)誤.例如ERROR 1146 (42S02)中,sqlstate_value值是42S02,mysql_error_code值是1146.MYSQL實(shí)例

【示例14-6】 下面定義"ERROR 1146 (42S02)"這個(gè)錯(cuò)誤,名稱為can_not_find.可以用兩種不同的辦法來定義,代碼如下：MYSQL實(shí)例

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

2．定義處理程序MYSQL實(shí)例

MySQL中可以使用DECLARE關(guān)鍵字來定義處理程序.其基本語(yǔ)法如下：MYSQL實(shí)例

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參數(shù)指明錯(cuò)誤的處理方式,該參數(shù)有3個(gè)取值.這3個(gè)取值分別是CONTINUE、EXIT和UNDO.CONTINUE表示遇到錯(cuò)誤不進(jìn)行處理,繼續(xù)向下執(zhí)行；EXIT表示遇到錯(cuò)誤后馬上退出；UNDO表示遇到錯(cuò)誤后撤回之前的操作,MySQL中暫時(shí)還不支持這種處理方式.MYSQL實(shí)例

注意：通常情況下,執(zhí)行過程中遇到錯(cuò)誤應(yīng)該立刻停止執(zhí)行下面的語(yǔ)句,并且撤回前面的操作.但是,MySQL中現(xiàn)在還不能支持UNDO操作.因此,遇到錯(cuò)誤時(shí)最好執(zhí)行EXIT操作.如果事先能夠預(yù)測(cè)錯(cuò)誤類型,并且進(jìn)行相應(yīng)的處理,那么可以執(zhí)行CONTINUE操作.MYSQL實(shí)例

condition_value參數(shù)指明錯(cuò)誤類型,該參數(shù)有6個(gè)取值.sqlstate_value和mysql_error_code與條件定義中的是同一個(gè)意思.condition_name是DECLARE定義的條件名稱.SQLWARNING表示所有以01開頭的sqlstate_value值.NOT FOUND表示所有以02開頭的sqlstate_value值.SQLEXCEPTION表示所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值.sp_statement表示一些存儲(chǔ)過程或函數(shù)的執(zhí)行語(yǔ)句.MYSQL實(shí)例

【示例14-7】 下面是定義處理程序的幾種方式.代碼如下：MYSQL實(shí)例

//辦法一：捕獲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'; 
//辦法三：先定義條件,然后調(diào)用 
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,執(zhí)行CONTINUE操作,并且輸出"CAN NOT FIND"信息.第二種辦法是捕獲mysql_error_code值.如果遇到mysql_error_code值為1146,執(zhí)行CONTINUE操作,并且輸出"CAN NOT FIND"信息.第三種辦法是先定義條件,然后再調(diào)用條件.這里先定義can_not_find條件,遇到1146錯(cuò)誤就執(zhí)行CONTINUE操作.第四種辦法是使用SQLWARNING.SQLWARNING捕獲所有以01開頭的sqlstate_value值,然后執(zhí)行EXIT操作,并且輸出"ERROR"信息.第五種辦法是使用NOT FOUND.NOT FOUND捕獲所有以02開頭的sqlstate_value值,然后執(zhí)行EXIT操作,并且輸出"CAN NOT FIND"信息.第六種辦法是使用SQLEXCEPTION.SQLEXCEPTION捕獲所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值,然后執(zhí)行EXIT操作,并且輸出"ERROR"信息.MYSQL實(shí)例

感謝閱讀此文,希望能贊助到大家,謝謝大家對(duì)本站的支持!MYSQL實(shí)例

歡迎參與《Mysql實(shí)例mysql 存儲(chǔ)過程詳解》討論，分享您的想法，維易PHP學(xué)院為您提供專業(yè)教程。

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/13283.html