mysql存储过程与触发器连接 mysql中存储过程与存储函数

mysql手册05_存储过程和存储函数

---

存储过程和存储函数是存储在数据库中的一段SQL语句集合。
通过调用存储过程或存储函数，可以简化开发，减少数据在数据库和服务器间的传输。
存储过程和存储函数的区别是：存储函数有返回值。

基本语句：

注：在创建存储过程时，为了避免冲突，需要临时修改语句结束符号：
delimiter $

创建存储过程：
delimiter $

create procedure pro_test1() 
begin 
select 'Hello MySQL'; 
end$

delimiter ;

调用存储过程：
call pro_test1();

+-------------+
| Hello MySQL |
+-------------+
| Hello MySQL |
+-------------+

查看存储过程的两种方法：
select name from mysql.proc where db = "demo01"$
show procedure status\G;

查看存储过程的定义/封装语句：
show procedure pro_test1\G;

删除存储过程：
drop procedure pro_test1;

变量：

declare定义变量和set设置变量值：

create procedure pro_test1() 
begin 
declare num int default 10;
set num = num+10;
select concat('num的值为：',num);
end$

调用存储过程：
call pro_test1();
+-------------------------------+
| concat('num的值为：',num)     |
+-------------------------------+
| num的值为：20                 |
+-------------------------------+

select..into..将查询结果赋值给变量：

create procedure pro_test2() 
begin 
declare num int;
select count(*) into num from city;
select concat('city表中的记录数为：',num);
end$

call pro_test2();
+--------------------------------------------+
| concat('city表中的记录数为：',num)         |
+--------------------------------------------+
| city表中的记录数为：4                      |
+--------------------------------------------+

if条件判断：

create procedure pro_test3()
begin 
declare height int default 175;
declare description varchar(50) default '';
if height >=180 then
	set description='高个子';
elseif height >=170 and height<180 then
	set description='中等个子';
else
	set description='小个子';
end if;
select concat('身高：', height,'对应的类型为：',description);
end$

call pro_test3();
+-----------------------------------------------------------------+
| concat('身高：', height,'对应的类型为：',description)           |
+-----------------------------------------------------------------+
| 身高：175对应的类型为：中等个子                                 |
+-----------------------------------------------------------------+

输入参数：
根据传递的身高变量，判定身高类型

create procedure pro_test4(in height int)
begin 
declare description varchar(50) default '';
if height >=180 then
	set description='高个子';
elseif height >=170 and height<180 then
	set description='中等个子';
else
	set description='小个子';
end if;
select concat('身高：', height,'对应的类型为：',description);
end$

call pro_test4(198);
+-----------------------------------------------------------------+
| concat('身高：', height,'对应的类型为：',description)           |
+-----------------------------------------------------------------+
| 身高：198对应的类型为：高个子                                   |
+-----------------------------------------------------------------+

call pro_test4(178);
+-----------------------------------------------------------------+
| concat('身高：', height,'对应的类型为：',description)           |
+-----------------------------------------------------------------+
| 身高：178对应的类型为：中等个子                                 |
+-----------------------------------------------------------------+

call pro_test4(155);
+-----------------------------------------------------------------+
| concat('身高：', height,'对应的类型为：',description)           |
+-----------------------------------------------------------------+
| 身高：155对应的类型为：小个子                                   |
+-----------------------------------------------------------------+

输出参数：
根据传入的身高变量，获取身高类型并返回

create procedure pro_test5(in height int, out description varchar(10))
begin 
if height >=180 then
	set description='高个子';
elseif height >=170 and height<180 then
	set description='中等个子';
else
	set description='小个子';
end if;
end$

call pro_test5(188,@description);
select @description;
+--------------+
| @description |
+--------------+
| 高个子       |
+--------------+
注：@a表示用户会话变量，连接关闭时将释放。@@a表示系统变量。

case结构：
给定月份，计算所在季度

create procedure pro_test6(mon int)
begin
declare result varchar(10);
case
when mon>=1 and mon<=3 then
set result='第一季度';
when mon>=4 and mon<=6 then
set result='第二季度';
when mon>=7 and mon<=9 then
set result='第三季度';
else
set result='第四季度';
end case;
select concat('传递的月份为：', mon ,'，计算出的结果为：', result) as content;
end$

