分页
declare @age int set @age=20 select @age select @age=age from T_Person where Id=2
print @age
select @@VERSION
select @@error select @@MAX_CONNECTIONS--可以创建的同时连接的最大数目 select @@SERVERNAME
declare @age int set @age=21 if(@age>18) begin select '成年' end else begin select '未成年' end
declare @avgScore int
select @avgScore= AVG(english) from T_Score if(@avgScore>60) begin select top 3 * from T_Score order by English desc end
------事务 declare @err int set @err=0 update bank set balance=balance-1000 where cid='0001' set @err=@err+@@error update bank set banance=balance+1000 where cid='0002' set @err=@err+@@error
if(@err>0) begin rollback transaction--事务回滚,更新出错 print '事务回滚' end else begin commit transaction --事务提交 print '事务提交' end
--考试题出难了,降低及格分数线60,(将该题目封装为一个存储过程。 --条件:及格人数 大于 总人数一半 --结果:输出最后的分数线
--作业:编写存储过程usp_upGrade --要求传入参数:@pass float --给没及格的人提分,一直加到及格人数大于总人数一半 --结果:输出调整之后的学员姓名及分数
alter procedure usp_UpGrade @pass float = 60 as declare @count int declare @fail int set @fail= (select COUNT(*) from T_Score where English< @pass)--不及格的人数 set @count=(select COUNT(*) from T_Score)--参加考试的总人数 while(@fail<@count/2) begin set @pass=@pass-2 end usp_UpGrade
---查询当前所有学生,同时要获得最大年龄 create procedure usp_GetMaxAge @maxAge int output---定义输出参数, 用output修饰 as begin select * from T_Person--查询所有学生 select @maxAge= MAX(age) from T_Person--查询最大年龄,赋值给输出参数 end
declare @maxage int --定义一个局部变量 set @maxAge=0 --设置初值 exec usp_GetMaxAge @maxAge output --给 存储过程 传参数 select @maxAge --查询获得的最大年龄
--2 create procedure usp_GetStusAndMaxAge @maxAge int output as begin select * from T_Person select @maxAge=COUNT(Age) from T_Person end go
declare @max int set @max=0 exec usp_GetStusAndMaxAge @max output select @max -----------------------------存储过程分页------------- alter procedure usp_GetPage @pageIndex int =1, @pageSize int =5 as begin select * from ( select ROW_NUMBER() over(order by ar_id) as rownum,* from Ams_Area ) as t where t.rownum between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize end
exec usp_GetPage 1,5 exec usp_GetPage 2,5--查询
alter procedure usp_GetPageMsgs @pageIndex int , @pageSize int , @pageCount int output,--输出参数,总页数 @rowCount float output--输出参数,总行数 as begin select * from ( select ROW_NUMBER() over(order by ar_id) as rownum,* from Ams_Area ) as t where t.rownum between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize select @rowCount=COUNT(*) from Ams_Area set @pageCount=CEILING(@rowCount/@pageSize) end
declare @rc int,@pc int exec usp_GetPageMsgs 3,5,@pc output,@rc output select @pc,@rc
-----------存储过程 老师版本--------------------- alter proc GetPagedListWithTotal @pageIndex int,--页码 @pageSize int, --页容量 @pageCount int output,--总页数 @rowCount float output--总行数 as select * from( select ROW_NUMBER() over(order by ar_id) as num,* from Area )as temp where num between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize --获得总行数 select @rowCount= COUNT(*) from Area set @pageCount = CEILING(@rowCount/@pageSize)--因为行数/页容量可能不是整数,也就是说,这些页装完后还会剩下几行,所以如果装不完的话,就+1页 -------------------------------------------------------