mysql 储存过程入门语法小结

看了几天Mysql存储过程总算有点头绪了,网络上资料很全,只是稍微有点散, 就此做个简单的了结, 希望能帮到和我一样的伙伴们吧, 储存过程是一组为了完成特定功能的SQL语句集(可以想象成编程上的函数)，经过编译之后存储在数据库中，当需要使用该SQL语句时用户只需要通过指定存储过程的名字并给定参数就可以调用执行它了，简而言之就是一组已经写好的命令，需要使用的时候拿出来用就可以了。

一、为毛要用储存过程?

存储过程是一个可编程的函数，它在数据库中创建并保存。它可以由SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。存储过程优点：

1）存储过程能实现较快的执行速度

如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。

2）存储过程允许标准组件是编程存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。

3）存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算

4）存储过程可被作为一种安全机制来充分利用
系统管理员通过执行某一存储过程的权限进行限制，能够实现相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全

5）存储过程能够减少网络流量

针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的事物语句被组织存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少了网络流量，降低了网络负载。

---

二、储存过程中输入输出参数

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","逗号分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:
1. IN : 参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
2. OUT : 该值可在存储过程内部被改变，并可返回
3. INOUT : 调用时指定，并且可被改变和返回

比如:

1. IN a int // 调用时，a可以赋值，但不能返回
2. OUT a int // 调用时，a无法赋值，但可以返回
3. INOUT a int // 调用时，a可以赋值，也可以返回

附上一个简单的示例代码辅助理解

# 比如 : 根据学号查总分
# 已有表结构 : score : { 学生编号 s_id string , 班级编号 c_id string , 成绩 score int}

DROP PROCEDURE IF EXISTS search;
CREATE PROCEDURE search(IN sid VARCHAR(255),OUT sum_score DOUBLE)
# search(IN 输入参数 输入参数类型  ,  OUT 输出参数 输出参数类型...)
BEGIN
    SELECT SUM(score) INTO sum_score FROM score WHERE s_id=sid; #INTO意思是把左边查询结果赋给右边的变量
END;



#调用
#out参数接收到后可以用select来查询储存过程结果
CALL search('03',@sum_score);  #把id为03传进去,定义一个@sum_score来接收输出参数
SELECT @sum_score AS SUMscore;  #使用select查看输出的结果

---

三、存储过程中的变量

用户变量： 以"@“开始，形式为”@变量名"。@设置的变量，只对当前用户使用的客户端全局生效
全局变量： 定义时，以如下两种形式出现，set GLOBAL 变量名 或者 set @@global.变量名，对所有客户端全局生效。不过只有具有super权限才可以设置全局变量
会话变量： 只对连接的客户端有效。
局部变量： 作用范围在begin到end语句块之间。在该语句块里设置的变量。declare就专门用于定义局部变量。而set语句可以设置不同类型的变量，包括会话变量和全局变量

1. 通俗理解术语之间的区别：
   用户定义的变量就叫用户变量。会话变量set[@]和全局变量set[@@/global]都是用户定义的变量。只是
   set[@]是对当前客户端生效 .
   set[@@/@@global]对所有客户端生效。即用户变量包括了会话变量和全局变量

2. 局部变量与用户变量的区别在于几点:
   2.1 : 用户变量是以"@"开头的。局部变量使用declare语句定义, 而且局部变量没有用@这个符号。
   2.2 : 作用范围 : 局部变量只在begin-end语句块之间有效。在begin-end语句块运行完之后，局部变量就消失了。

所以，最后它们之间的层次关系是：变量包括局部变量和用户变量。用户变量包括会话变量和全局变量。

---

四、MySQL存储函数-基本常用函数

1、字符串类
mysql储存过程函数-字符串函数：
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个字符,
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

2、数学类
mysql储存函数-数学函数：
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为小数位数]
注：返回类型并非均为整数，如下文：
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) //开平方

---

附上简单的示例代码辅助理解
创建一个批量插入数据的储存过程

# 已有表结构 : stu_info : { 学生编号 id int , 学生姓名 name string ,年龄 age string , 班级编号 class int }
# 创建一个procedure

drop procedure if exists insertt;        #如果已存在同名procedure先删除
create procedure insertt(n int)    #储存过程名字和参数n ,默认是IN
begin        #储存过程开始标记
set @a=0;        #定义一个用户变量a
set @e=10;       
while @a<n do      #定义循环函数的开始
    set @b=rand_string(6);    #函数体,执行对数据表的操作,调用其他功能函数等
    set @d=rand_sex(1);	    # rand_sex函数稍后定义
    insert into stu_info values(@a,@b,@d,@e);
    set @a=@a+1;
    set @e=@e+1;

    if @a%100>10        #可以定义条件函数,条件判断开始
        then set @e=@e+1;    #如果满足条件就执行then后面操作  
    else
        set @e=@e+2;  #如果不满足条件执行else操作
    end if;         #函数结束标记
end while;            #循环结束标记
end ;            #储存过程结束标记




#创建一个随机产生字符串的函数

set global log_bin_trust_function_creators=1; #开启函数功能,该值控制是否可以信任存储函数创建者如果设置为0（默认值），用户不得创建或修改存储函数
drop function if exists rand_string;    #如果函数已存在则先删除
create function rand_string(n int) returns varchar(255)  #创建函数并声明返回数据类型
begin        #函数开始标志begin
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    #定义局部变量使用declare,仅对当前函数有效
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i int DEFAULT 0;
    WHILE i<n DO    #定义循环函数
        set return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
        set i=i+1;
    end WHILE;    #循环函数结束
    return return_str;    #返回处理结果
END;    #函数结束标记end





#创建随机产生的字符串的函数,0或1随机出现

set global log_bin_trust_function_creators=1;
drop function if exists rand_sex;
create function rand_sex(n int)
returns varchar(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT '01';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i int DEFAULT 0;
    while i<n DO
        set return_str=concat(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*2),1));
        set i=i+1;
    end while ;
    return return_str;
end;


# 调用,插入2000条测试数据
call insertt(2000)

---

小白总结如有错误望指正
参考文档:
储存过程-原理、语法、函数详细说明
mysql储存过程变量的定义
mysql储存过程

关于储存过程的修改、删除可以参考存储过程的定义、修改和删除