1、创建存储过程
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数据库的任意数据类型
简单化定义：
create procedure 过程名(参数...) begin sql语句...end
调用：
call 过程名(实参)
注意：
在定义前，需要先把默认的语句结束’;’号改成其它，如’&&’,这样存储过程中定义的分号就不会被看成是语句结束(否则会直接被提交)。在定义完之后，还要把’;’还原成默认的结束符。
2、无参的存储过程
//建立存储过程mysql> delimiter %% mysql> create procedure p1() -> begin -> select sname,ssex from student; -> end %%mysql> delimiter ; //调用存储过程mysql> call p1(); +--------+------+| sname | ssex | +--------+------+| 赵英 | 女 | | 郑美丽 | 女 | | 李哲 | 男 | | 王洋 | 女 | | 王平 | 男 | | 赵东新 | 男 | | 王新 | 男 | | 陶丽平 | 男 | | 陈文 | 女 | | 蔡天 | 女 || 杨洋 | 男 | +--------+------+
3、有参的存储过程
//查询mysql> select * from employee; +------------+------------+------------+--------------+| employeeid | name | job | departmentid | +------------+------------+------------+--------------+| 6651 | ajay patel | programmer | 128 | | 7513 | nora edwar | programmer | 128 | | 9006 | candy burn | systems ad | 128 | | 9842 | ben smith | dba | 42 | | 9843 | pert park | dba | 42 | | 9845 | ben patel | dba | 128 | | 9846 | red right | null | 128 | | 9847 | run wild | null | 128 | | 9848 | rip this j | null | 128 | | 9849 | rip this j | null | 128 | | 9850 | reader u | null | 128 || 6651 | ajay patel | programmer | 128 | +------------+------------+------------+--------------+//建立存储过程mysql> delimiter && mysql> create procedure p4(in employeeid char(4),in name varchar(20),in job varchar(20),in departmentid int(11)) -> begin -> insert into employee value(employeeid,name,job,departmentid); -> select * from employee; -> end &&mysql> delimiter ; //调用存储过程mysql> call p4('7322','cid','udewhuuwdwq',127); +------------+------------+-------------+--------------+| employeeid | name | job | departmentid | +------------+------------+-------------+--------------+| 6651 | ajay patel | programmer | 128 | | 7513 | nora edwar | programmer | 128 | | 9006 | candy burn | systems ad | 128 | | 9842 | ben smith | dba | 42 | | 9843 | pert park | dba | 42 | | 9845 | ben patel | dba | 128 | | 9846 | red right | null | 128 | | 9847 | run wild | null | 128 | | 9848 | rip this j | null | 128 | | 9849 | rip this j | null | 128 | | 9850 | reader u | null | 128 | | 6651 | ajay patel | programmer | 128 || 7322 | cid | udewhuuwdwq | 127 | +------------+------------+-------------+--------------+
4、有返回值的存储过程
//建立存储过程mysql> delimiter && mysql> create procedure p9(in employeeid char(4),in name varchar(20),in job varchar(20),in departmentid int(11),out num int) -> begin -> insert into employee value(employeeid,name,job,departmentid); -> select count(*) into num from employee; -> end && mysql> delimiter ; //调用存储过程mysql> call p9('3632','dueh','xianggang',28,@num);//注意输出变量的输出mysql> select @num; +------+ | @num | +------+ | 15 | +------+//////////////////////////系统变量名称：@@变量名/// //用户变量名称：@变量名 ///
5、 变量的使用
在存储过程和函数中，可以定义和使用变量。用户可以使用declare关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是begin…end程序段中。本小节将讲解如何定义变量和为变量赋值。
1．定义变量
mysql中可以使用declare关键字来定义变量。定义变量的基本语法如下：
declare var_name[,...] type [default value]
其中， declare关键字是用来声明变量的；var_name参数是变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；default value子句将变量默认值设置为value，没有使用default子句时，默认值为null。
declare my_sql int default 10 ; //定义变量my_sql，数据类型为int型，默认值为10
2．为变量赋值
mysql中可以使用set关键字来为变量赋值。set语句的基本语法如下：
set var_name = expr[,var_name = expr] ...
其中，set关键字是用来为变量赋值的；var_name参数是变量的名称；expr参数是赋值表达式。一个set语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。
set my_sql = 30 ; //为变量my_sql赋值为30
mysql中还可以使用select…into语句为变量赋值。其基本语法如下：
select col_name[,…] into var_name[,…]
from table_name wehre condition
其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。
select d_id into my_sql from employee wehre id=2 ; //从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql
6、定义条件和处理程序
定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。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。
//定义"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 '42s02'set @info='can not find'; //方法二：捕获mysql_error_code declare continue handler for 1146 set @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';
第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42s02，执行continue操作，并且输出”cannot find”信息。
第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行continue操作，并且输出”can not find”信息。
第三种方法是先定义条件，然后再调用条件。这里先定义can_not_find条件，遇到1146错误就执行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”信息。
以上就是 【mysql 08】存储过程的内容。