加载中…
个人资料
岸芷汀蓝
岸芷汀蓝
  • 博客等级:
  • 博客积分:0
  • 博客访问:195,246
  • 关注人气:68
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
谁看过这篇博文
加载中…
正文 字体大小:

T-SQL学习二

(2009-08-13 17:53:21)
标签:

全文索引

触发器

from

游标

it

分类: 数据库
八:触发器
1:什么是触发器
2:一个简单的触发器
3:针对触发器的相关操作
4:使用触发器记录操作情况
5:视图上的触发器
6:触发器相关函数
一:什么是触发器
触发器是一种响应特定事件的特殊类型的存储过程
insert update... drop alter...等事件都有相应的触发器
二:简单的触发器
下面一个例子是在插入或者修改记录的时候的一个触发器
其中inserted表是一个临时表
存储的是将要插入的信息
这个触发器的目的是检查将要插入的信息是否符合规定
(在product表里没有特殊的记录)
这个例子是check约束所不能解决的了的
use AdventureWorks
go
create trigger orderDetailNotDiscontinued
on Sales.SalesOrderDetail
after insert, update
as
if exists
(
select 'true' from Inserted i
join Production.Product p
on i.ProductID = p.ProductID
where p.DiscontinuedDate is not null
)
begin
print('error appear.')
rollback tran
end
为了验证这个触发器
先给表加入一条不符合条件的记录
use AdventureWorks
go
update Production.Product
set DiscontinuedDate = getdate()
where ProductID = 680
好了现在来一条插入语句看看
use AdventureWorks
go
insert Sales.SalesOrderDetail
(SalesOrderID,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount)
values
(43660,5,680,1,1431,0)
得到的消息是:
error appear.
消息 3609,级别 16,状态 1,第 1 行
事务在触发器中结束。批处理已中止。
三:针对触发器的操作
1.禁用触发器
use AdventureWorks
go
alter table Sales.SalesOrderDetail
disable trigger all --这里的all可以是具体的触发器名
2.删除触发器
use AdventureWorks
go
drop trigger orderDetailNotDiscontinued
3.修改触发器
use xland
go
alter trigger tr_title
on mytable
for insert, update
as
if update(title)
begin
print('title1')
end
(这里的update()函数不懂没关系,下面会说到)
四:使用触发器记录操作情况
先看代码
use AdventureWorks
go
create table Production.inventoryAudit
(
id int identity primary key,
productid int not null
references Production.Product(ProductID),
netadjustment smallint not null,
modifieddate datetime default(current_timestamp)
)
go
create trigger ProductAudit
on Production.ProductInventory
for insert,update,delete
as
insert into Production.inventoryAudit
(productid,netadjustment)
select coalesce --coalesce哪个不为空用哪个
(i.ProductID,d.ProductID),
isnull(i.Quantity,0) - isnull(d.Quantity,0) as netadjustmenu
from Inserted i
full join Deleted d
on i.ProductID = d.ProductID
and i.LocationID = d.LocationID
where
isnull(i.Quantity,0) - isnull(d.Quantity,0) !=0
--isnull(i.Quantity,0)如果i.Quantity为空返回0(这个函数比较有用)
注意这里用了full join
其实如果不是高并发inserted和deleted中有一个表是不存在的
下面来测试一下这个触发器
use AdventureWorks
go
update Production.ProductInventory
set Quantity = Quantity +7
where ProductID = 1
and LocationID = 50
select * from Production.inventoryAudit
来看看消息:
(1 行受影响) 这是修改的一行
(1 行受影响) 这是触发器执行的一行
(1 行受影响) 这是select的一行
五:视图上的触发器
instead of触发器
先创建一个视图
use xland
go
create view UserArticle_vw
with Schemabinding
as
select a.id,a.title,u.username
from dbo.mytable a --必须有架构名,可以用as
join dbo.[user] u
on u.id = a.uid
检查该视图
use xland
go
select * from UserArticle_vw where id = 2
创建一个视图上的触发器
use xland
go
create trigger TRMytableInsert on UserArticle_vw
instead of insert
as
begin
if (select count(*) from inserted) >0
begin
insert into mytable
select i.title,u.id from inserted i
join [user] u
on u.username = i.username
if @@rowcount = 0 --返回受影响的行数
print('error appear')
end
end
对这个视图执行插入操作!(有了上面的触发器就可以插入了)
use xland
go
insert into UserArticle_vw (title,username) values ('zhe shi title','xland')
看下消息
(1 行受影响)
(1 行受影响)
类似的还有
insted of update触发器
insted of delete触发器
六:触发器的相关函数
看这个触发器
use xland
go
alter trigger tr_title
on mytable
for insert, update
as
if update(title)
begin
print('title1')
end
update()函数意思是
指定的列被更改时发生
看下验证语句
use xland
go
update mytable set title = 'xland' where id = 7
结果
title1
(1 行受影响)
还有一个函数
columns_updated()有检查多个列的能力
比较高级,也比较少用 在这里就不写了
九:全文索引
1:简要介绍全文索引
2:全文索引的相关操作
3:全文目录的相关操作
4:全文查询语法
5:综合实例
一:简要介绍下全文索引
可以利用索引快速访问数据库表中的特定信息。索引是对数据库表中一个或多个列的值进行排序的结构。
索引提供指针以指向存储在表中指定列的数据值,然后根据指定的排序次序排列这些指针。
数据库使用索引的方式与使用书的目录很相似:通过搜索索引找到特定的值,然后跟随指针到达包含该值的行
二:全文索引的相关操作
1.确认全文索引是否开启
对于SQL2005每个创建的数据库全文索引特性默认是开启的
可以通过这个语句来确认它是否开启了
SELECT databasePropertyex('xland','isfulltextenabled')
返回0为未开启,返回1为已开启
2.激活和禁止全文索引
禁用全文索引一定要注意,已经存在的全文目录,数据表同该目录的任何关联信息都会被删除
exec sp_fulltext_database @action = 'disable'
'enable'为激活
3.创建全文索引
use xland
go
create fulltext index on dbo.mytable --在某个表上创建全文索引
(title language English) --对某个列使用全文索引
key index Pk_mytable --索引名(一般就是表名前面加Pk)
on maincatalog
with change_tracking off, --关闭跟踪特性
no population --不进行初始化填充
其中Eglish可改为简体中文
可通过
select name alias from master..syslanguages
查询系统中支持的语言
修改和删除全文索引
drop fulltext index on dbo.mytable
三.全文目录的相关操作
1.创建全文目录
创建全文目录的时候,全文索引一定要为enable状态
全文目录存放的路径一定要有效
use xland
go
exec sp_fulltext_database @action = 'enable'
go
create fulltext catalog maincatalog
in path 'c:\fulltext'
更改和删除分别是alter和drop
drop fulltext catalog maincatalog
与其他的没有太多区别
2.填充全文目录
这是个后台的过程,表大的话虽然提示成功了,但是后台还在填充
alter fulltext index on dbo.mytable
start full population

