存储过程 触发器，视图

存储过程的好处
执行速度快 提高安全性 将少网络传输量

create proc usp_Demo
as
befin
select * from Score as t1 inner join student as t2 on t1.studentId=t2.SId where t1.english<60
end

执行
exec usp_Demo

//修改存储过程 alter
----
带参数的存储过程

create proc usp_Demo
@level int
as
befin
select * from Score as t1 inner join student as t2 on t1.studentId=t2.SId where t1.english<@level
end

exec usp_Demo 45
-----
参数的默认值
create proc usp_Demo
@level int =60
as
befin
select * from Score as t1 inner join student as t2 on t1.studentId=t2.SId where t1.english<@level
end

exec usp_Demo
----------------
单输出参数的
create proc usp_Demo3
@level int,@count int out
as
begin
select * from Score as t1 inner join student as t2 on t1.studentId=t2.SId where t1.english<@level
select @count=count(*) from Score wherer english<@level
end

declare @n int //定义变量
exec usp_Demo3 45,@n output //执行
print @n
-------------------------
分页存储过程
select * from
(select *,row_number() over(order by sid)as num from Student) as t
where num between 1 and 3


第n页 每页3条
select * from
(select *,row_number() over(order by sid) as num from student)as t
where num between (@pageIndex-1)*@pageSize+1 and @pageSize*@pageIndex
--创建存储过程
create proc usp_Getpaged
@pageIndex int,@pageSize int,@pageCount int output
as
begin
declare @num int
select @num=count(*)from student
--总页数
set @pageCount=ceiling(@num*1.0/@pageSize)
select * from
(select *,row_number() over(order by sid) as num from student)as t
where num between (@pageIndex-1)*@pageSize+1 and @pageSize*@pageIndex

end
--测试
declare @n int
exec usp_GetPaged 1,3,@n output
print @n1

-------------
c#调用存储过程

-----------------
触发器
触发器是一种特殊类型的存储过程，在表中数据发生变化时自动执行，可以用于sql的约束，默认值和规则

//把student表中的数据插入到现有表中
//insert into backupstudent select * from student
---创建触发器
先建2个表
create table Records
(
rId int identity(1,1) primary key,
rType int , -- 1存钱 -1 取钱
rMoney money,
userId char(4)
)
go
create table bank
(
cId char(4) primary key,
balance money, --余额
)

alter table bank
add constraint CH_balance check(balance >=10)

go
--delete from bank
insert into bank values('0001',1000)
insert into bank values('0002',10)
go


创建触发器
create trigger tr_Records
on Records
for insert
as
declare @rType int,@userId char(4),@money money
select @rType=rType,@userId=userId,@money=rmoney from inserted

updata bank set balance+@rType*@money where cId=@userId

//执行
insert into Records values(1,1000,'0001')


---------
创建视图
--视图
create view v_student
as
select * from Student inner join Class on Class.cId = Student.sClassId


select * from v_student
------------------