共計 11688 個字符，預(yù)計需要花費 30 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

丸趣 TV 小編給大家分享一下 MySQL 創(chuàng)建存儲程序的方法，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

簡單地說，存儲過程就是一條或者多條 SQL 語句的組合，可視為批文件，但是其作用又不僅限于批處理。

(1)創(chuàng)建存儲過程
(2) 創(chuàng)建存儲函數(shù)
(3) 變量的使用
(4) 定義條件和處理程序
(5) 光標的使用
(6) 流程控制的使用

(1)創(chuàng)建存儲過程

創(chuàng)建存儲過程需要使用 create procedure 語句，基本語法格式如下：

create procedure sp_name( [ proc_parameter ] )[ characteristics ... ] routine_body

create procedure 為用來創(chuàng)建存儲函數(shù)的關(guān)鍵字；sp_name 為存儲過程的名稱；proc_parameter 為存儲過程的參數(shù)列表，列表形式如下：

[in | out | inout] param_name type

in 表示輸入?yún)?shù)

out 表示輸出參數(shù)

inout 表示既可以輸入也可以輸出

param_name 表示參數(shù)名稱；type 表示參數(shù)的類型

characteristics 指定存儲過程的特征，有以下取值：

language SQL：說明 routine_body 部分是由 SQL 語句組成的，當(dāng)前系統(tǒng)支持的語言為 SQL，SQL 是 language 特性的唯一值。

[not] deterministic: 指明存儲過程執(zhí)行的結(jié)果是否正確。deterministic 表示每次執(zhí)行存儲過程時，相同的輸入會得到相同的輸出；而 not deterministic 表示相同的輸入可能得到不同的輸出。默認為 not deterministic。

{contains SQL | no SQL | reads SQL date | modifies SQL date}：指明子程序使用 SQL 語句的限制。contains SQL 表明子程序包含 SQL 語句；no SQL 表明子程序不包含 SQ；reads SQL data 表明子程序包含讀數(shù)據(jù)的語句；modifies SQL data 表明子程序包含寫數(shù)據(jù)的語句。默認為 contatins SQL。

SQL security {definer | invoker}：指明誰有權(quán)限來執(zhí)行。definer 表示只有定義者才能執(zhí)行；invoker 表示擁有權(quán)限的調(diào)用者可以執(zhí)行。默認為 definer。

comment‘string’：注釋信息，可以用來描述存儲過程或函數(shù)。

routine_body 是 SQL 代碼的內(nèi)容，可以用 begin…end 來表示 SQL 代碼的開始和結(jié)束。

【例 1】創(chuàng)建查看 fruits 表的存儲過程，代碼語句如下：

create procedure proc()
 BEGIN
 select * from fruits;
 END ;

這個代碼創(chuàng)建了一個查看 fruits 表的存儲過程，代碼執(zhí)行過程如下:

mysql  delimiter //mysql  create procedure Proc()
 -  begin
 -  select * from fruits;
 -  end //Query OK, 0 rows affected (0.36 sec)mysql  delimiter ;

提示:“delimiter //“語句的作用是將 MySQL 的結(jié)束符設(shè)置為 //，因為 MySQL 默認語句結(jié)束符號為分號”;”，這樣做是為了避免與存儲過程中 SQL 語句結(jié)束符相沖突。存儲過程定義完之后再使用 delimiter；“恢復(fù)默認結(jié)束符。使用 delimiter 命令時，應(yīng)避免使用反斜杠”，因為反斜杠是 MySQL 中的轉(zhuǎn)義字符。

【例 2】創(chuàng)建名稱為 CountProc 的存儲過程，代碼如下：

create procedure CountProc (OUT paraml int)beginselect count(*) into paraml from fruits;end;

上述代碼創(chuàng)建了一個獲取 fruits 表記錄條數(shù)的存儲過程, 名稱是 CountProc，count(*)計算后把結(jié)果放入?yún)?shù) paraml 中。代碼的執(zhí)行結(jié)果如下：

mysql  delimiter //mysql  create procedure CountProc(OUT paraml int )
 -  begin
 -  select count(*) into paraml from fruits;
 -  end //Query OK, 0 rows affected (0.08 sec)mysql  delimiter ;

