MySQL存储过程库存管理商品信息设计

huwei853

已于 2024-10-13 11:12:12 修改

阅读量801

点赞数 9

文章标签： mysql android 数据库

于 2024-09-24 15:58:55 首次发布

本文链接：https://blog.csdn.net/huwei853/article/details/142491728

版权

商品信息有基础信息入库信息两个部分组成
基础信息包括名称规格产地等
进货信息包括批号有效期入库价格出库价格
这样一个商品有一个基础信息会有多个入库信息！！1
如图

基础信息表展示
批次入库表展示
给客户展示信息信息
MySQL数据库存储过程基础信息添加修改
CREATE DEFINER=root@localhost FUNCTION jcxxtjxg(XINGMBH INT,wb1 varchar(100),wb2 varchar(10),wb3 varchar(10),wb4 varchar(50),wb5 varchar(50)
,wb6 decimal(16,2),wb7 varchar(50),wb8 varchar(50) ,wb9 varchar(50),wb10 varchar(50) ,wb11 varchar(10),wb12 varchar(100)
,wb13 varchar(10),wb14 varchar(50),wb15 varchar(50) ,wb16 decimal(16,2),wb17 decimal(16,2) ,wb18 varchar(50),wb19 varchar(50),wb20 varchar(10)
,wb21 varchar(10),wb22 varchar(50),wb23 varchar(50) ,wb24 varchar(50)
) RETURNS varchar(50) CHARSET utf8mb4
DETERMINISTIC
label:BEGIN
DECLARE zuidh INT DEFAULT 0;-- 最大号
DECLARE rukubianhao INT DEFAULT 0;-- 最大号
DECLARE py varchar(50); – 拼音码
set py=getFirstPinyin2(wb1); – 函数获取拼音码
IF XINGMBH = 0 THEN – 序号为零添加新数据
– if exists(select 基础编码 from sfzdb where 商品名称=wb1 and 规格=wb4 and 生产厂商=wb12) THEN
select max(基础编码) INTO zuidh from sfzdb where 商品名称=wb1 and 规格=wb4 and 生产厂商=wb12 ;
IF zuidh > 0 THEN

  RETURN  concat('此信息已经添加过了!,',zuidh); 
  LEAVE label;
  END IF;
		
   INSERT INTO sfzdb (`商品名称`,`单位`,`剂型`,`规格`,`商品分类`,`单价`,`位置`,`货号`,`状态`,`包装`,`费用类别`,`生产厂商`,`执行部门`,`药理功效`,`其他`,`最高限价`,`最低限价`,`给药途径`,`ATC大类`,`基药`,`甲乙`,`医保码`,`批准文号`,`国家码`,`拼音编码`)
             VALUES(wb1,wb2,wb3,wb4,wb5,wb6,wb7,wb8,wb9,wb10,wb11,wb12,wb13,wb14,wb15,wb16,wb17,wb18,wb19,wb20,wb21,wb22,wb23,wb24,py);
  SET zuidh=LAST_INSERT_ID(); -- 获取刚刚插入的编码	
 
		    UPDATE sfzdb SET `基础编码`=zuidh  where `序号`=zuidh;		
			
  RETURN  concat('数据添加成功！编码,',zuidh);
		 LEAVE label;

END IF;

IF XINGMBH > 0 THEN – 序号大于零的修改
UPDATE sfzdb SET 商品名称=wb1,单位=wb2,剂型=wb3,规格=wb4,商品分类=wb5,
单价=wb6,位置=wb7,货号=wb8,状态=wb9,包装=wb10,
费用类别=wb11,生产厂商=wb12,执行部门=wb13,药理功效=wb14,其他=wb15,
最高限价=wb16,最低限价=wb17,给药途径=wb18,ATC大类=wb19,
基药=wb20,甲乙=wb21,医保码=wb22,批准文号=wb23,国家码=wb24,拼音编码=py where 序号=XINGMBH;
RETURN concat(‘数据修改成功！编码’,XINGMBH);
END IF;
END

下面是每次入库代码
本程序可以自动匹配已经入库批次信息

CREATE DEFINER=root@localhost FUNCTION rksfxxtjxg(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 rukubianhao INT DEFAULT 0;
DECLARE zuidabianh INT DEFAULT 0;
DECLARE rukubianhaoXR 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;

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

if yewubianma=0 THEN – 没有业务变编号直接添加
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;

– 查询有没有做过信息做个就直接用没有添加一条新的
select 入库编码 INTO rukubianhao from sfxxb where 生产批号=ph and 有效期=yxq and 基础编码=jcbm and 入库单价=rkj and 出库单价=ckj and 进货公司=danweibianma ;
if rukubianhao>0 THEN
Insert Into sjjlb(业务编码,入库编码,数量,单价,备注,入库价) Values( zuidabianh,rukubianhao,shuliang ,rkj,bz,rkj);
ELSE
Insert Into sfxxb(入库单价,出库单价,生产批号,有效期,基础编码,进货公司) Values(rkj,ckj,ph ,yxq,jcbm,danweibianma);
Set rukubianhaoXR =LAST_INSERT_ID();
Update sfxxb set 入库编码=rukubianhaoXR where 序号=rukubianhaoXR;

Insert Into sjjlb(业务编码,入库编码,数量,单价,备注,入库价) Values(zuidabianh,rukubianhaoXR,shuliang ,rkj,bz,rkj);
END IF;
Set n2=n2+1;
end while;

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

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;

Update ckjlbmb set 往来单位=danweibianma, 办理人=jingbanren,收费方式=fangshi,备注=beizu where 业务编码=yewubianma;

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 rukubianhaoyjbcg>0 THEN – 已经入过库保存过有业务编号直接修改
Update sfxxb set 生产批号=ph,有效期=yxq,入库单价=rkj,出库单价=ckj,进货公司=danweibianma where 入库编码=rukubianhaoyjbcg;
– 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;

if rukubianhaoyjbcg=0 THEN – 没有商品新加入

set rukubianhao=0;
select 入库编码 INTO rukubianhao from sfxxb where 生产批号=ph and 有效期=yxq and 基础编码=jcbm and 入库单价=rkj and 出库单价=ckj and 进货公司=danweibianma ;
if rukubianhao>0 THEN
Insert Into sjjlb(业务编码,入库编码,数量,单价,备注,入库价) Values( yewubianma,rukubianhao,shuliang ,rkj,bz,rkj);
ELSE
Insert Into sfxxb(入库单价,出库单价,生产批号,有效期,基础编码,进货公司) Values(rkj,ckj,ph ,yxq,jcbm,danweibianma);
Set rukubianhaoXR =LAST_INSERT_ID();
Update sfxxb set 入库编码=rukubianhaoXR where 序号=rukubianhaoXR;

Insert Into sjjlb(业务编码,入库编码,数量,单价,备注,入库价) Values(yewubianma,rukubianhaoXR,shuliang ,rkj,bz,rkj);
END IF;

END IF;

Set n2=n2+1;
end while;

select 日期 INTO crrq from ckjlbmb where 业务编码=yewubianma;
RETURN concat(‘数据修改成功!’,yewubianma,‘*’,crrq);
LEAVE label;
END IF;

库存管理入库相对比较复杂先要做基础信息在做入库的批次信息。
这种库存管理程序只要掌握几个关键语句就可以编程，没有什么难度，剩下就是耐心。
门诊信息管理系统下载
https://download.csdn.net/download/huwei853/89879999?spm=1001.2014.3001.5503