MySQL创建存储程序的方法

共计 11688 个字符，预计需要花费 30 分钟才能阅读完成。

自动写代码机器人，免费开通

丸趣 TV 小编给大家分享一下 MySQL 创建存储程序的方法，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

简单地说，存储过程就是一条或者多条 SQL 语句的组合，可视为批文件，但是其作用又不仅限于批处理。

(1)创建存储过程
(2) 创建存储函数
(3) 变量的使用
(4) 定义条件和处理程序
(5) 光标的使用
(6) 流程控制的使用

(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=

(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  val is less than 0 
 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 创建存储程序的方法”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！

向 AI 问一下细节