sql server高级（函数，存储过程，触发器，游标）

[size=large]--函数与存储过程的区别[/size]
/*
二者都是解决具体业务的问题
区别是：
1.在业务上：函数是具体功能的问题，而存储过程
还要解决系统接口的问题，垮软件和语言访问的
问题
2.在原理上，存储过程是预处理，预编译的。而函数
只是普通的语句 。预处理的结构他的整个IO读写
查询算法都需要存储到dbms，便于下次使用直接
获得结果，性能比函数快，但是这种预处理的结构
会占用更多的DBMS存储。
3.语法上，函数能解决的问题，存储过程都可以
4.在做具体项目的选材上，select结构一般都给函数
数据处理一般都给存储过程。
如果某一个业务结构要返回多个数据。需要采用
存储过程，因为函数只能返回单一的数据。
5.语法上。函数只能有一个返回，并通过returns定义
通过return返回。函数必须有一个返回
存储过程可以返回，也可以有多个返回。他的返回是
通过out定义的。存储过程不仅可以通过out返回。
还可以通过隐式返回。也就是说存储过程可以显示返回
也可以隐式返回。返回的方法多样。

函数只能select，存储过程，select，insert update delete
都可以。


*/


create table stu
(
sid int identity(1,1) primary key,
sname nvarchar(20)
)

insert into stu values('张三')
insert into stu values('李四')
insert into stu values('王五')
create table scos
(
ssid int identity(1,1) primary key,
sid int references stu(sid),
sco int,
km nvarchar(20)
)
insert into scos values (1,60,'c')
insert into scos values (2,90,'c++')
insert into scos values (2,70,'SQL server')
insert into scos values (3,60,'java')


select * from stu
select * from scos

[size=large]--函数[/size]
--创建函数 findNameById 返回1行1列
create function findNameById
(
@sid int
)
returns nvarchar(20) --返回结果集 这种只能返回1行1列 ，有多行业只能得到最后一行的单元格
as
begin
declare @sname nvarchar(20)
select @sname=sname from stu where sid=@sid
return @sname
end

--删除函数
drop function findNameById
--执行函数
select dbo.findNameById(2) --必需加 dbo.

--这里使用函数，避免了笛卡尔积，提高了效率
select *,dbo.findNameById(sid) as 姓名 from scos


--创建函数 返回表格 为何return要放在上面 ????????
alter function getUsersScos
(
@sid int
)
returns table
as
return
select *,dbo.findNameById(sid) as 姓名 from scos
where sid=@sid


select * from dbo.getUsersScos(2)

--创建函数 返回表格
create function getUsersScos2
(
@sid int
)
returns @tmp table(ssid int,sid int,sco int,
km varchar(10),sname nvarchar(20))
as
begin
insert into @tmp
select *,dbo.findNameById(sid) as 姓名 from scos
where sid=@sid

return
end


select * from dbo.getUsersScos2(1)


[size=large]--存储过程[/size]
/**
@desc:
@author:
@version:
*/

--隐式返回 sys_refcursor
create proc findById1
@id int
as
begin
select sname from stu where sid=@id
end


--显式返回
create proc findById2
@id int,
@name nvarchar(20) out
as
begin
select @name=sname from stu where sid=@id
end

--存储过程隐式返回的调用
--调用1
findById1 1
--调用2
exec findById1 1
execute findById1 1

--存储过程显式返回的调用
--调用2 --显示返回的调用
declare @tmp nvarchar(20)
begin
exec findById2 1,@tmp out
print @tmp
end

[size=large]--游标[/size]
--指向当前行的指针，this
--场景：分析数据，抽奖，随机取样等
--常见问题：动静态游标的区别？
-- 游标都可以怎么移动？
-- 游标的语法是怎么样的？
--1.声明的for可以是任意sql
declare cur cursor for select sid,sname from stu

--2打开
open cur

--3.使用
--仅向前
fetch next from cur

--4.关闭
close cur

--5.释放
deallocate cur

--demo:将数据通过游标全部放入临时表
declare cur cursor for select sid,sname from stu

