存储过程学习

• • 概念
  • 语法
  • 游标
  • 函数

概念

存储过程和函数是事先经过编译并存储在数据库中的一段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;