mysql存储过程的学习笔记

• 优点：
• 通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。 但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。 在编译存储过程之后，MySQL将其放入缓存中。 MySQL为每个连接维护自己的存储过程高速缓存。 如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。
• 存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。
• 存储的程序对任何应用程序都是可重用的和透明的。 存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。
• 存储的程序是安全的。 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。
• 缺点：
• 如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果您在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不当于逻辑运算。
• 存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难。
• 很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能会导致应用程序开发和维护阶段的问题。

三种参数模式

• IN - 是默认模式。在存储过程中定义IN参数时，调用程序必须将参数传递给存储过程。 另外，IN参数的值被保护。这意味着即使在存储过程中更改了IN参数的值，在存储过程结束后仍保留其原始值。换句话说，存储过程只使用IN参数的副本。
• OUT - 可以在存储过程中更改OUT参数的值，并将其更改后新值传递回调用程序。请注意，存储过程在启动时无法访问OUT参数的初始值。
• INOUT - INOUT参数是IN和OUT参数的组合。这意味着调用程序可以传递参数，并且存储过程可以修改INOUT参数并将新值传递回调用程序。

DELIMITER $$  -- 重新定义分隔符 将 ; 定义成$$


CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE testStored( IN expression  INT,OUT expressions FLOAT ) -- 参数模式，默认是IN，多个参数用，分开
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
DECLARE x1,y1 INT DEFAULT 0; -- 声明两个变量 并将其默认值设置为0;
SET testV = 10;  -- 给testV分配一个变量

SELECT * FROM table_name WHERE userId=23;

IF expression THEN  -- 条件变量在使用时，需声明过
operation;
ELSEIF expressions THEN   -- 条件变量在使用时，需声明过
ohter-operations;
...
ELSE
      ohter-operation;
END IF;
--   CASE 的使用
/* if和case 的区别
当将单个表达式与唯一值的范围进行比较时，简单CASE语句比IF语句更易读。另外，简单CASE语句比IF语句更有效率。
当您根据多个值检查复杂表达式时，IF语句更容易理解。
如果您选择使用CASE语句，则必须确保至少有一个CASE条件匹配。否则，需要定义一个错误处理程序来捕获错误。IF语句则不需要处理错误。
在大多数组织(公司)中，总是有一些所谓的开发指导文件，为开发人员提供了编程风格的命名约定和指导，那么您应参考本文档并遵循开发实践。
在某些情况下，IF和CASE混合使用反而使您的存储过程更加可读和高效。
*/
  CASE  case_expression
WHEN when_expression_1 THEN commands
WHEN when_expression_2 THEN commands
...
ELSE other_commands -- 都不满足，执行此语句
  END CASE;
  --  While
  WHILE expression<1 DO   
   SET expression =expression+1;
END WHILE

-- repeat
/*
先，MySQL执行语句，然后评估求值表达式(expression)。如果表达式(expression)的计算结果为FALSE，
则MySQL将重复执行该语句，直到该表达式计算结果为TRUE。
LEAVE语句用于立即退出循环，而无需等待检查条件。LEAVE语句的工作原理就类似PHP，C/C++，Java等其他语言的break语句一样。
ITERATE语句允许您跳过剩下的整个代码并开始新的迭代。ITERATE语句类似于PHP，C/C++，Java等中的continue语句。
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;
    在LOOP语句之前放置一个loop_label循环标签。
如果x的值大于10，则由于LEAVE语句，循环被终止。
如果x的值是一个奇数，ITERATE语句忽略它下面的所有内容，并开始一个新的迭代。
如果x的值是偶数，则ELSE语句中的块将使用偶数构建字符串。
*/
REPEAT
operations;
UNTIL expression  -- 要注意的是UNTIL表达式中没有分号(;)。
END REPEAT
    
    END$$


DELIMITER ;

=================================================================

Repeat的使用

DELIMITER $$


USE `coinorder`$$


DROP PROCEDURE IF EXISTS `insertChangeHL`$$


