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语句;对表格进行增删，连接，但是不能创建数据表<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接口调用程序要快。