sqlserver函数存储过程.docx
- 文档编号:26335363
- 上传时间:2023-06-17
- 格式:DOCX
- 页数:19
- 大小:60.20KB
sqlserver函数存储过程.docx
《sqlserver函数存储过程.docx》由会员分享,可在线阅读,更多相关《sqlserver函数存储过程.docx(19页珍藏版)》请在冰豆网上搜索。
sqlserver函数存储过程
---关联更新的语句
updateShopBoughtNote
setProductAmount=(selectsum(productNum)fromShopBoughtNoteProductListbwhereb.bnid=ShopBoughtNote.bnid)
whereexists(select1fromShopBoughtNoteProductListb
whereb.bnid=ShopBoughtNote.bnid)
declare@iint
set@i=10
declare@sqlusevarchar(100),@sql1varchar(1000),@sql2varchar(1000),@sql3varchar(1000)
while@i<16
begin
set@sqluse='use[shop'+cast(@iasvarchar)+'_3s360]'
EXEC(@sqluse)
if(notexists(select*fromShopKuQu))
begin
BeginTransaction
set@sql1='use[shop'+cast(@iasvarchar)+'_3s360]insertintoShopKuQu(QYName,EAID)values(区域1,'+@i+')'
EXEC(@sql1)
end
if(notexists(select*fromShopCangKuHuoJia))
begin
set@sql2='use[shop'+cast(@iasvarchar)+'_3s360]insertintoShopCangKuHuoJia(HJName,QYID)values(货架1,1)'
EXEC(@sql2)
end
if(notexists(select*fromShopStoragePlace))
begin
set@sql3='use[shop'+cast(@iasvarchar)+'_3s360]insertintoShopStoragePlace(HJID,PlaceName,MianJi,SYMianJI)values(1,位置1,100,100)'
EXEC(@sql3)
end
set@i=@i+1
CommitTransaction
end
If(@@ERROR<>0)
RollbackTransaction
UPDATEShopBoughtNoteSETProductAmount=DBO.GetProductListCount(BNID),ProductMoney=dbo.GetProductListMoney(BNID)
SELECT*FROMShopBoughtNoteProductListWHEREBNID=1001
--获取采购单商品数量
createfunctionGetProductListCount(@bnidint)
returnsint
begin
declare@numint
SELECT@num=SUM(ProductNum)FROMShopBoughtNoteProductListWHEREBNID=@bnid
if(@numisnull)
set@num=0
return@num
end
--获取采购单商品数量
createfunctionGetProductListMoney(@bnidint)
returnsMONEY
begin
declare@MONEYMONEY
SELECT@MONEY=SUM(Pricing)FROMShopBoughtNoteProductListWHEREBNID=@bnid
if(@MONEYisnull)
set@MONEY=0
return@MONEY
end
=============创建存储过程=============
Procedure[dbo].[UP_getCrumb](@IDint)
as
DECLARE@cnameVARCHAR(600)
SET@cname=''
while@ID>0
begin
declare@cname2varchar(600)
set@cname2=(selectCategoryNamefromProductCategorywhereCategoryID=@ID)
IF(@cname<>'')
set@cname=' cid='+Cast(@IDasvarchar(50))+'''>'+@cname2+'>'+@cname ELSE set@cname=' cid='+Cast(@IDasvarchar(50))+'''>'+@cname2+'>'+@cname set@ID=(selectParentIDfromProductCategorywhereCategoryID=@ID) END SELECT@cnameCategoryPath----显示结果 执行: execUP_getCrumb13 结果为: 函数写好后直接运行就可以生成 函数里面不可以有insertdelete语句 函数一般返回值 在sqlserver中不用像oracle中一样写在beginend中间直接写就可以 =============过程中使用游标=============== declare@Productidint,@productPrcingmoney,@strvarchar(50)---声明使用的变量 DeclareMy_CursorCursorForselectt.ProductID,t.PricingfromProductMessaget–查询表这里貌似用什么字段才查什么字段 OpenMy_Cursor—打开游标 FetchnextFromMy_Cursorinto@Productid,@productPrcing—将当前行的中的两个字段赋值给两个变量 While(@@Fetch_Status=0) Begin Begin set@Productid=Convert(Char(20),@Productid) set@productPrcing=Convert(Char(20),@productPrcing) set@str=cast(@Productidasvarchar(50))+': '+cast(@productPrcingasvarchar(50)) print@str—输出结果 End FetchnextFromMy_Cursor–下一行 Into@Productid,@productPrcing End CloseMy_Cursor—关闭游标 deallocateMy_Cursor—销毁游标 注意: ==========存储过程执行动态sql语句======== createprocedure[dbo].[GetKuCunYJProduct] @eaidVARCHAR(50),--机构编号 @PidsVARCHAR(500) AS DECLARE@sqlVARCHAR(4000) SET@sql='SELECTProductID,ProductName,BatchMoney,SellMoney,ProductPicPath,dbo.GetProductSaleCount('+@eaid+',ProductID)asShopSale,BuyNum,Hits,dbo.getStockById('+@eaid+',ProductID)ASKuCun,DayBuyNumFROM[3S360].[3s360_Shop].dbo.ProductBaseWHEREProductIDIN(SELECTProductIDFROMdbo.ShopStockAlarmWHEREAlarmType=1ANDStockAlarm>=dbo.getStockById(EAID,ProductID)UNION SELECTProductIDFROMdbo.ShopStockAlarmWHEREAlarmType=2ANDStockAlarm>=dbo.getStockById(EAID,ProductID)ANDendtime EXEC(@sql) ======sqlserver中没有for循环只能这么写======= declare@iint set@i=13 declare@sqlvarchar(1000) while@i<16 begin BeginTry–异常捕获 set@sql='' SET@sql='use[shop'+cast(@iasvarchar)+'_3s360]altertableshopstoragePlaceProductaltercolumnProductNamevarchar(200)' print@sql EXEC(@sql) set@i=@i+1 endtry BeginCatch set@i=@i+1—处理异常这里的意义是忽略过程中的错误继续运行否则的话遇到错误就会中断 EndCatch end =============过程中使用事务============= --方式一 ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[USP_ProcedureWithTransaction_Demo]')andOBJECTPROPERTY(id,N'IsProcedure')=1) dropprocedure[dbo].[USP_ProcedureWithTransaction_Demo] GO CreatePROCEDURE[dbo].[USP_ProcedureWithTransaction_Demo] As Begin SETXACT_ABORTON BeginTransaction InsertIntoLock(LockTypeID)Values('A')--此语句将出错,LockTypeID为Int类型 UpdateLockSetLockTypeID=2WhereLockID=32 CommitTransaction SETXACT_ABORTOFF End GO --方式二 ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[USP_ProcedureWithTransaction_Demo]')andOBJECTPROPERTY(id,N'IsProcedure')=1) dropprocedure[dbo].[USP_ProcedureWithTransaction_Demo] GO CreatePROCEDURE[dbo].[USP_ProcedureWithTransaction_Demo] As Begin BeginTransaction InsertIntoLock(LockTypeID)Values('A')--此语句将出错,LockTypeID为Int类型 UpdateLockSetLockTypeID=1WhereLockID=32 CommitTransaction If(@@ERROR<>0) RollbackTransaction End GO --方式三 ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[USP_ProcedureWithTransaction_Demo]')andOBJECTPROPERTY(id,N'IsProcedure')=1) dropprocedure[dbo].[USP_ProcedureWithTransaction_Demo] GO CreatePROCEDURE[dbo].[USP_ProcedureWithTransaction_Demo] As Begin BeginTry BeginTransaction UpdateLockSetLockTypeID=1WhereLockID=32--此语句将出错,LockTypeID为Int类型 InsertIntoLock(LockTypeID)Values('A') CommitTransaction EndTry BeginCatch RollbackTransaction EndCatch End GO Exec[USP_ProcedureWithTransaction_Demo] 存储过程调试=====调试存储过程要在本机上调试远程调试会有连接问题 1.在存储过程中设置断点 2.调试—>逐句调试 3.F10一行一行运行 =========Sqlserver临时表变量============= DECLARE@ShopKuQutable----定义临时表变量 ( QYNamevarchar(50)NOTNULL, EAIDintNOTNULL ) Declare@qnamevarchar(20),@Eaidint declare@iINT SET@i=1 while@i<10----循环插入10行数据 begin insertinto@ShopKuQu(QYName,EAID)values('liyutao'+cast(@iasvarchar),@i) SET@i=@i+1 end DeclareMy_CursorCursorForselectQYName,EAIDfrom@ShopKuQu–定义游标 OpenMy_Cursor--打开游标 FetchnextFromMy_Cursorinto@qname,@Eaid—遍历游标 While(@@Fetch_Status=0) Begin–循环开始 if(@qname='liyutao3'or@Eaid=3)—条件判断语句 begin–如果开始 print'当前用户是: '+@qname—打印当前 print'当前用户Eaid: '+cast(@Eaidasvarchar) FetchnextFromMy_Cursor–游标转到下一行 Into@qname,@Eaid end–如果结束 else begin—否则开始 print@qname+'+'+cast(@Eaidasvarchar)--输出结果 FetchnextFromMy_Cursor–游标转到下一行 Into@qname,@Eaid end—否则开始 end–循环结束 CloseMy_Cursor deallocateMy_Cursor print'end' go =====Sqlserver执行动态sql语句返回值===== declare@numint, @sqlsnvarchar(4000) set@sqls='select@a=count(*)fromShopKuQu' execsp_executesql@sqls,N'@aintoutput',@numoutput print@num 1,它们之间最大的区别是嵌入式的参数,如下面一个语句 declare@sqlnvarchar(2000) declare@idvarchar(20) set@id='1' set@sql='selectcount(*)fromempwhereid='+@id exec@sql 我想把得到的count(*)传出来,用传统的exec是不好办到的,但是用sp_executesql则很容易就办到了: declare@sqlnvarchar(2000) declare@couint declare@idvarchar(20) set@id='1' set@sql='select@count=count(*)fromempwhereid=@id' execsp_executesql@sql,N'@countintout,@idvarchar(20)',@couout @id print@cou ===============查看被锁的表============== SELECTDISTINCTdb_name(A.dbid)AS数据库名,req_modeAS锁类型,object_name(rsc_objid)AS表名,B.* FROMmaster..sysprocessesAINNERJOINmaster..syslockinfobONA.spid=B.req_spid ORDERBYdb_name(a.dbid),object_name(rsc_objid) ===============释放被锁的表============== usemaster declare@sqlnvarchar(500) declare@spidnvarchar(20) declare#tbcursorfor selectspid=cast(spidasvarchar(20))frommaster..sysprocesseswheredbid=db_id('Shop14_3s360') open#tb fetchnextfrom#tbinto@spid while@@fetch_status=0 begin exec('kill'+@spid) fetchnextfrom#tbinto@spid end close#tb deallocate#tb ==============循环插入记录============= declare@iint set@i=10 declare@sqlusenvarchar(1000),@sql1nvarchar(1000),@sql2NVARCHAR(1000),@sql3NVARCHAR(1000) BeginTRANSACTION while@i<125 BEGIN DECLARE@numint Declare@MaxIDint set@sql1='use[shop'+cast(@iasvarchar)+'_3s360]select@a=count(*)fromShopKuQu'--查询库区数量 execsp_executesql@sql1,N'@aintoutput',@numoutput IF(@num=0)--如果没有数量 begin set@sql1='use[shop'+cast(@iasvarchar)+'_3s360]insertintoShopKuQu(QYName,EAID)values(''A'','+CAST(@iASVARCHAR)+')' PRINT@sql1 EXEC(@sql1) END SET@sql2='use[shop'+cast(@iasvarchar)+'_3s360]select@a=count(*)fromShopCangKuHuoJia' execsp_executesql@sql2,N'@aintoutput',@numoutput IF(@num=0) begin set@sql2='use[shop'+cast(@iasvarchar)+'_3s360]select@a=MAX(QYID)fromShopkuqu'---查询库区最大的id execsp_executesql@sql2,N'@aintoutput',@MaxIDoutput set@sql2='use[shop'+cast(@iasvarchar)+'_3s360]insertintoShopCangKuHuoJia(HJName,QYID)values(''A01'','+CAST(@MaxIDasvarchar)+')' EXEC(@sql2) END SET@sql3='use[shop'+cast(@iasvarchar)+'_3s360]select@a=count(*)fromShopStoragePlace' execsp_executesql@sql3,N'@aintoutput',@numoutput IF(@num=0) begin set@sql3='use[shop'+cast(@iasvarchar)+'_3s360]select@a=MAX(HJID)fromShopCangKuHuoJia'---查询最大的id execsp_executesql@sql3,N'@aintoutput',@MaxIDoutput set@sql3='use[shop'+cast(@iasvarchar)+'_3s360]insertintoShopStoragePlace(HJID,PlaceName,MianJi,SYMianJI)values('+CAST(@MaxIDasvarchar)+',''A0001'',100,100)' EXEC(@sql3) end set@i=@i+1 END
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sqlserver 函数 存储 过程