SQL 进阶学习之二

 

-- =========================================================================================
-- 2007/06/02 SQL学习
-- =========================================================================================
use test
go

select * from books
-- 定义全局游标（默认的方式）
declare curbook cursor scroll for
 select title,pages from  books
--
-- 第一个批开始
open curbook
--
declare @title nvarchar(20)
declare @page int
fetch first from curbook
 into @title,@page
if (@@fetch_status = 0)
begin
 print '第一个书名为：' + @title
 print '第一个书的页数为：' + cast(@page as nvarchar(5))
 print ''
end
--
close curbook
go

-- 第二个批开始
open curbook
declare @title nvarchar(20)
declare @page int
fetch absolute 4 from curbook
 into @title,@page
if (@@fetch_status = 0)
begin
 print '第4个书名为：' + @title
 print '第4个书的页数为：' + cast(@page as nvarchar(5))
 print ''
end
close curbook
go

-- 第三个批开始
open curbook
declare @title nvarchar(20)
declare @page int
fetch relative 1 from curbook
--fetch PRIOR from curbook
 into @title,@page
if (@@fetch_status = 0)
begin
 print '第3个书名为：' + @title
 print '第3个书的页数为：' + cast(@page as nvarchar(5))
 print ''
end
close curbook
go

 

-- 使用游标变量
-- 
declare @cur cursor
set @cur = cursor for select * from books -- 直接给游标变量赋值
--
open @cur
--
fetch next from @cur
--
close @cur
--
deallocate @cur
go

-- 将已经定义好的游标赋值给游标变量
declare curbook1 cursor scroll for
 select title,pages from  books
--
--open curbook
declare @cur1 cursor
-- 游标赋值给游标变量
set @cur1 = curbook1
-- 打开游标变量
open @cur1
--
declare @title nvarchar(20)
declare @page int
declare @id int
set @id = 0
--
--fetch first from @cur
fetch next from @cur1
 into @title,@page
while (@@fetch_status = 0)
begin
 set @id = @id + 1
 print '第' + cast(@id as nvarchar(2)) + '本书的书名为: ' + @title
 print '第' + cast(@id as nvarchar(2))  + '本书的页是为: ' + cast(@page as nvarchar(5)) 
 print ''
 fetch next from @cur1
  into @title,@page
end
print ''

--
fetch first from @cur1
 into @title,@page
if (@@fetch_status = 0)
begin
 print '第一个书名为：' + @title
 print '第一个书的页数为：' + cast(@page as nvarchar(5))
 print ''
end
--
close @cur1
--
deallocate @cur1
--
deallocate curbook1
--
select * from books

-- =================================
-- 在存储过程中使用游标
-- 游标变量常使用在存储过程里,通过存储过程的output参数将游标返回给应用程序。
-- 应用程序接受到返回的游标之后，可以将该游标赋值给一个游标变量，再使用游标变量完成后续操作。
--
-- 先创建一个存储过程，声明 @cur 为可以返回的参数
create procedure procCur
@cur cursor varying output
as
 set @cur = cursor local for
  select title from books
-- 打开游标变量
open @cur
go
-- 定义另一个游标变量
declare @nextcur cursor
declare @title nvarchar(20)
-- 将存储过程的返回值赋给游标变量
exec procCur @nextcur output
--
fetch next from @nextcur
 into @title
while(@@fetch_status = 0)
begin
 print @title
 fetch next from @nextcur
  into @title
end
close @nextcur

--
-- @@cursor_rows 全局变量 : 可以返回最后一次打开游标中包含有多少条记录
declare cur2 cursor static for
 select * from books
open cur2
print @@cursor_rows
close cur2
deallocate cur2

-- 使用函数查看游标状态
-- cursor_status
-- 返回值为:
-- 1 表示游标已经打开,但不确定其中有多少条记录
-- 2 表示游标已经关闭,而且可以确定其中没有一条记录。动态游标不会返回该结果。
-- -1 表示游标已经关闭
-- -2 该函数不适用
-- -3 指定名称的游标变量不存在，或存在游标变量，但没有为其赋值。
-- 示例如下:
--
declare @nextcur cursor
declare @title nvarchar(20)

exec procCur @nextcur output
if (cursor_status('variable','@nextcur') = 1)  -- 在使用游标前判断游标是否已经打开是一个很好的编程习惯
begin
 fetch next from @nextcur
  into @title
 while(@@fetch_status = 0)
 begin
  print @title
  fetch next from @nextcur
   into @title
 end
 close @nextcur
end
else
 print '游标没有打开'

 

