MySQL 存储过程



存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象

存储过程就是具有名字的一段代码，用来完成一个特定的功能。

创建的存储过程保存在数据库的数据字典中

优点：

存储过程可封装，并隐藏复杂的商业逻辑。

存储过程可以回传值，并可以接受参数。

存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。

存储过程可以用在数据检验，强制实行商业逻辑等。

缺点：

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。

存储过程的性能调校与撰写，受限于各种数据库系统。

存储过程的参数 ：

参数类型：

IN输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）

OUT输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

MYSQL 存储过程中的关键语法：

delimiter//                            声明语句结束符，可以自定义（默认分号）

create procedure 名称（声明参数1,...）                声明存储过程

begin...end                            存储过程开始和结束符号

declare 变量名 变量类型 （unsigned default 默认值）        定义变量

set 变量名=变量值                        变量赋值

参数类型（in|out|inout） 参数名 数据类型（int、varchar等）    声明参数



例子：

delimiter //                -- 将SQL语句的结束标记设置为//

drop procedure if exists testprint;    -- 如果存在该名称的存储过程则删除

create procedure testprint()        -- 创建一个名字为testprint的存储过程

begin                     -- 代码部分开始

select '您好!' as infos;        -- 存储过程的代码部分

end;                     -- 代码部分结束

//                    -- 结束标记

    

delimiter ;                -- 编写变异结束后将SQL语句的结束标记还原

call testprint;                -- 调用存储过程

in输入参数：

多个参数用逗号分开，SQL语句最后需加分号

delimiter //

create procedure addteacher(in p_tname varchar(20),in p_tsex varchar(4))

begin 

insert into teacher (tname,tsex) values(p_tname,p_tsex);

end;//

--调用存储过程使用call

--@pp_tname临时变量：@+变量名

delimiter ;

set @pp_tname='王五';

set @pp_tsex='男';

call addteacher(@pp_tname,@pp_tsex);

OUT输出参数：

delimiter //

drop procedure if exists selecttname;

create PROCEDURE selecttname(in p_sname varchar(20),out p_tname varchar(20))

begin 

--使用select ... into ...，将查询结果赋值给参数

select tname into p_tname from teacher 

inner join class on ctid=tid

inner join student on scid=cid

where sname=p_sname;

end;

//

delimiter ;

set @sname='一一';

--调用带有out输出参数的存储过程，作为输出参数的变量需要被赋值

set @tname='';

call selecttname(@sname,@tname);

select @tname;

条件语句

if-then-else 语句

以if开始 + 条件 + then + 操作 （+ else + 操作）+ 以end if结尾

delimiter //