(2)創(chuàng)建存儲函數(shù)

創(chuàng)建存儲函數(shù)，需要使用 create function 語句，基本語法如下：

create function func_name ( [ func_parameter] )
returns type
[characteristic ...] routine_body

create function 為用來創(chuàng)建存儲函數(shù)的關(guān)鍵字

func_name 表示存儲函數(shù)的名稱

func_parameter 為存儲過程的參數(shù)列表，參數(shù)列表形式為：[in | out | inout] param_name type

其中，in 表示輸入?yún)?shù)，out 表示輸出參數(shù)，inout 表示既可以輸入也可以輸出 param_name 表示參數(shù)名稱，type 表示參數(shù)的類型；returns type 語句表示函數(shù)返回數(shù)據(jù)的類型；characteristic 指定存儲函數(shù)的特性，取值與創(chuàng)建存儲過程時相同。

【例 3】創(chuàng)建存儲函數(shù)，名稱為 NameByZip，該函數(shù)返回 select 語句的查詢結(jié)果，數(shù)值類型為字符串型，代碼如下：

create function NameByZip()returns char( 50)return(select s_name from suppliers where s_call = 48075

代碼的執(zhí)行結(jié)果如下;

mysql  delimiter //mysql  create function NameByZip()
 -  returns char(50)
 -  return (select s_name from suppliers where s_call =  48075 
 -  //Query OK, 0 rows affected (0.06 sec)mysql  delimiter;

如果在存儲函數(shù)中的 ruturn 語句返回一個類型不同于函數(shù)的 returns 子句中指定類型的值，返回值將被強制為恰當(dāng)?shù)念愋汀?/p>

注意：指定參數(shù)為 in、out 或 inout 只對 procedure 是合法的。(function 中總是默認為 in 參數(shù)。)returns 子句只能對 function 做指定，對函數(shù)而言是強制的。它用來指定函數(shù)的返回類型，而且函數(shù)體必須包含一個 return value 語句。

(3)變量的使用

變量可以在子程序中聲明并使用，這些變量的作用范圍是在 begin…end 程序中的。

1. 定義變量

在存儲過程中使用 declar 語句定義變量，語法格式如下：

declare var_name[,varname]... date_type [default value];

var_name 為局部變量的名稱。default value 子句給變量提供一個默認值。值除了可以被聲明為一個常數(shù)之外，還可以被指定為一個表達式。如果沒有 default 子句，初始值為 null。

【例 4】定義名稱為 myparam 的變量，類型為 int 類型，默認值為 100，代碼如下：

declare myparam int default 100;

2. 為變量賦值

set var_name = expr [,var_name = expr]...;

存儲程序中的 set 語句是一般 set 語句的擴展版本。被參考變量可能是子程序內(nèi)聲明的變量，或者是全局服務(wù)器變量，如系統(tǒng)變量或者用戶變量。

【例 5】聲明 3 個變量，分別為 var1，var2 和 var3，數(shù)據(jù)類型為 int，使用 set 為變量賦值，代碼如下：

declare var1，var2，var3 int;set var1 = 10, var2 =20;set var3 = var1 + var2;

MySQL 中還可以通過 select…into 為一個或多個變量賦值，語句如下：

select col_name[,...] into var_name[,...] table_expr;

這個 select 語法把選定的列直接存儲到對應(yīng)位置的變量。col_name 表示字段名稱;var_name 表示定義的變量名稱；table_expr 表示查詢條件表達式，包括表名稱和 where 子句。

【例 6】聲明變量 fruitname 和 fruitprice，通過 select…into 語句查詢指定記錄并為變量賦值, 代碼如下:

declare fruitname char(50);declare fruitprice decimal(8,2);select f_name,f_price into fruitname,fruitpricefrom fruits where f_id=

(4)定義條件和處理程序

特定條件需要特定處理。定義條件是事先定義程序執(zhí)行過程中遇到的問題，處理程序定義了在遇到這些問題時應(yīng)當(dāng)采取的處理方式，并且保證存儲過程或函數(shù)在遇到警告或錯誤時能繼續(xù)執(zhí)行。這樣就增強了存儲程序處理問題的能力，避免程序異常停止運行。

1. 定義條件
定義條件使用 declare 語句，語法格式如下：

declare conditon_name Condition for [condition_type][condition_type];SQLSTATE [value] sqlstate_value | mysql_error_code

condition_name 表示條件的名稱

condition_type 表示條件的類型

sqlstate_value 和 mysql_error_code 都可以表示 MySQL 的錯誤

sqlstate_value 為長度為 5 的字符類型錯誤代碼

mysql_error_code 為數(shù)值類型錯誤代碼

例如：ERROR1142(42000)中，sqlstate_value 的值是 42000，mysql_error_code 的值為 1142。

這個語句指定需要特殊處理的條件。它將一個名字和指定的錯誤條件關(guān)聯(lián)起來。這個名字可以隨后被用在定義處理程序的 declare handler 語句中。

【例 7】定義 error 1148(42000) 錯誤，名稱為 command_not_allowed。可以用兩種不同的方法來定義，代碼如下：

[方法一]：使用 sqlstate_valuedeclare command_not_allowed condition for sqlstate  42000 []方法二]: 使用 mysql_error_codedeclare command_not_allowed condition for 1148

