概念
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程和函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值,而存储过程没有。
函数:是一个有返回值的过程;
过程:是一个没有返回值的函数;
语法
delimiter $ 声明分隔符(默认是分号;,命令行创建的时候需要用哦)。
为了方便,以下是Navicat Premium 15操作。
create procedure `test1`()
begin
select "hello pro";
end
调用存储过程 call test1();
查询存储过程状态信息show procedure status;
查询存储过程定义show create procedure test1;
删除drop procedure [if exists] test2;
create procedure `test2`()
begin
declare num int default 0; //声明并给与默认值
set num = num + 10;//赋值
select CONCAT('num的值是:',num);
end
declare num int;//声明
select count(*) into num from t_user;//赋值
select CONCAT('user表的记录数是:',num);
IN:该参数可以作为输入,也就是需要调用方传入值,默认。
OUT:该参数作为输出,也就是该参数可以作为返回值。
INOUT:既可以作为输入参数,也可以作为输出参数。
if 语法:
create procedure `test1`(in age int)
begin
declare description varchar(20) default '';
if age >= 50 then
set description = '年老';
elseif age >= 10 and age < 50 then //注意这里的else和if在一起写的!!
set description = '一般';
else set description = '太小';
end if;
select concat('年龄',age,'对应的描述',description);
end
调用:call test1(100);
输入加输出:
create procedure `test1`(in age int,out description varchar(100))
begin
if age >= 50 then
set description = '年老';
elseif age >= 10 and age < 50 then
set description = '一般';
else set description = '太小';
end if;
end
调用方法:call test1(100,@description);
select @description;
@description
在变量前面加@,是用户会话变量,整个会话过程中有作用。
在变量前面加@@,是系统变量。
case when语句:
create procedure `test1`(in age int)
begin
declare result varchar(100);
case
when age >= 50 then
set result = '年老';
when age >= 10 and age < 50 then
set result = '一般';
else set result = '太小';
end case;
select concat('结果是:',result);
end
where 循环:满足条件进行循环。
create procedure `test1`(in 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
repeat 循环:满足条件退出循环。
create procedure `test1`(in 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
loop循环(leave退出循环):
create procedure `test1`(in n int)
begin
declare total int default 0;
c:loop //c 是别名,退出时用。
set total = total + n;
set n = n - 1;
if n <=0 then
leave c; //leave 循环c。
end if;
end loop c;
select total;
end
游标
游标是用来存储查询结果集的数据类型,在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH和CLOSE。
CREATE TABLE `t_emp` (
`id` bigint(20) NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`salary` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
create procedure `test1`()
begin
declare e_id bigint;
declare e_name varchar(255);
declare e_age int;
declare e_salary decimal(10,2);
declare has_date int default 1;
declare emp_result cursor for select * from t_emp;
declare exit HANDLER for not found set has_date = 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,',薪资为:',e_salary);
until has_date = 0 end repeat;
close emp_result; -- 关闭游标
end
函数
CREATE FUNCTION `test`(empId bigint) RETURNS int(11)
NO SQL
BEGIN
return (select e.age from t_emp e where e.id = empId);
END
调用:SELECT test(1);
其他写法:
declare res int;
select e.age into res from t_emp e where e.id = empId;
return res;