MYSQL数据库存储过程字符串分割实现入库退货

库存管理的商品入库退货 相对简单
存储过程代码
CREATE DEFINER=root@localhost FUNCTION rksfxxtjxgtuih(yewubianma int ,
danweibianma varchar(50), – 进货公司的代码
jingbanren varchar(10), – 经办人
fangshi varchar(10), – 付款方式
wj int, – 电脑操作人
beizu varchar(100), – 备注
yw varchar(10), – 业务类型
str varchar(8000) – 要入库商品字符串
) RETURNS varchar(50) CHARSET utf8mb4
DETERMINISTIC
label:BEGIN – 程序太长 用LEAVE label; 跳转结束 少用条件else 让程序好读一些

DECLARE zuidabianh INT DEFAULT 0;

DECLARE next INT DEFAULT 1;
DECLARE n INT DEFAULT 0;
DECLARE n2 INT DEFAULT 0;
DECLARE jcbm INT DEFAULT 0;
DECLARE shuliang INT DEFAULT 0;
DECLARE rkj decimal(16,2) DEFAULT 0;
– DECLARE ckj decimal(16,2) DEFAULT 0;
– DECLARE ph varchar(60) DEFAULT 0;
– DECLARE yxq varchar(20);
DECLARE bz varchar(50);
DECLARE crrq varchar(50);
DECLARE pdskr varchar(20) ;
DECLARE rukubianhaoyjbcg INT DEFAULT 0;

if yewubianma>0 THEN
select 收费人,日期 INTO pdskr,crrq from ckjlbmb where 业务编码=yewubianma;
if pdskr is not null THEN –
RETURN concat(‘数据已经上账不能修改!’, yewubianma,‘*’,crrq);
LEAVE label;
END IF;
END IF;

set n =(length(str) - length(replace(str,‘!’,‘’)))/8; – 字符长度

Insert Into ckjlbmb(往来单位,办理人,收费方式,微机,备注,业务类型) Values(danweibianma,jingbanren,fangshi,wj,beizu,yw);
set zuidabianh =LAST_INSERT_ID();
UPDATE ckjlbmb SET 业务编码=zuidabianh WHERE 序号=zuidabianh;
set n2=0;
set next=1;
while n2<n do – 循环提取 插入每条数据
SET jcbm= CAST(reverse(substring_index(reverse(substring_index(str,‘!’,next)),‘!’,1)) AS SIGNED );
SET shuliang=CAST(reverse(substring_index(reverse(substring_index(str,‘!’,next+1)),‘!’,1)) AS SIGNED );
SET rkj=CAST(reverse(substring_index(reverse(substring_index(str,‘!’,next+2)),‘!’,1)) AS decimal(16,2));
– SET ckj=CAST(reverse(substring_index(reverse(substring_index(str,‘!’,next+3)),‘!’,1)) AS decimal(16,2));
– SET ph=reverse(substring_index(reverse(substring_index(str,‘!’,next+4)),‘!’,1));
– SET yxq=reverse(substring_index(reverse(substring_index(str,‘!’,next+5)),‘!’,1));
SET bz=reverse(substring_index(reverse(substring_index(str,‘!’,next+6)),‘!’,1));
SET rukubianhaoyjbcg= CAST(reverse(substring_index(reverse(substring_index(str,‘!’,next+7)),‘!’,1)) AS SIGNED );
set next=next+8;

if yewubianma=0 THEN – 没有业务变编号 直接添加
Insert Into sjjlb(业务编码,入库编码,数量,单价,备注,入库价) Values(zuidabianh,rukubianhaoyjbcg,-shuliang ,rkj,bz,rkj);
ELSE
– 有业务编码的 看是否添加过
if not exists(select 入库编码 from sjjlb where 入库编码=rukubianhaoyjbcg and 业务编码=yewubianma) THEN
Insert Into sjjlb(业务编码,入库编码,数量,单价,备注,入库价) Values(yewubianma,rukubianhaoyjbcg,-shuliang ,rkj,bz,rkj);
ELSE
UPDATE sjjlb SET 数量=-shuliang,备注 =bz,单价=rkj,入库价=rkj WHERE 入库编码=rukubianhaoyjbcg AND 业务编码=yewubianma;
END IF;

END IF;

Set n2=n2+1;
end while;

if yewubianma>0 THEN
set zuidabianh= yewubianma;
END IF;

select 日期 INTO crrq from ckjlbmb where 业务编码=zuidabianh; – 查询插入信息日期
RETURN concat(‘入库退货数据添加成功 !’,zuidabianh,‘*’,crrq);

END
vb6 程序调用
Private Sub 商品入口退货()
‘检查收费合规 略
With MSHFlexGrid2
hangshu = .Rows - 2
shuzushuzu = .TextMatrix(1, 19) & “!” & .TextMatrix(1, 7) & “!” & .TextMatrix(1, 8) & “!” & .TextMatrix(1, 10) & “!” & Replace(.TextMatrix(1, 12), “!”, “”) & “!” & Replace(.TextMatrix(1, 13), “!”, “”) & “!” & .TextMatrix(1, 14) & “!” & .TextMatrix(1, 18) & “!”
If hangshu > 1 Then
For a1 = 2 To hangshu
shuzushuzu = shuzushuzu & .TextMatrix(a1, 19) & “!” & .TextMatrix(a1, 7) & “!” & .TextMatrix(a1, 8) & “!” & .TextMatrix(a1, 10) & “!” & Replace(.TextMatrix(a1, 12), “!”, “”) & “!” & Replace(.TextMatrix(a1, 13), “!”, “”) & “!” & .TextMatrix(a1, 14) & “!” & .TextMatrix(a1, 18) & “!”
Next a1
End If
SQL = Val(Textdjh) & ",’" & RKDW & “‘,’” & Textjbr & “‘,’” & Combo5 & “',” & blcaozuoyuanbm & “,'” & Text2 & “‘,’” & blyewu & “'”
‘业务编码 入库单位编码 经办人 收费方式 微机 备注 业务类型
SQL = SQL & “,” & "’" & shuzushuzu & “'”

If blyewu = “入库” Then SQL = “SELECT rksfxxtjxg(” & SQL & “) AS AnnualSalary;”
If blyewu = “入库退货” Then SQL = “SELECT rksfxxtjxgtuih(” & SQL & “) AS AnnualSalary;”

Set RST = CNN.Execute(SQL)
FHZ = RST!AnnualSalary
RST.Close
MsgBox FHZ, vbOKOnly + 64, “温馨提示”
'从返回值 提取
Text6 = Mid(FHZ, InStr(1, FHZ, “") + 1, 20)
Text6 = Format(Text6, “yyyy-mm-dd hh:mm”)
Textdjh = Mid(FHZ, InStr(1, FHZ, “!”) + 1, (InStr(1, FHZ, "”) - InStr(1, FHZ, “!”) - 1))
单据显示

End With
End Sub

没有高深代码 实际工作应用。