mysql 存储过程详解

mysql存储过程

优点

• 增强sql语的功能和灵活性
• 标准组件式编程
• 较快的执行速度
• 减少网络流量

创建

delimiter // 

    create procedure test_name(IN in_param int)
    begin
    declare param int default 1;
    
    select count(*) from test_name into total_count;
    select in_param;
    end
//
delimiter;

参数：
delimiter：声明分隔符
into : 赋值到变量
IN：参数的值必须在调用时指定，不能被返回，为默认值

OUT：可在存储过程之内部改变并返回，只能当转出参数，不接受外部传入的参数，只能使用已经申明的变量

declare :定义局部变量

INOUT：调用时指定，可被改变、可返回

变量赋值

局部变量

> declare 声明  
> 语句执行完就消失
declare user_id int default 50;

用户变量

> 作用于整个连接
set @name = 'name';
select @name:= 213;

会话变量

全局变量

set @param_name = "param_name"

#在MySQL客户端使用用户变量

SELECT 'Hello World' into @x;
SELECT @x;

SET @y='Goodbye Cruel World';
SELECT @y;

SET @z=1+2+3;
SELECT @z;

#在存储过程中使用用户变量
CREATE PROCEDURE GreetWorld() SELECT CONCAT(@greeting,' World');
SET @greeting='Hello';
CALL GreetWorld();

#在存储过程间传递全局范围的用户变量
CREATE PROCEDURE p1() SET @last_proc='p1';
CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);
CALL p1();
CALL p2();

查看mysql存储过程

show procedure status;
select * from information_schema.routines;
select * from mysql.proc;

存储过程的控制语句

#变量作用域

DELIMITER //
  CREATE PROCEDURE proc()
    BEGIN
      DECLARE x1 VARCHAR(5) DEFAULT 'outer';
        BEGIN
          DECLARE x1 VARCHAR(5) DEFAULT 'inner';
          SELECT x1;
        END;
      SELECT x1;
    END;
    //
DELIMITER ;

#条件语句 IF-THEN-ELSE
DROP PROCEDURE IF EXISTS proc3;
DELIMITER //
CREATE PROCEDURE proc3(IN parameter int)
  BEGIN
    DECLARE var int;
    SET var=parameter+1;
    IF var=0 THEN
      INSERT INTO t VALUES (17);
    END IF ;
    IF parameter=0 THEN
      UPDATE t SET s1=s1+1;
    ELSE
      UPDATE t SET s1=s1+2;
    END IF ;
  END ;
  //
DELIMITER ;

#CASE-WHEN-THEN-ELSE语句
DELIMITER //
  CREATE PROCEDURE proc4 (IN parameter INT)
    BEGIN
      DECLARE var INT;
      SET var=parameter+1;
      CASE var
        WHEN 0 THEN
          INSERT INTO t VALUES (17);
        WHEN 1 THEN
          INSERT INTO t VALUES (18);
        ELSE
          INSERT INTO t VALUES (19);
      END CASE ;
    END ;
  //
DELIMITER ;

# 循环语句 WHILE-DO…END-WHILE

DELIMITER //
  CREATE PROCEDURE proc5()
    BEGIN
      DECLARE var INT;
      SET var=0;
      WHILE var<6 DO
        INSERT INTO t VALUES (var);
        SET var=var+1;
      END WHILE ;
    END;
  //
DELIMITER ;


    REPEAT...END REPEAT 此语句的特点是执行操作后检查结果
DELIMITER //
  CREATE PROCEDURE proc6 ()
    BEGIN
      DECLARE v INT;
      SET v=0;
      REPEAT
        INSERT INTO t VALUES(v);
        SET v=v+1;
        UNTIL v>=5
      END REPEAT;
    END;
  //
DELIMITER ;


# 循环语句LOOP...END LOOP
DELIMITER //
  CREATE PROCEDURE proc7 ()
    BEGIN
      DECLARE v INT;
      SET v=0;
      LOOP_LABLE:LOOP
        INSERT INTO t VALUES(v);
        SET v=v+1;
        IF v >=5 THEN
          LEAVE LOOP_LABLE;
        END IF;
      END LOOP;
    END;
  //
DELIMITER ;

ITERATE迭代



#ITERATE 通过引用复合语句的标号,来从新开始复合语句
DELIMITER //
  CREATE PROCEDURE proc8()
  BEGIN
    DECLARE v INT;
    SET v=0;
    LOOP_LABLE:LOOP
      IF v=3 THEN
        SET v=v+1;
        ITERATE LOOP_LABLE;
      END IF;
      INSERT INTO t VALUES(v);
      SET v=v+1;
      IF v>=5 THEN
        LEAVE LOOP_LABLE;
      END IF;
    END LOOP;
  END;
  //
DELIMITER ;

游标

> 数据缓冲区的思想，用来存放sql语句执行的结果，游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。

优点

• 针对行操作，可以满足对某个结果行进行特殊的操作。

缺点

• 也就是只能一行一行操作，在数据量大的情况下，是不适用的，速度过慢。而游标使用会有死锁，影响其他的业务操作，不可取。 当数据量大时，使用游标会造成内存不足现象

使用场景

• 循环处理、存储过程、函数中

使用步骤

-- 定义游标
declare cursor_name CURSOR for select * from users;
-- 打开游标 
open cursor_name
-- 使用游标
-- 需要使用fetch 取出数据， 存放到定义的变量中
-- FETCH [NEXT | PRIOR | FIRST | LAST] FROM <游标名> [ INTO 变量名1,变量名2,变量名3[,…] ]
declare id int ;
declare name varchar;
fetch next from cursor_name into id,name;
-- 关闭游标
close cursor;
-- 释放游标
deallocate cursor_name

参考

MySql存储过程（一）：简介
mysql全家桶（四）存储过程