-- ******************************************************************************************
-- ===================================================================================================
-- 触发器
-- 触发器实际上就是一种特殊类型的存储过程,其特殊性表现在:它是在执行某些特定的T-SQL语句时自动执行的。
-- 分为两类：一类是DML触发器，一类是DLL触发器
-- 有两种方法来保证数据的有效性和完整性: 约束和触发器
-- 触发器是针对数据表(库)的特殊的存储过程,当这个表发生了DML操作时,会自动激活执行,可以处理各种复杂的操作.
-- SQL SERVER 2005 新增了功能是: 在数据表(库)发生Create,Alter和Drop操作时,也会激活.
--
-- ===================================================================================================
-- 触发器的功能如下:
-- 1)完成比约束更复杂的数据约束
-- 2)检查所做的SQL是否允许
-- 3)修改其他数据表里的数据
-- 4)调用更多的存储过程
-- 5)发送SQL Mail
-- 6)返回自定义的错误信息
-- 7)更改原本要操作的SQL语句
-- 8)防止数据表结构更改或数据表被删除

-- 触发器的种类
-- 1)DML触发器:  After触发器和Instead Of 触发器
-- 2)DDL触发器(SQL SERVER 2005 新增)

-- DML触发器的工作原理:
-- SQL SERVER 2005 为每个DML触发器都定义了两个特殊的表: 插入表和删除表.这两个表是建立在内存中的,是逻辑表.
-- 对于这两个表,用户只有读取的权限,没有修改的权限.
-- 这两个表的结构与触发器所在数据的结构是完全一致的,当触发器的工作完成之后,这两个表会从内存中删除.
-- 插入表存放的是更新前的记录.
-- 删除表存放的是更新后的记录

-- After触发器的工作原理:
-- After触发器是在记录更变完后才激活执行的.
-- 可以用 Rollback Transaction 语句来回滚操作

-- Instead Of 触发器: 一般用来取代原本的操作.

-- 1) After触发器只能用于数据表中,Instead Of 触发器可以用于数据表和视图上,但两种表都不可以建立在临时表上
-- 2) 一个数据表可以有多个触发器,但是一个触发器只能对应一个表
-- 3) 在同一个数据表中,对每个DML操作可以建立多个After触发器,但Instead Of触发器针对每个操作只能建立一个
-- 4) Truncat table 语句不能激活Delete类型的触发器
-- 5) 不同的SQL语句，可以触发同一个触发器
-- ===================================================================================================
--
-- 设计简单的After触发器
--
select * from books
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  SongBin
-- Create date: 20070604
-- Description: 
-- =============================================
--
CREATE TRIGGER dbo.booksInsert
   ON  dbo.books
   AFTER INSERT
AS
BEGIN
 print '新添加了一种书'
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for trigger here
END
GO
-- 更新的触发器
CREATE TRIGGER dbo.booksUpdate
   ON  dbo.books
   AFTER update
AS
BEGIN
 print '更新了一种书'
END
GO
-- 删除的触发器
CREATE TRIGGER dbo.booksDelete
   ON  dbo.books
   AFTER delete
AS
BEGIN
 print '新删除了一种书'
END
GO

-- 测试建立的触发器
insert into books
(book_id,title)
values(5,'C# programming')

select * from books

insert into books
(book_id,title)
values(6,'SQL')

update books
set title = 'SQL SERVER 2005'
where book_id = 6

delete books
where book_id = 6
-- 测试成功

--
create trigger bookInsert1
on books
after insert
as
begin
 if (select pages from inserted ) > 500
 begin
  print '书的页数不能大于500页'
  rollback transaction
 end
end
go
-- 测试bookInsert1
insert into books
(book_id,title,pages)
values(7,'XML',600)
go
select * from books

-- 设置After触发器的激活顺序
--
create trigger bookinsert2
on books
after insert
as
begin
 print '再次提醒，又新增了一种书!'
end
go

insert into books
(book_id,title,pages)
values(7,'XML',400)
go

delete books where book_id = 7

-- 可以用存储过程 sp_settriggerorder 为每一个操作各指定一个最先执行或最后执行的触发器
--
 sp_settriggerorder 触发器名,
   激活次序,-- first,last,none
   激活触发器的动作
-- 每个操作只能设一个first触发器和一个last触发器，要取消设置好的话只要设为none即可
exec sp_settriggerorder 'bookinsert2',
 'first','insert'
go
exec sp_settriggerorder 'booksinsert',
 'last','insert'
go
--
insert into books
(book_id,title,pages)
values(7,'XML',400)
go

-- 只有After触发器可以设置激活次序，Instead Of触发器不可以设置激活次序
-- 激活触发器的动作必须和触发器内部的激活动作一致

-- 触发器的嵌套
create table testlog
(
 id int identity(1,1) not null,
 tablename nvarchar(50) not null,
 opsentence nvarchar(2000) not null,
 opcontent nvarchar(2000) not null,
 optime datetime not null
 constraint df_testlog_optime default (getdate()),
 constraint pk_testlog primary key clustered
 (
  id asc
 )
 with (ignore_dup_key = off) on [primary]
)
on [primary]
go
--
create trigger testloginsert
on testlog
after insert
as
begin
 print '测试时，表记录有所变动'
