一、自定义函数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...]