MYSQL存储过程，函数，光标

存储过程

　　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数据库的任意数据类型。

　　characteristic参数有多个取值。其取值说明如下：

　　language sql：说明routine_body部分是由sql语言的语句组成，这也是数据库系统默认的语言。

　　[not] deterministic：指明存储过程的执行结果是否是确定的。deterministic表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。not deterministic表示结果是非确定的，相同的输入可能得到不同的输出。默认情况下，结果是非确定的。

　　{ contains sql | no sql | reads sql data | modifies sql data }：指明子程序使用sql语句的限制。contains sql表示子程序包含sql语句，但不包含读或写数据的语句；no sql表示子程序中不包含sql语句；reads sql data表示子程序中包含读数据的语句；modifies sql data表示子程序中包含写数据的语句。默认情况下，系统会指定为contains sql。

　　sql security { definer | invoker }：指明谁有权限来执行。definer表示只有定义者自己才能够执行；invoker表示调用者可以执行。默认情况下，系统指定的权限是definer。

　　comment 'string'：注释信息。

　　技巧：创建存储过程时，系统默认指定contains sql，表示存储过程中使用了sql语句。但是，如果存储过程中没有使用sql语句，最好设置为no sql。而且，存储过程中最好在comment部分对存储过程进行简单的注释，以便以后在阅读存储过程的代码时更加方便。

【示例1】 下面创建一个名为num_from_employee的存储过程。代码如下：

create  procedure  num_from_employee (in emp_id int, out count_num int )  
          reads sql data  
          begin  
              select  count(*)  into  count_num  
              from  employee  
              where  d_id=emp_id ;  
          end 

　　上述代码中，存储过程名称为num_from_employee；输入变量为emp_id；输出变量为count_num。select语句从employee表查询d_id值等于emp_id的记录，并用count(*)计算d_id值相同的记录的条数，最后将计算结果存入count_num中。代码的执行结果如下：

mysql> delimiter &&  
mysql> create  procedure  num_from_employee
(in emp_id int, out count_num int )  
    -> reads sql data  
    -> begin  
    -> select  count(*)  into  count_num  
    -> from  employee  
    -> where  d_id=emp_id ;  
    -> end &&  
query ok, 0 rows affected (0.09 sec)  
mysql> delimiter ; 

　　代码执行完毕后，没有报出任何出错信息就表示存储函数已经创建成功。以后就可以调用这个存储过程，数据库中会执行存储过程中的sql语句。

　　说明：mysql中默认的语句结束符为分号（;）。存储过程中的sql语句需要分号来    结束。为了避免冲突，首先用"delimiter &&"将mysql的结束符设置为&&。最后再用"delimiter ;"来将结束符恢复成分号。这与创建触发器时是一样的。

函数

　　在mysql中，创建存储函数的基本形式如下：

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

　　其中，sp_name参数是存储函数的名称；func_parameter表示存储函数的参数列表；returns type指定返回值的类型；characteristic参数指定存储函数的特性，该参数的取值与存储过程中的取值是一样的，请读者参照14.1.1小节的内容；routine_body参数是sql代码的内容，可以用begin…end来标志sql代码的开始和结束。

　　func_parameter可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：param_name type

　　其中，param_name参数是存储函数的参数名称；type参数指定存储函数的参数类型，该类型可以是mysql数据库的任意数据类型。

【示例2】 下面创建一个名为name_from_employee的存储函数。代码如下：

create  function  name_from_employee (emp_id int )  
          returns varchar(20)  
          begin  
              return  (select  name  
              from  employee  
              where  num=emp_id );  
          end 

　　上述代码中，存储函数的名称为name_from_employee；该函数的参数为emp_id；返回值是varchar类型。select语句从employee表查询num值等于emp_id的记录，并将该记录的name字段的值返回。代码的执行结果如下：

mysql> delimiter &&  
mysql> create  function  name_from_employee (emp_id int )  
    -> returns varchar(20)  
    -> begin  
    -> return  (select  name  
    -> from  employee  
    -> where  num=emp_id );  
    -> end&&  
query ok, 0 rows affected (0.00 sec)  
mysql> delimiter ; 

结果显示，存储函数已经创建成功。该函数的使用和mysql内部函数的使用方法一样。

变量的使用

在存储过程和函数中，可以定义和使用变量。用户可以使用declare关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是begin…end程序段中。本小节将讲解如何定义变量和为变量赋值。

1．定义变量

　　mysql中可以使用declare关键字来定义变量。定义变量的基本语法如下：

declare  var_name[,...]  type  [default value] 

　　其中， declare关键字是用来声明变量的；var_name参数是变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；default value子句将变量默认值设置为value，没有使用default子句时，默认值为null。

