MySql存储过程
MySql存储过程一、创建存储函数
CREATE PROCEDURE 存储过程名
(参数列表)
BEGIN
MySQL句代码块
END
详细形式
CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
说明
1、sp_name参数是存储函数的名称
2、func_parameter表示存储函数的参数列表
func_parameter中的每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。其形式如下：
[ IN | OUT | INOUT ] param_name type
其中
(1)、MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATE PROCEDURE([[IN |OUT |INOUT ]参数名数据类形...])
IN输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)
-> BEGIN
-> SELECT p_in;
-> SET p_in=2;
-> SELECT p_in;
-> END;
调用
mysql > SET @p_in=1; mysql > CALL demo_in_parameter(@p_in);
OUT输出参数:该值可在存储过程内部被改变，并可返回
mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)
-> BEGIN
-> SELECT p_out;
-> SET p_out=2;
-> SELECT p_out;
-> END;
INOUT输入输出参数:调用时指定，并且可被改变和返回
mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)
-> BEGIN
-> SELECT p_inout;
-> SET p_inout=2;
-> SELECT p_inout;
-> END;
(2)、param_name参数是存储过程的参数名称；
(3)、type参数指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型。
3、RETURNS type指定返回值的类型
4、characteristic参数指定存储函数的特性，该参数的取值与存储过程中的取值是一样的
characteristic参数有多个取值。其取值说明如下：
(1)、LANGUAGE SQL：说明routine_body部分是由SQL语言的语句组成，这也是数据库系统默认的语言。
(2)、[NOT] DETERMINISTIC：指明存储过程的执行结果是否是确定的。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是非确定的，相同的输入可能得到不同的输出。默认情况下，结果是非确定的。
(3)、{ 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。
(4)、SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。默认情况下，系统指定的权限是DEFINER。
(5)、COMMENT 'string'：注释信息。
5、routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。
二、MySql存储过程使用时注意事项
1、参数如果不显式指定“in”、“out”、“inout”，则默认为“in”。习惯上，对于是“in” 的参数，我们都不会显式指定。多个参数用','分割。
2、MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”。
3、MySQL 存储过程参数，不能在参数名称前加“@”，如：“@a int”。
4、 MySQL 存储过程中的变量，不需要在变量名字前加“@”，虽然 MySQL 客户端用户变量要加个“@”。
5、MySQL 存储过程的参数不能指定默认值。
6、MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过程必须加 “as” 关键字。
7、如果 MySQL 存储过程中包含多条 MySQL 语句，则需要 begin end 关键字。
8、不能在 MySQL 存储过程中使用 “return” 关键字。
9、调用 MySQL 存储过程时候，需要在过程名字后面加“()”，即使没有一个参数，也需要“()”
三、MySql存储过程中的变量
使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中。
1．定义变量
MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：
DECLARE var_name[,...] type [DEFAULT value]
说明
(1)、DECLARE关键字是用来声明变量的；
(2)、var_name参数是变量的名称，这里可以同时定义多个变量；
(3)、type参数用来指定变量的类型；
(4)、DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。
实例
DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric number(8,2) DEFAULT 9.95;
DECLARE l_date date DEFAULT '1999-12-31';
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
定义变量备注
(1)、这个语句被用来声明局部变量。
(2)、要给变量提供一个默认值，请包含一个DEFAULT子句。
(3)、值可以被指定为一个表达式，不需要为一个常数。
(4)、如果没有DEFAULT子句，初始值为NULL。
(5)、局部变量的作用范围在它被声明的BEGIN ... END块内。
(6)、它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。
2．为变量赋值
MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下：
SET var_name = expr [, var_name = expr] ...
说明
(1)、SET关键字是用来为变量赋值的；
(2)、var_name参数是变量的名称；
(3)、expr参数是赋值表达式。
例如
SET my_sql = 30 ;
变量赋值备注
(1)、一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。
(2)、MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下：
SELECT col_name[,…] INTO var_name[,…] FROM table_name WEHRE condition
其中
col_name参数表示查询的字段名称；
var_name参数是变量的名称；
table_name参数指表的名称；
condition参数指查询条件。
四、MySQL存储过程的控制语句
1、变量作用域
内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派
给会话变量来保存其值。
2、if-then -else语句
mysql > CREATE PROCEDURE proc2(IN parameter int)
-> begin
-> declare var int;
-> set