阅读数:178 评论数:0

一、存储过程的意义

1、什么是存储过程

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过存储过程的名字并给定参数来调用执行。

2、使用权衡

（1）运行速度：大多数据高级的数据库系统都有statement cache，所以编译sql的花费没什么影响。但是执行存储过程要比直接执行sql花费更多（检查权限等），所以对于很简单的sql，存储过程没有什么优势。

（2）网络负荷：如果没有多次数据交互，存储过程与直接sql是一样的。

（3）安全机制：对于传统的c/s结构，连接数据库的用户可以不同，所以安全机制有用，但是在web的三层架构中，数据库用户基本上就是固定的一个，所以安全机制有点多余。

（4）开发调试：存储过程的开发调试比一般程序困难。

数据库中的存储过程可以看作是对编辑中面向对象方法的模拟，它允许控制数据的访问方式。

在命令行中创建存储过程时，一定要先改变默认分隔符

#  改变分隔符为//，这样可以在存储过程中使用分号
delimiter //

# 只有一个参数total,out表示该参数将被返回或传出，也
# 可以为in，表示传入的参数,inout表示传入后再修改后传出
# float表示参数的类型,如果有多个参数，可以用逗号分隔
create procedure total_orders (out total float)
BEGIN
  select sum(amount) into total from orders;
END
//

# 重新改变分隔符为;
delimiter ;

调用
call total_orders(@t);
查看结果
select @t;
查看定义
show create procedure total_orders;
删除
drop procedure total_orders;

二、基本语法

1、变量及赋值

不区分大小写。

（1）局部变量

用户自定义，在begin/end 块中有效。

--声明变量 
declare var_name type [default var_value];
--举例：
declare nickname varchar(32);

（2）用户变量

用户自定义，当前会话有效

--语法：
@var_name
--不需要提前声明，使用即声明

DELIMITER $$
CREATE PROCEDURE test.sp_var02()
BEGIN
	SET @nickname = 'zk';
END$$
DELIMITER ;
CALL sp_var02() ;
SELECT @nickname; 

（3）全局变量

由系统提供

--语法：
@@global.var_name

select @@global.character_set_client; 

2、出参和入参

in | out | inout param_name type

DROP PROCEDURE IF EXISTS `sp_param01`;

DELIMITER $$
CREATE PROCEDURE sp_param01(IN age INT)
BEGIN
	SET @user_age = age;
END$$
DELIMITER ;
CALL sp_param01(10);
SELECT @user_age;
-- OUT类型，只负责输出！
-- 如果查询的是表，且表中列明和参数名相同，那么就换参数或者给列起别名
DELIMITER $$

CREATE PROCEDURE sp_param02(OUT dept_no INT(11))
BEGIN
	SET dept_no = 10;
END$$
DELIMITER ;

-- 测试
SET @dept_no = 100;
CALL sp_param02(@dept_no);
SELECT @dept_no;
-- INOUT类型 
DELIMITER $$
CREATE PROCEDURE sp_param03(INOUT user_name VARCHAR(64))
BEGIN
	SET user_name = CONCAT('hello' ,user_name);
END$$
DELIMITER ;

SET @user_name = '小明';
CALL sp_param03(@user_name);
SELECT @user_name;

3、判断

（1）if

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

DELIMITER $$
CREATE PROCEDURE sp_hire_if()
BEGIN
	DECLARE result1 VARCHAR(32);
	DECLARE result2 VARCHAR(32);
	IF EXISTS(SELECT 1)  -- 是否存在
		THEN SET result1 = 'EXISTS';
	END IF;
	
	IF result2 <> '' -- 判断相等
	    THEN SET result2 = 'cond1';
	ELSE 
	     SET result2 = 'cond2';
	
	END IF;
	SELECT result1;
	SELECT result2;
END$$
DELIMITER ;

CALL sp_hire_if();

（2）case

--语法一
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

--语法二
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

DELIMITER $$
CREATE PROCEDURE sp_hire_case()
BEGIN
	DECLARE result VARCHAR(32);
	DECLARE message VARCHAR(64);
	CASE
	WHEN TIMESTAMPDIFF(YEAR,'2001-01-01',NOW()) > 40 
		THEN 
			SET result = '元老';
			SET message = '老爷爷';
	WHEN TIMESTAMPDIFF(YEAR,'2001-01-01',NOW()) > 38
		THEN 
			SET result = '老员工';
			SET message = '油腻中年人';
	ELSE 
		SET result = '新手';
		SET message = '萌新';
	END CASE;
	SELECT result;
