sql server游标的作用_SQL数据库下载

sql server游标的作用_SQL数据库下载sqlserver存储过程/游标及实例系统存储过程exec sp_databases 查看系统数据库exec sp_tables 查看系统表exec sp_rename ‘aaa’,’bbb’ 修改表明aaa为bbbexec

sqlserver存储过程/游标及实例   –系统存储过程   exec sp_databases –查看系统数据库   exec sp_tables –查看系统表   exec sp_rename ‘aaa’,’bbb’ –修改表明aaa为bbb   exec sp_rename ‘dbo.aaa’,’col1′,’col2′ –修改表aaa的列col1为col2   select * from sys.objects where type=’P’ –查询存储过程   –存储过程(简单无参)   use Task_Bogic   go   if(exists (select * from sys.objects where name=’proc_test’))   drop proc proc_test   go   create proc proc_test   as   select * from Task_Bogic.dbo.Tnfo where Status=7   go   exec proc_test   –存储过程(带参)   if(OBJECT_ID(‘proc_paramTest’,’P’) is not null)   drop proc proc_paramTest   go   create proc proc_paramTest(@status int,@hasItem bit)   as   select * from Task_Bogic.dbo.Tnfo where Status=@status and HasCheckitem=@hasItem   go   exec proc_paramTest 7,1;   –存储过程(带参且带通配符[注意:nvarchar(20)这里的20如果没有加,则执行结果不正确,必须加一个长度])   –1.参数增加通配符   if(OBJECT_ID(‘proc_para_test’,’P’) is not null)   drop proc proc_para_test   go   create proc proc_para_test(@status int,@title nvarchar(20)=’%a%’)   as   select * from Task_Bogic.dbo.Tnfo where Status=@status and Title like @title   go   exec proc_para_test 7,’%a%’   –2.拼接通配符   if(OBJECT_ID(‘proc_para_test’,’P’) is not null)   drop proc proc_para_test   go   create proc proc_para_test(@status int,@title nvarchar)   as   select * from Task_Bogic.dbo.Tnfo where Status=@status and Title like ‘%’+@title+’%’   go   exec proc_para_test 7,’a’   –带输出参数的存储过程   if(OBJECT_ID(‘proc_out_paraTest’,’P’) is not null)   drop proc proc_out_paraTest   go   create proc proc_out_paraTest(   @status int,   @priority int out,   @title nvarchar output   )   as   select @status=Status,@priority=Priority,@title=Title from Task_Bogic.dbo.Tnfo where Status=@status and Title like ‘%’+@title+’%’   go   declare @status int,   @priority nvarchar,   @temp nvarchar;   set @status=7;   set @temp=’a’   exec proc_out_paraTest @status,@priority out,@temp output   select @priority,@temp   print @priority+’,’+@temp   –不从缓存读取的存储过程(每次执行都重新编译执行)   if(object_id(‘proc_para_test’,’P’) is not null)   drop proc_para_test   go   create proc proc_para_test   with recompile   as   select * from Task_Bogic.dbo.Tnfo where status=7   go   exec proc_para_test;   –加密存储过程   if(object_id(‘proc_para_test’,’P’) is not null)   drop proc_para_test   go   create proc proc_para_test   with encryption   as   select * from Task_Bogic.dbo.Tnfo where status=7   go   exec proc_para_test   –包含游标的存储过程(暂无)   –if(object_id(‘proc_para_test’,’P’) is not null)   – drop proc proc_para_test   –go   –create proc proc_para_test   – @cursor cursor   –as   –分页存储过程   –row_number分页   if(object_id(‘proc_para_test’,’P’) is not null)   drop proc proc_para_test   go   create proc proc_para_test   @startIndex int,   @endIndex int   as   select count(*) from Task_Bogic.dbo.Tnfo;   select * from (select row_number() over(order by createTime) as rowId,* from Task_Bogic.dbo.Tnfo) Stu where Stu.rowId between @startIndex and @endIndex   go   exec proc_para_test 2,5   –根据传递的页码和每页显示数量分页   if(object_id(‘proc_para_test’,’P’) is not null)   drop proc proc_para_test   go   create proc proc_para_test   @pageIndex int,   @pageSize int   as   declare @startRow int,@endRow int   set @startRow=(@pageIndex-1)*@pageSize+1   set @endRow=@pageIndex*@pageSize   select * from (select *,ROW_NUMBER() over(order by CreateTime) as Row_Id from Task_Bogic.dbo.Tnfo) item where item.Row_Id between @startRow and @endRow   go   exec proc_para_test 1,10   –存储过程实例   –斐波那契数列计算   if(OBJECT_ID(‘proc_test’,’P’) is not null)   drop proc proc_test   go   create proc proc_test   as   declare @count int,@num1 int,@num2 int,@sum int   set @count=1   set @sum=0   set @num1=1   set @num2=1   while @count<=10   begin   set @sum=@num1+@num2;   set @num1=@num2;   set @num2=@sum;   set @count+=1;   end   select @sum   go   exec proc_test   –鸡兔同笼,头30,腿100   if(OBJECT_ID(‘proc_test’,’P’) is not null)   drop proc proc_test   go   create proc proc_test   as   declare @num1 int,@num2 int   set @num1=1   while @num1<=30   begin   set @num2=30-@num1   if 100=@num1*4+@num2*2   begin   print ‘兔:’+convert(nvarchar,@num1)+’只,鸡’+convert(nvarchar,@num2)+’只’   end   set @num1+=1   end   go   exec proc_test   –二一次方程组算法   if(OBJECT_ID(‘proc_test’,’P’) is not null)   drop proc proc_test   go   create proc proc_test   as   go   exec proc_test   –set nocount on 不再返回行数【但是@@Rowcount仍会计算】,只会提示(命令已成功完成)set nocount off关闭不返回行数(即返回受影响行数)   set nocount on   select * from Tnfo where Status=7   set nocount off   select * from Tnfo where Status=7   –set rowcount 5 设置返回行数(5代表5行),查询数据只能返回5行,和select top 5 一样。使用set rowcount 0,取消设置。   set rowcount 5   select * from Tnfo where Status=7   set rowcount 0   select * from Tnfo where Status=7   –@@rowcount返回行数,这里查询10行,则就是10,一般循环的时候使用,如果@@rowcount不大于0,则没有查询到数据。   select top 10 * from Tnfo   select @@ROWCOUNT   –游标   declare cur_task cursor for –定义游标   select * from [Task_Bogic].[dbo].[Tnfo] where status=7 and title like ‘%a%’   open cur_task –打开游标   fetch next from cur_task –第一条数据   while @@fetch_status=0 –如果存在数据   begin   fetch next from cur_task –查询下一条   end   close cur_task –关闭游标   deallocate cur_task –释放游标   go   –游标使用   select id,createTime from [Task_Bogic].[dbo].[Tnfo] where status=7 and title like ‘%a%’   declare @id uniqueidentifier,@cur_createTime datetime,@num int   set @num=-1   declare cur_task cursor for –定义游标   select id,createTime from [Task_Bogic].[dbo].[Tnfo] where status=7 and title like ‘%a%’   open cur_task –打开游标   fetch next from cur_task into @id,@cur_createTime –第一条数据   while @@fetch_status=0 –如果存在数据   begin   print convert(nvarchar(100),@id)+’—–‘+convert(nvarchar(100),@cur_createTime,120)   update [Task_Bogic].[dbo].[Tnfo] set CreateTime=DATEADD(DAY,@num,GETDATE()) where Id=@id   fetch next from cur_task into @id,@cur_createTime –查询下一条   set @num-=1   end   close cur_task –关闭游标   deallocate cur_task –释放游标   go    

2024最新激活全家桶教程,稳定运行到2099年,请移步至置顶文章:https://sigusoft.com/99576.html

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请联系我们举报,一经查实,本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处:https://sigusoft.com/75936.html

(0)
上一篇 2024年 8月 4日 下午4:36
下一篇 2024年 8月 4日 下午4:42

相关推荐

关注微信