MySQL存储过程

Posted on | Edited on | In

简介

更多基础知识可参考MySQL存储过程，很棒很详细的入门文章。

SQL 语句需要先编译然后执行，而存储过程(Stored Procedure)是一组为了完成特定工程的SQL语句集，经编译后存储在数据库中，用户通过制定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

个人感觉存储过程增强了兼容性。实现相对复杂的功能（比如返回一组数据，然后遍历结果集），之前可能要用python操作mysql来实现。但是利用存储过程可以写成一组代码，使用者可以直接复制粘贴到MySQL shell或者客户端来使用。

自己写的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
drop procedure if exists A;  --如果存在存储过程A，则删除

delimiter // -- 因为存储过程中每条SQL语句都要以分号结尾，所以这里要先将结束符改为 //， 否则存储过程在编译的过程中遇到分号就会结束编译，导致只编译了一部分。
create procedure A(IN wiki_id_template varchar(15)) -- 定义存储过程，就像一个函数
begin -- 内容写在 begin, end之间

declare group_id varchar(50); -- 定义变量，一定要紧跟着begin写！
declare revision_count int;

declare s int default 0;
declare mycur cursor for select distinct wiki_id from Wiki where wiki_id like wiki_id_template; -- 声明游标，mycur是一个多行结果集

-- 设置终止标记
declare continue handler for sqlstate '02000' set s=1; 

-- 创建一个临时表，只用于显示结果
create temporary table if not exists tmpTable
(
group_id varchar(50) not null,
revision_count integer not null
);
-- 使用临时表前先清空
truncate table tmpTable;
    open mycur; -- 打开游标
    fetch mycur into group_id; -- 将游标当前指向的值赋给变量group_id
    
    while s <> 1 do -- 开始while循环
    select count(*) into revision_count from Revision where page_id like concat(group_id, '%');
    insert into tmpTable values(group_id,revision_count);
    fetch mycur into group_id;
    end while; -- 终止while循环
    
    close mycur; -- 关闭游标

select * from tmpTable; -- 将临时表中的数据显示出来
end// -- 存储过程结束，//是我们自己定义的结尾符

delimiter ; -- 把结尾符重新改为 ;
call A('2016twgss%'); -- 调用存储过程

存储过程参数

  • IN 只进不出，即存储过程会接受传入的变量，但是存储过程内部对该变量的修改不会返回
  • OUT 只出不进，即存储过程接收到的变量的值一定为空，无论传入的值是什么，且内部的修改会返回。
  • INOUT 有进有出，存储过程接受传入的变量，且内部做的修改会返回。

需要注意的点

  1. 形如下面这样的变量定义语句一定要紧跟在存储变量的begin之后写，否则会报错
1
declare s int default 0;

参考