MYSQL储存过程与储存函数

1.存储过程

MySQL中提供存储过程与存储函数机制，我们先将其统称为存储程序，一般的SQL语句需要先编译然后执行，存储程序是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，当用户通过指定存储程序的名字并给定参数（如果该存储程序带有参数）来调用才会执行。

• 与储存函数是一组为了完成特定功能的SQL 语句集合

• 经编译后保存在数据库中

• 通过指定存储过程的名字并给出参数的值

• 带参数，也可返回结果

• 可包含数据操纵语句、变量、逻辑控制语句等

优点 ：

1.存储过程是通过处理封装在容易使用的单元中，简化了复杂的操作。

2.性能：存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。（减少网络流量，提高执行速度）

3.复用：存储的程序对任何应用程序都是可重用的和透明的。 存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。（减少数据库连接次数，复用性高）

4.安全：存储的程序是安全的。 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。（安全性高）

缺点：

1.如果使用大量的存储过程，那么使用这些存储过程的每个连接的内存使用量将大大增加。
此外，如果在存储过程中过度使用大量的逻辑操作，那么CPU的使用率也在增加，因为MySQL数据库最初的设计就侧重于高效的查询，而不是逻辑运算。

2.存储过程的构造使得开发具有了复杂的业务逻辑的存储过程变得困难。

3.很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。

4.开发和维护存储过程都不容易。
开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能导致应用    程序开发和维护阶段的问题。

5.对数据库依赖程度较高，移值性差。

2.创建和调用存储过程

数据准备：

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

insert  into `class`(`id`,`name`) values 
(1,'Java'),
(2,'UI'),
(3,'产品');


DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`name`,`class_id`) values 
(1,'张三',1),
(2,'李四',1),
(3,'王五',2),
(4,'赵刘',1),
(5,'钱七',3);

*查询数据
select * from class;

select * from student;

 

1.创建格式

注意：

如果没有声明分隔符，编译器会将其视为普通SQL语句进行处理，编译过程会报错
正确用法：首先用DELIMITER关键字声明当前段的分隔符，最后要将分隔符还原为默认字符

调用存储过程

call stu_grop();

示例：

#如果存在则删除
drop procedure if exists stu_grop(); 
delimiter//      #声明分隔符
create procedure stu_grop()
begin #过程体开始 
    select 'Hello';
end// #过程体结束 
delimiter;       #恢复默认分割符
​
#调用
call stu_grop();

2.查看存储过程 

-- 查看db01数据库中的所有存储过程
select * from mysql.proc where db='test';

-- 查看存储过程的状态信息
show procedure status;

-- 查看存储过程的创建语句
show create procedure stu_grop();
-- 删除存储过程
drop procedure stu_grop;
-- drop procedure if exists stu_grop;

3.储存过程的语法

1.变量

declare：声明变量

CREATE PROCEDURE test2 ()
begin
	
	declare num int default 0;		-- 声明变量,赋默认值为0
	select num+10;
	
end ;

call test2();			-- 调用存储过程

set：赋值操作

CREATE PROCEDURE test3 ()
begin
	
	declare num int default 0;
	set num =20;			-- 给num变量赋值
	select num;
	
end ;

call test3();

into：赋值操作

CREATE PROCEDURE test4 ()
begin
	
	declare num int default 0;			
	select count(1) into num from student;
	select num;
end ;

call test4();

 2.if语句

DELIMITER $$
CREATE PROCEDURE test5 ()
BEGIN

		declare day int default 1;			
		declare class_day	varchar(30);
		IF `day` = 0 THEN
		SET class_day='星期天';
		ELSEIF `day` = 1 THEN
		SET class_day='星期一';
		ELSEIF `day` = 2 THEN
		SET class_day='星期二';
		ELSE
		SET class_day='无效日期';
		END IF;
		SELECT class_day;

	END$$
DELIMITER ;

CALL test5();

3.参数传递

 

 in-输入参数

-- 定义一个输入参数
CREATE PROCEDURE test6 (in id int)
begin
	
	declare class_day varchar(30);
	
	if id=1 then
		set class_day='星期一';
	elseif id=2 then
		set class_day='星期二';
	else
		set class_day='星期三';
	end if;
	
	select class_day;
end ;

call test6(2);

out-输出参数

