mysql 存储过程procedure

一、存储过程概念

• 存储过程是一组为了完成特定功能的sql语句集，经编译后存储在数据库中，用户通过指定存储过程名字并给定参数来调用它；
• 存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有sql语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式；

二、存储过程的优点：

• 1、存储过程增强了sql语言的功能和灵活性。存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断、运算；
• 2、存储过程允许标准组件是编程；存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的sql语句。数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响；
• 3、存储过程能实现较快的执行速度。如果某一操作包含大量的sql代码或分别被多次运行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的sql语句在每次运行时都要进行编译和优化，速度相对要慢一些。
• 4、存储过程能减少网络流量；针对同一个数据库对象的操作，如果这一操作所涉及的sql语句被组织成存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，降低了网络负载；
• 5、存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

三、存储过程格式

CREATE PROCEDURE 存储过程名 ([参数[, , , ]])
[特性...] 过程体

例子
delimiter //  -- 指定存储过程结束符为//
CREATE PROCEDURE proc1(OUT s INT)
	BEGIN
		SELECT COUNT(*) INTO s FROM USER;
-- 把查询结果赋值给out类型的参数s；通过out类型的参数把存储过程中的结果返回给调用环境
	END;
	//  -- 标记存储过程结束
delimiter ;   -- 把sql语句结束分隔符还原成;    

四、delimiter作用

• delimiter是分隔符的意思。因为MySQL默认以;为分隔符，如果我们没有声明分隔符，那么编译器会把存储过程当成sql语句进行处理，则存储过程的编译过程会报错，所以要事先用delimiter关键字声明当前段分隔符，这样MySQL才会将;当做存储过程的代码，不会执行这些代码，用完了之后要把分隔符还原。
• delimiter // 即把MySQL语句的结束分隔符;改成了// 原来的分号就不起作用了，在结束语句的时候需要加上//才会执行。

五、MySQL存储过程参数有3种类型

• IN 输入参数，表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值；
• OUT 输出参数，该值可在存储过程内部被改变，并可返回；
• INOUT 输入输出参数，调用时指定，并且可被改变和返回；

例子1：
CREATE PROCEDURE proc2(P_IN INT)  --P_IN没有声明输入输出类型，则默认为in类型
	BEGIN
		SELECT P_IN;  --查询P_IN值
		SET P_IN = 1000;  --重新设置P_IN值
		SELECT P_IN;  --再次查询P_IN值
	END;
--调用存储过程：
SET @P_IN = 10; --声明一个变量并赋值，声明变量是以@开头
CALL proc2(@P_IN); --调用存储过程并传递参数
SELECT @P_IN;  --查询结果
	
例子2：
CREATE PROCEDURE proc3(OUT P_OUT INT)  --OUT和INOUT类型的参数必须要显示的声明
	BEGIN
		SELECT P_OUT;  --OUT类型不可把值带入过程，所以此时为null
		SET P_OUT = 1000;  --在存储过程中给out类型的参数重新赋值，通过out类型的参数把结果带回给调用环境；
		SELECT P_OUT; --再次查询P_OUT值
	END;
--调用存储过程：
SET @P_OUT = 10;
CALL proc3(@P_OUT);
SELECT @P_OUT;
	
例子3：
CREATE PROCEDURE proc4(INOUT P_IN_OUT INT)  --OUT INOUT 类型的参数必须要显示的声明
	BEGIN
		DECLARE a VARCHAR(50) DEFAULT "asd"; --在存储过程中使用变量；声明变量的语句必须写在begin END 之间
		SELECT a;  --查询变量值
		SET a = "www";  --给变量重新赋值
		SELECT a;  --重新查询变量
		SELECT P_IN_OUT;  --INOUT 类型的参数是可以把数据带入给过程的
		SET P_IN_OUT = 1000;  --在存储过程中给INOUT类型的参数重新赋值，通过INOUT类型的参数把结果带回给调用环境；
		SELECT P_IN_OUT; --再次查询P_OUT值
	END;
--调用存储过程：
SET @P_IN_OUT = 10;
CALL proc4(@P_IN_OUT);
SELECT @P_IN_OUT;

