首页 > 数据库 > 其他综合 >

MySQL自定义函数与存储过程

2017-04-11

MySQL自定义函数与存储过程,自定义函数 (user-defined function UDF)是一种对mysql扩展的途径,其用法和内置函数相同。

一、 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语句;对表格进行增删,连接,但是不能创建数据表

(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接口调用程序要快。

相关文章
最新文章
热点推荐