MySQL存储过程

需求：

今天公司提出一个需求，使用不同表来存储不同用户的数据，思考了一番，决定在新建用户时直接创建该用户的表，使用用户创建时的短名称加上主键（考虑到短名称可能重复）作为前缀再加上后缀作为表名。

但是在使用 mybatis 创建表的时候发现代码过多、比较繁杂，于是决定使用存储过程进行创建。

介绍：

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

种类：

系统存储过程

以 sp_ 开头，用来进行系统的各项设定、取得信息、相关管理工作。
本地存储过程

用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。
临时存储过程

分为两种存储过程：
1. 本地临时存储过程，以井号 # 作为其名称的第一个字符，则该存储过程将成为一个存放在 tempdb 数据库中的本地临时存储过程，且只有创建它的用户才能执行它。
2. 全局临时存储过程，以两个井号 ## 号开始，则该存储过程将成为一个存储在 tempdb 数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。
远程存储过程

在 SQL Server 2005中，远程存储过程（Remote Stored Procedures）是位于远程服务器上的存储过程，通常可以使用分布式查询和 EXECUTE 命令执行一个远程存储过程。
扩展存储过程

扩展存储过程（Extended Stored Procedures）是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以 xp_ 开头。

优/缺点：

优点

重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。
减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。
安全性。参数化的存储过程可以防止 SQL 注入式攻击，而且可以将 Grant、Deny 以及 Revoke 权限应用于存储过程。

简单讲：

存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
当对数据库进行复杂操作时（如对多个表进行 Update、Insert、Query、Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
存储过程可以重复使用，可减少数据库开发人员的工作量。
安全性高，可设定只有某些用户才具有对指定存储过程的使用权。

有一点需要注意的是，一些网上盛传的所谓的存储过程要比 sql 语句执行更快的说法，实际上是个误解，并没有根据，包括微软内部的人也不认可这一点，所以不能作为正式的优点，希望大家能够认识到这一点。

缺点

调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

示例：

# 删除存储过程
drop procedure test_select;

# 1. 创建无参的存储过程
DELIMITER $ -- 声明存储过程的结束符
CREATE PROCEDURE test_select()
BEGIN
	SELECT * FROM emp;
END $
DELIMITER ;
# 调用无参的存储过程
CALL test_select();

# 2. 创建带输入参数的存储过程
DELIMITER $ -- 声明存储过程的结束符 IN：代表参数的方向为输入参数，即实参传值到形参的过程，此时IN可以省略
CREATE PROCEDURE select_name(IN param_name varchar(20))
BEGIN
	SELECT * FROM emp WHERE name=param_name;
END $
DELIMITER ;
# 调用带输入参数的存储过程
CALL select_name('小明');

# 3. 创建带输出参数的存储过程
DELIMITER $ -- OUT：代表参数是输出参数，所谓输出参数就是在存储过程中赋值反向传递到实参的过程，输出参数的OUT不能省略
CREATE PROCEDURE select_count(OUT param_count INT)
BEGIN
  SELECT COUNT(1) INTO param_count FROM emp;
END $
DELIMITER ; # 中间必须有空格，否则报错
# 调用带输出参数的存储过程，@表示定义局部变量
CALL select_count(@param_count);
SELECT @param_count; # 显示变量的值

# 4. 创建带输入输出参数的存储过程
DELIMITER $
CREATE PROCEDURE select_value(IN param_name VARCHAR(20),OUT param_age INT,OUT param_sex VARCHAR(2),OUT param_birthday date)
BEGIN
	SELECT age,sex,birthday INTO param_age,param_sex,param_birthday  FROM emp WHERE name=param_name; 
END $
DELIMITER ;
# 调用带输入输出参数的存储过程
CALL select_value('小明',@age,@sex,@birthday);
SELECT @age,@sex,@birthday;

# 5. 创建插入记录的存储过程
DELIMITER $
CREATE PROCEDURE insert_user(param_name VARCHAR(20), param_age INT,param_sex VARCHAR(2),param_birthday date,param_addr varchar(100))
BEGIN
	insert into emp values(null,param_name,param_age,param_sex,param_birthday,param_addr);
END $
DELIMITER ;
# 调用创建插入记录的存储过程
CALL insert_user('小明',20,'男','2000-10-20','郑州');
# 查看数据
select * from emp;

实现：

DROP PROCEDURE IF EXISTS create_table;
create procedure create_table(in headname VARCHAR(200))
BEGIN 
	set @csql=concat("create table if not exists ",headname,"_table ","(
        id int primary key auto_increment,
        name varchar(20) unique,
        age int default 18,
        sex char(6) default '男',
        birthday date,
        address varchar(200)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;");
	# 定义预处理语句
	PREPARE test from @csql;
	# 执行预处理语句
	EXECUTE test;
END;
# 调用存储过程
CALL create_table('aaa');