MySQL 存储过程

存储过程的使用

（一）概念

1. 存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。类似于java中的方法。
2. 存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
3. 存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

（二）优点

1. 存储过程在创建的时候直接编译，而sql语句每次使用都要编译，提高执行效率
2. 一个存储过程可以被重复使用.提高代码的复用性
3. 一条sql语句，可能需要访问几张表，对数据库连接好几次，存储过程只会连接一次
4. 存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

（三）缺点

1. 可移植性太差了（把特有的语句转换为通用的写法）
2. 对于简单的sql语句，毫无意义
3. 对于只有一类用户的系统安全性毫无意义
4. 团队开发，标准不定好的话，后期维护很麻烦
5. 对于开发和调试都很不方便
6. 复杂的业务逻辑，用存储过程还是很吃力的

（四）创建存储过程语法

语法：
	CREATE PROCEDURE sp_name (参数类型 参数名  数据类型 ，...) 
	BEGIN           
		SQL语句集合；              	        
	END

说明：
	sp_name ：存储过程名字
	BEGIN ... END: 存储过程开始和结束符号 
	参数类型:参数类型可以是以下三种[ IN | OUT | INOUT ]:
					IN 输入参数：表示调用者向过程传入值
					OUT 输出参数：表示过程向调用者传出值
					INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值

（五）案例

1. 创建无参数存储过程

调用存储过程
	语法：call 存储过程名;
#1. 创建无参数存储过程  查询user表中的数据
    
    
CREATE PROCEDURE p1()
BEGIN
		select username,password from user;
END;


#调用存储过程
CALL p1;
#删除存储过程
DROP PROCEDURE p1;

2. IN(输入)参数存储过程

说明： IN 表示这个存储过程需要的输入参数 
    
    
    
CREATE PROCEDURE p2(in param1 int,in param2 varchar(30))
BEGIN
	select * from user where uid = param1;
END;

call p2(3,'张三');

3. out（输出）参数存储过程说明

说明：out表示这个存储过程需要的输出参数 
    
    
create PROCEDURE p3(out param1 int)
BEGIN
		select count(*) into param1 from myemployees.employees;
END;  


#定义会话变量
set @a = 0;
#接收输出参数的值
call p3(@a);
#显示变量 @a
select @a;

4. inout参数存储过程说明

说明：inout是可以接受一个参数并输出一个参数


CREATE PROCEDURE p4(inout param1 int)
BEGIN
	select count(*) into param1 from myemployees.employees where department_id = param1;
END;

#调用存储过程
set @a = 50;
call p4(@a);
select @a;

5. 删除存储过程

语法：  DROP PROCEDURE 存储过程名;

6.带有循环的存储过程

#5.带有循环的存储过程  while 输出 1-1000之和
create procedure p5(out param1 int,in param2 int)
begin
	#定义局部变量
	declare sum int default 0;
	declare i int default 0;
	while i <=param2 do
	 set sum = sum + i ;
	 set i = i + 1;
	end while;
	#输出变量sum
  select sum into param1;
end;


#删除存储过程
drop procedure p5;

#调用存储过程
set @a = 0;
call p5(@a,1000);

select @a;

参数说明：
	1. DECLARE：声明变量
	2. set: 给某个变量赋值
	3. while  .. do  ... end while : while 循环的语法

使用存储过程实现批量添加数据

create PROCEDURE pp(in number int)
BEGIN
		declare str varchar(3000) default '';
		declare i int default 0;
		declare a varchar(20) default '';
		declare b varchar(20) default '';
		declare c varchar(20) default '';
		declare d varchar(20) default '';
		set str = '先帝创业未半而中道崩殂今天下三分益州疲弊此诚危急存亡之秋也然侍卫之臣不懈于内忠志之士忘身于外者盖追先帝之殊遇欲报之于陛下也诚宜开张圣听以光先帝遗德恢弘志士之气不宜妄自菲薄引喻失义以塞忠谏之路也宫中府中俱为一体陟罚臧否不宜异同若有作奸犯科及为忠善者宜付有司论其刑赏以昭陛下平明之理不宜偏私使内外异法也侍中侍郎郭攸之费祎董允等此皆良实志虑忠纯是以先帝简拔以遗陛下愚以为宫中之事事无大小悉以咨之然后施行必能裨补阙漏有所广益将军向宠性行淑均晓畅军事试用于昔日先帝称之曰能是以众议举宠为督愚以为营中之事悉以咨之必能使行阵和睦优劣得所亲贤臣远小人此先汉所以兴隆也亲小人远贤臣此后汉所以倾颓也先帝在时每与臣论此事未尝不叹息痛恨于桓灵也侍中尚书长史参军此悉贞良死节之臣愿陛下亲之信之则汉室之隆可计日而待也';
		while(i<number) DO
			set a =  SUBSTRING(str,ceil(RAND()*340),1);
			set b =  SUBSTRING(str,ceil(RAND()*340),1);
			set c =  SUBSTRING(str,ceil(RAND()*340),1);
			set d =  SUBSTRING(str,ceil(RAND()*340),1);
			set i = i +1;
			insert into user_test values(null,concat(a,b,c,d),CEIL(rand()*100));
		end while;
END;


call pp(5000000);


create table user_test(
	id int primary key auto_increment,
	name varchar(200),
	age int
)