詳解MySQL建立儲存程式(預存程序和函數)

簡單來說，預存程序就是一條或多條SQL語句的組合，可視為批次文件，但其作用又不限於批次。

(1)建立預存程序
(2)建立儲存函數
(3)變數的使用
(4)定義條件與處理程序
(5)遊標的使用
(6)流程控制的使用

（免費學習推薦：mysql影片教學##）

---

(1)建立預存程序

建立預存程序需要使用

create procedure 語句，基本語法格式如下：

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

create procedure 為用來建立儲存函數的關鍵字；sp_name為預存程序的名稱；proc_parameter為預存程序的參數列表，列表形式如下：

[in | out | inout] param_name type

in表示輸入參數
• #out表示輸出參數
• inout表示既可以輸入也可以輸出
• param_name表示參數名稱；type表示參數的類型

characteristics指定預存程序的特徵，有以下取值：

• language SQL ： 說明routine_body部分是由SQL語句組成的，目前系統支援的語言為SQL，SQL是language特性的唯一值。

• [not] deterministic: 指明儲存程序執行的結果是否正確。 deterministic表示每次執行預存程序時，相同的輸入會得到相同的輸出；而not deterministic表示相同的輸入可能會得到不同的輸出。預設為not deterministic。

• {contains SQL | no SQL | reads SQL date | modifies SQL date } ：指明子程式使用SQL語句的限制。 contains SQL表示子程式包含SQL語句；no SQL表示子程式不包含SQ；reads SQL data 顯示子程式包含讀取資料的語句；modifies SQL data表示子程式包含寫資料的語句。預設為contatins SQL。

• SQL security {definer | invoker}：指明誰有權限執行。 definer表示只有定義者才能執行；invoker表示擁有權限的呼叫者可以執行。預設為definer。

• comment ‘string’：註解訊息，可以用來描述預存程序或函數。

routine_body是SQL程式碼的內容，可以用begin…end來表示SQL程式碼的開始和結束。

【例1】建立查看fruits表的預存過程，程式碼語句如下：

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

這個程式碼建立了一個查看fruits表的預存程序，程式碼執行過程如下:

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

提示:“delimiter //“語句的作用是將MySQL的結束符號設為//，因為MySQL預設語句結束符號為分號”;”，這樣做是為了避免與預存程序中SQL語句結束符相衝突。預存程序定義完後再使用"delimiter ；「恢復預設結束符。使用delimiter指令時，應避免使用反斜線”"，因為反斜線是MySQL中的轉義字元。

【例2】建立名稱為CountProc的儲存過程，程式碼如下：

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

上述程式碼建立了一個取得fruits表記錄條數的儲存程序,名稱是CountProc， count(*)計算後把結果放入參數paraml中。程式碼的執行結果如下：

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)建立儲存函數

建立儲存函數，需要使用create function語句，基本語法如下：

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

#create function為用來建立儲存函數的關鍵字
• func_name表示儲存函數的名稱
• func_parameter為預存程序的參數列表，參數列表形式為：
• [in | out | inout ] param_name type

其中，in表示輸入參數，out表示輸出參數，inout表示既可以輸入也可以輸出param_name表示參數名稱，type表示參數的型別；returns type語句表示函數傳回資料的類型；characteristic指定儲存函數的特性，取值與建立預存程序時相同。

【範例3】建立儲存函數，名稱為NameByZip，函數傳回select語句的查詢結果，數值類型為字串型，程式碼如下：

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

程式碼的執行結果如下;

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;

如果在儲存函數中的ruturn語句傳回一個型別不同於函數的returns子句中指定型別的值，傳回值將會被強制為適當的型別。

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

(3)變數的使用

變數可以在子程式中宣告並使用，這些變數的作用範圍是在begin…end程式中的。

1.定義變數

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

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

var_name為局部變數的名稱。 default value子句給變數提供一個預設值。值除了可以被宣告為一個常數之外，還可以被指定為一個表達式。如果沒有default子句，初始值為null。

【例4】定义名称为myparam的变量，类型为int类型，默认值为100，代码如下：

declare myparam int default 100;

2.为变量赋值

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

存储程序中的set语句是一般set语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。

【例5】声明3个变量，分别为var1，var2和var3，数据类型为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语法把选定的列直接存储到对应位置的变量。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='a1;

(4)定义条件和处理程序

特定条件需要特定处理。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样就增强了存储程序处理问题的能力，避免程序异常停止运行。

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为数值类型错误代码

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

这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的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为错误处理方式，参数取3个值：continue、exit和undo。
• continue表示遇到错误不处理，继续执行；
• 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匹配数值类型错误代码

sp_statement参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数。

【例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:先定义条件,然后调用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"，执行continue操作，并且输出"NO_SUCH_TABLE"信息。
第二种，捕获mysql_error_code值。如果遇到mysql_error_code值为1146，就执行continue操作，并且输出"NO_SUCH_TABLE"信息。
第三种，先定义条件再调用条件。这里先定义no_such_table条件，遇到1146错误就执行continue操作。
第四种，使用sqlwarning。sqlwarning捕获所有以01开头的sqlstate_value值，然后执行exit操作，并且输出"ERROE"信息。
第五种，使用not found。not found捕获所有以02开头的sqlstate_value值，然后执行exit操作，并且输出"NO_SUCH_TABLE"信息。
第六种，使用SQLEXCEPTION。sqlexception捕获所有没有被sqlwarning或not found捕获的sqlstate_value值，然后执行exit操作，并且输出"ERROR"信息。