--2打开
open cur

--3.使用
--定义空的临时表
create table #tmp(sid int,sname nvarchar(20))
select * from #tmp
--仅向前
declare @id int
declare @name nvarchar(20)
fetch next from cur into @id,@name
while @@fetch_status=0 --游标状态 ：当前有数据
begin
insert into #tmp values(@id,@name)
fetch next from cur into @id,@name--增量
end

--
--查看
select * from #tmp
--4.关闭
close cur

--5.释放
deallocate cur


--动态游标
declare cur scroll cursor for select * from scos

--打开
open cur

--使用
fetch first from cur --第一条
fetch prior from cur --前面一条
fetch next from cur --下一条
fetch last from cur --最后一条
fetch absolute 4 from cur --定位到第4条
fetch relative 2 from cur --定位到当前位置的下面第二条

close cur
deallocate cur

[size=large]--触发器[/size]
--常用问题：
--1触发器的定义
--监控数据insert、update和delete的更改的行为
--PG可以通过建立日志和备份，防止用户错误的操作
--通过触发器还可以解决反范式用户对数据造成的不一致
--2.触发器监控的目标？、
--除了监控表，还能监控列？
--3.怎么通过触发器备份和建立日志呢？
--幻表，也叫虚表。其表结构是什么样的？
/*
-- inserted(:new) deleted(:old)
select N N
insert Y N
delete N Y
update Y Y
*/

--4.触发器在何时监控？
--创建触发器 给表
create trigger tri1
on stu
after insert,delete,update
as
begin
select '新'
select * from inserted --新的幻表（这样好理解些）
select '老'
select * from deleted --旧的幻表
end

insert into stu values('张佳')

select * from stu

--修改触发器 给表的某列加触发器（下面的例子新增也会触发，）
alter trigger tri1
on stu
after insert,delete,update
as
begin
if update(sname)
begin
select '您修改的此列，此列不允许修改'
rollback tran
end
end

drop trigger tri1

update stu set sname='张三1' where sid=1

insert into stu values('伍佰')


--监控用户表的所有行为，把用户对数据的更改（触发器的详细例子）
--自动备份到日志表
select * from stu

create table stulog(
sid int,
sname nvarchar(20),
type varchar(50),
updatetime datetime
)

alter trigger tri2
on stu
after insert,delete,update
as
begin
declare @id int
declare @name varchar(20)

IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
begin
--新增触发
select @id=sid,@name=sname from inserted
insert into stulog values(@id,@name,'insert',getdate())
end
else IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
begin
--修改触发
select @id=sid,@name=sname from deleted
insert into stulog values(@id,@name,'updateBefore',getdate()) --修改之前的数据
select @id=sid,@name=sname from inserted
insert into stulog values(@id,@name,'updateAfer',getdate()) --修改之后的数据
end
else
begin
--删除触发
select @id=sid,@name=sname from deleted
insert into stulog values(@id,@name,'delete',getdate())
end
end


insert into stu values('黎明')


--创建视图
create view V_STUSCO
as
select a.sid,sname,b.km,b.sco from stu a
inner hash join scos b on a.sid=b.sid

select * from V_STUSCO
select * from stu
select * from scos
insert into V_STUSCO values(3,'e','yoga',80)
--我们不能给这个视图插入数据 因为该视图有多个基表 需要通过 instead of 触发器来完成
create trigger ttri
on V_STUSCO
instead of insert
as
begin
declare @id int
declare @newId int
declare @name nvarchar(20)
declare @km nvarchar(20)
declare @sco int
--取得幻表数据
select @id=sid,@name=sname,@km=km,@sco=sco from inserted
--数据转储到基表
insert into stu values(@name) --新增学生信息
--由于id是自动获取。所以我们要查处id
select @newid=sid from stu where sname=@name --获得新增学生的id，scos需要用到
insert into scos values(@newid,@sco,@km)
end

select * from V_STUSCO

--通过视图插入
insert into V_STUSCO values(5,'王佩','yoga',90) --这里的5没有用，只是为了列的一一对应，少写列名

select * from stu
select * from scos