MySQL 存储过程

2023-02-22 12:07:34

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

参考

http://xdj651897373-126-com.iteye.com/blog/1819924

http://blog.tankywoo.com/2015/04/01/mysql-stored-procedure.html

http://*.com/questions/8549619/mysql-dynamically-build-query-string-in-a-stored-procedure-based-on-logic

码农公寓

MySQL 存储过程

简介

基本语法

基本函数

参考

相关文章