【MySQL 08】存儲過程

首先,對于sql的存儲過程,就類似于java里面的定義方法,調用方法。

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數據庫的任意數據類型

簡單化定義:

create?procedure?過程名(參數...)  begin  ????SQL語句...end

調用:

call?過程名(實參)

注意:?
??????在定義前,需要先把默認的語句結束’;’號改成其它,如’&&’,這樣存儲過程中定義的分號就不會被看成是語句結束(否則會直接被提交)。在定義完之后,還要把’;’還原成默認的結束符。

2、無參的存儲過程

//建立存儲過程mysql>?delimiter?%%  mysql>?create?procedure?p1()????  ->?begin????  ->?select?Sname,Ssex?from?student;????  ->?end?%%mysql>?delimiter?;??  //調用存儲過程mysql>?call?p1();?  +--------+------+|?Sname??|?Ssex?|  +--------+------+|?趙英????|?女???|  |?鄭美麗??|?女???|  |?李哲????|?男???|  |?王洋????|?女???|  |?王平????|?男???|  |?趙東新??|?男???|  |?王新????|?男???|  |?陶麗平??|?男???|  |?陳文????|?女???|  |?蔡天????|?女???||?楊洋????|?男???|  +--------+------+

3、有參的存儲過程

//查詢mysql>?select?*?from?employee;  +------------+------------+------------+--------------+|?employeeID?|?name???????|?job????????|?departmentID?|  +------------+------------+------------+--------------+|?6651???????|?Ajay?Patel?|?Programmer?|??????????128?|  |?7513???????|?Nora?Edwar?|?Programmer?|??????????128?|  |?9006???????|?Candy?Burn?|?Systems?Ad?|??????????128?|  |?9842???????|?Ben?Smith??|?DBA????????|???????????42?|  |?9843???????|?Pert?Park??|?DBA????????|???????????42?|  |?9845???????|?Ben?Patel??|?DBA????????|??????????128?|  |?9846???????|?Red?Right??|?NULL???????|??????????128?|  |?9847???????|?Run?Wild???|?NULL???????|??????????128?|  |?9848???????|?Rip?This?J?|?NULL???????|??????????128?|  |?9849???????|?Rip?This?J?|?NULL???????|??????????128?|  |?9850???????|?Reader?U???|?NULL???????|??????????128?||?6651???????|?Ajay?Patel?|?Programmer?|??????????128?|  +------------+------------+------------+--------------+//建立存儲過程mysql>?delimiter?&&  mysql>?create?procedure?p4(in?employeeID?char(4),in?name?varchar(20),in?job?varchar(20),in?departmentID?int(11))????->?begin????  ->?insert?into?employee?value(employeeID,name,job,departmentID);????->?select?*?from?employee;????->?end?&&mysql>?delimiter?;??  //調用存儲過程mysql>?call?p4('7322','cid','udewhuuwdwq',127);  +------------+------------+-------------+--------------+|?employeeID?|?name???????|?job?????????|?departmentID?|  +------------+------------+-------------+--------------+|?6651???????|?Ajay?Patel?|?Programmer??|??????????128?|  |?7513???????|?Nora?Edwar?|?Programmer??|??????????128?|  |?9006???????|?Candy?Burn?|?Systems?Ad??|??????????128?|  |?9842???????|?Ben?Smith??|?DBA?????????|???????????42?|  |?9843???????|?Pert?Park??|?DBA?????????|???????????42?|  |?9845???????|?Ben?Patel??|?DBA?????????|??????????128?|  |?9846???????|?Red?Right??|?NULL????????|??????????128?|  |?9847???????|?Run?Wild???|?NULL????????|??????????128?|  |?9848???????|?Rip?This?J?|?NULL????????|??????????128?|  |?9849???????|?Rip?This?J?|?NULL????????|??????????128?|  |?9850???????|?Reader?U???|?NULL????????|??????????128?|  |?6651???????|?Ajay?Patel?|?Programmer??|??????????128?||?7322???????|?cid????????|?udewhuuwdwq?|??????????127?|  +------------+------------+-------------+--------------+

4、有返回值的存儲過程

//建立存儲過程mysql>?delimiter?&&  mysql>?create?procedure?p9(in?employeeID?char(4),in?name?varchar(20),in?job?varchar(20),in?departmentID?int(11),out?num?int)  ????->?begin  ????->?insert?into?employee?value(employeeID,name,job,departmentID);  ????->?select?count(*)?into?num?from?employee;  ????->?end?&&    mysql>?delimiter?;?    //調用存儲過程mysql>?call?p9('3632','dueh','xianggang',28,@num);//注意輸出變量的輸出mysql>?select?@num;  +------+  |?@num?|  +------+  |???15?|  +------+//////////////////////////系統變量名稱:@@變量名///?//用戶變量名稱:@變量名?///

5、 變量的使用

在存儲過程和函數中,可以定義和使用變量。用戶可以使用DECLARE關鍵字來定義變量。然后可以為變量賦值。這些變量的作用范圍是BEGIN…END程序段中。本小節將講解如何定義變量和為變量賦值。

1.定義變量

MySQL中可以使用DECLARE關鍵字來定義變量。定義變量的基本語法如下:

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

其中, DECLARE關鍵字是用來聲明變量的;var_name參數是變量的名稱,這里可以同時定義多個變量;type參數用來指定變量的類型;DEFAULT value子句將變量默認值設置為value,沒有使用DEFAULT子句時,默認值為NULL。

DECLARE??my_sql??INT??DEFAULT?10?;?//定義變量my_sql,數據類型為INT型,默認值為10

2.為變量賦值

MySQL中可以使用SET關鍵字來為變量賦值。SET語句的基本語法如下:

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

其中,SET關鍵字是用來為變量賦值的;var_name參數是變量的名稱;expr參數是賦值表達式。一個SET語句可以同時為多個變量賦值,各個變量的賦值語句之間用逗號隔開。

SET?my_sql?=?30?;?//為變量my_sql賦值為30

MySQL中還可以使用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?;?//從employee表中查詢id為2的記錄,將該記錄的d_id值賦給變量my_sql

6、定義條件和處理程序

定義條件和處理程序是事先定義程序執行過程中可能遇到的問題。并且可以在處理程序中定義解決這些問題的辦法。這種方式可以提前預測可能出現的問題,并提出解決辦法。這樣可以增強程序處理問題的能力,避免程序異常停止。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。

//定義"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表示一些存儲過程或函數的執行語句。

//定義處理程序的幾種方式  //方法一:捕獲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';
  • 第一種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為42S02,執行CONTINUE操作,并且輸出”CANNOT 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”信息。

以上就是?【MySQL 08】存儲過程的內容,更多相關內容請關注PHP中文網(www.php.cn)!

? 版權聲明
THE END
喜歡就支持一下吧
點贊11 分享