存储过程,触发器,等等。。。

2018-06-17 19:04:17来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

存储过程

if (object_id('proc_find_stu', 'P') is not null)
    drop proc proc_find_stu
go

create proc proc_find_stu(@startId int, @endId int,@outID int output)
as

    select * from Studentss where id between @startId and @endId
    
   set  @outID=(select COUNT(1) from Studentss where id between @startId and @endId)
 


  declare @ss int=0;
  exec proc_find_stu 1,6, @ss out
  select @ss

触发器---修改触发器

create trigger class_stu
on goods
for update
as
   declare @oldnumber  int,@newnumber int,@id int ;
 
      select @oldnumber=  number  from deleted;--旧数量
       select @id= goodsid  from deleted    ;--id   
 
    select @newnumber = number from inserted;--新数量

    update cangku set number=@oldnumber-(@oldnumber-@newnumber) where goodsid=@id;

    drop trigger class_stu

    update goods set  number=number-1 where goodsid=1;

    select * from goods g inner join cangku c on g.goodsid=c.goodsid

视图

use Students
select * from Studentss
--创建视图
if (exists (select * from sys.objects where name = 'v_stu'))
    drop view v_stu

create view v_stu  as select Id, StuName, StuClass, TeamName,Bishi,Jineng from Studentss;

select * from v_stu


--修改视图
alter view v_stu as  select id, name, sex from student;
alter view v_stu(编号, 名称,班级, 小组名称,笔试,机试)  as select Id, StuName, StuClass, TeamName,Bishi,Jineng from Studentss

select * from v_stu where 班级='1510A';

select * from information_schema.views;

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:datatable 转excel

下一篇:Directory文件类