2. 定義處理程序

定義處理程序時，使用 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 為錯誤處理方式，參數(shù)取 3 個值：continue、exit 和 undo。

continue 表示遇到錯誤不處理，繼續(xù)執(zhí)行；

exit 遇到錯誤馬上退出；

undo 表示遇到錯誤后撤回之前的操作，MySQL 中暫時不支持這樣的操作。

condition_value 表示錯誤類型，可以有以下取值：

sqlstate[value] sqlstate_value 包含 5 個字符串錯誤值

condition_name 表示 declare condition 定義的錯誤條件名稱

sqlwarning 匹配所有以 01 開頭的 sqlstate 錯誤代碼

notfound 匹配所有以 02 開頭的 sqlstate 錯誤代碼

sqlexception 匹配所有沒有被 sqlwarning 或 not found 捕獲的 sqlstate 錯誤代碼

mysql_error_code 匹配數(shù)值類型錯誤代碼

sp_statement 參數(shù)為程序語句段，表示在遇到定義的錯誤時，需要執(zhí)行的存儲過程或函數(shù)。

【例 8】定義處理程序的幾種方式如下：

方法 1：捕獲 sqlstate_valuedeclare continue handler for sqlstate  42S02  set @info= No_SUCH_TABLE 方法 2: 捕獲 mysql_error_codedeclare continue handler for 1146 set @info= No_SUCH_TABLE 方法 3: 先定義條件, 然后調(diào)用 declare no_such_table condition for 1146;declare continue handler for NO_SUCH_TABLE SET @info= NO_SUCH_TABLE 方法 4: 使用 sqlwarningdeclare exit handler for sqlwarning set @info= ERROR 方法 5: 使用 not founddeclare exit handler for not found set @info=  NO_SUCH_TABLE  方法 6: 使用 sqlexceptiondeclare exit handler forsqlexception set @info= ERROR

上述代碼是 6 種定義處理程序的方法。

第一種，捕獲 sqlstate_value 值。如果遇到 sqlstate_value 值為 42S02，執(zhí)行 continue 操作，并且輸出 NO_SUCH_TABLE 信息。
第二種，捕獲 mysql_error_code 值。如果遇到 mysql_error_code 值為 1146，就執(zhí)行 continue 操作，并且輸出 NO_SUCH_TABLE 信息。
第三種，先定義條件再調(diào)用條件。這里先定義 no_such_table 條件，遇到 1146 錯誤就執(zhí)行 continue 操作。
第四種，使用 sqlwarning。sqlwarning 捕獲所有以 01 開頭的 sqlstate_value 值，然后執(zhí)行 exit 操作，并且輸出 ERROE 信息。
第五種，使用 not found。not found 捕獲所有以 02 開頭的 sqlstate_value 值，然后執(zhí)行 exit 操作，并且輸出 NO_SUCH_TABLE 信息。
第六種，使用 SQLEXCEPTION。sqlexception 捕獲所有沒有被 sqlwarning 或 not found 捕獲的 sqlstate_value 值，然后執(zhí)行 exit 操作，并且輸出 ERROR 信息。