【示例3】 下面定义变量my_sql，数据类型为int型，默认值为10。代码如下：

declare  my_sql  int  default 10 ; 

2．为变量赋值

　　mysql中可以使用set关键字来为变量赋值。set语句的基本语法如下：

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

　　其中，set关键字是用来为变量赋值的；var_name参数是变量的名称；expr参数是赋值表达式。一个set语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。

【示例4】 下面为变量my_sql赋值为30。代码如下：

set  my_sql = 30 ; 

　　mysql中还可以使用select…into语句为变量赋值。其基本语法如下：

select  col_name[,…]  into  var_name[,…] from  table_name  wehre  condition 

　　其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。

【示例5】 下面从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql。代码如下：

select  d_id  into  my_sql  from  employee  wehre  id=2 ; 

定义条件和处理程序

　　定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。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。

【示例6】 下面定义"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 '42000'
set @info='can not find'; 
//方法二：捕获mysql_error_code 
declare continue handler for 1148set @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';

上述代码是6种定义处理程序的方法。

第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42000，执行continue操作，并且输出"can not find"信息。

第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1148，执行continue操作，并且输出"can not find"信息。

第三种方法是先定义条件，然后再调用条件。这里先定义can_not_find条件，遇到1148错误就执行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"信息

 

定义条件和处理程序

 

create table t8(s1 int,primary key(s1))

delimiter //
create procedure handlerdemo()
begin
declare continue handler for sqlstate '23000' set @x2=1;
set @x=1;
insert into t8 values(1);
set @x=2;
insert into t8 values(1);
set @x=3;
end;
//
delimiter ;

/* 调用存储过程*/
call handlerdemo();

/* 查看调用存储过程结果*/
select @x

 

@x是一个用户变量，执行结果@x等于3，这表明mysql执行到程序的末尾。

如果declare continue handler for sqlstate '23000' set @x2=1;，这一行不存在

第二个insert因primary key约束而失败之后，mysql可能已经采取exit策略，并且select @x可能已经返回2

注意：@x表示用户变量，使用set语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其他客户端所使用

即有作用域的，该客户端退出时，客户端连接的所有变量将自动释放

 

这里的变量跟sqlserver没有什么区别，都是用来存储临时值的

mysql这里的条件和预定义程序其实跟sqlserver的自定义错误是一样的

 

光标

mysql里叫光标，sqlserver里叫游标，实际上一样的

 

查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。

光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。

1．声明光标

mysql中使用declare关键字来声明光标。其语法的基本形式如下：

declare cursor_name cursor for select_statement ; 

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

下面声明一个名为cur_employee的光标。代码如下：

declare cur_employee cursor for select name, age from employee ;

上面的示例中，光标的名称为cur_employee；select语句部分是从employee表中查询出name和age字段的值。

 

2．打开光标

mysql中使用open关键字来打开光标。其语法的基本形式如下：

open  cursor_name ; 

其中，cursor_name参数表示光标的名称。

下面打开一个名为cur_employee的光标，代码如下：

open  cur_employee ; 

 

3．使用光标

mysql中使用fetch关键字来使用光标。其语法的基本形式如下：

 

fetch cur_employee into var_name[,var_name…] ; 

 

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

下面使用一个名为cur_employee的光标。将查询出来的数据存入emp_name和emp_age这两个变量中，代码如下：

fetch  cur_employee into emp_name, emp_age ; 

上面的示例中，将光标cur_employee中select语句查询出来的信息存入emp_name和emp_age中。emp_name和emp_age必须在前面已经定义。

4．关闭光标

mysql中使用close关键字来关闭光标。其语法的基本形式如下：

 

close  cursor_name ; 

 

其中，cursor_name参数表示光标的名称。

【示例14-11】 下面关闭一个名为cur_employee的光标。代码如下：

close  cur_employee ; 

上面的示例中，关闭了这个名称为cur_employee的光标。关闭之后就不能使用fetch来使用光标了。

 

注意：mysql中，光标只能在存储过程和函数中使用！！

 

到目前为止存储函数，存储过程、变量、条件、预定义程序、光标跟sqlserver差不多，只不过语法不同，结构不同

刚开始的时候会有不适应

流程控制的使用

存储过程和函数中可以使用流程控制来控制语句的执行。

mysql中可以使用if语句、case语句、loop语句、leave语句、iterate语句、repeat语句和while语句来进行流程控制。

每个流程中可能包含一个单独语句，或者是使用begin...end构造的复合语句，构造可以被嵌套

1．if语句

if语句用来进行条件判断。根据是否满足条件，将执行不同的语句。其语法的基本形式如下：

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

其中，search_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。

