SQL基本操作（三）：存储过程和触发器

--不带参数的存储过程 返回学生学号，姓名，课程号，成绩，学分
--检查是否已存在同名的存储过程，若有，删除
use xscj
if EXISTS(select name from sysobjects where name='student_info' and type='p')
drop procedure student_info
go
--创建存储过程
create procedure student_info
as
select a.学号,姓名,课程名,成绩,t.学分
from xs a inner join xs_kc b
on a.学号=b.学号 inner join kc t
on b.课程号=t.课程号
go
--student_info存储过过程的执行
EXECUTE student_info
go

--使用带参数的存储过程
use xscj
if EXISTS(select name from sysobjects where name='student_info1' and type='p')
drop procedure student_info1
go
create procedure student_info1(@name char(8),@cname char(16))
as
select a.学号,姓名,课程名,成绩,t.学分
from xs a inner join xs_kc b
on a.学号=b.学号 inner join kc t
on b.课程号=t.课程号
where a.姓名=@name and t.课程名=@cname
go
--调用存储过程
EXECUTE student_info1 '王林','计算机基础'
go
--另一种方法调用存储过程
EXECUTE student_info1 @name='王林',@cname='计算机基础'
go
--使用带有通配符参数的存储过程
use xscj
if EXISTS(select name from sysobjects where name='st_info' and type='p')
drop procedure st_info
go
--建立存储过程
create procedure st_info (@name varchar(30))
as
select a.学号,姓名,课程名,成绩,t.学分
from xs a inner join xs_kc b
on a.学号=b.学号 inner join kc t
on b.课程号=t.课程号
where 姓名 like @name
go
--调用存储过程
EXECUTE st_info '[王张]%'
--计算指定学生的总成绩
--使用带output参数的存储过程
use xscj
if EXISTS(select name from sysobjects where name='totalcredit' and type='p')
drop procedure totalcredit
go
--建立存储过程
create procedure totalcredit(@name varchar(40),@total int output)
as
select @total=SUM(成绩)
from xs,xs_kc
where xs.学号=xs_kc.学号 and 姓名=@name
group by xs.学号
go
--调用存储过程
use xscj
declare @total int
EXECUTE totalcredit '王林',@total output
select '王林',@total
go
--使用output游标参数的存储过程,output游标参数用于返回存储过程的局部游标
--在XS表上声明并打开一个游标
--with encryption 对用户隐藏存储过程的文不，且调用后不显示结果
use xscj
if EXISTS(select name from sysobjects where name='st_cursor' and type='p')
drop procedure st_cursor
go
--创建存储过程
create procedure st_cursor (@st_cursor CURSOR varying output) with encryption
as
set @st_cursor=CURSOR  forward_only static 
for
select *from xs
open @st_cursor
go
--调用存储过程
use xscj
go
declare @MyCursor CURSOR
EXECUTE st_cursor @st_cursor=@MyCursor output
while (@@FETCH_STATUS=0)
begin
fetch next from @MyCursor
end
close @MyCursor
deallocate @MyCursor
--sp_helptext 可显示规则，默认值，未加密的存储过程，用户定义函数，触发器或视图的文本
--若加密则显示 对象备注已加密
EXEC sp_helptext st_cursor
EXEC sp_helptext totalcredit
-创建用户定义的系统的存储过程
--创建一个过程,显示表名以xs开头的所有及其对应的索引.如果没有指定参数，该过程返回表名以kc开头的所有表对应的索引
if EXISTS(select name from sysobjects where name='sp_showtable' and type='p')
drop procedure sp_showtable
go
use master
go
--创建存储过程
create procedure sp_showtable (@TABLE varchar(30)='kc%')
as
select tab.name as table_name,
       inx.name as index_name,
       indid as index_id
from sysindexes inx inner join sysobjects tab on tab.id=inx.id
where tab.name like @table
go
--调用存储过程
use xscj
EXEC sp_showtable 'xs%'
go
--当向XS_KC表插入一个记录时,检查该记录的学号在XS表是否存在,检查课程号在KC表中是否存在
--若有一项为否,则不允许插入
use xscj
if EXISTS(select name from sysobjects where name='check_trig' and type='tr')
drop trigger check_trig
go
--创建触发器
create trigger check_trig on xs_kc for insert
as
select *from inserted a 
where a.学号 not in (select b.学号 from xs b) or a.课程号 not in(select c.课程号 from kc c)
begin
raiserror('违背数据的一致性.',16,1)
rollback transaction
end
go 
--数据测试      
insert into xs_kc values('001101','112',80,null) 
--在XS_KC表上创建一个触发器，若对学号列和课程号列修改，则会提示信息，并取消修改操作
use xscj
if EXISTS(select name from sysobjects where name='update_trig' and type='tr')
drop trigger update_trig
go
--创建触发器
create trigger update_trig on xs_kc for update
as
--检查学号列（C0) 和 课程号列(C1) 是否被修改，如果有些列被修改了，则取消修改操作
if(columns_updated()&3)>0
begin
raiserror('违背数据的一致性.',16,1)
rollback transaction
end
go 
--在XSCJ数据库中创建表，视图和触发器，以说明instead of insert 触发器的使用
use xscj
create table books
(
BookKey int identity(1,1),
BookName nvarchar(10) not null,
Color nvarchar(10) not null,
ComputedCol AS (BookName+Color),
Pages int
)
go
--建立一个视图，包含基表的所有列
create View View2
as
select BookKey,BookName,Color,ComputedCol,Pages 
from books
go
--在View2视图上创建一个INSTEAD OF INSERT 触发器
create trigger insteadTrig on View2 instead of insert
as
begin
--实际插入时,INSERT语句中不包含BookKey字段和ComputedCol字段的值
       insert into books
select BookName,Color,Pages from inserted
end
go
--正确的INSERT语句
insert into books(BookName,Color,Pages) values('计算机辅助设计','红色',100)
select *from books
--不正确的INSERT语句(因为'ComputedCol'，因为该列是计算列)
insert into books(BookKey,BookName,Color,ComputedCol,Pages) values(2,'计算机辅助设计','红色','绿色',100)
--但对于视图View2，正确的INSERT语句
--虽然将BookKey和ComputedCol字段值传递了InteadTrig触发器
--但触发器中的INSERT语句没有选择inserted表BookKey和ComputedCol字段的值
insert into View2(BookKey,BookName,Color,ComputedCol,Pages) values(2,'计算机辅助设计','红色','绿色',100)
select *from View2
--如果在XS表中添加或更改数据,则将向客户端显示一条信息
--使用带有提示消息的触发器
use xscj
if EXISTS(select name from sysobjects where name='reminder' and type='tr')
drop trigger reminder
go
--创建存储过程
create trigger reminder on xs for insert,update
as
--失败的提示信息
raiserror(4008,16,10)
--失败就回滚！该操作不进行（如果不要此句的话，虽然有提示操作失败，但操作还是要进行）
rollback transaction
go
--插入一条记录，弹出提示信息
insert into xs values('111111','王林','计算机',1,'1980-2-10',50,null)
--修改触发器reminder
use xscj
go
alter trigger reminder on xs for update
as
raiserror('执行的操作是修改',16,10)
go
--插入一条记录
insert into xs values('011101','王林','计算机',1,'1980-2-10',50,null)
--删除触发器reminder
use xscj
if EXISTS(select name from sysobjects where name='remindr' and type='tr')
drop trigger reminder
go