-- 定义一个输入参数和一个输出参数
CREATE PROCEDURE test7 (in id int,out class_day varchar(100))
begin
	
	if id=1 then
		set class_day='星期一';
	elseif id=2 then
		set class_day='星期二';
	else
		set class_day='星期三';
	end if;
	
	select class_day;
end ;

call test7(1,@class_day);	-- 创建会话变量		

select @class_day;		-- 引用会话变量

 @xxx：代表定义一个会话变量，整个会话都可以使用，当会话关闭（连接断开）时销毁
@@xxx：代表定义一个系统变量，永久生效。

4.case语句

案例：传递一个月份值，返回所在的季节。

CREATE PROCEDURE test8 (in month int,out season varchar(10))
begin
	
	case 
		when month >=1 and month<=3 then
			set season='spring';
		when month >=4 and month<=6 then
			set season='summer';
		when month >=7 and month<=9 then
			set season='autumn';
		when month >=10 and month<=12 then
			set season='winter';
	end case;
end ;

call test8(9,@season);			-- 定义会话变量来接收test8存储过程返回的值

select @season;

5.while语句

案例：计算任意数的累加和

CREATE PROCEDURE test10 (in count int)
begin
	declare total int default 0;
	declare i int default 1;
	
	while i<=count do
		set total=total+i;
		set i=i+1;
	end while;
	select total;
end ;

call test10(10);

6.repeat循环

REPEATE…UNTLL 语句的用法和 Java中的 do…while 语句类似，都是先执行循环操作，再判断条件，区别是REPEATE 表达式值为 false时才执行循环操作，直到表达式值为 true停止。 

CREATE PROCEDURE test11 (count int)		-- 默认是输入(in)参数
begin
	declare total int default 0;
	repeat 
		set total=total+count;
		set count=count-1;
		until count=0				-- 结束条件,注意不要打分号
	end repeat;
	select total;
end ;

call test11(10);

7.loop循环

执行过程中可使用 LEAVE语句或者ITEREATE来跳出循环，也可以嵌套IF等判断语句。 

1. LEAVE 语句效果对于Java中的break，用来终止循环；
2. ITERATE语句效果相当于Java中的continue，用来跳过此次循环。进入下一次循环。且ITERATE之下的语句将不在进行。

DELIMITER $$
CREATE 
    PROCEDURE test12(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     demo_sum:LOOP-- 循环开始
		SET num = num+1;
		IF num > 10 THEN
		    LEAVE demo_sum; -- 结束此次循环
		ELSEIF num <= 9 THEN
		    ITERATE demo_sum; -- 跳过此次循环
		END IF;
		
		SET SUM = SUM+num;
		END LOOP demo_sum; -- 循环结束
	END$$
DELIMITER;
CALL test12(0,@sum);

SELECT @sum;

8.游标

游标是用来存储查询结果集的数据类型，可以帮我们保存多条行记录结果，我们要做的操作就是读取游标中的数据获取每一行的数据。

声明游标

declare cursor_name cursor for statement;

打开与关闭游标

open cursor_name;  --打开
close cursor_name;  --关闭

CREATE PROCEDURE test13 ()		-- 默认是输入(in)参数
begin
	
	declare id int(11);
	declare `name` varchar(20);
	declare class_id int(11);
	-- 定义游标结束标识符
	declare has_data int default 1;
	
	declare stu_result cursor for select * from student;
	-- 监测游标结束
	declare exit handler for not FOUND set has_data=0;
	
	-- 打开游标
	open stu_result;
	
	repeat 
		fetch stu_result into id,`name`,class_id;
		
		select concat('id: ',id,';name: ',`name`,';class_id',class_id);
		until has_data=0		-- 退出条件,注意不要打分号
	end repeat;
	
	-- 关闭游标
	close stu_result;
	
end ;

call test13();

 4.存储函数

储存过程和存储函数非常相似，区别在于存储函数必须有返回值

 临时表

临时表就是临时要用创建的表，临时表的作用仅限于本次会话，等连接关闭后重新打开连接临时表将不存在
temporary：代表创建的表是一张临时表 

create temporary table temp_table(
	id int,
	name varchar(10)
);
insert into temp_table values (1,'1');

select * from temp_table ;

• 注意：临时表示查询不到的

show tables;   -- 不会显示临时表的存在