mysql自定义函数、存储过程、游标、触发器、事件(定时任务)

创建函数FUN_GET_ORG_CHILD(rootId)

CREATE  FUNCTION `FUN_GET_ORG_CHILD`(rootId varchar(100))

RETURNS varchar(2000) CHARSET utf8mb4//设置返回值类型
BEGIN   
DECLARE sTemp varchar(2000);  //定义局部变量
DECLARE sTempChd varchar(1000); //定义局部变量
SET sTemp = '$';   //变量赋值
SET sTempChd = rootId; //变量赋值 
WHILE sTempChd is not null and sTempChd <> '0' DO   //while循环
    SET sTemp = concat(sTemp, ',', sTempChd);   
    SELECT group_concat(ORG_ID) INTO sTempChd FROM T_SYS_ORG where FIND_IN_SET(PARENT_ID, sTempChd)>0;   
END WHILE;   
RETURN sTemp;   
END

此sql：SELECT * FROM T_SYS_ORG where FIND_IN_SET(ORG_ID, FUN_GET_ORG_CHILD(#{orgId}))  这样就能查询某个机构的本下级信息了。简单记录一下

扩展一：DEFINER

扩展二：select *  into   变量A  from  table_name     或者 select * from table_name into 变量A

              将查出的结果赋值给变量A

MySQL自定义函数：

mysql 自定义函数（user-defined function ） 简称UDF 是对mysql功能的一个扩展。

语法：    

DELIMITER $$
CREATE FUNCTION 函数名(变量名1 数据类型1,变量名2 数据类型2,...) RETURNS 返回值类型
BEGIN
变量的声明
sql语句
RETURN 值; 
END $$
DELIMITER;

/*
注意：
1、参数 包含两部分：参数名 参数类型
2、函数体：肯定会有return语句，如果没有会报错
     如果return语句没有放在函数体的最后也不报错，但不建议
3、函数体中仅有一句话，则可以省略begin end
4、使用 delimiter语句设置结束标记
*/

 简单示例：统计user表的用户数量

DELIMITER //
CREATE FUNCTION countUser() RETURNS INT
BEGIN
DECLARE total int default 0;
select count(*) into total from user;
return total;
END //
DELIMITER;

1、DELIMITER 定界符

      需要定义定界符，否则是创建不了函数的，因为mysql见到'分号'就认为执行结束了，只有开始

创建时定义分界符，结束时在配对一个分界符，mysql认为这个时候才结束，使得函数能够完整编译创建

附一个详细讲delimiter的博客：MySql中 delimiter 详解

2、变量的声明

                MYSQL中，变量分为三类:局部变量，用户变量、系统变量(会话变量，全局变量)

      局部变量：      

                    (存储过程中)通过DECLARE可以定义一个局部变量，default设置默认值。

                    declare声明变量必须放在最前面（可能是语法要求吧：declare声明的变量要放在begin...end块的第一行）

                    作用范围：BEGIN...END块中

                    语法：DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]

                    多个同类型的变量 可以分开或者一起声明   DECLARE total,total1,total2 VARCHAR(10) default null;

                    赋值：通过set或者 select * into 变量 from table 或者 select * from table into 变量

       用户变量：                  

                    用户变量不用事前申明，在用的时候直接用“@变量名”使用即可。

                    第一种用法：set @num=1; 或set @num:=1; //这里要使用set语句创建并初始化变量，直接使用@num变量

                    第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where …

                    注意上面两种赋值符号，使用set时可以用“=”或“:=”，但是使用select时必须用“:=赋值”

                    作用范围：在客户端链接到数据库实例整个过程中都是有效的。     

       系统变量

                  会话变量

                                仅对当前客户端连接有效，
                                使用set session varname=value;或者set @@session.varname=value;设置会话级别的系统变量
                                或者使用set local varname=value;或者set @@local.varname=value;设置会话级别的系统变量
                                其中，修改会话变量不需要root权限，且session可以省略，如：set varname=value;

                                查看所有的会话变量：
                                                                   SHOW [SESSION] VARIABLES;
                                查看一个会话变量也有如下三种方式：

                                                                    select @@var_name;

                                                             select @@session.var_name;

                                                             show session variables like “%var%”;

                全局变量

                                使用set global varname=value;或者set @@global.varname=value;设置全局系统变量的值
                                其中，修改全局变量时需要root权限，且global不可省略。

                                查看所有的全局变量：
                                                                  show global variables;
                                要想查看一个全局变量，有如下两种方式：
                                                                  select @@global.var_name;
                                                                  show global variables like “%var%”;

3、函数

      存储过程和函数区别: 存储没有返回值，使用输出参数返回数据，可以有多个,函数有返回值，只能返回一个。

字符串函数

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 (string ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string ) //去除后端空格
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

 数学函数

ABS (number2 ) //绝对值
BIN (decimal_number) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber) //转十六进制
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数] 注：返回类型并非均为整数

日期函数

ADDTIME (date2 ,time_interval) //将time_interval天加到date2 秒
ADDDATE(‘2019-04-15’,2); #2天
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) //小时

4、函数中常用的流程控制语句

• if
• case
• loop
• leave
• iterate
• repeat
• while<