call pro_test6(9);
+---------------------------------------------------------------+
| content                                                       |
+---------------------------------------------------------------+
| 传递的月份为：9，计算出的结果为：第三季度                     |
+---------------------------------------------------------------+

while循环：
计算从1加到n

create procedure pro_test7(n int)
begin
	declare total int default 0;
	declare num int default 1;
	while num <= n do
		set total = total + num;
		set num = num +1;
	end while; 
	select total;
end$

call pro_test7(10);
+-------+
| total |
+-------+
|    55 |
+-------+

repeat循环：
计算从1加到n
注意until语句后面没有分号！

create procedure pro_test8(n int)
begin
	declare total int default 0;
	repeat
		set total = total + n;
		set n = n-1;
		until n=0
	end repeat;
	select total;
end$

call pro_test7(10);
+-------+
| total |
+-------+
|    55 |
+-------+

loop循环，leave退出：
计算从1加到n

create procedure pro_test9(n int)
begin
	declare total int default 0;
	c:loop
	set total=total+n;
	set n = n-1;
	if n<=0 then
		leave c;
	end if; 
	end loop c;
	select total;
end$

call pro_test9(10);
+-------+
| total |
+-------+
|    55 |
+-------+

游标：用来存储查询结果集:
一次fetch读取一行数据
若fetch次数超过数据行数，将报错

准备工作：
create table emp(
	id int(11) not null auto_increment,
	name varchar(50) not null comment '姓名',
	age int(11) comment '年龄',
	salary int(11) comment '薪水',
	primary key(id)
)engine=innodb default charset=utf8;

insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);

查询emp表的数据并逐行获取进行展示：
create procedure pro_test10()
begin
	declare e_id int(11);
	declare e_name varchar(50);
	declare e_age int(11);
	declare e_salary int(11);
	declare emp_result cursor for select * from emp;
	
	open emp_result;
	
	fetch emp_result into e_id,e_name,e_age,e_salary;
	select concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary);
	
	fetch emp_result into e_id,e_name,e_age,e_salary;
	select concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary);
	
	close emp_result;
end$

call pro_test10();
+-------------------------------------------------------------------------+
| concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary) |
+-------------------------------------------------------------------------+
| id=1, name=金毛狮王, age=55, salary=3800                                |
+-------------------------------------------------------------------------+
1 row in set (0.05 sec)

+-------------------------------------------------------------------------+
| concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary) |
+-------------------------------------------------------------------------+
| id=2, name=白眉鹰王, age=60, salary=4000                                |
+-------------------------------------------------------------------------+
1 row in set (0.05 sec)

循环获取游标数据：
注：退出条件的声明必须紧跟在游标声明之后！

查询emp表的数据并逐行获取进行展示：
create procedure pro_test11()
begin
	declare e_id int(11);
	declare e_name varchar(50);
	declare e_age int(11);
	declare e_salary int(11);
	declare has_data int default 1;
	
	declare emp_result cursor for select * from emp;
	declare exit handler for not found set has_data=0;
	
	open emp_result;
	
	repeat
		fetch emp_result into e_id,e_name,e_age,e_salary;
		select concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary);
		until has_data=0
	end repeat;
	
	close emp_result;
end$

call pro_test11();
+-------------------------------------------------------------------------+
| concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary) |
+-------------------------------------------------------------------------+
| id=1, name=金毛狮王, age=55, salary=3800                                |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

+-------------------------------------------------------------------------+
| concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary) |
+-------------------------------------------------------------------------+
| id=2, name=白眉鹰王, age=60, salary=4000                                |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

+-------------------------------------------------------------------------+
| concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary) |
+-------------------------------------------------------------------------+
| id=3, name=青翼蝠王, age=38, salary=2800                                |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

+-------------------------------------------------------------------------+
| concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary) |
+-------------------------------------------------------------------------+
| id=4, name=紫衫龙王, age=42, salary=1800                                |
+-------------------------------------------------------------------------+
1 row in set (0.01 sec)

存储函数：

定义一个存储函数，获取满足条件的总记录数:
create function fun1(countryId int)
returns int
begin
	declare cnum int;
	select count(*) into cnum from city where country_id = countryId;
	return cnum;
end$

调用存储函数：
select fun1(1);
+------+
| $    |
+------+
|    3 |
+------+

删除存储函数：
drop function fun1;

注：若创建存储函数报错：
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
使用以下命令解决：
set global log_bin_trust_function_creators=1$