MySQL存储过程

MYSQL支持把几种对象存放在服务器供以后使用，这几种对象有一些可以根据情况通过程序代码调用，有一些会在数据表被修改时自动执行，还有一些可以在预定时刻自动执行，可以分为以下几种：

1. 存储函数。返回一个计算结果，该结果可以用在表达式里。
2. 存储过程。不直接返回结果，但可以用来完成一般的运算或是生成一个结果集并传递回客户。
3. 触发器。与数据表相关联，当那个数据表被写入、删除 、更新语句修改时，触发器将自动执行。
4. 事件。根据时间表在预定时刻自动执行。

我们着重介绍存储过程。

一，存储过程

存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL5.0终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。

二，创建存储过程

2.1 关于分隔符

在存储过程中使用复合语句时，有这样一个问题：复合语句块里的语句要以分号（；）彼此隔开，而分号同时也是mysql程序默认使用的语句分隔符，所以在使用mysql程序定义存储程序时会发生冲突。解决这个问题的办法是使用 delimiter 命令把mysql程序的语句分隔符重定义为另一个字符或者字符串，它必须是在存储过程中没有出现过的，在重定义之后，mysql程序就不会把分号解释为语句终止符了，它将把整个对象定义作为一条语句传递给服务器。

在定义完存储过程之后，再把mysql程序的语句终止符重新定义为分号。

2.2 创建格式

MYSQL存储过程创建的格式： CREATE PROCEDURE 过程名 (过程参数,[,.....]]) .

例如：

mysql> DELIMITER //

mysql> CREATE PROCEDURE proc1(OUT cnt int)

-> BEGIN

-> SELECT COUNT(*) INTO cnt FROM mysql.user;

-> END

-> //

mysql> DELIMITER ;

在这里注意这几个问题

1. 过程的开始与结束，分别用BEGIN和END（不分大小写）来标识。
2. 存储过程可能会有输入与输出参数，这里表示输出参数，用cnt来标识。

2.3 调用参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值。

OUT 输出参数:该值可在存储过程内部被改变，并可返回。

INOUT 输入输出参数:调用时指定，并且可被改变和返回。

1. IN参数定义。
   CREATE PROCEDURE proc_in_param(IN param_in int) ;
   如下列过程体
   
   -> BEGIN
   -> SELECT param_in; /*打印传递进过程体时参数的值*/
   -> SET p_in=102; /*修改参数的为102*/
   -> SELECT param_in; /*打印出修改之后的参数的值*/
   -> END;
   -> EOF
   
   传入的参数虽然在过程中被修改，但是参数不会影响外面的参数值。
   mysql > SET @param_in=1;
   mysql > call proc_in_param(@param_in);
   在执行完过程后，param_in参数的值还是1，可以用 select @param_in查看。
   
   
2. OUT参数定义
   
   CREATE PROCEDURE proc_out_param(OUT param_out int);
   OUT和IN参数不一样，在过程体里面，没有调用SETparam_out = ？之前，该值一直是NULL，就算是在过程外已经赋值了也是这个结果。如果在过程内被重新赋值，此时过程调用结束后，该赋值是可见的，就是调用过程结束后，如果用SELECT可以查看过程内赋的值。
   
   
3. INOUT参数定义
   
   CREATE PROCEDURE proc_inout_param(INOUT param_inout int);
   这个参数综合前面的IN和OUT参数，就是如果过程调用之前给参数赋值了，这个值过程体内是可见的。过程体内给参数赋值了的话，过程调用之后，这个值是可见的。

2.4 变量

• 变量的定义
  
  定义变量格式：
  
  DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
  
  其中，datatype为MySQL的数据类型，如:int, float, date, varchar(length)
  
  如:
  DECLARE var int unsigned default 0;
  DECLARE var number(5,3) DEFAULT 9.935;
  
• 变量赋值
  
  SET variable_name = expression[,variable_name = expression ...]；
  
  
• 用户变量
  
  用户变量名一般以@开头，如下所示:
  
  1）在MySQL客户端使用用户变量。
  mysql > SELECT 'info_db' into @var;
  mysql > SELECT @var;
  
  SET @var='info_db';
  SET @var=1+2+3;
  2) 在存储过程中使用用户变量
  mysql > CREATE PROCEDURE Hello( ) SELECT CONCAT(@greeting,' World');
  mysql > SET @greeting='Hello';
  mysql > call Hello( );
  

2.5 注释

可以使用两种风格注释 ：

1）双模杠：-- 该风格一般用于单行注释
2）c风格：/* 注释内容 */ 一般用于多行注释

三，存储过程的查询

我们可以用如下命令查看某个数据库下面的存储过程：

select name from mysql.proc where db=’数据库名’;
或者
select routine_name from information_schema.routines where routine_schema='数据库名';
或者
show procedure status where db='数据库名';

查看详细的存储过程：

SHOW CREATE PROCEDURE 数据库.存储过程名;

四，存储过程的修改 和删除

ALTER PROCEDURE 用于对存储过程的修改。

DROP PROCEDURE用于删除存储过程。

五，存储过程的控制语句

5.1 条件语句

-> if parameter=0 then
-> update t set s1=s1+1;

-> else

-> update t set s1=s1+2;

-> end if;

5.2 case 语句

case var
-> when 0 then
-> insert into t values(17);
-> when 1 then
-> insert into t values(18);
-> else
-> insert into t values(19);
-> end case;

5.3 循环语句

while var<6 do
-> insert into tbl_name values(var);
-> set var=var+1;
-> end while;

repeat
-> insert into tbl_name values(var);
-> set var=var+1;
-> until var>=5
-> end repeat;

-> LOOP_LABLE:loop
-> insert into tbl_name values(var);
-> set var=var+1;
-> if var >=5 then
-> leave LOOP_LABLE; // 另外有个ITERATE LOOP_LABLE，由表示重新进入循环
-> end if;

-> end loop;

六，存储过程的基本函数

(1).字符串类
CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格
(2).数学类
ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
注：返回类型并非均为整数，如：
(1)默认变为整形值
(2)可以设定小数位数，返回浮点型数据
SIGN (number2 ) //

(3).日期时间类
ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符号,正负或0
SQRT(number2) //开平方