sql增删改查存储过程精Word格式文档下载.docx
- 文档编号:22109908
- 上传时间:2023-02-02
- 格式:DOCX
- 页数:29
- 大小:17.88KB
sql增删改查存储过程精Word格式文档下载.docx
《sql增删改查存储过程精Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《sql增删改查存储过程精Word格式文档下载.docx(29页珍藏版)》请在冰豆网上搜索。
set@employee_ID='
emp1001'
--作动见编号就知道是什么表
else
emp'
+cast(cast(substring(@employee_ID,4,4asint+1asvarchar(20
insertintotb_employeevalues(@employee_ID,@employee_name,@employee_sex,
@employee_birthday,@employee_phone,@employee_cardID,
@employee_address,@gov_id,@employee_study,@employee_basepay
End
CREATEprocproc_employee_update
updatetb_employeesetemployee_name=@employee_name,
employee_sex=@employee_sex,
employee_birthday=@employee_birthday,
employee_phone=@employee_phone,
employee_cardID=@employee_cardID,
employee_address=@employee_address,
gov_id=@gov_id,
employee_basepay=@employee_basepay,
employee_study=@employee_study
whereemployee_ID=@employee_ID
GO
createprocproc_favor_delete
@house_favorIDvarchar(10=null,
@proc_infovarchar(20output
deletefromtb_favorwherehouse_favorID=@house_favorID;
if(@@error=0
set@proc_info='
OK'
systemerror:
'
+cast(@@errorasvarchar(6
createprocproc_favor_insert
@favor_namevarchar(20='
@favor_remarkvarchar(50='
select@house_favorID=Max(house_favorIDfromtb_favor
if(@house_favorIDisnull
set@house_favorID='
fav1001'
fav'
+cast(cast(substring(@house_favorID,4,4asint+1asvarchar(20
ifexists(selectfavor_namefromtb_favorwherefavor_name=@favor_name
isHave'
--控制不要输入两个同样的信息
insertintotb_favorvalues(@house_favorID,@favor_name,@favor_remark
ok'
end
createprocproc_favor_update
@house_favorIDvarchar(10,
updatetb_favorsetfavor_name=@favor_name,favor_remark=@favor_remarkwherehouse_favorID=@house_favorID
createprocproc_fitment_delete
@house_fitmentIDvarchar(10=null
deletefromtb_fitmentwherehouse_fitmentID=@house_fitmentID
CREATEprocproc_fitment_insert
@house_fitmentIDvarchar(10=null,
@fitment_namevarchar(20='
@fitment_remarkvarchar(50='
select@house_fitmentID=Max(house_fitmentIDfromtb_fitment
print@house_fitmentID
if(@house_fitmentIDisnull
set@house_fitmentID='
fit1001'
fit'
+cast(cast(substring(@house_fitmentID,4,4asint+1asvarchar(20
ifexists(selectfitment_namefromtb_fitmentwherefitment_name=@fitment_name
insertintotb_fitmentvalues(@house_fitmentID,@fitment_name,@fitment_remark
createprocproc_fitment_update
updatetb_fitmentsetfitment_name=@fitment_name,fitment_remark=@fitment_remarkwherehouse_fitmentID=@house_fitmentID
createprocproc_floor_delete
@house_floorIDvarchar(10=null
deletefromtb_floorwherehouse_floorID=@house_floorID
CREATEprocproc_floor_insert
@house_floorIDvarchar(10=null,
@floor_namevarchar(20='
@floor_remarkvarchar(50='
select@house_floorID=Max(house_floorIDfromtb_floor
if(@house_floorIDisnull
set@house_floorID='
flo1001'
flo'
+cast(cast(substring(@house_floorID,4,4asint+1asvarchar(20
ifexists(selectfloor_namefromtb_floorwherefloor_name=@floor_name
insertintotb_floorvalues(@house_floorID,@floor_name,@floor_remark
createprocproc_floor_update
updatetb_floorsetfloor_name=@floor_name,
floor_remark=@floor_remark
wherehouse_floorID=@house_floorID
createprocproc_gov_delete
@gov_idvarchar(10=null
deletefromtb_govwheregov_id=@gov_id
CREATEprocproc_gov_insert
@gov_idvarchar(10=null,
@gov_namevarchar(20='
@gov_remarkvarchar(50='
select@gov_id=Max(gov_idfromtb_gov
if(@gov_idisnull
set@gov_id='
gov1001'
gov'
+cast(cast(substring(@gov_id,4,4asint+1asvarchar(20
ifexists(selectgov_namefromtb_govwheregov_name=@gov_name
insertintotb_govvalues(@gov_id,@gov_name,@gov_remark
createprocproc_gov_update
as
updatetb_govsetgov_name=@gov_name,
gov_remark=@gov_remark
wheregov_id=@gov_id
createprocproc_house_delete
@house_IDvarchar(10
deletefromtb_housewherehouse_ID=@house_ID
CREATEprocproc_house_insert
@house_IDvarchar(10=null,
@house_companyNamevarchar(50,
@huose_typeIDvarchar(10,
@house_seatIDvarchar(10,
@house_statevarchar(10,
@house_fitmentIDvarchar(10,
@house_favorIDvarchar(10,
@house_mothedIDvarchar(10,
@huose_mapvarchar(50,
@house_pricefloat,
@house_floorIDvarchar(10,
@house_buildYearint,
@house_areavarchar(20,
@house_remarkvarchar(50,
@user_idsvarchar(10
set@house_ID=(selectMax(house_IDfromtb_house
declare@sqlvarchar(300
if(@house_IDisnull
set@house_ID='
hou1001'
hou'
+cast(substring(@house_ID,4,4+1asvarchar(10
insertintotb_housevalues
(@house_ID,
@house_companyName,
@huose_typeID,
@house_seatID,
@house_state,
@house_fitmentID,
@house_favorID,
@house_mothedID,
@huose_map,
@house_price,
@house_floorID,
@house_buildYear,
@house_area,
@house_remark,
@user_ids
--上面先插入
set@sql='
selectuser_id用户编号,house_price房价,house_area房屋面积fromtb_intentwhere
huose_typeID='
+@huose_typeID+'
andhouse_seatID='
+@house_seatID+'
andhouse_fitmentID='
+@house_fitmentID+'
andhouse_floorID='
+@house_floorID+'
andhouse_favorID='
+@house_favorID+'
andhouse_mothedID='
+@house_mothedID+'
print@sql
exec(@sql
CREATEprocproc_house_update
@house_IDvarchar(10,
@house_buildYearvarchar(10,
@house_remarkvarchar(50
updatetb_houseset
house_companyName=@house_companyName,
huose_typeID=@huose_typeID,
house_seatID=@house_seatID,
house_fitmentID=@house_fitmentID,
house_favorID=@house_favorID,
house_mothedID=@house_mothedID,
huose_map=@huose_map,
house_price=@house_price,
house_floorID=@house_floorID,
house_buildYear=@house_buildYear,
house_area=@house_area,
house_remark=@house_remark
wherehouse_ID=@house_ID
CREATEprocproc_intent_insert
@intend_IDvarchar(10=null,
@user_idvarchar(10,
@house_fitmentIDvarchar(10,
@house_pricenumeric(10,
@house_areavarchar(20
set@intend_ID=(selectMax(intent_IDfromtb_intent
if(@intend_IDisnull
set@intend_ID='
int1001'
int'
+cast(substring(@intend_ID,4,4+1asvarchar(10
insertintotb_intentvalues
(
@intend_ID,
@user_id,
@house_area
selecthouse_id房屋编号,user_ids户主编号,house_price价格,house_area房屋面积fromtb_housewhere
createprocproc_login_delete
@login_namevarchar(20=null,
@ReturnInfonvarchar(50=nulloutput
--删除时给你两种方法员工编号和用户名
if(@employee_IDisnulland@employee_IDisnull
set@ReturnInfo='
xingxibuquan'
deletefromtb_loginwhereemployee_ID=@employee_IDorlogin_name=@login_name
systeminfo'
+cast(@@errorasvarchar(10
createprocproc_login_insert
@login_idvarchar(10=null,
@login_pwdvarchar(15=null,
@login_powervarchar(10=null,
a
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 增删 存储 过程