一、 Mysql 自定义函数
自定义函数 (user-defined function UDF)是一种对mysql扩展的途径,其用法和内置函数相同。
自定义函数的两个必要条件:1、参数(不是必有的,例如select version())2返回值 (必有的)。函数可以返回任意类型的值,同样可以接收这些类型的参数,参数与返回值没有必然的内在联系
创建自定义函数语法:
CREATE FUNCTION function_name(parameter_nametype,[parameter_name type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body
简单来说就是:
CREATE FUNCTION 函数名称(参数列表)
RETURNS 返回值类型
函数体
关于函数体:
1. 函数体由合法的SQL语句构成;
2. 函数体可以是简单的select或insert语句;
3. 函数体如果为符合结构则使用begin…end;
4. 复合结构可以包括声明、循环、控制结构;
自定义函数中定义局部变量语法:
DECLARE变量1[,变量2,... ]变量类型 [DEFAULT 默认值];
为变量赋值语法:
SET parameter_name = value[parameter_name = value...];
删除自定义函数:
DROP FUNCTION function_name;
调用自定义函数语法:
SELECT function_name(parameter_value,...)
实例:
创建不带参数的自定义函数
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %h点:%i分:%s秒');
创建带参数的自定义函数:
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN(num1+num2)/2;
创建具有复合结构函数体的自定义函数
修改分隔符:DELEMITER 分隔符
Eg:DELIMITER // /* 将命令分隔符”;”改为”//“ */
当函数体内需要执行的是多条语句时,要使用BEGIN...END语句;且当编写函数体内容的时候,需要使用 DELIMITER 关键字将分隔符先修改为别的,否则编写语句的时候写到’;’的时候会直接执行,导致函数编写失败
Eg:
DELIMITER //
CREATE FUNCTION ADD_USER(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT user(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
// /* 此处的”//“为告诉系统函数定义结束 */
二、 Mysql 存储过程
上述过程中,如果省略了语法分析和编译的阶段,则效率可提高。因此用存储过程来解决这个问题。
存储过程:是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理(类似函数)。存储过程存储在数据库内,可以由应用程序调用执行,而且允许用户申明变量以及进行流程控制。存储过程可以接受输入类型的参数和输出类型的参数,并且可以存在多个返回值。只在第一次调用时进行语法分析和编译,以后的调用直接调用编译的结果,效率大大提高。
优点:
1、增强SQL语句的功能和灵活性:可以通过控制语句对流程进行控制和判断
2、实现较快的执行速度:客户端第一次调用存储过程时,MySQL引擎会对其进行语法分析、编译等操作,然后将编译结果存储到内存中,所以第一次和之前的效率一样,然而以后会直接调用内存中的编译结果,效率提高
3、减少网络流量:例如删除一个记录,我们原本要输入DELETE FROM xx WHERE ...; 要传输的字符较多,如果写成存储过程,就只要调用存储过程的名字和相应参数就行,传输的字符数量较少,所以减少了网络流量。
创建存储过程语法:
create procedure sp_name(in 过程参数,out 过程参数,inout 过程参数...)
begin
/*此为过程体*/
End
关于过程体
(1)过程体由合法的SQL语句构成;
(2)过程体可以是任意SQL语句;对表格进行增删,连接,但是不能创建数据表<br>
(3)过程体如果为复合结构则使用BEGIN...END语句
(4)复合结构可以使用条件、循环等控制语句
参数类型说明
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT
IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
OUT 输出参数:该值可在存储过程内部被改变,并可返回
INOUT 输入输出参数:调用时指定,并且可被改变和返回
调用:
CALL sp_name([parameter[,...]])
CALL sp_name[()]
修改存储过程//不能修改过程体要修改过程体需删除存储过程,重新创建
ALTER PROCEDURE sp_name [characteristic]
COMMENT 'string'
|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY{DEFINER|INVOKER}
删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name;
实例:
创建一个无参存储过程
CREATE PROCEDURE sp1() SELECT VERSION();
创建带有INT类型参数的存储过程(根据传入的ID来删除记录)
DELIMITER //
CREATE PROCEDURE removeUserByID(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id; //参数名称最好不要和表中的字段相同
END
//
DELIMITER ;
CALL removeUserById(3);
创建带有IN和OUT类型参数的存储过程(删除数据表中对应id 的记录并返回剩余的记录数)
CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUTuserNums INT UNSIGNED)
-> BEGIN
-> DELETE FROM test WHEREid=p_id;
-> SELECT count(id) FROM testINTO userNums; //意思是将剩余id记录数放入参数userNums
-> END
-> //
CALL removeUserAndReturnUserNums(27,@nums)
其中27就是要删除的id=27所在的记录,@nums是一个用户变量,用来接收返回的剩余记录数量。
关于变量
1.用户变量:以"@"开始,形式为"@变量名"。用户变量跟mysql客户端是绑定的,设置的变量,只对当前用户使用的客户端生效
2.全局变量:定义时,以如下两种形式出现,set GLOBAL 变量名 或者 set @@global.变量名。对所有客户端生效。只有具有super权限才可以设置全局变量
3.会话变量:只对连接的客户端有效。
4.局部变量:作用范围在begin到end语句块之间。declare语句专门用于定义局部变量。set语句是设置不同类型的变量,包括会话变量和全局变量
创建多out类型参数的存储过程(根据年龄删除用户,返回修改的数目和剩余的数目)
DELIMITER //
CREATE PROCEDURE rmUserByAgeAndRtInfos(IN p_age SMALLINT UNSIGNED, OUTdelNums SMALLINT UNSIGNED, OUT leftNums SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age = p_age;
SELECT ROW_COUNT() INTO delNums;
SELECT COUNT(id) FROM users INTO leftNums;
END
//
调用过程:
CALL remove_user_return_infos(7, @删除记录数, @剩下记录数);
查看变化:
SELECT @删除记录数, @剩余记录;
存储过程与自定义函数的区别:
1、存储过程实现的功能相对复杂,函数针对性较强
2、存储过程可以返回多个值,函数只能有一个返回值
3、存储过程一般独立执行,函数可以作为 sql 语句的组成部分来出现
另外,存储过程也比通过API接口调用程序要快。