MYSQL主从表插入，使用存储过程

来源：互联网发布：假钞在淘宝中的暗语编辑：程序博客网时间：2024/06/05 22:33

-- MYSQL主从表插入数据使用存储过程

-- 存储过程

drop procedure if exists addRow; -- 存在即删除
create procedure addRow(
   in t_enterprise_keyid int,
   in number varchar(50),
   in serial varchar(50),
in date date,
   in type varchar(255),
in man varchar(100),
   in operator varchar(100),
   in remark varchar(255),
   in state varchar(50),
   Qualification varchar(21845)
) -- 定义参数
begin
   declare i int ;
   declare Start1 int;
   declare Length int ;
   declare TotalLenght int ;
   declare number0 int ;
   declare comma int ;
   declare casenum int ;
   declare filed varchar(100) ;
   declare number1 int ;
   declare number2 varchar(50) ;
   declare number3 varchar(100) ;
   declare number4 varchar(100) ;
   declare number5 varchar(50) ;
   declare number6 varchar(30) ;
   declare number7 int ;
   declare number8 varchar(100) ;
   declare number9 decimal(10) ;
   declare number10 decimal(10) ;
   declare number11 decimal(10) ;
   declare number12 decimal(10) ;
   declare number13 varchar(255) ;
   declare number14 varchar(255) ;
declare stmtNovelSearch varchar(2000) ;
   set i=1;
   set Start1=1;
   set Length=0;
   set   comma=0;
   set TotalLenght=length(Qualification); -- 计算输入参数长度
       insert into t_inorder(t_enterprise_keyid,number,serial,date,type,man,operator,remark,state) values (t_enterprise_keyid,number,serial,date,type,man,operator,remark,state);
       set number0=LAST_INSERT_ID();
    select TotalLenght;
       while i <=TotalLenght do -- i=1 开始          -- select SUBSTRING(Qualification,i,1);
           if(SUBSTRING(Qualification,i,1)=',') -- 注意 mysql 的substring函数截取字符串是从1开始的，而不是0 与java JavaScript不同。
           -- 从第i位开始截取一个字符，看是否等于，
               then
               set comma=comma+1;
               set filed=SUBSTRING(Qualification,Start1,Length); -- 从start1开始截取length长度的字符
              select filed;
               set casenum=comma%14;
               case casenum
                   when 0 then
                       set number14=filed;
                       insert into t_inorder_detail(t_inorder_keyid,t_goods_keyid,t_goods_serial,t_goods_name,t_goods_standard,t_goods_colour,t_goods_unit,t_warehouse_keyid,t_warehouse_name,in_count,out_count,plan_count,price,batch,remark) values (number0,number1,number2,number3,number4,number5,number6,number7,number8,number9,number10,number11,number12,number13,number14);
                   when 1 then
                       set number1=filed;
                   when 2 then
                       set number2=filed;
                   when 3 then
                       set number3=filed;
                   when 4 then
                       set number4=filed;
                   when 5 then
                       set number5=filed;
                   when 6 then
                       set number6=filed;
                   when 7 then
                       set number7=filed;
                   when 8 then
                       set number8=filed;
                   when 9 then
                       set number9=filed;
                   when 10 then
                       set number10=filed;
                   when 11 then
                       set number11=filed;
                   when 12 then
                       set number12=filed;
                   when 13 then
                       set number13=filed;
                   -- else
               end case;
               set Start1=i+1;
               set Length=0;
               else
               set Length=Length+1; -- 如果不是，则截取的长度加1
           end if;
   set i=i+1;
   end while;
end -- 调用存储过程字段名以，分隔注意最后，结尾 call

-- 使用存储过程
set @t_enterprise_keyid = 1001;
set @number = 'SA20150813121627';
set @serial = '商品编号';
set @date = '2015-08-11';
set @type = '入库类型';
set @man = '检验人';
set @operator = '操作人';
set @remark = '备注';
set @state = '单据状态';
set @Qualification='1001,商品编号,商品名称,商品规格,商品颜色,商品单位,11,仓库名称,10,10,10,100,入库批次,备注,1002,商品编号2,商品名称2,商品规格2,商品颜色2,商品单位,22,仓库名称2,20,20,20,200,入库批次2,备注2,1003,商品编号3,商品名称3,商品规格3,商品颜色3,商品单位,33,仓库名称3,30,30,30,300,入库批次3,备注3,';
call addRow(@t_enterprise_keyid,@number,@serial,@date,@type,@man,@operator,@remark,@state,@Qualification);

0 0