MySQL学习笔记 4：自定义函数和MySQL存储过程

一、自定义函数UDF

1.  语法结构

CREATE FUNCTION functon_name([参数名 参数类型，...])

RETURNS 返回值类型

函数体

2.例子

DELIMITER //

CREATE FUNCTION adduser(username VARCHAR(20))

RETURNS INT UNSIGNED

BEGIN

INSERT test(username) VALUES(username);

RETURN LAST_INSERT_ID();

END

//

首先将分隔符修改为//,函数体包含在BEGIN..END中

二、MySQL存储过程

1.简介

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。可以加快执行速度，减少网络流量。

2.创建存储过程

CREATE [DEFINER = {user | CURRENT_USER}]

PROCEDURE sp_name ([proc_parameter [,...]])

[characteristic ... ] routine_body

2.1  sp_name是存储过程的名字

2.2  proc_parameter ： [ IN  | OUT | INOUT ] param_name type

       IN : 表示该参数的值必须在调用存储过程时指定

       OUT : 表示该参数的值可以被存储过程改变，并且可以返回

       INOUT : 表示该参数在调用时指定，并且可以被改变和返回

2.3  characteristic为特性

       COMMENT 'string'

       | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

       | SQL SECURITY {DEFINER | INVOKER}

2.4  routine_body为过程体，其中不可以执行创建数据库或者数据表的操作，如果由多于一条SQL语句组成则用BEGIN ... END。

3.调用存储过程

CALL sp_name ([parameter [,...])

CALL sp_name[()]

4. 删除存储过程

不能修改存储过程的过程体，要修改过程体只能先把存储过程删除了，重新创建

DROP PROCEDURE [ IF EXISTS] sp_name

5.例子（带有IN和OUT类型参数）

DELIMITER //

CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)

BEGIN

DELETE FROM users WHERE id = p_id;

SELECT count(id) FROM users INTO userNums;

END

//

调用 CALL removeUserAndReturnUserNums(22,@nums);

其中@nums是用户变量，只对当前用户客户端有效

6.例子（带有多个OUT类型参数）

DELIMITER //

CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED, OUT deleteUser SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)

BEGIN

DELETE FROM users WHERE age = p_age;

SELECT ROW_COUNT() INTO deleteUsers;

SELECT COUNT(id) FROM users INTO userCounts;

END

//

其中ROW_COUNT()返回被更新的记录数

7.存储过程和自定义函数的区别

存储过程实现的功能复杂一些，而函数的针对性更强

存储过程可以返回多个值，函数只能返回一个值

存储过程一般独立执行，而函数可以作为其它SQL语句的组成部分出现

8.修改存储过程

只能修改特性

ALTER PROCEDURE sp_name [characteristic...]