11.12存储过程复习及例题

存储过程：相当于C#中的方法（有参数，返回值）

向特定表中插入记录的存储过程
注册
向T_users 插入一条用户写好的信息
use UserDB1
seclect *  from T_users
Create proc usp_Registor
@name varchar(50),
@pwd varchar(50),
@email varchar(50)
as begin
insert into T_users(FuserName,Fpassword,Femail) values(@name,@password,@email)
end
exec usp_Registor'白白','111','bai@126.com'

---------删除存储过程删除特定表中的一条记录
use School
select * from Class
create proc usp_Student_Delete
@sId int
as begin
delete from Student where sId=@sId
end

exec usp_Student_Delete @sId=14

--------------修改特定表中的某条记录（更新操作）

select * from Class
insert into Class values('Net','优秀班集体')
insert into Class values('Java','优秀班集体')

create proc  usp_Class_update
@Cid int,
@Cname varchar(50),
@CDes varchar(50)
as begin
upate Class  set cName=@Cname,cDescription=@CDes where  clsId=@Cid

end
exec usp_Class_update 10,'3G','优秀班集体'

 

----------------查询所有记录
create proc usp_Class_SelectAll
as
begin
select * from Class
end
exec usp_Class_SelectAll

 

------------针对特定表分页的存储过程

create proc usp_MyStudent_GetDateByPageIndex
@pageSize int=10,  //默认每页10 条
@pageIndex int  
as
begin
select * from
(select *,Row_Number() over(order by Fid) as rowIndex from MyStudent)
as tbl
where tb1.rowIndex between (@pageSize*(@pageIndex-1)+1)and @pageSize*@pageIndex
end
exec usp_MyStudent_GetDateByPageIndex
5,3