MySQL 存储过程

jopen 8年前

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

简介

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

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

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

mysql> DELIMITER // mysql> CREATE PROCEDURE proc1(OUT s int) -> BEGIN -> SELECT COUNT(*)  INTO s FROM person; -> END // mysql> DELIMITER;

基本语法

基本结构 

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;

调用语句如下， 

CALL some_func(params...);

变量

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

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

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

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

用户变量 

SET @mvar = 'Hello World';

变量赋值 

SET var_name = 表达式;

条件语句 

if var=0 then insert into person values ('f'); else insert into person values ('ff'); end if;

case语句 

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语句一样。 

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

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

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

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

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内置了一些函数，这些函数可以极大地提高编写存储过程的效率。

字符串操作如下， 

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相关操作如下， 

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) //开平方

时间相关操作如下， 

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