MySql⾃定义函数，存储过程，游标的使⽤⾸先整理⼀下mysql内置的⼀些函数
MySQL内置函数
⼀、字符函数
（1）CONCAT()//字符连接
（2）CONCAT_WS()//使⽤指定的分隔符进⾏字符连接
（3）FORMAT()//数字格式化
（4）LOWER()//转化⼩写
（5）UPPER()//转换⼤写
（6）LEFT()//获取左侧字符
（7）RIGHT()//获取右侧字符
（8）LENGTH()//取得字符串长度
（9）LTRIM(),RTRIM(),TRIM()//删除前导、后续空格或者指定字符
（10）REPLACE()//替换
（11）SUBSTRING()//字符串截取
（12）[NOT] LIKE//模式匹配——百分号%代表任意个字符；下划线_代表任意⼀个字符
⼆、数值函数
（1）CEIL()//进⼀取整
（2）FLOOR()//舍⼀取整
（3）DIV//整数除法
（4）MOD//取余数，与%⼀样
（5）POWER()//幂运算
（6）ROUND()//四舍五⼊
（7）TRUNCATE()//截断
三、⽐较函数
（1）[NOT] BETWEEN…AND… //[不]范围之内
（2）NOT IN() //[不]在列出值范围内
（3）IS [NOT] NULL //[不]为空
四、⽇期时间函数
（1）SELECT NOW(); // 当前⽇期时间
（2）SELECT CURDATE(); // 当前⽇期
（3）SELECT CURTIME(); // 当前时间
（4）SELECT DATE_ADD();//⽇期的变化
continue语句执行过程（5）DATEDIFF(); // ⽇期的差值
（6）DATE_FORMAT(); // ⽇期格式化
五、信息函数
（1）SELECT CONNECTION_ID() //连接ID
（2）SELECT DATABASE() //当前数据库
（3）SELECT LAST_INSERT_ID() //最后插⼊记录的ID
（4）SELECT USER() //当前⽤户
（5）SELECT VERSION() //版本信息
六、聚合函数
（1）AVG()//平均值
（2）COUNT()//计数
（3）MAX()//最⼤值
（4）MIN()//最⼩值
（5）SUM()//求和
七、加密函数
（1）MD5();//信息摘要算法
（2）PASSWORD();//加密算法，主要⽤途修改当前⽤户密码
⽤户⾃定义函数
简称UDF（user-defined function），其⽤法与内置函数相同
函数可以返回任意类型的值，也可以接受这些类型的参数，参数不能超过1024个；创建⾃定义函数：
CREATE FUNCTION function_name
RETURNS
{STRING/INTEGER/REAL/DECIMAL}
routine_body - 函数体
函数体：
(1)函数体由合法的SQL语法构成;
(2)函数体可以是简单的SELECT或INSERT语句;
(3)函数体如果为复合结构则使⽤BEGIN…END语句；
(4)复合结构可以包括声明，循环，控制结构。
（5）RETURNS TYPE语句表⽰函数返回数据的类型；
注意：RETURNS CHAR(50)数据类型的时候，RETURNS 是有S的，⽽RETURN (SELECT 语句)的时候RETURN是没有S的
如果在存储函数中的RETURN语句返回⼀个类型不同于函数的RETURNS⼦句中指定类型的值，返回值将被强制转换为恰当的类型。
例如，如果⼀个函数返回⼀个SET或ENUM值，但是RETURN语句返回⼀个整数，对于SET成员集的
相应ENUM成员，从函数返回的值是字符串。
（FUNCTION中总是默认是IN参数）RETURNS⼦句对FUNCTION做指定，对函数⽽⾔这是强制的。他⽤来指定函数的返回类型，⽽且函数体必须包含⼀个RETURN value语句
创建不带参数的⾃定义函数
1. ⾃定义函数f1
DROP FUNCTION IF EXISTS f1
CREATE FUNCTION f1()
RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m⽉%d⽇ %H点%m分%s秒')
调⽤函数f1()
SELECT f1();
2.定义带参数的函数
CREATE FUNCTION f2(a SMALLINT UNSIGNED, b SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (a+b)/3
SELECT f2(10, 15)
定义带有多参数，多条语句的⾃定义函数
修改分隔符：DELEMITER 分隔符
例：DELIMITER // /* 将分隔符修改为 '//'
当函数体内需要执⾏的是多条语句时，要使⽤END语句
且当编写函数体内容的时候，需要使⽤ DELIMITER 关键字将分隔符先修改为别的，否则编写语句的时候写到 ‘;’ 的时候会直接执⾏，导致函数编写失败
删除函数：DROP FUNCTION [IF EXISTS] function_name
例：
CREATE FUNCTION ADD_USER(p_id SMALLINT,username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT user(p_id,username) VALUES(p_id,username);
RETURN LAST_INSERT_ID();
END
3.带有in的存储过程
//创建带有INT类型参数的存储过程
DESC users;
DELIMITER //
CREATE PROCEDURE removeUserByID(IN id INT UNSIGNED) //id = id将会导致误解BEGIN
DELETE FROM users WHERE id = id;
END
//
DELIMITER ;
CALL removeUserById(3); //参数名称最好不要和表中的字段相同
SELECT * FROM users; //全删除了
DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN DELETE FROM users WHERE id = p_id;
END
//
DELIMITER ;
SELECT * FROM users;
CALL removeUserById(22);
SELECT * FROM users WHERE id = 22;
4.带有in|out 的存储过程
创建带有IN OUT类型参数的存储过程
CREATE PROCEDURE removerUserAndReturnUserName(IN showID INT UNSIGNED,OUT showName INT UNSIGNED)
BEGIN
DELETE FROM user WHERE id = showID;
SELECT count(ID) FROM user INTO showName;
END
//
SELECT count(ID) FROM user INTO showName; /* 该语句中的 INTO 含义就是将 SELECT 语句结果的表达式返回到 showName 变量中 / CALL removerUserAndReturnUserName(10,@nums); /* @nums 所代表的就是⽤户变量，可⽤ SELECT @nums 输出 /
⽤ DECLARE 声明的变量是局部变量，局部变量只能存在于 END 之间，且声明时必须置于 END 的第⼀⾏
⽽通过 /SET @id = 07 这种⽅法设置的变量我们称之为⽤户变量，只能存在于当前⽤户所使⽤的客户端有效。
CALL rmUserAndRtUserNums(27, @nums);
SELECT @nums; //@nums - 就是⽤户变量
DECLARE声明的变量都是在BEGIN与END之间，是局部变量
SET @i = 7; //通过@或SET设置的变量称为⽤户变量
⾃定义存储过程
语法
CREATE PROCEDURE sp_name ([ proc_parameter ]) [ characteristics..] routine_body
proc_parameter指定存储过程的参数列表，列表形式如下：
[IN|OUT|INOUT] param_name type
其中in表⽰输⼊参数，out表⽰输出参数，inout表⽰既可以输⼊也可以输出；param_name表⽰参数名称；type表⽰参数的类型
1.创建带有多个OUT类型参数的存储过程
先使⽤⼀个⽅法，ROW_COUNT（）
INSERT test(username) VALUES('A'),('B,'),('C');
SELECT ROW_COUNT(); //返回被插⼊的记录总数
SELECT * FROM test;
UPDATE test SET username = CONTCAT(username, '--immoc') WHERE id <= 2;
SELECT row_COUNT(); //返回更新的记录总数
创建多个输出参数的存储过程

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。