转载

MySQL 存储过程

存储过程是通过给定的语法格式编写自定义的数据库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