[大数据]存储过程与存储函数

存储过程与存储函数

存储函数与存储过程概述

存储过程和存储函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值，而存储过程没有

创建存储过程

CREATE procedure procedure_name([proc_paramter[,.....]])
begin
	--- sql语句
end;	

示例：

delimiter $
create procedure pro_test1()
begin
	select "hello Mysql";
end$
delimiter;

DELIMITER
该关键字用来声明SQL语句的分隔符，告诉Mysql解释器，该命令是否已经结束运行了，mysql是否可以执行了。默认情况下，delimiter是分号。在命令行客户端中，如果一行命令以分号结束，那么回车后，mysql将执行该命令

调用存储过程

call procedure_name();

查看存储过程

-- 查询db_name数据库中的所有的存储过程
select name from mysql.procc where db="db_name"
--查询存储过程的状态
show procedure status;
-- 查询某个存储过程的定义
show create procedure test.pro_test1\G;

删除存储过程

DROP PROCEDURE procedure_name;

语法

存储过程是可以编译的，意味着可以使用变量，表达式，控制结构，来完成比较复杂的功能

变量

• DECLARE
  通过Declare可以定义一个局部变量，该变量的作用范围是能在begin…end块中。

DECLARE var_name[,....] type[DEFAULT value]
例子:
CREATE PROCEDURE pro_test1()
BEGIN
DECLARE num INT DEFAULT 10;
SELECT CONCAT("number的值为:",num);
END
CALL pro_test1();

• SET
  直接使用SET，可以赋常量或者表达式，具体语法；

SET val_name = expr [,var_name = expr]...
例如:
CREATE PROCEDURE pro_test2()
BEGIN 
DECLARE num INT DEFAULT 0;
SET num = num+10;
END

也可以通过selec…into方式进行赋值操作

例子:
CREATE PROCEDURE pro_test3()
BEGIN
DECLARE num INT DEFAULT 0;
SELECT COUNT(*) INTO num FROM sys_config; 
SELECT CONCAT("city表中的数据为",num);
END

CALL pro_test3();

if条件判断

语法结构：

if search_condition then statement_list
[else if search_condition then statement_list]...
[else statement_list]
end if;

需求：

根据自定义的身高变量，判断当前身高所属的身材类型
180 及以上----------->身材高挑
170 - 180 ------------>标准身材
170以下 ----------->一般身材
create PROCEDURE pro_test4()
BEGIN
	DECLARE height INT DEFAULT 175;
	DECLARE description VARCHAR(50) DEFAULT "";
	IF height >= 180 then
		SET description = "身材高挑";
	ELSEIF height >=170 AND height < 180 THEN
	 SET 	description="标准身材";
	ELSE
		SET description = "一般身材";
	END IF;
SELECT CONCAT("身高",height,"身材类型为",description);
END		

CALL pro_test4();

传递参数

语法格式:

create procedure procedure_name([in/out/inout] 参数名 参数类型)
...
IN:该参数可以作为输入，也就是需要调用方传入值,默认
OUT:该参数作为输出，也就是该参数可以作为返回值
INTOUT:既可以作为输入参数，也可以作为输出参数

IN：
需求：

根据定义的身高变量，判断当前身高所属的身材类型
CREATE PROCEDURE pro_test5(IN height INT)
BEGIN
	DECLARE desceiption VARCHAR(50) DEFAULT '';
	IF height > 180 THEN
		SET desceiption = "高挑身材";
	ELSEIF height >=170 AND height <= 180 THEN
			SET desceiption="标准身材";
	ELSE 
				SET desceiption = "一般身材";
	END IF;
	SELECT CONCAT("身高",height,"类型",desceiption);
	END
	
	CALL pro_test5(181);

OUT：
根据传入的身高变量，获取当前身高的所属的身材类型（返回值）

CREATE PROCEDURE pro_test6(IN height INT,OUT descroption VARCHAR(50))
	BEGIN
	IF height > 180 THEN
		SET descroption = "高挑身材";
	ELSEIF height >=170 AND height <= 180 THEN
			SET descroption="标准身材";
	ELSE 
				SET descroption = "一般身材";
	END IF;
END	


CALL pro_test6(181,@descroption)

SELECT @descroption

case语法格式

方式1:
CASE case_value
	WHEN when_value THEN statemant_list
	[WHEN_VALUE THEN statement_list]...
	[ELSE statement_list]
END CASE
方式2:
CASE
	WHEN search_condition THEN statement_list
	[WHEN search_condition THEN statement_list]...
	[ELSE statement_list]
END CASE		

需求：
给定一个月份，计算出所在的嫉妒

create PROCEDURE pro_test7(IN mon INT)
BEGIN
DECLARE result VARCHAR(10);
CASE
	WHEN mon >=1 AND mon <=3 THEN
		set result = "第一季度";
	WHEN mon >=4 AND mon <=6 THEN
		set result = "第二季度";
		WHEN mon >=7 AND mon <=9 THEN
		set result = "第三季度";
			WHEN mon >=10 AND mon <=12 THEN
		set result = "第四季度";	
	ELSE
				SET result = "第四季度";
END CASE;
SELECT CONCAT("传递的月份为",result);				
END


CALL pro_test7(6)

while语法

语法结构

while search_condition do
	statement_list
end while	

需求

计算从1加到n的值
create PROCEDURE pro_test8(IN n INT)
BEGIN 
	DECLARE total INT DEFAULT 0;
	DECLARE starte INT DEFAULT 0;
	WHILE starte <= n DO
	SET total = total + starte;
	SET starte = starte + 1;
END WHILE;

SELECT CONCAT("statter",starte,"total",total);

END

CALL pro_test8(3)

repeat结构

在条件的循环控制语句，当满足条件时退出。while是满足条件才执行，repeat是满足条件就退出循环。
语法结构：

Repeat 
	statement_list
	until search_condition
END REPEAT;	

需求：
计算从1到n的值
示例：

CREATE PROCEDURE pro_test9(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	


CALL pro_test9(100)

loop循环

LOOP实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用LEVEL语句实现，具体语法如下:

[begin_label] LOOP
	statement_list
END LOOP [end_label]	

如果不在statement_list中增加退出循环的语句，那么LOOP语句可以用来实现简单的死循环

leave语句

用来从标注的流程构造中退出，通常BEGIN…END或者循环一起使用。下面是一个使用LOOP和LEAVEL的简单例子，退出循环：

 BEGIN
	DECLARE total INT DEFAULT 0;
	
	c:LOOP
		SET total = total + n;
		SET n = n - 1;
		if n <= 0 THEN
		 LEAVE c;
		END IF; 
	END loop c;	
	SELECT total;
END	

CALL pro_test10(10)

游标

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环处理。光标的使用包含光标的声明，OPEN FETCH 和close，其语法分别如下
声明光标:

DECLARE coursor_name COURSOR FOR select_statement;

OPEN光标：

OPEN coursor_name;

FETCH 光标:

FETCH coursor_name INTO var_name[,var_name]....

CLOSE光标:

CLOSE coursor_name;