【例 9】定義條件和處理程序，具體執(zhí)行的過程如下：

mysql  create table test.t(s1 int,primary key (s1));Query OK, 0 rows affected (0.14 sec)mysql  delimiter //mysql  create procedure handlerdemo()
 -  begin
 -  declare continue handler for sqlstate  23000  set @x2=1;
 -  set @x =1;
 -  insert into test.t values(1);
 -  set @x=2;
 -  insert into test.t values(1);
 -  set @x=3;
 -  end;
 -  //Query OK, 0 rows affected (0.06 sec)[調(diào)用存儲過程]mysql  delimiter ;mysql  call handlerdemo();Query OK, 0 rows affected (0.08 sec)[查看調(diào)用過程結(jié)果]mysql  select @x;+------+| @x |+------+| 3 |+------+1 row in set (0.00 sec)

可以看到，@x 是一個用戶變量，執(zhí)行結(jié)果 @x 等于 3，這表明 MySQL 被執(zhí)行到程序末尾。

var_name 表示用戶變量，使用 set 語句為其賦值。用戶變量與連接有關(guān)，一個客戶端定義的變量不能被其他客戶端看到或使用。當(dāng)客戶端退出時，該客戶端連接的所有變量將自動釋放。

(5)光標的使用

MySQL 中光標只能在存儲過程和函數(shù)中使用。

查詢語句可能返回多條記錄，如果數(shù)據(jù)量非常大，需要在存儲過程和存儲函數(shù)中使用光標來逐條讀取查詢結(jié)果集中的記錄。光標必須在聲明處理程序之前被聲明，并且變量和條件還必須在聲明光標或處理程序之前被聲明。

1. 聲明光標

MySQL 中使用 declare 關(guān)鍵字來聲明光標，語法形式如下：

declare cursor_name cursor for select_statement

其中，cursor_name 參數(shù)表示光標的名稱；select_statement 表示 select 語句的內(nèi)容，返回一個用于創(chuàng)建光標的結(jié)果集。

【例 10】聲明名稱為 cursor_fruit 的光標，代碼如下：

declare cursor_fruit cursor for select f_name,f_price from fruits;

該代碼中光標名稱為 cursor_fruit，select 語句部分從 fruits 表匯總查詢出 f_name 和 f_price 字段的值。

2. 打開光標

open cursor_name{光標名稱}

這個語句打開先前聲明的名稱為 cursor_name 的光標。

【例 11】打開名稱為 cursor_fruit 的光標，代碼如下：

open cursor_fruit ;

3. 使用光標

使用光標的語法格式:

fetch cursor_name into var_name [，var_name ] ... {參數(shù)名稱}

其中，cursor_name 參數(shù)表示光標的名稱；var_name 表示將光標中的 select 語句查詢出來的信息存入該參數(shù)中，var_name 必須在聲明光標之前就定義好。

【例 12】使用名稱為 cursor_fruit 的光標。將查詢出來的數(shù)據(jù)存入 fruit_name 和 fruit_price 兩個變量中，代碼如下:

fetch cursor_fruit into fruit_name,fruit_price;

4. 關(guān)閉光標
關(guān)閉光標的語法格式:

close cursor_name(光標名稱)

這個語句關(guān)閉先前打開的光標。
如果未被明確地關(guān)閉，那么光標將在它被聲明的復(fù)合語句的末尾被關(guān)閉。

【例 13】關(guān)閉名稱為 cursor_fruit 的光標，代碼如下：

close cursor_fruit;

(6)流程控制的使用

流程控制語句用來根據(jù)條件控制語句的執(zhí)行。MySQL 中用來構(gòu)造控制流程的語句有 IF 語句、case 語句、loop 語句、leave 語句、iterate 語句、repeat 語句和 while 語句。每個流程中可能包含一個單獨語句，或者是使用 begin…end 構(gòu)造的符合語句，構(gòu)造可以被嵌套。