use xland
go
exec sp_fulltext_table @tabname = 'dbo.mytable',
@action = 'start_full'
3.查看结果
select title from mytable where contains(title,'全文')
四:全文查询语法
1.contains:精确的匹配
where contains(*,' "my name is" ') --对短语的精确匹配,注意要有双引号
where contains(*,' "my" or "name is" ') --或者的关系
还有'and not' 'and'
还有临近词和权重 我就不多说了
2.freetext:模糊匹配(你输入swim可以匹配出swam)与contains语法相同
3.containstable
此函数返回一个表
此表包含两个列
一个是key(就是主健啦) 一个是rank(返回的行与搜索结果的匹配程度,越大越精确)
select title from mytable p
join containstable(mytable, title , 'xland') ct
on p.id = ct.[key]
4.freetexttable与containstable类似
附件:使用旧式语法创建全文索引的例子
use xland
go
--检查数据库pubs是否支持全文索引,如果不支持
--则使用sp_fulltext_database 打开该功能
if(select databaseproperty('xland','isfulltextenabled'))=0
execute sp_fulltext_database 'enable'
--建立全文目录FT_xland
execute sp_fulltext_catalog 'FT_xland','create'
--为mytable表建立全文索引数据元
execute sp_fulltext_table 'mytable','create','FT_xland','PK_mytable'--最后一个是主键名
--设置全文索引列名
execute sp_fulltext_column 'mytable','title','add'
--建立全文索引
--activate,是激活表的全文检索能力,也就是在全文目录中注册该表
execute sp_fulltext_table 'mytable','activate'
--填充全文索引目录
execute sp_fulltext_catalog 'FT_xland','start_full'
go
--检查全文目录填充情况
While fulltextcatalogproperty('FT_xland','populateStatus')<>0
begin
--如果全文目录正处于填充状态,则等待30秒后再检测一次
waitfor delay '0:0:30'
end
--全文目录填充完成后,即可使用全文目录检索
select * from mytable where CONTAINS(title,'xland')
一:什么是游标
游标是可以在结果集中上下游动的指针
二:创建一个简单的游标
use xland
go
--声明变量以后有用
declare @id int
declare @title varchar(max)
declare @username varchar(50)
--定义一个游标并打开它
declare tablecursor cursor for
select a.id,a.title,u.username from mytable a join [user] u on u.id = a.uid
open tablecursor
--开始提取记录,放入指定的变量
fetch next from tablecursor into @id,@title,@username
while @@fetch_status = 0 --0表示提取成功-1找不到记录-2超出了最后一条
begin
--cast(@id as varchar)为了正确的执行输出
print(cast(@id as varchar)+' '+@title+' '+@username)
--提取下一条记录
fetch next from tablecursor into @id,@title,@username
end
三:游标的作用域
做个存储过程,
里面的游标是全局的,
存储过程内部没有关闭释放游标
代码如下
use xland
go
create proc spCursorScope
as
declare @id int
declare @title varchar(max)
declare @username varchar(50)
declare @num int
set @num = 1
--定义一个全局游标
declare tablecursor cursor global for
select a.id,a.title,u.username from mytable a join [user] u on u.id = a.uid
open tablecursor
fetch next from tablecursor into @id,@title,@username
while (@num <=3) and (@@fetch_status = 0)
begin
set @num = @num+1
print(cast(@num as varchar) +' '+ cast(@id as varchar)+' '+@title+' '+@username)
fetch next from tablecursor into @id,@title,@username
end
--不关闭游标,不释放内存
--close tablecursor
--deallocate tablecursor
接着执行这个存储过程
执行语句如下
use xland
go
exec spcursorscope
执行结果
1 2 测试看看 xland
2 4 asdfasdf xland
3 5 asdfasdf xland
4 6 全文索引全文索引全文索引xland的全文索引 xland
再看一种执行方式
use xland
go
exec spcursorscope
declare @id int
declare @title varchar(max)
declare @username varchar(50)
declare @num int
set @num = 5
while (@num <=8) and (@@fetch_status = 0)
begin
set @num = @num+1
fetch next from tablecursor into @id,@title,@username
print(cast(@num as varchar) +' '+ cast(@id as varchar)+' '+@title+' '+@username)
end
--关闭游标,释放内存
close tablecursor
deallocate tablecursor
看执行结果
1 2 测试看看 xland
2 4 asdfasdf xland
3 5 asdfasdf xland
4 6 全文索引全文索引全文索引xland的全文索引 xland
6 7 xland xland
7 8 可以吗 xland
8 9 应该没问题 xland
9 10 暗暗 xland
我们在存储过程的外部调用了游标
说明游标是全局的
但不建议这样使用游标
四:游标的滚动
next --移动到下一条记录
prior --移动到上一条记录
first --移动到第一条记录
last --移动到最后一条记录
看例子
use xland
go
declare @id int
declare @title varchar(max)
declare @username varchar(50)
declare @num int
set @num = 1
--定义一个局部的可滚动的游标
declare tablecursor cursor local scroll for
select a.id,a.title,u.username from mytable a join [user] u on u.id = a.uid
open tablecursor
--滚过来
fetch next from tablecursor into @id,@title,@username
print(cast(@num as varchar) +' '+ cast(@id as varchar)+' '+@title+' '+@username)
while (@num <=3) and (@@fetch_status = 0)
begin
set @num = @num+1
fetch next from tablecursor into @id,@title,@username
print(cast(@num as varchar) +' '+ cast(@id as varchar)+' '+@title+' '+@username)
end
--滚回去
while (@num >1) and (@@fetch_status = 0)
begin
set @num = @num-1
fetch prior from tablecursor into @id,@title,@username
print(cast(@num as varchar) +' '+ cast(@id as varchar)+' '+@title+' '+@username)
end
close tablecursor
deallocate tablecursor
五:静态游标
static的游标,程序员都知道static的意思,我就不多说了
先看例子
use xland
go
--使用select into创建一个临时表
select id,title into cursortable from mytable where id <6
--定义局部的可滚动的静态的游标
declare cursortest cursor local scroll static for
select id,title from cursortable
declare @id int,@title varchar(max)
open cursortest
fetch next from cursortest into @id ,@title
while @@fetch_status = 0
begin
print (cast(@id as varchar)+' '+@title)
fetch next from cursortest into @id ,@title
end
update mytable set title ='这是我更新的数据' where id = 4
select id,title from mytable where id<6
--滚到第一条
fetch first from cursortest into @id,@title
while @@fetch_status =0
begin
print (cast(@id as varchar)+' '+@title)
fetch next from cursortest into @id,@title
end
close cursortest
deallocate cursortest
drop table cursortable
返回的消息:
(3 行受影响)
2 测试看看
4 asdfasdf
5 asdfasdf
title1
(1 行受影响)
(3 行受影响)
2 测试看看
4 asdfasdf
5 asdfasdf
其中一行受影响就是
select 语句的执行结果,为
2 测试看看
4 这是我更新的数据
5 asdfasdf
静态游标:一旦创建就与实体记录分开了,并不维持任何锁
实体表发生了更新,并不影响游标里的情况
六:键驱动的游标
看例子
use xland
go
--使用select into创建一个临时表
select id,title into cursortable from mytable
--给这个临时表来个主键
alter table cursortable add constraint pkcursor primary key (id)
--允许将显式值插入到标识列中
set identity_insert cursortable on
--定义局部的可滚动的静态的游标
declare cursortest cursor local scroll static for
select id,title from cursortable
declare @id int,@title varchar(max)
open cursortest
fetch next from cursortest into @id ,@title
while @@fetch_status = 0
begin
print (cast(@id as varchar)+' '+@title)
fetch next from cursortest into @id ,@title
end
update cursortable set title ='这不是我更新的数据' where id = 4
delete from cursortable where id = 2
insert into cursortable (id,title) values (33,'这是插入的')
select id,title from cursortable
--滚到第一条
fetch first from cursortest into @id,@title
while @@fetch_status != -1
begin
if @@fetch_status = -2
begin
print 'delete'
end
else
begin
print (cast(@id as varchar)+' '+@title)
end
fetch next from cursortest into @id,@title
end
close cursortest
deallocate cursortest
drop table cursortable
得到的消息
(8 行受影响)
2 测试看看
4 这是我更新的数据
5 asdfasdf
6 全文索引全文索引全文索引xland的全文索引
7 xland
8 可以吗
9 应该没问题
10 暗暗
(1 行受影响)
(1 行受影响)
(1 行受影响)
(8 行受影响)
delete
4 这不是我更新的数据
5 asdfasdf
6 全文索引全文索引全文索引xland的全文索引
7 xland
8 可以吗
9 应该没问题
10 暗暗
得到的结果
4 这不是我更新的数据
5 asdfasdf
6 全文索引全文索引全文索引xland的全文索引
7 xland
8 可以吗
9 应该没问题
10 暗暗
33 这是插入的
如果把keyset改成dynamic
删除的记录没有通知
消息里显示出了
插入的记录和更新的记录
可以在游标滚动的时候修改表的记录

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
前一篇:T-SQL学习
后一篇:Hibernate(一)
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

    < 前一篇T-SQL学习
    后一篇 >Hibernate(一)
      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有