【例9】定义条件和处理程序，具体执行的过程如下：

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)[调用存储过程]mysql> delimiter ;mysql> call handlerdemo();Query OK, 0 rows affected (0.08 sec)[查看调用过程结果]mysql> select @x;+------+| @x   |+------+|    3 |+------+1 row in set (0.00 sec)

可以看到，@x 是一个用户变量，执行结果@x等于3，这表明MySQL被执行到程序末尾。

• "var_name"表示用户变量，使用set语句为其赋值。用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

(5)光标的使用

MySQL中光标只能在存储过程和函数中使用。

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

1.声明光标

MySQL中使用declare关键字来声明光标，语法形式如下：

declare cursor_name cursor for select_statement

其中，cursor_name参数表示光标的名称；select_statement表示select语句的内容，返回一个用于创建光标的结果集。

【例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 ] ... {参数名称}

其中，cursor_name参数表示光标的名称；var_name表示将光标中的select语句查询出来的信息存入该参数中，var_name必须在声明光标之前就定义好。

【例12】使用名称为cursor_fruit的光标。将查询出来的数据存入fruit_name和fruit_price两个变量中，代码如下:

fetch cursor_fruit into fruit_name,fruit_price;

4.关闭光标
关闭光标的语法格式:

close cursor_name(光标名称)

这个语句关闭先前打开的光标。
如果未被明确地关闭，那么光标将在它被声明的复合语句的末尾被关闭。

【例13】关闭名称为cursor_fruit的光标，代码如下：

close cursor_fruit;

(6)流程控制的使用

流程控制语句用来根据条件控制语句的执行。MySQL中用来构造控制流程的语句有IF语句、case语句、loop语句、leave语句、iterate语句、repeat语句和while语句。每个流程中可能包含一个单独语句，或者是使用begin…end构造的符合语句，构造可以被嵌套。

1.if语句

if语句包含多个条件判断，根据判断的结果为true或false执行相应的语句，语法格式如下：

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

如果expr_condition求值为真，相应的SQL语句列表被执行；如果没有expr_condition匹配，则else子句里的语句列表被执行。statement_list列表可包括一个或多个语句。

MySQL中还有一个if()函数，它不同于这里描述的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来结束。

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语句会被执行
• when_value表示表达式可能的值。
• 如果某个when_value表达式和case_expr表达式结果相同，则执行对应的then关键字后面的statement_list中的语句。
• statement_list表示不同when_value值的执行语句。

【例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;

当val值为1时，输出字符串"val is 1";当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表示不同条件的执行语句

该语句中，when语句将被逐个执行，直到某个expr_condition表达式为真，则执行对应then关键字后面的statement_list语句。如果没有条件匹配，else子句里的语句被执行。

注意：存储程序中的case语句和case控制流程函数的区别：
存储程序中的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 &#39;val is less than 0&#39;;
	when val > 0 then select 'val is greater than 0';
	else select 'val is 0';end case;

当val值为空时，输出字符串"val is null";当val值小于0时，输出字符串"val is less than 0";当val值大于0时，输出字符串"val is greater than 0";否则输出字符串"val is 0"。

3.loop语句

loop循环语句用来重复执行某些语句，与if和case语句相比，loop只是创建一个循环操作过的过程，并不进行条件判断。退出循环过程使用leave子句。loop语法格式如下:

[loop_label:] loop
	statement_listend loop [loop_label]

loop_label表示loop语句的标注名称，该参数可省略。statement_list参数表示需要循环执行的语句。

【例17】使用loop语句进行循环操作，id值小于等于10之前，将重复执行循环过程，SQL语句如下：

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

该示例循环执行id加1的操作。当id值小于10时，循环重复执行。当id值大于或等于10时，使用leave语句退出循环。loop循环都以end loop结束。

4.leave语句

leave语句用来退出任何被标注的流程控制构造，leave语句基本格式如下：

leave label

其中，label参数表示循环的标志。leave和begin…end或循环一起被使用。

【例18】使用leave语句退出循环，代码如下：

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

该示例循环执行count加1的操作，当count的值等于50时，使用leave语句跳出循环。

5.iterate语句

iterater label语句将执行顺序转到语句段开头处，语法格式如下：

iterate label

iterate只可以出现在loop、repeat和while语句内。iterate的意思为"再次循环"，label参数表示循环的标志。iterate语句必须跟在循环标志前面。

【例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，当p1的值小于10时重复执行p1加1操作；当p1大于等于10并且小于等于20时，打印消息"p1 is between 10 and 20";当p1大于20时，退出循环。

6.repeat语句

repeat语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，若表达式为真，则循环结束；否则重复执行循环中的语句。repeat语句的语法格式如下：

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

repeat_label为repeat语句的标注名称，该参数可以省略；repeat语句内的语句或语句群被重复，直至expr_condition为真。

【例20】repeat语句示例，id值等于10之前，将重复执行循环过程，代码如下：

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

该示例循环执行id加1的操作。当id值小于10时，循环重复执行；当id值大于或者等于10时，退出循环。repeat循环都以end repeat结束。

7.while语句

while语句创建一个带条件判断的循环过程，与repeat不同，while在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。while语句的基本格式如下：

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

• while_label为while语句的标注名称
• expr_condition为进行判断的表达式，如果表达式结果为真，while语句内的语句或语句群被执行，直至expr_condition为假，退出循环。

【例21】while语句示例，i值小于10时，将重复执行循环过程，代码如下：

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

相关免费学习推荐：mysql数据库(视频)

以上是詳解MySQL建立儲存程式(預存程序和函數)的詳細內容。更多資訊請關注PHP中文網其他相關文章！