存储

存储

关注
发信
关注(28)粉丝(399)

详解MySQL创建存储程序(存储过程和函数)

详解MySQL创建存储程序(存储过程和函数)-LMLPHP

简单地说，存储过程就是一条或者多条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 '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创建存储程序(存储过程和函数)的详细内容，更多请关注Work网其它相关文章！

09-18 04:39
Powered by LMLPHP ©2024 存储 0.002209