END$$
DELIMITER ;
CALL sp_hire_case();

4、循环

（1）Loop

loop是死循环，需要手动退出循环，我们可以使用leave来退出。

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

-- 循环打印1到10
-- leave控制循环的退出
DELIMITER $$
CREATE
    PROCEDURE sp_flow_loop01()
    BEGIN
	DECLARE c_index INT DEFAULT 1;
	DECLARE result_Str VARCHAR(256) DEFAULT '1';

	cnt:LOOP
		IF c_index >=10
			THEN LEAVE cnt; -- 退出
		 END IF;
		 SET c_index = c_index+1;
		 SET result_str = CONCAT(result_str,',',c_index);
	END LOOP cnt;
	SELECT result_str;
    END$$
DELIMITER ;

CALL sp_flow_loop();
-- iterate + leave控制循环
DELIMITER $$
CREATE PROCEDURE sp_flow_loop02()
BEGIN
	DECLARE c_index INT DEFAULT 1;
	DECLARE result_str  VARCHAR(256) DEFAULT '1';
	cnt:LOOP

		SET c_index = c_index + 1;
		SET result_str = CONCAT(result_str,',',c_index);
		IF c_index < 10 THEN 
			ITERATE cnt; 
		END IF;
		--  当c_index < 10为false时执行
		LEAVE cnt;
		
	END LOOP cnt;
	SELECT result_str;
	
END$$

CALL sp_flow_loop02();

（2）repeat

[begin_label:] REPEAT
    statement_list
UNTIL search_condition	-- 直到…为止，才退出循环
END REPEAT [end_label]


DELIMITER $$
CREATE PROCEDURE sp_flow_repeat()
BEGIN
	DECLARE c_index INT DEFAULT 1;
	-- 收集结果字符串
	DECLARE result_str VARCHAR(256) DEFAULT '1';
	count_lab:REPEAT
		SET c_index = c_index + 1;
		SET result_str = CONCAT(result_str,',',c_index);
		UNTIL c_index >= 10
	END REPEAT count_lab;
	SELECT result_str;
END$$

CALL sp_flow_repeat();

（3）while

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]
-- 循环打印1到10
DELIMITER $$
CREATE PROCEDURE sp_flow_while()
BEGIN
	DECLARE c_index INT DEFAULT 1;
	-- 收集结果字符串
	DECLARE result_str VARCHAR(256) DEFAULT '1';
	WHILE c_index < 10 DO
		SET c_index = c_index + 1;
		SET result_str = CONCAT(result_str,',',c_index);
	END WHILE;
	SELECT result_str;
END$$
CALL sp_flow_while();

5、游标和handler

用游标得到某一个结果集，逐行处理数据。

在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement
-- 打开语法
OPEN cursor_name
-- 取值语法
FETCH cursor_name INTO var_name [, var_name] ...
-- 关闭语法
CLOSE cursor_name

三、常用使用实例

1、循环遍历查询的结果集

-- 建立存储过程之前需要判断该存储过程是否存在，若存在则删除
DROP PROCEDURE IF EXISTS update_album_count;
-- 创建存储过程，update_album_count为存储过程名
CREATE PROCEDURE update_album_count()
-- 标识存储过程开始
BEGIN
	-- 定义变量
	DECLARE s int DEFAULT 0;
	DECLARE memberId int;
	DECLARE count int;
	-- 定义游标，并将sql结果集赋值到游标中，report为游标名
	DECLARE report CURSOR FOR SELECT member_id, COUNT(member_id)
							  FROM member_album 
							  GROUP BY member_id
							  HAVING COUNT(member_id) > 0
							  ORDER BY member_id ASC;
	-- 声明当游标遍历完后将标志变量置为某个值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
	-- 打开游标
	OPEN report;
	-- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
	FETCH report INTO memberId, count;
	-- 当s不等于1时，也就是未遍历完时，会一直循环
	WHILE s <> 1 DO
	-- 执行业务逻辑
	UPDATE member_album_count t SET t.img_pass_count = count WHERE t.member_id = memberId;
	-- 当s等于1时代表遍历已完成，退出循环
	FETCH report INTO memberId, count;
	END WHILE;
	-- 关闭游标
	CLOSE report;
-- 标识存储过程结束
END;