1.if 語句

if 語句包含多個條件判斷，根據(jù)判斷的結(jié)果為 true 或 false 執(zhí)行相應(yīng)的語句，語法格式如下：

if expr_condition then statement_list [elseif expr_condition then statement_list]...
 [else statement_list]end if

如果 expr_condition 求值為真，相應(yīng)的 SQL 語句列表被執(zhí)行；如果沒有 expr_condition 匹配，則 else 子句里的語句列表被執(zhí)行。statement_list 列表可包括一個或多個語句。

MySQL 中還有一個 if()函數(shù)，它不同于這里描述的 if 語句。

【例 14】if 語句示例

if val is null
 then select ‘val is null’;
 else select  val is not null end if

該示例判斷 val 值是否為空，如果為空輸出字符串 val is null；否則輸出字符串 val is not null。if 語句都需要使用 end if 來結(jié)束。

2.case 語句

case 是另一個進行條件判斷的語句，有兩種語句格式，第一種：

case case_expr when when_value then statement_list [when when_value then statement_list]...
 [else statement_list]end case

case_expr 表示條件判斷的表達式，決定了哪一個 when 語句會被執(zhí)行

when_value 表示表達式可能的值。

如果某個 when_value 表達式和 case_expr 表達式結(jié)果相同，則執(zhí)行對應(yīng)的 then 關(guān)鍵字后面的 statement_list 中的語句。

statement_list 表示不同 when_value 值的執(zhí)行語句。

【例 15】使用 case 流程控制語句的第 1 種格式，判斷 val 值等于 1、等于 2 或者兩者都不等，SQL 語句如下：

case val when 1 then select ‘val is 1’；when 2 then select ‘val is 2’；else select ‘val is not 1 or 2’；end case;

當(dāng) val 值為 1 時，輸出字符串 val is 1 當(dāng) val 值為 2 時，輸出字符串 val is 2 否則輸出字符串 val is not 1 or 2。

case 語句的第 2 種格式如下：

case
 when expr_condition then statement_list [when expr_condition then statement_list]
 [else statement_list]end case

expr_condition 表示條件判斷語句

statement_list 表示不同條件的執(zhí)行語句

該語句中，when 語句將被逐個執(zhí)行，直到某個 expr_condition 表達式為真，則執(zhí)行對應(yīng) then 關(guān)鍵字后面的 statement_list 語句。如果沒有條件匹配，else 子句里的語句被執(zhí)行。

注意：存儲程序中的 case 語句和 case 控制流程函數(shù)的區(qū)別：
存儲程序中的 case 語句不能有 else null 子句，并且用 end case 替代 end 來終止。

【例 16】使用 case 流程控制語句的第 2 種格式，判斷 val 是否為空、小于 0、大于 0 或者等于 0，SQL 語句如下：

case
 when val is null then select ‘val is null’;
 when val   0 then select  val is less than 0 
 when val   0 then select  val is greater than 0 
 else select  val is 0 end case;

當(dāng) val 值為空時，輸出字符串 val is null 當(dāng) val 值小于 0 時，輸出字符串 val is less than 0 當(dāng) val 值大于 0 時，輸出字符串 val is greater than 0 否則輸出字符串 val is 0。

3.loop 語句

loop 循環(huán)語句用來重復(fù)執(zhí)行某些語句，與 if 和 case 語句相比，loop 只是創(chuàng)建一個循環(huán)操作過的過程，并不進行條件判斷。退出循環(huán)過程使用 leave 子句。loop 語法格式如下:

[loop_label:] loop
 statement_listend loop [loop_label]

loop_label 表示 loop 語句的標注名稱，該參數(shù)可省略。statement_list 參數(shù)表示需要循環(huán)執(zhí)行的語句。

【例 17】使用 loop 語句進行循環(huán)操作，id 值小于等于 10 之前，將重復(fù)執(zhí)行循環(huán)過程，SQL 語句如下：

