mysql存储过程简记

何为存储过程？

存储过程是一种可以反复调用的数据库程序。
存储过程是为了完成特殊的计算，经编译保存在数据库中，用户指定存储过程的名字并给定参数来调用执行，存储过程实际上就是sql语言层面上的封装与重用。

优点

• 有助于提高程序性能。 当创建存储过程时 ，编译后被保存在数据库中。但mysql实现的存储过程不同 ，MySQL的存储过程按需编译，编译之后放在缓存中。MySQL为存储过程提供高速缓存
• 存储过程有助于减少应用程序和数据库中的流量
• 存储的过程对任何应用都是可重用的和透明的。
• 存储的过程是安全的。

缺点

• 大量存储过程会使得内存增加，CPU的使用率可能也会飙升
• 存储过程的构造使开发具有业务逻辑的存储过程变的更加困难
• 存储过程难以调试
• 开发和维护存储过程并不容易

存储过程语法

创建存储过程

DELIMITER 与存储过程语法无关

DELIMITER //
CREATE PROCEDURE getAllProducts()
BEGIN
	SELECT * FROM test.comment_target;
END
DELIMITER ;

使用存储过程

调用存储过程

call getAllProducts

drop存储过程

DROP procedure IF EXISTS `getAllProducts`;

声明变量

DECLARE v_name varchar(22) DEFAULT 'default_value';

变量类型，Mysql变量如INT，VARCHAR，DATETIME 也可DEFAULT NULL

DECLARE a,b INT(22) DEFAULT NULL;
SET a=1;
SELECT COUNT(*) INTO total_products FROM products;

存储过程参数

1. IN参数

CREATE PROCEDURE getAllProducts(IN productCode varchar(22))
BEGIN
	SELECT * FROM test.comment_target where p_code= productCode ;
END

调用

call getAllProducts('1100');

2. OUT 参数

CREATE PROCEDURE CountByStatus( IN orderStatus VARCHAR(25), OUT total INT)
BEGIN
 SELECT count(*)
 INTO total
 FROM comment_target WHERE  score_target>0;
END

调用

call CountByStatus('shipped',@total);
select @total;

3. INOUT参数

CREATE PROCEDURE setCount(INOUT count INT(4),IN inc INT(4))
BEGIN
 SET count = count + inc;
END

调用

SET @count=1;
CALL setCount(@count,1);
CALL setCount(@count,3);
CALL setCount(@count,4);
SELECT @count;

存储过程返回多个参数

即多个out参数，此处不演示

存储过程IF语法

1. IF语法

IF expression THEN 
   statements;
END IF;

2. IF-ELSE语法

IF expression THEN
   statements;
ELSE
   else-statements;
END IF;

3. IF ELSEIF ELSE语法

IF expression THEN
   statements;
ELSEIF elseif-expression THEN
   elseif-statements;
...
ELSE
   else-statements;
END IF;

存储过程CASE语法

CASE  case_expression
   WHEN when_expression_1 THEN commands
   WHEN when_expression_2 THEN commands
   ...
   ELSE commands
END CASE;

存储过程中的MySQL循环

1. WHILE循环

WHILE expression DO
   statements
END WHILE

注意： 需要初始化值，否则容易死循环

2. REPEAT循环

REPEAT
 statements;
UNTIL expression
END REPEAT

注意：UNTIL语句后没有;
3. LOOP，LEAVE和ITERATE语句

• LEAVE用于退出循环，类似break
• ITERATE用于跳过当前循环进行下一个，类似continue
• LOOP用于反复执行某个代码块
  例如：

CREATE PROCEDURE testLoop()
 BEGIN
 DECLARE x INT;
        DECLARE str  VARCHAR(255);
 SET x = 1;
        SET str =  '';
 loop_label:  LOOP
 IF  x > 10 THEN 
 LEAVE  loop_label;
 END  IF;
 SET  x = x + 1;
 IF (x mod 2) THEN
     ITERATE  loop_label;
 ELSE
    SET  str = CONCAT(str,x,',');
 END IF;
    END LOOP;    
    SELECT str;
END;

MySQL游标

要处理存储过程中的结果集，则需要使用游标。
游标的作用：迭代查询返回的一组行，并相应处理每一行，游标为只读

• 只读：无法通过光标更新基础表中的数据。
• 不可滚动：只能按照SELECT语句确定的顺序获取行。不能以相反的顺序获取行。 此外，不能跳过行或跳转到结果集中的特定行。
• 敏感：有两种游标：敏感游标和不敏感游标。敏感游标指向实际数据，不敏感游标使用数据的临时副本。敏感游标比一个不敏感的游标执行得更快，因为它不需要临时拷贝数据。但是，对其他连接的数据所做的任何更改都将影响由敏感游标使用的数据，因此，如果不更新敏感游标所使用的数据，则更安全。 MySQL游标是敏感的。

语法：

DECLARE cursor_name CURSOR FOR SELECT_statement;

游标声明必须在变量之后，游标必须始终与Select 语句关联
使用OPEN语句打开游标。OPEN语句初始化游标的结果集，因此您必须在从结果集中提取行之前调用OPEN语句。

OPEN cursor_name INTO variables list;

FETCH检索光标指向下一行，并将光标移动到结果集中的下一行

FETCH cursor_name INTO variables list;

关闭游标

CLOSE  cursor_name ;

使用MySQL游标时，还必须声明一个NOT FOUND处理程序来处理当游标找不到任何行时的情况。 因为每次调用FETCH语句时，游标会尝试读取结果集中的下一行。

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

DECLARE  v_finished INTEGER DEFAULT 0;
DECLARE v_email VARCHAR(255) DEFAULT "";

-- 定义员工邮箱的游标
DECLARE email_cursor CURSOR FOR
	SELECT email FROM employees;
-- 定义NOT FOUND 处理
DECLARE CONTINUE HANDLER 
FOR NOT FOUND SET v_finished = 1;	

-- 打开游标
OPEN email_cursor;
-- 迭代电子邮件列表，使用分隔符连接每个电子邮件：
get_email:LOOP
 FETCH email_cursor INTO v_email;
 IF v_finished=1 THEN 
 LEAVE get_email;
 END IF;
 -- 构建电子邮箱列表
 set email_list= CONCAT(v_email,",",email_list);
END LOOP get_email;
-- 关闭游标
CLOSE email_cursor;