mysql 存储过程

创建

• ‘；’为创建存储过程的结束标志，使用DELIMITER $$可更改标志
• 格式

CREATE PROCEDURE <procedure_name>([<IN | OUT | INOUT> <param_name> <param_type>])
BEGIN
sql
END;

CREATE PROCEDURE myprocedure(IN param INTEGER)
BEGIN
     SELECT * FROM tb_role WHERE tb_role.role_id = param;
END;

删除

DROP PROCEDURE myprocedure;

调用

CALL myprocedure(1);

检索数据库中的存储过程

SHOW PROCEDURE STATUS WHERE db='database_name';

查看存储过程

SHOW CREATE PRECEDURE database_name.procedure_name;

参数

参数类型：IN、OUT、INOUT代表输入、输出、输入输出

IN

• 可以为常量或变量
• 输入参数只作为参数传递，不改变自身值

CREATE PROCEDURE testparam(IN param INTEGER)
BEGIN
     SELECT param;
     SET param = 2;
     SELECT param;
END;
------------
SET @param = 1;
CALL testparam(@param);
SELECT @param;  --此时param为1，输入参数只作为参数传递，不改变自身值

OUT

• 只能为变量
• 输出参数会改变参数值

CREATE PROCEDURE testparam(OUT param INTEGER)
BEGIN
     SELECT param; -- 此处为null，因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
     SET param = 2;
     SELECT param;
END;
------------
SET @param = 1;
CALL testparam(@param);
SELECT @param;  --此时param为2，存储过程会改变输出参数值

INOUT

• 只能是变量
• 既可以向过程传递参数，也可以将参数作为返回值

变量定义

局部变量

定义
DECLARE <var_name> <var_type> DEFAULT <var_value>

• 局部变量只能在存储过程中定义

CREATE PROCEDURE testvar()
BEGIN
     DECLARE var varchar(20) DEFAULT 'hello world!'; -- 局部变量定义
     select var as title;
END;

赋值

CREATE PROCEDURE testvar()
BEGIN
     DECLARE var varchar(20) DEFAULT 'hello world!'; -- 局部变量
     set var = '你好世界';
     select var as title;
END;

用户变量

• 用户变量名一般以@开头

定义

      SET @param = 'hello world!';

删除/赋值

      SET @param = null;

INTO

可用于查询变量赋值

SET @cnt = 0;
select count(*) INTO cnt from tb_user;

BEGIN/END作用域

• BEGIN/END可命名，局部变量作用域只在当前语句内

CREATE PROCEDURE test()
BEGIN
     BEGIN
          DECLARE var varchar(20) DEFAULT '123';
          BEGIN
               DECLARE var varchar(20) DEFAULT '456';
               select var title; --456
          END;
          select var title; -- 123
     END;
END;

IF/ELSE

IF <exp> THEN
<sql>
ELSE
<sql>
END;

CREATE PROCEDURE get_user_name(IN param INTEGER)
BEGIN
     DECLARE cnt INTEGER DEFAULT 0;
     select count(*) INTO cnt from tb_user;--将统计值赋值给局部变量--
     IF param > cnt THEN
        select concat('param need <', cnt) 'msg';
     ELSE
         select u.name 'name' from tb_user u where u.user_id = param;
     END IF;
END;

CASE

• 语法
  CASE <var>
  WHEN <var> THEN
  <sql>
  WHEN <var> THEN
  <sql>
  ELSE
  <sql>
  END CASE;

CREATE PROCEDURE test_case(IN param INTEGER)
BEGIN
     CASE param
     WHEN 1 THEN
          insert into tb_auth (auth_name, auth_desc, create_time)
           VALUE('test','',date_format(now(),'%Y-%m-%d'));
     WHEN 2 THEN
          insert into tb_auth (auth_name, auth_desc, create_time)
           VALUE('test','',date_format(now(),'%Y-%m-%d %h:%i:%s'));
     ELSE
           insert into tb_auth (auth_name, auth_desc, create_time)
           VALUE('test','',date_format(now(),'%h:%i:%s'));
     END CASE;
END;

WHILE

• 语法
  WHILE <exp> DO
  <sql>
  END WHILE;

CREATE PROCEDURE test_while(IN param INTEGER)
BEGIN
     DECLARE cnt INTEGER DEFAULT 0;
     WHILE cnt < param DO
           INSERT INTO tb_role (role_name, role_desc, create_time) VALUE('','','');
           SET cnt = cnt + 1;
     END WHILE;
END;