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

xiaui520

--函数与存储过程的区别
/*
  二者都是解决具体业务的问题
    区别是：
   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

--函数
--创建函数  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)


--存储过程
/**
  @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

--游标
--指向当前行的指针，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

--触发器
--常用问题：
--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