六、在客户端中使用用户变量：用户变量一般以@开头

	方式1、
	SELECT "bb" INTO @str;-- 定义用户变量str,并赋值为"bb"
	SELECT @str;
	
	方式2、
	SET @str1="cc";
	SELECT @str1;
	
	方式3、
	SET @str2=1+12;
	SELECT @str2;

七、在存储过程中使用用户变量

	CREATE PROCEDURE proc5()  
		BEGIN
			SELECT CONCAT(@mystr, "123");
		END;
	--调用存储过程
	SET @mystr = "55";
	CALL proc5();

八、在存储过程中传递全局范围的用户变量

CREATE PROCEDURE proc6()  
	BEGIN
		SET @LAST_PROC = "proc6";
	END;
		
	CREATE PROCEDURE proc7()  
		BEGIN
			SELECT CONCAT("LAST_PROC值是：", @LAST_PROC);
		END;
	--调用存储过程
CALL proc6();
CALL proc7();

九、管理存储过程

• 查询数据库中的存储过程：SHOW PROCEDURE STATUS WHERE DB=“wf_work”;
• 查询存储过程的详细内容：SHOW CREATE PROCEDURE wf_work.proc7;
• MySQL不提供对存储过程进行修改；如果要修改，必须先删除原存储过程，重新编写代码；或者创建一个新的存储过程；
• 删除存储过程：drop PROCEDURE if EXISTS proc7;

十、MySQL存储过程变量的范围

有效范围是在该存储过程之内；即begin END范围中

CREATE PROCEDURE proc8()  
	BEGIN
		DECLARE x1 VARCHAR(20) DEFAULT "qq";  --x1的有效范围是在该存储过程之内			
		BEGIN 
			DECLARE x1 VARCHAR(20) DEFAULT "zz";
			SELECT x1;  --zz  优先使用内部块的变量值
			DECLARE x2 VARCHAR(20) DEFAULT "aa";
		END 
		SELECT x1;  --qq
		SELECT x2;  --无法访问x2的值，因为x2已经超出使用范围；
	END;
CALL proc8();

十一、在存储过程中使用流程控制语句

1、if...   ELSE...  
		CREATE PROCEDURE proc9(IN P_IN INT)  
			BEGIN
				DECLARE aa INT;
				SET aa = P_IN+1;
				if aa=100 then   -- 判断是否相等用=
					INSERT INTO t1 VALUES(0);
				END if;
				
				if aa=1 then   -- 判断是否相等用=
					INSERT INTO t1 VALUES(100);
				else
					INSERT INTO t1 VALUES(200);
				END if;
			END;
			CALL proc9(0);
	
2、case语句
		CREATE PROCEDURE proc10(IN P_IN INT)  
			BEGIN
				DECLARE aa INT;
				SET aa = P_IN+1;
				
				case aa
				when 1 then
					INSERT INTO t1 VALUES(1);
				when 2 then
					INSERT INTO t1 VALUES(2);
				ELSE 
					INSERT INTO t1 VALUES(3);
				END case;
			END;
			CALL proc10(0);
	
3、while循环语句
		CREATE PROCEDURE proc11()  
			BEGIN
				DECLARE aa INT DEFAULT 0;
				while aa<10 DO 
					INSERT INTO t1 VALUES(aa);
					SET aa=aa+1;
				END while;
			END;
			CALL proc11();
	
4、repeat... END repeat循环
		CREATE PROCEDURE proc12()  
			BEGIN
				DECLARE aa INT DEFAULT 0;
				repeat
					INSERT INTO t1 VALUES(aa);
					SET aa=aa+1;
					until aa>10  --退出循环的语句，末尾没有分号
				END repeat;
			END;
			CALL proc12();
	
5、loop...end loop 循环
		CREATE PROCEDURE proc13()  
			BEGIN
				DECLARE aa INT DEFAULT 0;
				loop1:loop 
					INSERT INTO t1 VALUES(aa);
					SET aa=aa+1;
					if aa>10 then 
						LEVEL loop1;  --退出循环
					END if;
				END loop;
			END;
			CALL proc13();