MySQL 存储过程

前言

备份时需要数据库启动状态，恢复时需要关闭状态

---

存储过程就是具有名字的一段代码，用来完成一个特定的功能。

1.为什么要使用存储过程？ 将重复性很高的一些操作，封装到一个存储过程种，简化了对这些SQL的调用。 批量处理数据。 封装SQL 语句集，统一接口，确保数据安全。 相对于ORACLE 数据库来说，MYSQL存储过程功能相对比较弱，使用较少。

2.DELIMITER 理解？ 它与存储过程语法无关，他将标准分隔符；改为其他符号，主要作用是告诉MYSQL 解析器，该语句是否可以执行了，在CMD中；号作为解析符，会将当前行执行，当写多行执行语句时；号不在适用，所以需要更换一下结束符。

优点

存储过程可封装，并隐藏复杂的商业逻辑。 存储过程可以回传值，并可以接受参数。 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。 存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。 存储过程的性能调校与撰写，受限于各种数据库系统。

一、存储过程基本语法

BEGIN … END 存储过程开始和结束符号 SET @a = 1 变量赋值 DELIMITER 声明 CREATE PROCEDURE 存储过程名(参数) 创建mysql存储过程 CREATE FUNCTION 存储函数名(参数) 存储过程体

二、创建数据库

CREATE TABLE account(aname VARCHAR(20),amount DECIMAL(10,2));

CREATE TABLE accflow(anme VARCHAR(20),cur_amount DECIMAL(10,2));

三、存储过程基础

-- 创建存储过程    CREATE PROCEDURE 方法名（） 无参
CREATE PROCEDURE pd1()
BEGIN
	SELECT * from accflow;
END;

结果:

-- 创建存储过程
CREATE PROCEDURE pd1()
BEGIN
	SELECT * from accflow;
END
> OK
> 时间: 0.017s

---

MYSQL 有特定方法去调用存储过程方法 (CALL)

-- 调用 pd1(); 
CALL pd1();

这时候就可以看到返回的结果集了。

到这呢，它已经是一个最简单的存储过程了，只做了一件事，就是查询。

查看存储过程：

-- 查看存储过程
SHOW PROCEDURE STATUS WHERE db = 'db1';

-- 声明变量
CREATE PROCEDURE pd2()
BEGIN
	DECLARE z VARCHAR(20);                       -- 定义变量为 z 并且声明长度
	set z = '1';								 -- 赋值
 	SELECT * from accflow where aname = z;
END;

-- 调用 pd2 
CALL pd2();

-- 删除存储过程 
DROP PROCEDURE pd2

存储过程传递参数 in,out,inout

-- 查看当前DB 所有的存储过程 IN
CREATE PROCEDURE test1(IN sname VARCHAR(20))
BEGIN											
 	SELECT * from accflow where aname = sname;
END;

-- 调用 test1 
CALL test1('1');

-- IN OUT 
CREATE PROCEDURE test2(IN sname VARCHAR(20),OUT tname VARCHAR(20))
BEGIN		
	set tname = '张三丰';
 	SELECT * from accflow where aname = sname;
END;

-- 调用 test2 
CALL test2('1',@tname);
-- 查询 tname 
select @tname

-- IN OUT INOUT
CREATE PROCEDURE test3(IN sname VARCHAR(20),OUT tname VARCHAR(20),INOUT amount DECIMAL(10,2))
BEGIN		
	set tname = '张三丰';
 	SELECT cur_amount into amount  from accflow where aname = sname;
END;

-- 调用 test3 
CALL test3('1',@tname,@amount);
select @amount

-- 错误写法1
CALL test3('1',@tname,@'1000');
select @amount

-- 错误写法2
set @amount = 1000;
CALL test3('1',@tname,@amount);
select @amount
 
 -- 解释为什么是错误
 -- 查询时候发现都是元数据并没有改变
 -- 因为这个地方（cur_amount into amount） 只是做了个赋值的作用
  
-- 可以这样
CREATE PROCEDURE test4(IN sname VARCHAR(20),OUT tname VARCHAR(20),INOUT amount DECIMAL(10,2))
BEGIN		
	set tname = '张三丰';
	set amount = 1000;
 	SELECT cur_amount   from accflow where aname = sname;
END;
	
-- 调用 test4 	
CALL test4('1',@tname,@amount);
select @tname,@amount
	
-- 删除 
drop PROCEDURE test4

流程控制

CREATE PROCEDURE test5(in sid int(11))
BEGIN		
	if  sid = 1 THEN 			 -- 要注意这块 =  和java 是有区别的
		select * from accflow;
	else
		select * from account;
	end if;
END;
	
-- 调用 test5 	
CALL test5(2);

CREATE PROCEDURE test6(inout sid int(11))
BEGIN		
	if  sid = 1 THEN 			 -- 要注意这块 =  和java 是有区别的
		select * from accflow;
	else
		select * from account;
	end if;
	
	-- while
	while  sid < 10 do
	 set sid = sid + 1;  -- 自增
	END WHILE;    -- 结束循环

END;

-- 调用 test6 	
set @sid =1;
CALL test6 (@sid2);
	
CREATE PROCEDURE test7(inout sid int(11))
BEGIN		
	if  sid = 1 THEN 			 -- 要注意这块 =  和java 是有区别的
		select * from accflow;
	else
		select * from account;
	end if;
	
	-- while
	while  sid < 10 do
	 set sid = sid + 1;  -- 自增
	END WHILE;    -- 结束循环
	
	repeat
	set sid = sid + 1;
	until sid = 20   -- 注意没有分号
	end repeat;

END;

-- 调用 test7 	
set @sid =1;
CALL test7 (@sid);	
select @sid	

函数调用

-- 准备出插入数据的表
create table emp1(eid int(11),ename varchar(10), age int (3))

--
select substr('0123456789fhjjrewnwejkthiwejrjkwebijwehtiwehrebKJDHGJJGELRLKGHRJGKFWGIREERJGEJRNGRE', FLOOR(rand() * 62) +1,1)

-- 实现5位随机一个拼装
CREATE FUNCTION str_generate()
-- 返回字符串
returns VARCHAR(20) DETERMINISTIC   
BEGIN
-- 声明
	DECLARE source_str VARCHAR(62);  -- 其实这块可以按java 的逻辑去理解，它就是变量
	-- 接受变量
	DECLARE return_str VARCHAR(6);
	DECLARE x int(2);
	set source_str = '0123456789fhjjrewnwejkthiwejrjkwebijwehtiwehrebKJDHGJJGELRLKGHRJGKFWGIREERJGEJRNGRE';
	set x = 1;
	set return_str = '';
	REPEAT
		set return_str = CONCAT(return_str,substr(source_str, FLOOR(rand() * 62) +1,1));
		set x = x + 1;
	UNTIL x > 5 
	END REPEAT;
	return return_str;
END;

-- 查询
select str_generate();

-- 删除函数
drop function str_generate

-- 使用存储过程批量插入
CREATE PROCEDURE pro_insert()
BEGIN
  --  这里我用Java的语言来解释下，定义变量，然后赋值（必须）
  DECLARE i int(11);
	set i = 100;
  while i<= 200 do
	insert into emp1 VALUES(i,str_generate(),FLOOR(rand() * 30));
	set i = i + 1;
	end while;
	-- 最后一起提交
	COMMIT;
END;

call pro_insert();

select * from emp1

drop PROCEDURE pro_insert

一定要注意；该有的必须有，不该有的不能有。 很多次出错，都是因为多一个或者少一个；