CREATE DEFINER=`coinorder_ln`@`%` PROCEDURE `insertChangeHL`()
BEGIN
-- 声明变量 
    DECLARE aid INT ; -- 要更新0的 id 
    DECLARE bid INT ; -- 要更新新数值的 id
    DECLARE aMoney FLOAT ;    -- 要更新的old money
    DECLARE bMoney FLOAT ;  
    DECLARE uid INT ; -- 用户信息 id
    DECLARE done INT DEFAULT 0;
    DECLARE result_code INT DEFAULT 0;
     /* 声明游标 */
   
    DECLARE insertRs CURSOR FOR SELECT a.id AS aid, a.userId AS uid,a.availBalance AS aMoney FROM  t_account_spot_test a WHERE a.type = 6  AND a.userId NOT IN (SELECT c.userId FROM  t_account_spot1 c  WHERE c.type=8);
     /* 异常处理 */    
   
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code=1;  -- 在执行过程中出任何异常设置result_code为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   -- 如果表中没有下一条数据则置为1
    START TRANSACTION; -- 开始事务
    
    /*打开要插入的游标*/
    OPEN insertRs; 
    START TRANSACTION; -- 开始事务
    /* 逐个取出当前记录LingQi字段的值，需要进行更新操作 */
    FETCH NEXT FROM insertRs INTO aid, uid, aMoney; 
    /* 遍历数据表 */
    REPEAT
IF NOT done THEN
INSERT INTO t_account_spot1 (userId,availBalance,frozenBlance,`type`,`version`) VALUES(uid,535*aMoney,0,8,0);
UPDATE t_account_spot_test a SET a.availBalance = 0 WHERE a.id = aid;
END IF;
FETCH NEXT FROM insertRs INTO aid, uid, aMoney;
    UNTIL done END REPEAT;
    /* 关闭插入游标 */
    CLOSE insertRs;
    
    IF result_code = 1 THEN -- 可以根据不同的业务逻辑错误返回不同的result_code，这里只定义了1和0
ROLLBACK;
    ELSE
COMMIT;
    END IF;
    
    END$$
DELIMITER ;

==================================================================

游标的使用

DELIMITER $$


USE `coinorder`$$


DROP PROCEDURE IF EXISTS `updateChange6`$$


CREATE  PROCEDURE `updateChange6`(t,h)
BEGIN
    
    -- 声明变量 
    DECLARE aid INT ; -- 要更新0的 id 
    DECLARE bid INT ; -- 要更新新数值的 id
    
    DECLARE aMoney FLOAT ;    -- 要更新的old money
    DECLARE bMoney FLOAT ;
     
    DECLARE uid INT ; -- 用户信息 id
    DECLARE done INT DEFAULT 0;
    DECLARE result_code INT DEFAULT 0;
     /* 声明游标 */
    DECLARE updateRs CURSOR FOR SELECT a.id AS aid ,b.id AS bid , a.availBalance AS aMoney,b.availBalance AS bMoney FROM  t_account_spot  a , t_account_spot b WHERE a.userId=b.userId   AND b.type=8 AND a.type=6;    
    
     /* 异常处理 */    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code=1; -- 在执行过程中出任何异常设置result_code为1
    /*打开要更新的游标*/
    OPEN updateRs; 
    
    START TRANSACTION; -- 开始事务
    /* 逐个取出当前记录字段的值，需要进行更新操作 */
    
    /* 遍历数据表 143 */
    REPEAT
  FETCH NEXT FROM updateRs INTO aid,bid,aMoney,bMoney; 
  UPDATE t_account_spot  b SET b.availBalance = (bMoney+535*aMoney) WHERE b.id = bid;
  UPDATE t_account_spot  a SET a.availBalance = 0 WHERE a.id = aid;
    UNTIL done END REPEAT;
    /* 关闭更新游标 */
    CLOSE updateRs;
    
    IF result_code = 1 THEN -- 可以根据不同的业务逻辑错误返回不同的result_code，这里只定义了1和0
ROLLBACK;
    ELSE
COMMIT; 
    END IF;
    END$$


DELIMITER ;

 IF(C != 1) THEN 
 SIGNAL SQLSTATE '45000'  -- 返回错误或者警告
 SET MESSAGE_TEXT = 'Order No not found in orders table';
 END IF;

要列出您有权访问的数据库的所有存储过程，请使用SHOW PROCEDURE STATUS  末尾可以使用LIKE和Where过滤

要显示特定存储过程的源代码，请使用SHOW CREATE PROCEDURE