存储过程和函数是 事先经过编译并存储在数据库中的一段sql语句的集合,调用存储过程和函数可以简化应用开发人员很多工作,减少数据在数据库和应用服务器之间的传输,有利于提高数据处理效率。存储过程和函数的区别在于函数必须有返回值,而存储过程没有。函数:是一个有返回值的过程;过程:是一个没有返回值的函数。
创建
create procedure procedure_name ([porc_parameter[,...]])
begin
-- SQL语句
end;
###示例:(delimiter用于定义mysql语句的分隔符)
delimiter $
create procedure pro_test1()
begin
select 'hello Mysql';
end $
delimiter ;
调用
call procedure_name();
###示例
mysql> call pro_test1();
+-------------+
| hello Mysql |
+-------------+
| hello Mysql |
+-------------+
1 row in set (0.17 sec)
查询存储过程
##试了一下mysql8应该和老版本的查看有所不同
mysql> SELECT ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='demo_01';
+--------------+--------------+
| ROUTINE_TYPE | ROUTINE_NAME |
+--------------+--------------+
| PROCEDURE | pro_test1 |
+--------------+--------------+
###查看存储过程定义信息
mysql> show create procedure demo_01.pro_test1\G
*************************** 1. row ***************************
Procedure: pro_test1
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `pro_test1`()
begin
select 'hello Mysql';
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.35 sec)
删除存储过程
drop procedure pro_test1;
存储过程是可以编程的,意味着可以使用变量,表达式,控制语句,来完成比较复杂的功能。
变量
DECLARE var_name[,...] type [DEFAULT value]
###示例:
delimiter $
create procedure pro_test2()
begin declare num int default 5;
select num+ 10;
end$
delimiter ;
set var_name = expr [,var_name=expr] ...
示例:
delimiter $
create procedure pro_test3()
begin
declare name varchar(20);
set name = 'MYSQL';
select name;
end$
delimiter ;
delimiter $
create procedure pro_test4()
begin
declare countnum int;
select count(*) into countnum from city;
select countnum;
end$
delimiter ;
mysql> call pro_test4();
+----------+
| countnum |
+----------+
| 4 |
+----------+
if 条件判断
if search_condition then statement_list
[elseif search_condition then statement_list]...
[slse statement_list]
end if;
需求:根据定义的身高变量,判断当前身高的所属的身材类型180 及以上 ---》 身材高挑;170 -180 --》标准身材;170以下 ---》一般身材
#示例
delimiter $
create procedure pro_test6()
begin
declare height int default 175;
declare description varchar(50);
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$
delimiter ;
####调用
mysql> call pro_test6();
+-------------------------------------------------------------+
| concat('身高',height,'对应的身材类型: ',description) |
+-------------------------------------------------------------+
| 身高175对应的身材类型: 标准身材 |
+-------------------------------------------------------------+
1 row in set (0.02 sec)
参数传递
create procedure procedure_name([in/out/inout] 参数名 参数类型)
...
IN 该参数可以作为输入,也就是需要调用方传入的值,默认。
OUT 该参数作为输出,也就是该参数可以作为返回值。
INOUT 既可以作为输入参数,也可以作为输出参数
delimiter $
create procedure pro_test7(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$
delimiter ;
###调用
mysql> call pro_test7(180);
+---------------------------------------------------------------+
| concat('身高',height,'对应的身材类型: ',description) |
+---------------------------------------------------------------+
| 身高180对应的身材类型: 身材高挑 |
+---------------------------------------------------------------+
1 row in set (0.02 sec)
delimiter $
create procedure pro_test8(in height int, out description varchar(100))
begin
if height >= 180 then
set description='身材高挑';
elseif height>=170 and height< 180 then
set description='标准身材';
else
set description='一般身材';
end if;
end$
delimiter ;
####调用测试,
mysql> call pro_test8(175,@des);
Query OK, 0 rows affected (0.00 sec)
mysql> select @des;
+--------------+
| @des |
+--------------+
| 标准身材 |
+--------------+
1 row in set (0.00 sec)
####@变量 :表时用户会话变量,当前连接有效,@@变量:时系统变量。
mysql> set name='aaas';
ERROR 1193 (HY000): Unknown system variable 'name'
mysql> set @name='aaas';
Query OK, 0 rows affected (0.00 sec)
mysql> select @name;
+-------+
| @name |
+-------+
| aaas |
+-------+
语法结构:
#方式一
case case_value
when when_value then statement_list
[when when_value then statement_list] ...
[else statement_list]
end case;
#方式二
case
when search_condition then statement_list
[when search_condition then statement_list] ...
[else statement_list]
end case;
需求: 给定一个月份,然后计算出所在的季度
delimiter $
-- 默认输入参数可以省略in
create procedure pro_test9(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$
delimiter ;
###调用测试
mysql> call pro_test9(8);
+------------------------------------------------------+
| content |
+------------------------------------------------------+
| 传递的月份为:8,计算的结果为:第三季度 |
+------------------------------------------------------+
while search_condition do
statement_list
end while
需求从1加到n的值,示例:
delimiter $
-- 默认输入参数可以省略in
create procedure pro_test10(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$
delimiter ;
###调用
mysql> call pro_test10(100);
+-------+
| total |
+-------+
| 5050 |
+-------+
repeat
statement_list
until search_condition
end repeat;
需求,计算从1加到n:
delimiter $
-- 默认输入参数可以省略in
create procedure pro_test11(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$
delimiter ;
###调用测试
mysql> call pro_test11(50);
+-------+
| total |
+-------+
| 1275 |
+-------+
[begin_label:] loop
statement_list
end loop [end_label]
如果不在statement_list中早呢更加循环的语句,那么loop语句可以用来实现简单的死循环
delimiter $
-- 默认输入参数可以省略in
create procedure pro_test12(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$
delimiter ;
###调用测试
mysql> call pro_test12(4);
+-------+
| total |
+-------+
| 10 |
+-------+
#声明光标
declare cursor_name cursor for select_statement;
#open光标
open cursor_name;
#fetch光标
fetch cursor_name into var_name [,var_name]...
#close 光标
close cursor_name;
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,3300),(null,'白眉鹰王',44,3100),(null,'紫衫龙王',40,2900),(null,'张无忌',40,2900);
###创建存储过程
delimiter $
create procedure pro_test13()
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,',e_age=',e_age,',e_salary=',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary);
close emp_result;
end;
delimiter ;
####调用存储过程
mysql> call pro_test13();
+--------------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary) |
+--------------------------------------------------------------------------+
| id=1,name=金毛狮王,e_age=55,e_salary=3300 |
+--------------------------------------------------------------------------+
1 row in set (0.35 sec)
+--------------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary) |
+--------------------------------------------------------------------------+
| id=2,name=白眉鹰王,e_age=44,e_salary=3100 |
+--------------------------------------------------------------------------+
###完善的获取数据方法
delimiter $
create procedure pro_test14()
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,',e_age=',e_age,',e_salary=',e_salary);
until has_data=0
end repeat ;
close emp_result;
end;
delimiter ;
###调用测试
mysql> call pro_test14();
+--------------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary) |
+--------------------------------------------------------------------------+
| id=1,name=金毛狮王,e_age=55,e_salary=3300 |
+--------------------------------------------------------------------------+
1 row in set (0.36 sec)
+--------------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary) |
+--------------------------------------------------------------------------+
| id=2,name=白眉鹰王,e_age=44,e_salary=3100 |
+--------------------------------------------------------------------------+
1 row in set (0.36 sec)
+--------------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary) |
+--------------------------------------------------------------------------+
| id=3,name=紫衫龙王,e_age=40,e_salary=2900 |
+--------------------------------------------------------------------------+
1 row in set (0.36 sec)
+--------------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',e_age=',e_age,',e_salary=',e_salary) |
+--------------------------------------------------------------------------+
| id=4,name=张无忌,e_age=40,e_salary=2900 |
+--------------------------------------------------------------------------+
1 row in set (0.37 sec)
语法结构:
create function function_name([param type ...])
return type
begin
...
end;
案例: 定义一个存储函数,请求满足条件的总记录数(创建如果报错:1418-此函数中没有声明DETERMINISTIC,NO SQL或READS SQL DATA,只用声明才会启用二进制日志记录,您可能想使用不太安全的log_bin_trust_function_creators变量)
delimiter $$
create function fun1(countryId int)
returns int
NO SQL
begin
declare cnum int;
select count(*) into cnum from city where country_id=countryId;
return cnum;
end $$
delimiter ;
mysql> select fun1(1);
+---------+
| fun1(1) |
+---------+
| 3 |
+---------+
-- 查看该参数,默认为0
select @@log_bin_trust_function_creators;
-- 设置为1
set GLOBAL log_bin_trust_function_creators=1;
删除存储函数
mysql> drop function fun1;