mysql 存储过程简单使用

一、使用场景

需要将一个复杂的逻辑单元进行封装，屏蔽使用者对细节关注

案例：因为数据库单表增量较大，考虑将该表水平拆分，分成32张表，路由字段为用户id，按照用户id%32将数据散落到各个分表中，中间件采用sharding-sphere。表拆分完成以后，遇到尴尬的问题，生成相同结构的表，所以此处采用了存储过程辅助生成表。

 

二、存储过程说明

存储过程将一段通用的操作封装在一起，这样在不同平台都可以通用了。

存储过程没有返回值，而且没有sql语句调用，只能是call 调用，而且不返回结果集，执行就执行了。

 

三、存储过程语法说明

一、输入/输出参数（in,out,inout）

1）IN 输入参数

1. 表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

2. 仅需要将数据传入存储过程，并不需要返回计算后的该值。

3. 只能当做传入参数

2）OUT 输出参数

1. 该值可在存储过程内部被改变，并可返回

2. 不接受外部传入的数据，仅返回计算之后的值。

3. 只能当做转出参数

3）INOUT 输入输出参数

1. 调用时指定，并且可被改变和返回

2. 需要数据传入存储过程经过调用计算后，再传出返回值

3. 可当做传入转出参数

二、变量声明（declare,set）

mysql存储过程中declare 和set 定义变量的区别：

1) declare :

declare 变量名 数据类型

例如：

declare i int;

修改变量值：

set 变量名 =  值；

例如：

set i = 0;  

2）set：

使用set 或者select 直接赋值，变量名以@开头

例如：

set @temp=1;

 

可以在会话的任何地方声明，作用域是整个会话，称为会话变量，也是全局变量。

以declare关键字声明的变量，只能在存储过程中使用，称为存储过程变量。 这种变量需要设置变量类型，而且只存在begin....end  这段内

两者区别：

在调用存储过程时，declare声明的变量都会别初始化为null，而会话变量（以@开头的变量）则不会再被初始化，在一个会话内只被初始化一次，之后是对上一次会话的计算结果，相当于这个会话的全局变量。

三、打印参数（输出）

执行计划中，用select + 变量打印变量值。

四、调用存储过程

call + 存储过程

例如：

call copyTable('channel','',5);

五、查看删除存储过程

显示所有存储过程：

show procedure status;

删除指定存储过程：

drop procedure 存储过程名 ;

 

踩坑：mysql 存储过程中不能把变量赋值到表名处，所以，采用预sql，将要执行的sql批出来，再去执行，最后通过deallocate释放执行存储过程中使用的数据库资源。 

六、 示例（复制表）

问题描述：将demo表分成demo0，demo1....，demo31共32张分表。

我们知道，根据现有表结构创建表对应sql

create table ${tableName} like ${copyTable};

 根据上面复制表sql，我们将其转为存储过程：

-- 创建建表存储过程
delimiter //                            #定义标识符为双斜杠
drop procedure if exists copyTable;          
create procedure copyTable(in copyTableName varchar(250),in separatorName varchar(250),in cNums INT)       
begin
    declare i int;
    set i = 0;                   
    repeat
        set @temp := CONCAT(copyTableName,separatorName,i);    #拼接临时生成的表名
        set @sqlTemp := CONCAT('create table ',@temp,' like ',copyTableName);    #拼接要执行的sql语句
        PREPARE stmt from @sqlTemp;          #创建预sql
        EXECUTE stmt;                        #执行预sql
        DEALLOCATE PREPARE stmt;             #释放执行中使用的所有数据库资源
        select CONCAT(@temp,' create success !') as msg ;    #打印
        set i = i + 1; 
    until i > cNums end repeat;               
end
//

 最后，我们调用存储过程

call copyTable('demo', '', 31);