MYSQL主从表插⼊,使⽤存储过程
-- 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) ;
mysql存储过程使用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_cou 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);
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论