end
go
--
create trigger bookinsert3
on books
after insert
as
begin
 declare @name nvarchar(20),@page nvarchar(max)
 set @name = (select title from inserted)
 set @page = (select title from inserted)
 
 insert into testlog(tablename,opsentence,opcontent)
 values('books表','插入记录','书名 ：' + @name + ' 页数: '+ @page)
end
go
-- 测试
insert books
(book_id,title,pages)
values(8,'HTML',200)

select * from testlog

--
-- 触发器的递归
-- 触发器的递归的是指一个触发器从其类部又一次激活该触发器.
-- 直接递归和间接递归
-- 一般情况下,SQL Server 服务器是不允许递归的,需要自行打开该功能
--

-- Instead Of 触发器的使用范围
-- 1) 数据库里的数据禁止修改
-- 2) 有可能要回滚修改的SQL语句
-- 3) 在视图中使用触发器
-- 4) 用自己的方式去修改数据

-- 设计 Instead Of 触发器
-- 将bookInsert1改为Instead Of触发器
--
create trigger bookInsert1_1
on books
instead of insert
as
begin
 set nocount on
 declare @id int ,
 @title nvarchar(50),
 @aid int,
 @page int

 set @id = (select  book_id from inserted)
 set @title = (select  title from inserted)
 set @aid = (select   author_id from inserted)
 set @page = (select   pages from inserted)
 if @page > 500
  print '页数不能大于500页'
 else
  insert into books
  (book_id,title,author_id,pages)
  values (@id,@title,@aid,@page)
end
go

drop trigger bookInsert1_1
--
insert into books
(book_id,title,author_id,pages)
values (11,'ASP',8,600)
--
select * from books

-- 查看DML触发器
-- 用系统存储过程查看触发器
 sp_help bookinsert1
 sp_help 'bookinsert1'
--
sp_helptext bookinsert1
sp_helptext 'bookinsert1'
--
-- 修改DML触发器
-- alter trigger 触发器名
sp_rename '旧触发器名','新触发器名'-- （慎用，可能导致某些脚本或存储过程运行出错）

-- 删除
drop
-- 禁用与启用DML触发器
--
alter table 数据库表名
 disable 或 enable trigger 触发器名或ALL -- ALL禁用所有的触发器

 

-- DDL触发器
-- 使用DDL触发器的几种情况：
-- 1）数据库里的库架构或数据表架构很重要，不允许被修改
-- 2）防止数据库或数据表被误操作删除
-- 3）在修改某个数据表结构的同时修改另一个数据表的相应的结构
-- 4）要记录对数据库结构操作的事件
--
-- 用于保护数据库中的数据表不被修改,不被删除
create trigger test_table
on database
for drop_table ,alter_table
as
begin
 print '对不起,你不能对数据表进行操作'
 rollback;
end
--
-- 保护当前服务器里的所有数据库不能被删除
create trigger nodatabase
on all server
for drop_database
as
 print '对不起,你不能删除数据库' 
 rollback;
go
-- 测试
drop table m
-- 测试
drop database SCR

sp_helptext nodatabase
sp_helptext test_table

-- 知道修改了多少条记录 @@rowcount
create trigger bookdelete1
on books
after delete
as
begin
 print '你这次删除了 ' + cast(@@rowcount as varchar) + ' 条记录'
end
go
--
select * from books
--
delete books
where book_id = 8

-- 插入记录的自动编号是多少
-- @@identity

 
-- 如何知道某个字段是否被修改
-- update(字段名)

-- 如何返回错误信息
-- raiserror()
--
set ansi_nulls on
set quoted_identifier on
go
create trigger yesupdate
on books
instead of update
as
begin
 set nocount on
 if update(pages)
  begin
   declare @id int,@title nvarchar(50),@page int
   set @id = (select book_id from inserted)
   set @title = (select title from inserted)
   set @page = (select pages from inserted) 

   update books
   set pages = @page
   where book_id = @id and title = @title
  end
 else
  begin
   print '只能修改页数字段'
   raiserror('处理页数字段之外的其他字段信息不能修改',16,5)
  end
end
go
-- 测试
-- 成功
update books
set pages = 300
where book_id = 8
-- 更改其他字段失败
update books
set title = '更改'
where book_id = 8

-- 成错误消息并启动会话的错误处理。RAISERROR 可以引用 sys.messages 目录视图中存储的用户定义消息，
-- 也可以动态建立消息。该消息作为服务器错误消息返回到调用应用程序，或返回到 TRY…CATCH 构造的关联 CATCH 块。