create procedure proc(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;  

//

case语句

case + 变量|参数 + when + 值1 + then + 操作 + when + 值2 + then + 操作... + else + 操作 + end case

delimiter //

create procedure proc(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;  

//  

循环语句

一：

while 条件 do  循环体 end while

delimiter //

create procedure proc()  

begin 

declare var int;  

set var=0;  

while var<6 do  

insert into t values(var);  

set var=var+1;  

end while;  

end;  

//  

二：

repeat 循环体 until 循环条件  end repeat;

delimiter //

create procedure proc()  

begin 

declare v int;  

set v=0;  

repeat  

insert into t values(v);  

set v=v+1;  

until v>=5  

end repeat;  

end;  

//

  

三：

名称（自定义）：loop 循环体 if 条件 leave 名称 end if end loop

delimiter //

create procedure proc()  

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;  

//  

JDBC调用存储过程

不带输出参数：

//加载驱动

Class.forName("com.mysql.jdbc.Driver");

//获得连接

String url = "jdbc:mysql://127.0.0.1:3306/lianxi1";

String user = "root";

String userPass = "123456";

Connection con = DriverManager.getConnection(url, user, userPass);

//创建存储过程的对象  

java.sql.CallableStatement  cs = con.prepareCall("{call addstu(?,?)}");

//给存储过程的参数设置值

cs.setString(1, "张三");

cs.setInt(2, "男");

//执行存储过程 

n = cs.executeUpdate();

带输出参数：

//加载驱动

Class.forName("com.mysql.jdbc.Driver");

//获得连接

String url = "jdbc:mysql://127.0.0.1:3306/lianxi1";

String user = "root";

String userPass = "123456";

Connection con = DriverManager.getConnection(url, user, userPass);

//创建存储过程的对象  ，querystu根据id查姓名

java.sql.CallableStatement  cs = con.prepareCall("{call querystu(?,?)}");

//给存储过程的参数设置值，第一个参数为id

cs.setInt(1, 2);

//第二个参数，为out name varchar(20)

//注册存储过程的第二个参数registerOutParameter(),

cs.registerOutParameter(2, java.sql.Types.VARCHAR);

//执行存储过程

cs.execute();

//得到存储过程的输出参数值,如果输出为int类型则使用getInt();

String name = cs.getString(2);



Mybatis调用存储过程

有输出参数时:

XML映射文件内书写语句需要加属性statementType="CALLABLE"

注释书写SQL语句需要声明：@Options(statementType=StatementType.CALLABLE)

输出参数写法：#{name,mode=OUT,jdbcType=VARCHAR} ，OUT、VARCHAR需要大写

XML文件内书写

不带输出参数：statementType="CALLABLE"可以省略    

<insert id="addclass" parameterType="map" statementType="CALLABLE">

    call addstu(#{name},#{sex})

</insert>

带输出参数：

注意！传入的参数类型直接为：parameterType="String"无法获得输出参数

<select id="selecttname" statementType="CALLABLE" parameterType="map">

    call selectname(#{id},#{name,mode=OUT,jdbcType=VARCHAR})

</select>

带输出参数的调用：

String str = "config/mybatis-config.xml";

InputStream is = Resources.getResourceAsStream(str);

SqlSessionFactory ss = new SqlSessionFactoryBuilder().build(is);

SqlSession s = ss.openSession();

Map<Object,String> map = new HashMap<>();

map.put("id", 1);

s.selectOne("selectname",map);

System.out.println(map.get("name"));

注解模式，带输出参数写法：

@Select("call selectname(#{id},#{name,mode=OUT,jdbcType=VARCHAR})")

@Options(statementType=StatementType.CALLABLE)

public void selecttname1(Map<Object,String> map);

 

什么是存储引擎：
Mysql中的数据用各种不同的技术存储在文件（或者内存）中， 每一种技术都使用不同的存储机制，

索引技巧  锁定水平并且最 终提供广泛的不同的功能和能力。通过选择不同的额技术你能过够获得额外  的速度或者功能从而改   善你的应用整体功能。不同的存储引擎性能是不一的你能够获得额外的速度或者功能，从而改善你的应用的整体功能。查看Mysql可用的引擎可以用：show engines查询出来后边有 yes 的 是支持的 no 是不支持的。
2.Mysql 的常见引擎   ：
1.MEMORY：
memory使用 存在内存中的内容来创建表，每个MEMORY表实际对应一个磁盘文件，格式是.frm（就是表里的结构）。memory类型的表访问速度非常快，因为它的数据放在内存中                               并且默认使用 	HASH索引，但是一旦服务器关闭	表中的数据就会丢失，                                但是表还会继续存在。​
2.MylSAM:
拥有较高的插入（如 insert into 新增），查询速度，但不支持事物,也不支持外键。                           每个MylSAM在磁盘上存放3个文件，其中文件名和表名相同，但是扩展名 分别为                           .frm 存储表的一个定义 . MYD：存储真是的数据  .MYI：存储索引			MyISAM适合：						(1)做很多count 的计算；					(2)插入不频繁，查询非常频繁；					(3)没有事务。
3.InnoDB:
5.5版本后Mysql的默认数据库，事物型数据库的首选引擎，支持ACID事物，支持行级锁定 innoDB写的处理效率差，他会占用空间来保存数据和索引。		InnoDB适合：			(1)可靠性要求比较高，或者要求事务；			(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建			   让所有的灵活性成为可能的开关是提供给ANSI SQL的MySQL扩展——TYPE参数。	           MySQL能够让你在表格这一层指定数据库引擎，所以它们有时候也指的是table formats。		下面的示例代码表明了如何创建分别使用MyISAM、ISAM和HEAP引擎的表格。					   		 下面的代码显示了如何使用ALTER TABLE把MyISAM表格移动到InnoDB的引擎：						 代码：ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB						3. Memory:所有数据置于内存的存储引擎，拥有的极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 Mysql 重新启动时丢失。					 4.Merge：将一定数量的 MyISAM 表联合而成一个整体，在超大规模数据存储时很有用。	5.Archive：非常适合存储大量的独立的，作为历史纪录的数据。因为它们不经常被读取。					   Archive 拥有高效的插入速度，但其对查询的支持相对较差。				   6.CSV：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .CSV文件这是一个普通的文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引。7.BlackHole:黑洞引擎，写入的任何数据都会消失，一般用于记录 binlog 做复制的中继。
ACID:
A  事务的原子性(Atomicity)：指一个事务要么全部执行,要么不执行.也就是说一个事务不可能只执行				了一半就停止了.比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱				却没出来.这两步必须同时完成.要么就不完成.					C 事务的一致性(Consistency)：指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了				a+b=10,一个事务改变了a,											 那么b也应该随之改变.				I 独立性(Isolation）:事务的独立性也有称作隔离性,是指两个以上的事务不会出现交错执行的状态.									 因为这样可能会导致数据不一致.				D 持久性(Durability）:事务的持久性是指事务执行成功以后,该事务所对数据库所作的更改便是持久				的保存在数据库之中， 不会无缘无故的回滚.
Innodb介绍：
innoDB是一种可靠性高的高性能 Mysql 数据库存储引擎，它有如下优点：​		

1.它遵循ACID模式设计，具有与事物（Transactions），回滚和保护用户数据的崩溃恢复能力。			2.提供行锁。			3.InnoDB表基于主键在磁盘上安排数据，有优化的常见查询功能。每一个InnoDB表都有一个叫做		  聚集索引的主键索引，能尽可能减少数据查询次数。		  		4.为了维护数据完整性，InnoDB还支持外键完整性约束。				5.可以与其它MySQL存储引擎混合使用InnoDB表。列如，能用一个结合操作在一个单独查询中综合源自		  InnoDB和InnoDB表的数据		  		6.InnoDB是针对提高  CPU效率而设计的，并且在处理大数据时表现最佳。MyISAM和InnoDB的区别；
什么是存储过程：
一组可编程的函数，是为完成 特定 功能的SQL语句                      存储 过程是具有名字的代码段 ，来完成一些特定的功能。                        创建的存储过程保存在数据库的字典中。
为什么用存储过程：
将重复性高的一些操作，封装到一个存储过程中，简化对Sql的调用。                          批量处理                          统一接口，	确保数据的安全                          相对与oracle数据来说，Mysql的存储过程功能较弱 使用较少
存储过程的创建 和调用：
delimiter $ $: 他与存储过程无关 delimiter 语句将标准分割符–分号（；）更改 $ $ , 因为我们想将存储过程 作为一个整体传递给服务器，而不是让mysql 工具一次解析每个语句，告诉mysql解析器 ，该段命令是否已经结束了，mysql是否可以执行了，默认情况下delimiter是分号；。在命令行客户端中，如果有一行以分号结束，那么回车 后执行 改命令，但是有的时候，不希望mysql这么做。在为可能输入较多的语句，且 语句包含有分号，使用delimiterr$ $  这样只有$ $出现之后mysql解析器解析执行这段语句
案例：
创建存储过程：
delimiter $$  -- 定义好开始  符号 可以 为$$ 或者//
 CREATE PROCEDURE user_tt()  -- 给存储过程取一个名字
BEGIN -- 开始
SELECT * from t_user; --sql语句执行 注意sql 语句结束有分号
END $$  --结束  注意 符号 和定义开始一致



调用存储过程：
delimiter；-- 把分号改回来

CALL user_tt;


查看存储过程：

查看所有的存储过程：SHOW PROCEDURE STATUS;


##查看指定数据库的存储过程：SHOW PROCEDURE STATUS  WHERE  db=“存储过程名”;


查看 指定存储过程源代码： SHOW  CREATE  PROCEDURE  + 存储过程名


删除存储过程 ：drop PROCEDURE  +存储过程名；


存储过程变量：
delimiter $$  -- 定义好开始  符号 可以 为$$ 或者//
 CREATE PROCEDURE text_user()  -- 给存储过程取一个名字
BEGIN -- 开始

  -- 声明变量  关键 字declare   `DEFAULT ''  默认值 空字符串



DECLARE  jie  VARCHAR(255) default ''; 

DECLARE   x,y INT   default 0;


SELECT * from t_user; -- sql语句执行 注意sql 语句结束有分号


END $$  -- 结束  注意 符号 和定义开始一致




给变量赋值：



变量的范围：如果在存储过程中声明变量，那么当达到存储过程的 END 语句时  它超出了范围。因此在其他代码块无法 访问
存储过程参数：
三种类型：
                               in  输入类型
                                
                                --  根据传入的名称， 获取对应的信息、
delimiter $$
CREATE PROCEDURE getName(in name VARCHAR(225))-- 传入参数  in 关键字  VARCHAR类型
BEGIN 

 SELECT * from t_user where  user_code =name; -- 给要查的 赋值


END $$

delimiter;


        out类型的 输出



delimiter $$
CREATE PROCEDURE getMima(in name VARCHAR(225),out mima int )-- 传入参数 out  关键字  输出数据
BEGIN 

 SELECT  user_pass into mima  from t_user where  user_code = name; -- 给要查的 user_pass into mima  赋值  name 


END $$



查询结果
CALL  getMima('鲁班',@m);  -- @m 就时out 的参数  首先执行这句话
SELECT @m; -- 这里的@m 就是上面所定义的 在这可以直接输出 这只是简写

SELECT @m  from  DUAL 这个是mysql 数据库 提供的一张假表  可以加上参数调用



 inout 类型是又输入又是输出
  delimiter $$
CREATE PROCEDURE test1808(inout num  int ,in abc  int  )-- 传入参数 out  关键字  输出数据
BEGIN 


set num = num + abc;
 
END $$

delimiter;

查询结果
set @num1=220;
CALL test1808(@num1,10);
SELECT @num1;


存储过程语句：
if语句：                   IF expression THEN

statements；

END  IF;

IF expression THEN

statements;

ELSE

else-statements;

END IF;

CASE语句：

CASE case_expression

WHEN when_expression_2  THEN commands                   …

ELSE commands

END CASE;

循环语句:

WHILE expression DO

statements

END WHILE

2)REPEAT

stalemeents;

UNTIL  expression

END REPEAT
自定义函数
案例：

  定义 一个自定义函数  在定义函数 和存储过程有区别  定义函数function
delimiter $$
CREATE  FUNCTION rand_str(n int) returns  varchar(225)
BEGIN
-- 生明一个str 52字母
DECLARE str VARCHAR(100)  DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
-- 记录 当前是第几个
DECLARE i INT DEFAULT 0;
-- 生成的结果
DECLARE  res_str  VARCHAR(255)   DEFAULT '';
-- 循环  
WHILE i < n DO
-- 随机生成一个指定的字符串 b bc bce 
--   1+ RAND()*52  floor（1+rand ()*52）;
--  SUBSTR(str FROM (1+rand()*52),1)
set res_str=CONCAT(res_str,SUBSTR(str,FLOOR(1+RAND()*52),1));
SET i= i+1;
END WHILE;
RETURN res_str;
END $$
delimiter ;


结果
SELECT rand_str(5);


案例批量新增：
这里调用了以上的随机数 方法 函数

delimiter $$
CREATE  PROCEDURE insert_emp10(in  startNum int, in  max_num int) 
                        --  startNum 从几开始
                        --   max_num 要插入多少条数据
begin 
-- 声明 一个变量叫记录当前是第几条数据
 DECLARE  i INT default 0;
-- 默认情况是自动 提交数据
  set autocommit = 0; -- 不让它自动 提交
repeat 
set i= i+1; -- 每循环一次 让i+1
-- 插入数据
INSERT into  emp VALUES (startNum+i,rand_str(5),FLOOR(10+RAND()*30));
                             --  startNum代表是id ,+i是每次循环+1
                             -- rand_str(5) 调用 数据 数 对name 每一次循环
                               --   都去生成一个5个字母的名字
                               --  FLOOR(10+RAND()*30) 年龄 也是随机的 +10
                               --  是应为随机数是从0开始 *30 是到30岁
until i =max_num  -- 当i=max_num 就让它退出循环

end  repeat;
commit;  -- 整体提交所有数据  提高效率 
end $$

delimiter ;

结果：

Mysql存储过程

一. 过程化sql

       过程块sql主要有两种类型，几命名块和匿名块。匿名块每次执行时都要编译，而且不能存储到存储过程中，也不能在其他过程化sql块中调用，过程和函数时命名块，他们在编译时保存在数据库中，成为持久型存储模块（Persistent Stored Module，PSM），可以被反复调用，运行速度较快。

二. 基本语法

1. 创建存储过程

create procedure sp_name()
begin
.........
end

2. 调用存储过程

基本语法：call sp_name()
       注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

3. 删除存储过程

基本语法：
       drop procedure sp_name//

注意事项
       不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

4. 其他常用命令

show procedure status
        显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

show create procedure sp_name
        显示某一个MySQL存储过程的详细信息

三. 在存储过程中使用用户变量

1. 在mysql客户端使用用户变量

    mysql> SELECT 'Hello World' into @x;   给x赋值
    mysql> SELECT x;                       查询x   

 
    mysql> SET @y='Goodbye Cruel World';      
    mysql> select @y;


    mysql> SET @z=1+2+3;
    mysql> select @z; 

2. 在存储过程中使用用户变量

mysql> CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
mysql> SET @greeting='Hello';
mysql> CALL GreetWorld( );

3. 在存储过程间传递全局范围的用户变量

mysql> CREATE PROCEDURE p1( )   SET @last_procedure='p1';
mysql> CREATE PROCEDURE p2( ) SELECT CONCAT('Last procedure was ',@last_procedure);
mysql> CALL p1();
mysql> CALL p2();

4. 含参的存储过程

DELIMITER //
CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
BEGIN
SELECT p_in; --查询输入参数
SET p_in=2; --修改
select p_in;--查看修改后的值
END//

  执行结果:

mysql> set @p_in=1

mysql> call sp_demo_in_parameter(@p_in)

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值       

对于DELINITER 的用法，请看我的下篇博客 存储过程分号处理办法

 

MySQL存储过程


  
存储过程(Stored Procedure)是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。




存储过程简介

存储过程是存储在数据库目录中的一段声明式SQL语句。它可以被触发器、其它存储过程及应用（如Java、Python、PHP等）调用。调用自身的存储过程称为递归存储过程。大部分数据库管理系统都支持递归存储过程，然而MySQL对递归存储过程的支持并不是特别好，因此在MySQL中使用递归存储过程之前应该先对当前的MySQL数据库版本是否支持递归存储过程进行检测。

MySQL是最流行的开源RDBMS（关系型数据库管理系统），它在社区和企业中都得到了广泛的应用。然而，在MySQL存在的前十年，它并不支持存储过程(stored procedures)、存储函数(stored functions)、触发器(triggers)和事件(events)。从5.0版本开始，这些特性才被加到MySQL数据库引擎中，使得它变得更加灵活和强大。

MySQL存储过程的优点

存储过程通常有助于提升应用的性能，一旦创建，存储过程就被编译存储于数据库中。然而，MySQL中的存储过程的实现机制略微不同，它只有在需要的时候才进行编译。在编译后，MySQL把它放到缓存中。对于每个单独的连接，MySQL都维护一个存储过程缓存。如果一个应用在一个连接中多次使用一个存储过程，那么它用的就是编译过的版本，否则存储过程的工作方式就像查询一样。
存储过程有助于减少应用和数据库之间的流量。因为它不再需要发送多个冗长的SQL语句，只需要发送存储过程的名字和参数即可。
存储过程是可复用的且对应用透明。存储过程为所有应用提供数据库接口，因此开发人员不必再开发早已在存储过程中支持的功能。
存储过程是安全的。数据库管理员可以为应用授予访问数据库中存储过程的权限，而不必授予底层的数据库表的权限。
MySQL存储过程的缺点

如果使用大量的存储过程，那么每个连接的内存使用将大大增加。另外，如果在存储过程中滥用大量的逻辑操作，那么cpu的使用也会增加，这是因为在数据库的设计中对逻辑运算的支持并不是很好。
存储过程的结构使得开发具有复杂业务逻辑的存储过程时变得非常困难。
存储过程的debug也是困难的。只有一少部分数据库管理系统支持存储过程的debug。不幸的是，MySQL没有提供调试存储过程的组件。
存储过程的开发和维护是困难的。开发和维护存储过程通常要求具备一些专业技能，然而这并不是所有应用开发人员都具有的。这可能导致在应用开发和维护阶段的问题。
MySQL的存储过程有利有弊，在开发过程中，要根据自己的业务需求决定是否使用存储过程。



MySQL存储过程实例

本部分以MySQL Workbench为例，创建一个用于批量建表的存储过程，该语句可用于分库分表中表的批量建立。

右键单击数据库下面的Stored Procedures, 选择Create Stored Procedure..., 会建立一个新的存储过程，默认名字为new_procedure,将其修改为自己的存储过程的名字，例如batch_create_table。如图所示：


编辑存储过程，完成批量建立4个表的功能。 

表名分别为：order_remarks_0,order_remarks_1,order_remarks_2,order_remarks_3. 

完整的存储过程代码：

CREATE PROCEDURE `batch_create_table` ()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
DECLARE table_pre VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=0;
SET table_name='';
SET table_pre='order_remarks_';
SET sql_text='';

while i<4 do
    SET table_name=CONCAT(table_pre,i);
    SET sql_text=CONCAT('CREATE TABLE ', table_name, '(
              id                int(10)     UNSIGNED  AUTO_INCREMENT   ,
              mall_id           int(11)     UNSIGNED  NOT NULL         ,
              order_sn          varchar(255)          NOT NULL         ,
              remarks_type      tinyint(4)      NOT NULL  DEFAULT 1    ,
              remark            mediumtext      NOT NULL               ,
              operator_id       int(11)         NOT NULL  DEFAULT 0    ,
              operator_name     varchar(255)    NOT NULL  DEFAULT \'\'   ,
              operator_type     tinyint(4)      NOT NULL  DEFAULT 0    ,
              created_at        timestamp       NOT NULL  DEFAULT CURRENT_TIMESTAMP ,
              updated_at        timestamp       NOT NULL  DEFAULT \'0000-00-00 00:00:00\'  ,
              visible           tinyint(4)      NOT NULL  DEFAULT 1    ,
              PRIMARY KEY (id),
              KEY order_sn  (order_sn),
              KEY created_at  (created_at),
              KEY updated_at  (updated_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ' );
    SELECT sql_text;
    SET @sql_text=sql_text;
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;  
    SET i=i+1;
end while;
END
点击右下角的Apply，弹出Review SQL Script的提示，代码里会自动增加一些代码，如图所示： 

确保无误后，点击Apply将该SQL脚本应用与数据库。点击SCHEMAS右边的更新按钮，可以看到Stored Procedures下多了一个名为batch_create_table的存储过程。如图所示：


最终生成的存储过程第一行由CREATE PROCEDURE `batch_create_table` ()变成了CREATE DEFINER=`root`@`localhost` PROCEDURE `batch_create_table`() 

其它内容不变。如图所示：


调用存储过程，在Query标签中使用call batch_create_table();调用存储过程。刷新SCHEMA后会看到新建了4个表，如图所示： 


  
如果提示Error Code: 1067 Invalid default value for 'updated_at'，说明当前MySQL版本的SQL_MODE限制不能为timestamp字段设置默认值。
  
这种情况一般可以使用DEFAULT 0和DEFAULT CURRENT_TIMESTAMP来设置默认值，但后者一般只能出现一次，这与MySQL版本有关。或者可以把SQL_MODE设为ALLOW_INVALID_DATES。这样就不必修改代码了。依次执行下列语句： 
SET sql_mode = 'ALLOW_INVALID_DATES'; 

  call batch_create_table();
  



变量和参数



存储过程中的变量

本部分将介绍存储过程中变量的声明、使用及作用域。

变量声明

在存储过程中，使用DECLARE来声明变量。DECLARE的语法结构如下所示：

DECLARE variable_name datatype(size) DEFAULT default_value;

首先，在DECLARE关键字后指明变量名。注意：* 变量名必须遵循MySQL表中字段名的命名规则. *
其次，指定变量的数据类型和大小。一个变量可以是任意的MySQL数据类型，例如INT、VARCHAR、DATETIME等。
最后，声明一个变量后，它的初始值是NULL。可以使用DEFAULT关键字为变量指定一个默认值。
变量声明的一些例子：

声明变量total_sale，数据类型为INT，默认值为0
DECLARE total_sale INT DEFAULT 0;

同时声明具有相同的数据类型和默认值的多个变量
DECLARE x, y INT DEFAULT 0;
变量赋值

使用SET为变量赋值

DECLARE total_count INT DEFAULT 0;
SET total_count = 10;
使用SELECT INTO将一次查询的结果赋值给变量

DECLARE total_products INT DEFAULT 0
SELECT COUNT(*) INTO total_products FROM products
变量作用域

在存储过程中声明一个变量，它的作用域到存储过程的END语句为止。
在BEGIN END语句块中声明一个变量，它的作用域到END为止。变量只在它自己的作用域中有效，因此在不同的作用域中可以声明多个同名的变量。但是，这并不是一个好的编程习惯。
以@符号开始的变量是会话变量（‘session variable’），它在整个session的声明周期中都是有效的。
存储过程参数

本部分将介绍带参的MySQL存储过程。

参数介绍 

参数使得存储过程更加灵活和有用。在MySQL中，参数有三种形式：IN，OUT，INOUT.

IN – 默认的参数模式。当在存储过程中定义一个IN参数时，调用程序必须传一个参数给存储过程。另外，IN参数的值是受保护的。这意味着，即使参数的值在存储过程中被改变了，它的原始值在存储过程结束后仍然得到了保留。也就是说，存储过程是在IN参数的副本上工作的。
OUT – OUT参数的值可以在存储过程中被改变，并且新值会被返回给调用程序。注意，存储过程在开始时不能获得OUT参数的初始值。
INOUT – INOUT是IN和OUT参数的结合。这意味着，调用程序可以传递该参数，并且调用程序可以修改INOUT参数并把新值返回给调用程序。
在存储过程中定义一个参数的语法如下：

MODE param_name param_type(param_size)

MODE可以是IN、OUT或INOUT，取决于参数在存储过程中的作用。
参数名param_name必须遵循MySQL中列名的命名规则。
参数名后是数据类型和大小，与变量类似，数据类型可以是任意的MySQL数据类型。
如果存储过程有多个参数，则参数之间以逗号,分隔。
实例

IN参数

在存储过程GetOfficeByCountry中使用IN参数来查找指定国家的办公室信息：

DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
BEGIN
SELECT *
FROM offices
WHERE country = countryName;
END //
DELIMITER ;

调用存储过程CALL GetOfficeByCountry('USA');，结果如图所示： 


调用存储过程CALL GetOfficeByCountry('France');，结果如图所示： 

OUT参数

存储过程CountOrderByStatus：

DELIMITER $$
  CREATE PROCEDURE CountOrderByStatus(
   IN orderStatus VARCHAR(25),
   OUT total INT)
  BEGIN
   SELECT count(orderNumber)
   INTO total
   FROM orders
   WHERE status = orderStatus;
  END$$
DELIMITER ;

调用存储过程，指定参数orderStatus为‘Shipped’，参数@total来获得返回值。

CALL CountOrderByStatus('Shipped',@total);
SELECT @total;

结果如图所示：
INOUT参数 

使用INOUT参数的存储过程：

DELIMITER $$
  CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))
  BEGIN
   SET count = count + inc;
  END$$
DELIMITER ;

在该存储过程中有两个参数，一个是INOUT参数count，另一个是IN参数inc。该存储过程把count参数增加inc。 

调用存储过程：

SET @counter = 1;
CALL set_counter(@counter,1); -- 2
CALL set_counter(@counter,1); -- 3
CALL set_counter(@counter,5); -- 8
SELECT @counter; -- 8
多返回值存储过程

…



MySQL IF语句

IF语法

IF expression THEN 
 statements;
END IF;


IF ELSE语法

IF expression THEN
 statements;
ELSE
 else-statements;
END IF;


IF ELSEIF ELSE语法

IF expression THEN
 statements;
ELSEIF elseif-expression THEN
 elseif-statements;
...
ELSE
 else-statements;
END IF;


实例

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(
  in  GetCustomerLevel() int(11), 
  out p_customerLevel  varchar(10))
BEGIN
  DECLARE creditlim double;

  SELECT creditlimit INTO creditlim
  FROM customers
  WHERE customerNumber = p_customerNumber;

  IF creditlim > 50000 THEN
SET p_customerLevel = 'PLATINUM';
  ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
      SET p_customerLevel = 'GOLD';
  ELSEIF creditlim < 10000 THEN
      SET p_customerLevel = 'SILVER';
  END IF;

END$$

在存储过程GetCustomerLevel()中，有两个参数：客户编号GetCustomerLevel()和客户级别p_customerLevel。该存储过程的功能是根据客户信用额度来判断客户编号对应的客户级别。 

首先在customers表中查询客户的信用额度，然后根据该额度对客户的级别进行判断，如果信用额度大于50000，则是PLATINUM;如果在[10000,50000]之间，则是GOLD;如果小于10000，则是SILVER。

判断逻辑如图所示：


MySQL CASE语句



循环



游标Cursor



错误处理



列出存储过程

参考文献

MySQL Stored Procedure