医药销售管理系统sql语句文档格式.doc
《医药销售管理系统sql语句文档格式.doc》由会员分享,可在线阅读,更多相关《医药销售管理系统sql语句文档格式.doc(11页珍藏版)》请在冰点文库上搜索。
![医药销售管理系统sql语句文档格式.doc](https://file1.bingdoc.com/fileroot1/2023-4/29/bef672ea-4d4f-4f0d-b829-6a3157f9816d/bef672ea-4d4f-4f0d-b829-6a3157f9816d1.gif)
createtablesellMain/*创建医药销售主表*/
(SaleNointconstraintSM_PRIMPRIMARYKEY,
WorkNochar(10)FOREIGNKEYREFERENCESWorkInfor(WorkNo),
SaleDateDateTime,
AmountMoney)
createtablesellChild/*创建医药销售子表*/
(SaleNointconstraintSC_PRIMPRIMARYKEY,
MedicineCodechar(6)FOREIGNKEYREFERENCESMedInfor(MedicineCode),
MedicineNamevarchar(32)NOTNULL,
PriceMoney,
NumberInt,
Uintchar(8),
/*插入数据的存储过程*/
createprocMedID_proc
@MedKindeCodechar(10),@KindExplanationvarchar(12)
as
insertintoMedID(MedKindeCode,KindExplanation)values(@MedKindeCode,@KindExplanation)
execMedID_proc'
0001'
'
口腔溃疡'
0002'
感冒'
0003'
发烧'
0004'
拉肚子'
0005'
'
外伤'
createprocMedInfor_proc
@MedicineCodechar(6),@MedicineNamevarchar(8),@MedKindeCodechar(10),@Pricemoney,@ListPricemoney,
@Numberint,@FirmCodechar(10),@UserfulllifeDatetime
insertintoMedInfor(MedicineCode,MedicineName,MedKindeCode,Price,ListPrice,
Number,Supplicer,Userfulllife)values(@MedicineCode,@MedicineName,@MedKindeCode,@Price,@ListPrice,
@Number,@FirmCode,@Userfulllife)
execMedInfor_proc'
1001'
板蓝根'
5,3,'
100'
014'
2010-12-5'
2002'
四季感康'
14,10.5,'
150'
051'
2010-12-12'
2003'
银黄颗粒'
12,8.8,'
120'
2012-10-6'
2004'
感冒清热软胶囊'
17,12,'
015'
'
2011-11-1'
3001'
阿斯匹林'
15,11,'
2010-12-1'
3002'
布洛芬'
21,17.5,'
120'
2010-6-5'
4001'
泻利挺'
25,20,'
2012-10-2'
4002'
诺氟沙星胶囊'
15,12,'
2012-9-16'
5001'
碘酒'
5,2.5,'
50'
2012-10-12'
5002'
创口贴'
2,1,'
250'
2015-5-1'
createprocGueInfor_proc
@GuestCodechar(10),@GuestNamevarchar(16),@GLinkvarchar(12),@GLinkTellvarchar(11),
@Cityvarchar(8)
insertintoGueInfor(GuestCode,GuestName,GLink,GLinkTell,
City)values(@GuestCode,@GuestName,@GLink,@GLinkTell,@City)
execGueInfor_proc'
015112'
zhangsan'
xiaozhang'
668401'
jiaxing'
065114'
lisi'
xiaofang'
614425'
yuyao'
052114'
wangwu'
xiaowu'
659024'
wenzhou'
043115'
zhaoliu'
615874'
shangyu'
014221'
awu'
651283'
linan'
025471'
asha'
691472'
dongyang'
createprocFirmInfor_proc
@FirmCodechar(10),@FirmNamevarchar(16),@Linkvarchar(12),@LinkTellvarchar(11),@Cityvarchar(8)
insertintoFirmInfor(FirmCode,FirmName,Link,LinkTell,City)
values(@FirmCode,@FirmName,@Link,@LinkTell,@City)
execFirmInfor_proc'
yangshengtang'
xiaotai'
681472'
huzhou'
baozhilin'
zhangqing'
658421'
deqing'
pinmingdayaofang'
oudan'
65417'
xiangshan'
createprocWorkInfor_proc
@WorkNochar(10),@Namevarchar(12),@UserRegNamechar(6),@Passwordchar(10),@Positionchar(10),@PowerInt
insertintoWorkInfor(WorkNo,Name,UserRegName,Password,Position,Power)
values(@WorkNo,@Name,@UserRegName,@Password,@Position,@Power)
execWorkInfor_proc'
075101'
ZKL'
zkl01'
456789'
jingli'
'
075201'
ZJM'
zjm01'
123789'
dongshi'
075215'
WMX'
wmx05'
147258'
xiaomi'
075120'
ZZW'
zzm20'
123456'
buzhang'
createprocsellMain_proc
@SaleNoint,@WorkNochar(10),@SaleDateDateTime,@AmountMoney
insertintosellMain(SaleNo,WorkNo,SaleDate,Amount)
values(@SaleNo,@WorkNo,@SaleDate,@Amount)
execsellMain_proc'
12'
2009-1-1'
1000
13'
1500
15'
800
20'
1200
alterprocsellChild_proc
@SaleNoint,@MedicineCodechar(6),@MedicineNamevarchar(32),@PriceMoney,@NumberInt,@Uintchar(8),@AmountMoney
insertintosellChild(SaleNo,MedicineCode,MedicineName,Price,Number,Uint,Amount)
values(@SaleNo,@MedicineCode,@MedicineName,@Price,@Number,@Uint,@Amount)
execsellChild_proc'
5,'
bao'
100
14,'
he'
210
15,'
300
/*删除数据的存储过程*/
createprocMedID_delete_proc
@MedKindeCodechar(10)
deletefromMedId
whereMedKindeCode=@MedKindeCode
execMedID_delete_proc'
createprocMedInfor_delete_proc
@MedicineNamevarchar(8)
deletefromMedInfor
whereMedicineName=@MedicineName
createprocGueInfor_delete_proc
@GuestCodechar(10)
deletefromGueInfor
whereGuestCode=@GuestCode
createprocFirmInfor_delete_proc
@FirmCodechar(10)
deletefromFirmInfor
whereFirmCode=@FirmCode
createprocWorkInfor_delete_proc
@WorkNochar(10)
deletefromWorkInfor
whereWorkNo=@WorkNo
createprocsellMain_delete_proc
@SaleNoint
deletefromsellMain
whereSaleNo=@SaleNo
createprocsellChild_delete_proc
deletefromsellChild
/*修改数据的存储过程*/
createprocMedID_update_proc
@MedKindeCodechar(10),@KindExplanationvarchar(12),@MedKindeCode1char(10)
updateMedID
setMedKindeCode=@MedKindeCode,KindExplanation=@KindExplanation
whereMedKindeCode=@MedKindeCode1
execMedID_update_proc'
createprocMedInfor_update_proc
@MedicineCode1char(6),@MedicineNamevarchar(8),@MedKindeCodechar(10),@Pricemoney,@ListPricemoney,
@Numberint,@FirmCodechar(10),@UserfulllifeDatetime,@MedicineCodechar(6)
updateMedInfor
setMedicineCode=@MedicineCode1,MedicineName=@MedicineName,MedKindeCode=@MedKindeCode,Price=@Price,
ListPrice=@ListPrice,Number=@Number,FirmCode=@FirmCode,Userfulllife=@Userfulllife,MedicineCode=@MedicineCode
whereMedKindeCode=@MedKindeCode
createprocGueInfor_update_proc
@GuestCode1char(10),@GuestNamevarchar(16),@GLinkvarchar(12),@GLinkTellvarchar(11),
@Cityvarchar(8),@GuestCodechar(10)
updateGueInfor
setGuestCode=@GuestCode1,GuestName=@GuestName,GLink=@GLink,GLinkTell=@GLinkTell,
City=@City
createprocFirmInfor_update_proc
@FirmCode1char(10),@FirmNamevarchar(16),@Linkvarchar(12),@LinkTellvarchar(11),@Cityvarchar(8),
updateFirmInfor
setFirmCode=@FirmCode1,FirmName=@FirmName,Link=@Link,LinkTell=@LinkTell,City=@City
createprocWorkInfor_update_proc
@WorkNo1char(10),@Namevarchar(12),@UserRegNamechar(6),@Passwordchar(10),@Positionchar(10),@PowerInt,
updateWorkInfor
setWorkNo=@WorkNo1,Name=@Name,UserRegName=@UserRegName,Password=@Password,Position=@Position,Power=@Power
createprocsellMain_update_proc
@SaleNo1int,@WorkNochar(10),@SaleDateDateTime,@AmountMoney,@SaleNoint
updatesellMain
setSaleNo=@SaleNo1,WorkNo=@WorkNo,SaleDate=@SaleDate,Amount=@Amount
createprocsellChild_update_proc
@SaleNo1int,@MedicineCodechar(6),@MedicineNamevarchar(32),@PriceMoney,@NumberInt,@Uintchar(8),@AmountMoney,
updatesellChild
setSaleNo=@SaleNo1,MedicineCode=@MedicineCode,MedicineName=@MedicineName,Price=@Price,Number=@Number,Amount=@Amount
/*建立存储过程实现单表查询*/
/*建立名为“单表查询1”的存储过程,用来查询某种药品的信息*/
createproc单表查询1
@MedicineCodechar(6)
AS
select*
fromMedInfor
whereMedicineCode=@MedicineCode
/*建立名为“单表查询2”的存储过程,用来查询某个客户的信息*/
createproc单表查询2
@GuestCodechar(10)
fromGueInfor
whereGuestCode=@GuestCode
/*建立名为“单表查询3”的存储过程,用来查询某个员工的信息*/
createproc单表查询3
fromWorkInfor
whereWorkNo=@WorkNo
/*建立名为“单表查询4”的存储过程,用来查询某个供应商的信息*/
createproc单表查询4
fromFirmInfor
whereFirmCode=@FirmCode
/*建立名为“单表查询5”的存储过程,用来查询某个药品代码对应的药品类型的信息*/
createproc单表查询5
fromMedID
whereMedKindeCode=@MedKindeCode
/*建立存储过程实现连接查询*/
/*建立名为“连接查询1”的存储过程,用来查询某个药品名称对应的药品类型的信息*/
createproc连接查询1
selectMedicineName,KindExplanation
fromMedInfor,MedID
whereMedID.MedKindeCode=MedInfor.MedKindeCodeand
MedicineName=@MedicineName
/*建立名为“连接查询2”的存储过程,用来查询某个供应商提供的药品类型*/
createproc连接查询2
@FirmNamevarchar(16)
selectFirmName,KindExplanation