mysql的自定义的函数和存储过程与触发器

自定义函数的两个必要条件：参数，返回值。

存储过程的概念：存储过程是sql集合和控制语句的预编译集合，以一个名称存储并作为一个单元处理。

范例：

#创建不带参数函数
create function f1() returns varchar(30)
return DATE_FORMAT(NOW(),"%y年%m月%d日 %H点:%i分:%s秒");
#运行函数
select f1();

#创建符合结构函数体，给表插入username的属性
create function adduser(username VARCHAR(32)) returns int(11)
BEGIN
insert test(username) VALUES(username);
return LAST_INSERT_ID();//返回id值
end

select adduser('王国');

#删除函数
drop FUNCTION f1

-- 创建带参函数(计算两个数的平均值)f2括号是带参的参数类型，returns 后面跟的是返回值的类型，return 代表返回的结果体
create function f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED) RETURNS float(10,2) UNSIGNED return (num1+num2)/2;

select f2(10,15);

存储过程的定义：

优点：增强了sql语句的功能性和灵活性，实现较快的执行速度，减少网络的流量

-- 创建无参存储过程
create PROCEDURE pr() select VERSION()

-- 创建删除的存储函数（参数是IN的类型，删除test表id=2的数据）

create PROCEDURE removTestById(IN p_id int)
BEGIN
delete from test where id=p_id;
end

call removTestById(2);

#带OUT的参数返回值（表示有返回值）

create PROCEDURE removeIdAndReturnCount(IN p_id int ,OUT count int)
BEGIN
delete from test where id=p_id;
select count(*) from test into count ; -- nto代表的意思是把count(*)的值赋值给count返回带回去
end

call removeIdAndReturnCount(3,@count) -- 第一个直接写传的参数第二个参数是返回的值，要用@方式的值来接收，代表用户变量，存储函数里的变量叫局部变量
select @count;

mysql的自定义的函数和存储过程与触发器

-- 3.4 带有条件判断的存储过程
-- 需求：输入一个整数，如果1，则返回“星期一”,如果2，返回“星期二”,如果3，返回“星期三”。其他数字，返回“错误输入”;
DELIMITER $
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
IF num=1 THEN
SET str='星期一';
ELSEIF num=2 THEN
SET str='星期二';
ELSEIF num=3 THEN
SET str='星期三';
ELSE
SET str='输入错误';
END IF;
END $

CALL pro_testIf(4,@str);

SELECT @str;

-- 3.5 带有循环功能的存储过程
-- 需求：输入一个整数，求和。例如，输入100，统计1-100的和
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
-- 定义一个局部变量
DECLARE i INT DEFAULT 1;
DECLARE vsum INT DEFAULT 0;
WHILE i<=num DO
SET vsum = vsum+i;
SET i=i+1;
END WHILE;
SET result=vsum;
END $

触发器

-- 需求：当向员工表插入一条记录时，希望mysql会自动同时往test表里插入数据

create TRIGGER tri_addtest after insert on employee for each row
-- 当网员工表插入一条记录时
insert into test(username) values('员工表插入数据了')

触发器创建语法四要素：

1.、引发对象(哪一张表引发的？也就是说表M是谁？)

2、触发时间(after/before)

3、被触发的操作(insert/update/delete)【也就是操作B是什么？位于begin和end之间】

4、本触发器名称

语法：

Create trigger triggerName【本触发器名称】

after/before insert/update/delete on 表名【引发对象表M】

for each row #这句话在mysql是固定的

begin

sql语句;

end;

上述语句含义：建立一个触发器，在【 insert/update/delete on 表名】之前或之后，执行begin和end之间的sql语句。

mysql的自定义的函数和存储过程与触发器

触发器

-- 需求：当向员工表插入一条记录时，希望mysql会自动同时往test表里插入数据

相关推荐