1.存储过程概述

1.1.什么是存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。MySQL 5.0 版本开始支持存储过程。
它是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

1.2.存储过程的优缺点

优点：

  • 存储过程在服务器端运行，执行速度快。
  • 存储过程执行一次后，经过第一次编译后就不需要再次编译，提高了系统性能。
  • 确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限

缺点：

  • 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
  • 存储过程的性能调校与撰写，受限于各种数据库系统。

2.存储过程语法

2.1.创建存储过程

创建存储过程语法：

  1. create procedure 存储过程名(参数列表)
  2. begin
  3.     -- 过程体
  4. end;

实例：根据部门编号，返回此部门所有员工的总工资。

1
2
3
4
5
drop procedure if exists getsalbydeptno;
create procedure getsalbydeptno(in dno int,out salsum decimal(7,2))
begin
    select sum(sal) into salsum from emp where deptno=dno;
end;
  1. 存储过程参数有三种模式：in，out和inout。
    • in：传入参数（默认）
    • out：传出参数
    • inout：此参数是in和out参数的组合。这意味着存储过程可以修改此参数并将新值传递回调用程序。
  2. 注意：参数名不能与字段名重名。
  3. 在select语句中，使用into关键词给传出参数赋值。

2.2.调用存储过程

调用存储过程语法：

1
2
call getsalbydeptno(10,@salsum);
select @salsum;
  1. 使用call关键词调用存储过程。
  2. 传出参数使用 @salsum 的形式声明。

2.3.删除存储过程

删除存储过程语法：

drop procedure [if exists] 存储过程名;

删除getsalbydeptno存储过程：

drop procedure if exists getsalbydeptno;

2.4.查看存储过程

查看数据库中的所有存储过程：

show procedure status;

3.存储过程实例

需求：公司员工离职后，需要从emp表中删除此员工的信息。但是为了能够保存历史记录，所以删除的员工信息需要备份到emp_copy表中。下面使用存储过程来完成这个功能：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/* 创建存储过程 */
drop procedure if exists backup;
create procedure backup(in eno int)
begin
    declare tex int default 0;
    select 1 into tex from information_schema.tables where table_name='emp_copy';
    if tex<>1 then
        create table emp_copy select * from emp where 1=0;
    end if;
    insert into emp_copy select * from emp where empno=eno;
    delete from emp where empno=eno;
end;

/* 调用存储过程 */
/* call backup(7369); */
  • declare tex int default 0; 声明一个变量tex，并赋默认值为0；
  • select 1 into tex from information_schema.tables where table_name=’emp_copy’;
    从系统表information_schema.tables中查询是否存在 emp_copy 表，如果存在，给变量tex 赋值为 1；
  • if tex<>1 then … end if; 判断tex变量的值是否为1；