MySQL 存储过程

存储过程是通过给定的语法格式编写自定义的数据库API，类似于给数据库编写可执行函数。

简介

存储过程是一组为了完成特定功能的SQL语句集合，是经过编译后存储在数据库中。

存储过程增强了SQL语言的功能和灵活性，它可以使用流控制语句编写来完成复杂的判断和计算。

存储过程是把完成特定功能的SQL语句集合统一在数据库中进行处理，避免了多次网络IO请求造成的网络负载。

1
2
3
4
5
6
mysql> DELIMITER //
mysql> CREATE PROCEDURE proc1(OUT s int)
        -> BEGIN
        -> SELECT COUNT(*) INTO s FROM person;
        -> END //
mysql> DELIMITER;

基本语法

基本结构

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER //
DROP PROCEDURE IF EXISTS some_func;
CREATE PROCEDURE some_func
(
    IN param1 INT,
    IN param2 VARCHAR(32),
    OUT res INT
)
BEGIN
    SQL-SCRIPT
END //
DELIMITER;

调用语句如下，

1
CALL some_func(params...);

变量

在存储过程中，函数参数包含三种变量，

  • IN 输入参数，必须在调用存储过程时指定
  • OUT 输出参数，可在存储过程内部被改变，返回该结果
  • INOUT 输入输出参数，调用时指定并且可被改变和返回

在存储过程内部，参数的定义如下，

1
DECLARE var_name [, var_name] var_type [default value];

用户变量

1
SET @mvar = 'Hello World';

变量赋值

1
SET var_name = 表达式;

条件语句

1
2
3
4
5
if var=0 then 
  insert into person values ('f');
else
  insert into person values ('ff');
end if;

case语句

1
2
3
4
5
6
7
8
case var
when 0 then
  insert into person values ('f');
when 1 then
  insert into person values ('ff');
else
  insert into person values ('fff');
end case;

循环语句

WHILE语句，同C语言中的while语句一样。

1
2
3
4
while var < N do
  insert into person values ('f');
  set var = var + 1;
end while;

REPEAT语句，类似于C语言中的do while语句。

1
2
3
4
5
repeat
  insert into person values ('f');
  set var = var + 1;
until var >= N
end repeat;

LOOP语句，没有结束的判断语句，利用leave来跳出循环，类似于break。

1
2
3
4
5
6
7
8
set @var = 0;
loop_name:loop
  set @var = @var + 1;
  if @var > 5 then
    leave loop_name;
  end if;
end loop loop_name;
select @var;

基本函数

mysql内置了一些函数，这些函数可以极大地提高编写存储过程的效率。

字符串操作如下，

1
2
3
4
5
6
7
8
9
10
11
12
13
CHARSET(str) //获取字符集
CONCAT(str1, str2, ...) //联接字符串
INSTR(str, substr) //返回substr出现在str中的第一个位置
LOCATE(substr, str, start_position) //返回substr在str的start_position开始第一次出现的位置
LCASE(str) //将所有字符转换为小写
LEFT(str, length) //返回str从左边开始的length个字符
LENGTH(str) //返回str长度
LOAD_FILE(file_name) //读取文见内容
LPAD(str, length, pad) //重复在str的首部插入pad，直到str的长度达到length
LTRIM(str) //去除str首部的空格
RTRIM(str) //去除str尾部的空格
STRCMP(str1, str2) //字符串比较
SUBSTRING(str, start_position, length) //截取字符串，默认第一个字符下标为1

math相关操作如下，

1
2
3
4
5
6
7
8
9
10
11
12
13
ABS(i) //绝对值
BIN(i) //十进制->二进制
CEILING(i) //向上取整
CONV(i, from, to) //进制转换
FLOOR(i) //向下取整
FORMAT(i, n) //保留小数位数
HEX(i) //转十六进制
LEAST(i0, i1, i2, ..) //求最小值
MOD(i, demoninator) //求余
POWER(I, POWER) //求指数
RAND([seed]) //随机数
ROUND(I, [, DECIMALS]) //四舍五入，decimals为保留小数位数
SQRT(number2) //开平方

时间相关操作如下，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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

参考

http://xdj651897373-126-com.iteye.com/blog/1819924
http://blog.tankywoo.com/2015/04/01/mysql-stored-procedure.html
http://stackoverflow.com/questions/8549619/mysql-dynamically-build-query-string-in-a-stored-procedure-based-on-logic