存储过程的好处

执行速度快 提高安全性 将少网络传输量

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

------------------