注意：mysql还有一个if()函数，他不同于这里描述的if语句

下面是一个if语句的示例。代码如下：

if age>20 then set @count1=@count1+1;  
elseif age=20 then set @count2=@count2+1;  
else set @count3=@count3+1;  
end if; 

该示例根据age与20的大小关系来执行不同的set语句。

如果age值大于20，那么将count1的值加1；如果age值等于20，那么将count2的值加1；

其他情况将count3的值加1。if语句都需要使用end if来结束。

2．case语句

case语句也用来进行条件判断，其可以实现比if语句更复杂的条件判断。case语句的基本形式如下：

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

其中，case_value参数表示条件判断的变量；

when_value参数表示变量的取值；

statement_list参数表示不同when_value值的执行语句。

case语句还有另一种形式。该形式的语法如下：

case 
when search_condition then statement_list 
[when search_condition then statement_list] ... 
[else statement_list] 
end case 

其中，search_condition参数表示条件判断语句；

statement_list参数表示不同条件的执行语句。

下面是一个case语句的示例。代码如下：

case age 
when 20 then set @count1=@count1+1; 
else set @count2=@count2+1; 
end case ; 

代码也可以是下面的形式：

case 
when age=20 then set @count1=@count1+1; 
else set @count2=@count2+1; 
end case ; 

本示例中，如果age值为20，count1的值加1；否则count2的值加1。case语句都要使用end case结束。

注意：这里的case语句和“控制流程函数”里描述的sql case表达式的case语句有轻微不同。这里的case语句不能有else null子句

并且用end case替代end来终止！！

 

3．loop语句

loop语句可以使某些特定的语句重复执行，实现一个简单的循环。

但是loop语句本身没有停止循环的语句，必须是遇到leave语句等才能停止循环。

loop语句的语法的基本形式如下：

[begin_label:] loop 
statement_list 
end loop [end_label] 

其中，begin_label参数和end_label参数分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；

statement_list参数表示需要循环执行的语句。

下面是一个loop语句的示例。代码如下：

add_num: loop  
set @count=@count+1;  
end loop add_num ; 

该示例循环执行count加1的操作。因为没有跳出循环的语句，这个循环成了一个死循环。

loop循环都以end loop结束。

 

4．leave语句

leave语句主要用于跳出循环控制。其语法形式如下：

leave label 

其中，label参数表示循环的标志。

 

下面是一个leave语句的示例。代码如下：

add_num: loop 
set @count=@count+1; 
if @count=100 then 
leave add_num ; 
end loop add_num ; 

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

 

5．iterate语句

iterate语句也是用来跳出循环的语句。但是，iterate语句是跳出本次循环，然后直接进入下一次循环。

iterate语句只可以出现在loop、repeat、while语句内。

iterate语句的基本语法形式如下：

iterate label 

其中，label参数表示循环的标志。

下面是一个iterate语句的示例。代码如下：

add_num: loop 
set @count=@count+1; 
if @count=100 then 
leave add_num ; 
else if mod(@count,3)=0 then 
iterate add_num; 
select * from employee ; 
end loop add_num ; 

该示例循环执行count加1的操作，count值为100时结束循环。如果count的值能够整除3，则跳出本次循环，不再执行下面的select语句。

说明：leave语句和iterate语句都用来跳出循环语句，但两者的功能是不一样的。

leave语句是跳出整个循环，然后执行循环后面的程序。而iterate语句是跳出本次循环，然后进入下一次循环。

使用这两个语句时一定要区分清楚。

 

6．repeat语句

repeat语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。repeat语句的基本语法形式如下：

[begin_label:] repeat 
statement_list 
until search_condition 
end repeat [end_label] 

其中，statement_list参数表示循环的执行语句；search_condition参数表示结束循环的条件，满足该条件时循环结束。

下面是一个repeat语句的示例。代码如下：

repeat 
set @count=@count+1; 
until @count=100 
end repeat ; 

该示例循环执行count加1的操作，count值为100时结束循环。

repeat循环都用end repeat结束。

 

7．while语句

while语句也是有条件控制的循环语句。但while语句和repeat语句是不一样的。

while语句是当满足条件时，执行循环内的语句。

while语句的基本语法形式如下：

[begin_label:] while search_condition do 
statement_list 
end while [end_label] 

其中，search_condition参数表示循环执行的条件，满足该条件时循环执行；

statement_list参数表示循环的执行语句。

下面是一个iterate语句的示例。代码如下：

while @count<100 do 
set @count=@count+1; 
end while ; 

该示例循环执行count加1的操作，count值小于100时执行循环。

如果count值等于100了，则跳出循环。while循环需要使用end while来结束。

https://www.cnblogs.com/lyhabc/p/3793524.html