declare id int default 10add_loop:loopset id = id +1;
 if  =10 then leave add_loop;
 end if;end loop add_ loop;

該示例循環(huán)執(zhí)行 id 加 1 的操作。當(dāng) id 值小于 10 時，循環(huán)重復(fù)執(zhí)行。當(dāng) id 值大于或等于 10 時，使用 leave 語句退出循環(huán)。loop 循環(huán)都以 end loop 結(jié)束。

4.leave 語句

leave 語句用來退出任何被標注的流程控制構(gòu)造，leave 語句基本格式如下：

leave label

其中，label 參數(shù)表示循環(huán)的標志。leave 和 begin…end 或循環(huán)一起被使用。

【例 18】使用 leave 語句退出循環(huán)，代碼如下：

add_num:loopset @count=@count+1;if @count=50 then leave add_num;end loop add_num;

該示例循環(huán)執(zhí)行 count 加 1 的操作，當(dāng) count 的值等于 50 時，使用 leave 語句跳出循環(huán)。

5.iterate 語句

iterater label 語句將執(zhí)行順序轉(zhuǎn)到語句段開頭處，語法格式如下：

iterate label

iterate 只可以出現(xiàn)在 loop、repeat 和 while 語句內(nèi)。iterate 的意思為 再次循環(huán)，label 參數(shù)表示循環(huán)的標志。iterate 語句必須跟在循環(huán)標志前面。

【例 19】iterate 語句示例：

create procedure doiterate()begin
 declare p1 int default 0;
 declare p1 int default 0;
 my_loop:loop;
 set p1 = p1 + 1;
 if p1   10 then iterate my_loop;
 elseif p1   20 then leave my_loop;
 end if;
 select  p1 is between 10 and 20 end loop my_loop;end

首先定義 p1=0，當(dāng) p1 的值小于 10 時重復(fù)執(zhí)行 p1 加 1 操作；當(dāng) p1 大于等于 10 并且小于等于 20 時，打印消息 p1 is between 10 and 20 當(dāng) p1 大于 20 時，退出循環(huán)。

6.repeat 語句

repeat 語句創(chuàng)建一個帶條件判斷的循環(huán)過程，每次語句執(zhí)行完畢之后，會對條件表達式進行判斷，若表達式為真，則循環(huán)結(jié)束；否則重復(fù)執(zhí)行循環(huán)中的語句。repeat 語句的語法格式如下：

[repeat_label:] repeat
 statement_list
until expr_conditionend repeat [repeat_label]

repeat_label 為 repeat 語句的標注名稱，該參數(shù)可以省略；repeat 語句內(nèi)的語句或語句群被重復(fù)，直至 expr_condition 為真。

【例 20】repeat 語句示例，id 值等于 10 之前，將重復(fù)執(zhí)行循環(huán)過程，代碼如下：

declare id int default 0；repeatset id = id + 1；until id  = 10end repeat;

該示例循環(huán)執(zhí)行 id 加 1 的操作。當(dāng) id 值小于 10 時，循環(huán)重復(fù)執(zhí)行；當(dāng) id 值大于或者等于 10 時，退出循環(huán)。repeat 循環(huán)都以 end repeat 結(jié)束。

7.while 語句

while 語句創(chuàng)建一個帶條件判斷的循環(huán)過程，與 repeat 不同，while 在執(zhí)行語句執(zhí)行時，先對指定的表達式進行判斷，如果為真，就執(zhí)行循環(huán)內(nèi)的語句，否則退出循環(huán)。while 語句的基本格式如下：

[while_label:] while expr_condition do
 statement_listend while [while_label]

while_label 為 while 語句的標注名稱

expr_condition 為進行判斷的表達式，如果表達式結(jié)果為真，while 語句內(nèi)的語句或語句群被執(zhí)行，直至 expr_condition 為假，退出循環(huán)。

【例 21】while 語句示例，i 值小于 10 時，將重復(fù)執(zhí)行循環(huán)過程，代碼如下：

declare i int default 0；while i  10 doset i = i + 1;end while;

以上是“MySQL 創(chuàng)建存儲程序的方法”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！

向 AI 問一下細節(jié)