mysql存储过程小解

mysql 存储过程
1.创建语法
 delimiter $$                 --$$表示改变默认的分隔符，代表以下为存储过程，不然会以SQL的方式执行
 drop procedure if exists pro_name$$ --创建存储过程之前判断是否存在，存在则先删除
 create procedure pro_name(
 in paramIn type(length),                 --IN:输入参数，不写则默认为IN，并且在存储过程不能改变值
 out paramOut type(length),                 --Out：输出参数
 inout paranInOut type(length)                 --InOut 输入输出参数
 )
 begin
 ...SQL...
 end $$
 delimiter;                 --恢复mysql的默认分隔符
2.mysql变量类型
  <1>局部变量
    存储过程中用declare声明的变量。如 Declare name varchar(100) default 'dawa';
    default值可以不设置，默认为对象类型的系统默认值。
  <2>用户变量
    以"@"符号开头,如 set @name = 'dawa'或者在存储过程中select @name := ...
    用户变量只对当前当前用户使用的客户端有效。
  <3>会话变量
    以"@@"符号开头，如 set @@session.name = 'dawa' 或set session name = 'dawa';
    会话变量仅对连接的客户端有效。
  <4>全局变量
    以"@@"符号开头，如 set @@global.name = 'dawa' 或者set global name = 'dawa';
    对所有客户端生效，只有super权限才可以设置global变量
   declare专门用于声明局部变量，set用于设置变量值。注set设置值有两种方式 set param = value 或 set param := value；
3.常用语法
  <1>if...then...elseif...then...else...end if;
     示例：
        DELIMITER $$     
    DROP PROCEDURE IF EXISTS HelloWorld$$   
    CREATE PROCEDURE HelloWorld (
      param INT,
      OUT paramout INT,
      INOUT paramInOut INT
    ) 
    BEGIN
      IF param MOD 3 = 0 THEN 
        SET paramout := param DIV 3;
         SET paramInOut := param MOD 3;
      ELSEIF param MOD 2 = 0 THEN 
          SET paramout := param DIV 2;
          SET paramInOut := param MOD 2;
      ELSE
          SET paramout := param DIV 5;
          SET paramInOut := param MOD 5;
      END IF;

      SELECT paramout,paramInOut ;
    END $$   
    DELIMITER ;
  <2>循环：
    a.LOOP...END LOOP
    示例：
        DELIMITER $$     
        DROP PROCEDURE IF EXISTS HelloWorld$$   
        CREATE PROCEDURE HelloWorld (param INT, OUT paramout INT) 
        BEGIN
          DECLARE counter INT (10) ;
          SET counter := param ;
          SET paramout = 0 ;
          my_loop :
          LOOP
            SET paramout := paramout + counter ;
            SET counter := counter - 1 ;
            IF counter <= 0 
            THEN LEAVE my_loop ;
            END IF ;
          END LOOP my_loop ;
        END $$
        DELIMITER ;
    b.WHILE...DO...END WHILE
    示例：
        DELIMITER $$     
        DROP PROCEDURE IF EXISTS HelloWorld$$   
        CREATE PROCEDURE HelloWorld (param INT, OUT paramout INT) 
        BEGIN
          DECLARE counter INT (10) ;
          SET counter := param ;
          SET paramout = 0 ;
          WHILE
            counter >= 0 DO 
            SET paramout := paramout + counter ;
            SET counter := counter - 1 ;
          END WHILE ;
        END $$
        DELIMITER ;
    c.REPEAT...UNTIL...END REPAEAT
    示例：
        DELIMITER $$     
        DROP PROCEDURE IF EXISTS HelloWorld$$   
        CREATE PROCEDURE HelloWorld (param INT, OUT paramout INT) 
        BEGIN
          DECLARE counter INT (10) ;
          SET counter := param ;
          SET paramout = 0 ;
          REPEAT
            SET paramout := paramout + counter ;
            SET counter := counter - 1 ;
            UNTIL counter <= 0 
          END REPEAT ;

        END $$
        DELIMITER ;
  <3>游标Cursor：用于查询数据批处理，cursor使用方法
    declare cursor_name cursor  for select...from table;
    open cursor_name;
        my_loop:LOOP
            fetch cursor into variable;
            if..then
            leave my_loop;
            end if;
        end LOOP;
    close cursor_name;
    示例：
        DELIMITER $$

        USE `cssdj_shixi` $$

        DROP PROCEDURE IF EXISTS `test` $$

        CREATE DEFINER = `root` @`localhost` PROCEDURE `test` (OUT paramout VARCHAR (100)) 
        BEGIN
          DECLARE counter INT DEFAULT 0 ;
          DECLARE done INT DEFAULT 0;
          DECLARE notice CURSOR FOR SELECT operaName FROM g_prac_notice ;
          DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := 1;
          DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
          START TRANSACTION;
          OPEN notice ;
            notice_loop :LOOP
                FETCH notice INTO paramout ;
                SET counter := counter + 1 ;
                IF done = 1 OR counter = 1 THEN
                    LEAVE notice_loop;
                END IF;
            END LOOP notice_loop ;
          CLOSE notice ;
          COMMIT;
          SELECT paramout,done,counter;
        END $$

        DELIMITER ;
    
4.异常处理
  <1>语法：
    DECLARE 
     CONTINUE                       -- 继续
    |EXIT                 -- 退出
    |UNDO                -- 撤回，暂不支持
    HANDLER
    FOR
     mysql_error_code               -- mysql对应的错误代码
    |SQLSTATE[VALUE] sqlstate_value -- SQLState标准错误代码
    |condition_name                 -- 自定义异常
    |SQLWARNING                     -- SQLSTATE中以"01"开头的异常,默认继续执行
    |NOT FOUND             -- SQLSTATE中以"01"开头的异常，默认继续执行
    |SQLEXCEPTION            -- SQLSTATE中不是以"00"，"01"，"02"开头的其他异常
    statement            -- 执行语句：如 set done = 1;

      condition_name：mysql_error_code及sqlstate_value值阅读性比较差
    示例：
        #原来的
        DECLARE CONTINUE HANDLER FOR 1216 statement;
        #改变的
        DECLARE foreign_key_error CONDITION FOR 1216 ;
        DECLARE CONTINUE HANDLER FOR foreign_key_error statement;

 <2>示例：
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := 1;
     DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;