SQL-Server使用点滴(三-方法篇)

除了基本的数据库，数据表，数据记录操作之外，SQL-Server还为我们提供了比较丰富的数据处理方法。这些方法包括：过程，函数，触发器，视图。

下面就针对这些方法做逐一介绍：

一，存储过程

Create Procedure ProName（ --对应有Alter和Drop方法

@Param1 TypeName1=DefaultValue1,

@Param2 TypeName2=DefaultValue2,

...

@ParamN TypeNameN=DefaultValueN) --这里括号可以省略

With Encryption --加密改存储过程，该句可以省略掉

过程主体

--End

1, 存储过程可以看作是一个局部的临时回话，其中所有建立的临时表，变量都会在过程结束时自动释放掉。但是过程中可以使用过程外部的临时表（即调用过程的会话)，

并且这些临时表不会在过程结束时释放。如果想使用外部变量，只有通过Output型参数进行传递，没办法直接使用。

2,存储过程的嵌套层数最大是32层，这点对于函数和触发器也是一样的，并且共享层数，使用@@NestLevel可以看到当前的嵌套层级；存储过程最大的内容容量是128M，含注释。

3,可以用sp_helptext过程来查看存储过程内容（加密的只能通过工具SqlPrompt6.0以上工具查看)，该内容系统存在系统表syscomments表中。

可以用sp_depends过程来查看存储过程中使用的对象列表；可以用sp_rename来为存储过程改名。

4,存储过程也可以创建成临时存储过程，只需要在名称前加#或##即可。临时存储过程会在创建该临时存储过程结束时释放，不能被其他用户调用，会占用tempDB空间。

5,存储过程中，如果进行了表结构或索引内容的更改，那么调用该存储过程的用户，必须具备这些表和索引的拥有权，否则无法正常执行。

6,存储过程中可以随意更改系统标记，例如:Set datefirst 1 --将星期一作为一周的第一天；Set Language N'Simplified Chinese',将系统语言设置为简体中文。

二，函数

函数定义相对过程较为复杂，常用的有三种，标量函数（返回标准定义数据类型)，表值函数(返回一个表)，多语句表值函数(返回表参与函数体)

1,标量函数

标量函数，即定义一个SQL标准类型的返回值，并用Return(参数)的模式进行返回。

Create Function FuncName( --对应有Alter和Drop方法

@Param1 TypeName1=DefaultValue1,

@Param2 TypeName2=DefaultValue2,

...

@ParamN TypeNameN=DefaultValueN) --这里括号可以省略

Returns ReturnType

With Encryption --加密函数，该句可以省略掉

Begin --注意这里的Begin和End不可以省略。

函数主体

Return(返回值) --这里返回的是一个ReturnType类型的值。

End

2，表值函数

定义的返回值是一个表，并且用Return(单记录集)来获取返回的表。

Create Function FuncName( --对应有Alter和Drop方法

@Param1 TypeName1=DefaultValue1,

@Param2 TypeName2=DefaultValue2,

...

@ParamN TypeNameN=DefaultValueN) --这里括号可以省略

Returns Table --注意，这里的Table是个固定保留字

With Encryption --加密函数，该句可以省略掉

Begin --注意这里的Begin和End不可以省略。

函数主体

Return(Select ...) --这里返回的是一个语句。

End

3，多语句表值函数

定义的返回值是一个表，这个表在定义返回值时，就确定表名和表结构，并且在函数主体中对这个表进行处理，返回时仅仅用一个不带参数的Return结束。

Create Function FuncName( --对应有Alter和Drop方法

@Param1 TypeName1=DefaultValue1,

@Param2 TypeName2=DefaultValue2,

...

@ParamN TypeNameN=DefaultValueN) --这里括号可以省略

Returns @TbName Table(ColList...) --注意，这里是一个完整的表定义

With Encryption --加密函数，该句可以省略掉

Begin --注意这里的Begin和End不可以省略。

函数主体 --函数主题中@TbName是可以参与语句运算的。

Return --这里是直接一个不带任何参数的返回。

End

4，函数规范中的约定

1>，函数不支持实参，只支持形参。

2>，函数参数的默认值在调用时不能缺省，必须用default来站位，这个跟过程不同。并且在调用函数的时候必须加拥有者，例如dbo.

3>，可以用sp_helptext,sp_depends,sp_rename来对参数进行基本操作。

4>，函数不允许更改系统标志位或使用非确定性的表达式。

例如： set datefirst 7; @@CPU_Busy; @@Max_Connections;@@Connections;@@Total_Errors;

Getdate(),NewId(),Rand()

5>，对于【多语句表值函数】的限制比较宽，除了常用的控制和赋值语句外，可以使用游标，Insert,Update,Delete语句，但是这些

语句也局限于处理局部变量和表，不允许将数据返回到外部。另外，可以使用Execute调用扩展存储过程。

三，触发器

触发器主要的功能是用来保证数据的完整性，这点跟外键的Reference的作用有点类似。不过它比外键或者Check约束更强大的是可以跨越几个表。

在处理的方法上，触发器算是比较复杂，而且很影响系统性能的一种方法，其执行的时候都是被动执行，常伴随随着表操作Insert,Update,Delete一起

执行。触发器不亦频繁使用，而且使用时要特别小心。

触发器从大类上分，主要有instead of 触发器和 After触发器，一般都以After触发器为主(默认选择)。触发器子功能又分为三种:insert,Update,Delete，

我们建Instead of 或After触发器时可以选择这三种触发器的一个子集。触发器创建格式如下：

Create Trigger TrNameOn (TbName 或 ViewName)

With Encryption

For 功能子集或 instead of 功能子集 --这里For开头的代表是After触发器，Instead of代表instead触发器,两种只能选一种。子集是insert,Update,Delete

--中的一种或几种。

触发器方法主段 --这里会出现几张系统临时表,表名分别为：inserted,deleted。

--其中，insert子功能会使系统出现表Inserted;delete子功能会使系统出现表deleted;而update子功能会同时出现inserted和deleted

这里主要针对After类型的触发器作一说明：

1，下面通过一个举例，来实现通过触发器，对一个表数据修改，插入，删除历史数据的保存。

先是建立了四个表，T_TRTest, T_TRTest_History, T_TRTest_Append, T_TRTest_Modify 第一个是数据表，后三个保存对应操作的历史记录。

IF not EXISTS(SELECT 1 FROM sysobjects WHERE NAME='T_TRTest' AND xtype='U')
CREATE TABLE T_TRTest        --主测试表，用来建立触法器
(sid uniqueidentifier Default(Newid()) primary key,
TestData VARCHAR(10))

IF not EXISTS(SELECT 1 FROM sysobjects WHERE NAME='T_TRTest_History' AND xtype='U')
CREATE TABLE T_TRTest_History --历史表，记录删除的数据
(sid uniqueidentifier Default(Newid()) primary key,
msid UNIQUEIDENTIFIER,    --用来保存测试表主键
TestData VARCHAR(10)IF not EXISTS(SELECT 1 FROM sysobjects WHERE NAME='T_TRTest_Append' AND xtype='U')
CREATE TABLE T_TRTest_Append --同步表，新增数据时，同时记录到这里
(sid uniqueidentifier Default(Newid()) primary key,
msid UNIQUEIDENTIFIER,
TestData VARCHAR(10))

IF not EXISTS(SELECT 1 FROM sysobjects WHERE NAME='T_TRTest_Modify' AND xtype='U')
CREATE TABLE T_TRTest_Modify --修改表，记录所有的修改细节
(sid uniqueidentifier Default(Newid()) primary key,
msid UNIQUEIDENTIFIER,
TestData VARCHAR(10),
OpSign VARCHAR(1)           --用来记录操作类型，'I'为新增的数据，'D'为删除的数据。
)

GO

--==========分界线，上面是建表，下面是建出发器================================
---建立触发器，为了方便理解共建立三个独立事件的触发器
--先建立删除触发器
IF exists(select 1 from dbo.sysobjects where NAME='TRD_T_TRTest' AND xtype='TR')
DROP TRIGGER TRD_T_TRTest
GO
CREATE TRIGGER TRD_T_TRTest on T_TRTest   --删除触发器
for DELETE
as
BEGIN
--把所有删除的数据都拷贝到历史表中
INSERT T_TRTest_History (msid,TestData)
SELECT sid, TestData FROM DELETED
END
GO

--再建立新增触发器
IF exists(select 1 from dbo.sysobjects where NAME='TRI_T_TRTest' AND xtype='TR')
DROP TRIGGER TRI_T_TRTest
GO
CREATE TRIGGER TRI_T_TRTest on T_TRTest   --插入触发器
for INSERT
as
BEGIN
--把所有新增的数据都同时拷贝到新增记录表中
INSERT T_TRTest_Append (msid,TestData)
SELECT sid, TestData FROM INSERTED
END
GO

--最后建立修改触发器
IF exists(select 1 from dbo.sysobjects where NAME='TRU_T_TRTest' AND xtype='TR')
DROP TRIGGER TRU_T_TRTest
GO
CREATE TRIGGER TRU_T_TRTest on T_TRTest   --更新触发器
for UPDATE
as
BEGIN
--先把所有新增的数据都拷贝到修改表中,并且操作标记设置为I
INSERT T_TRTest_Modify (msid,TestData,OpSign)
SELECT sid, TestData, 'I' FROM INSERTED
--再把所有删除的数据也拷贝到修改表中，并且操作标记设置为D
INSERT T_TRTest_Modify (msid,TestData,OpSign)
SELECT sid, TestData, 'D' FROM Deleted
END
GO

--INSERT T_TRTest(TestData) VALUES('AABBCC')
--INSERT T_TRTest(TestData) VALUES('BBCCDD')
--INSERT T_TRTest(TestData) VALUES('FFFFFF')

--UPDATE T_TRTest SET TestData='SSSSSS'
--WHERE TestData='FFFFFF'

--DELETE T_TRTest

--SELECT * FROM T_TRTest
--SELECT * FROM T_TRTest_History
--SELECT * FROM T_TRTest_Append
--SELECT * FROM T_TRTest_Modify

以上的例子分别说明了After触发器中的Update,Insert,Delete触发器的用法。

2，利用RollBack实现回滚，防止对特定的数据进行操作。同样举例如下：

IF not EXISTS(SELECT 1 FROM sysobjects WHERE NAME='T_TRTestRollBack ' AND xtype='U')
CREATE TABLE T_TRTestRollBack        --主测试表，用来建立触法器
(sid uniqueidentifier Default(Newid()) primary key,
TestDataRollBack VARCHAR(10))

IF exists(select 1 from dbo.sysobjects where NAME='TRD_T_TRTestRollBack' AND xtype='TR')
DROP TRIGGER TRD_T_TRTestRollBack
GO
CREATE TRIGGER TRD_T_TRTestRollBack on T_TRTestRollBack   --删除触发器
for DELETE
as
BEGIN
IF EXISTS(SELECT 1 FROM Deleted WHERE TestDataRollBack='AAA')
Rollback
END
GO

INSERT T_TRTestRollBack(TestDataRollBack)VALUES('AAA')
INSERT T_TRTestRollBack(TestDataRollBack)VALUES('BBB')

DELETE T_TRTestRollBack WHERE TestDataRollBack='BBB'
DELETE T_TRTestRollBack WHERE TestDataRollBack='AAA'

在这个例子中，我们会发现，试图删除内容为'AAA'的数据是永远删除不掉的，并且系统会报触发器回滚异常。

3，instead of触发器

Instead of 触发器会完全用触发器的主体语句去代替对表的Insert,Update,Delete等操作。其使用场景一般有两种，一是

防止误操作，这个比Rollback效率高，也不会报异常；另一个是保护特定的字段，不被操作。例如：

IF not EXISTS(SELECT 1 FROM sysobjects WHERE NAME='T_TRTestInstead ' AND xtype='U')
CREATE TABLE T_TRTestInstead --主测试表，用来建立触法器
(sid uniqueidentifier Default(Newid()) primary key,
TestDataInstead VARCHAR(10))

IF exists(select 1 from dbo.sysobjects where NAME='TRD_T_TRTestInstead' AND xtype='TR')
DROP TRIGGER TRD_T_TRTestInstead
GO
CREATE TRIGGER TRD_T_TRTestInstead
on T_TRTestInstead
instead of Delete
as
begin
IF EXISTS(SELECT 1 FROM Deleted WHERE TestDataInstead='AAA')
SELECT * FROM deleted
end
GO

IF exists(select 1 from dbo.sysobjects where NAME='TRD_T_TRTestInstead2' AND xtype='TR')
DROP TRIGGER TRD_T_TRTestInstead2
GO
CREATE TRIGGER TRD_T_TRTestInstead2
on T_TRTestInstead
instead of INSERT
as
begin
INSERT INTO T_TRTestInstead(TestDataInstead)
SELECT TestDataInstead FROM inserted
end
GO

INSERT T_TRTestInstead(sid,TestDataInstead)VALUES('00000000-0000-0000-0000-000000000000','AAA')
INSERT T_TRTestInstead(sid,TestDataInstead)VALUES('00000000-0000-0000-0000-000000000000','BBB')
INSERT T_TRTestInstead(sid)VALUES('00000000-0000-0000-0000-000000000000')

DELETE T_TRTestInstead WHERE TestDataInstead='BBB'
DELETE T_TRTestInstead WHERE TestDataInstead='AAA'

SELECT * FROM T_TRTestInstead

以上的例子建立了两个instead of触发器，我们在后面的执行代码中可以看到，主动插入Sid值时，全部失效了，系统会按照其默认的

DF约束增加值，这种方法有利于保护indentity，timestamp, 及计算数据字段不受其他语句的干扰；另外，我们发现数据根本删除不

掉了，并且删除数据总AAA内容的数据，还会直接显示出所有AAA内容的记录，原本的Delete功能已经消失了。

四，视图

Create View VwName

With Check Option --对View更新时即时改变数据，可省略

With Encryption --可省略

具有 Select的语句段

View 可以查看一个或多个表的内容，常用于使用频率比较高的表访问。设计中常用的方法是，一，将多个表当作一个表使用，这样有助于代码

和数据流程的简练。二，将一个表根据标志位和使用字段不同，拆作多个表（视图）使用，这样的好处是数据结构比较紧密，运行效率高。

在使用View中，应该注意以下几点：

1，不要企图使用Create View [其他数据库]..ViewName 来在其他数据库上建立View,只允许在当前数据库上建立View,但是可以在内容中

利用[其他数据库]的前缀来访问其他数据库中的表。

当然，也有种间接在其他服务器上建立View的方法: [其他数据库]..sp_executesql N' 创建View语句' 来实现跨数据库建视图。

2，视图的创建名称在sysobjects表；视图的创建语句在syscomments表；视图所用到的表在sysdepends表中。

这里需要注意的是调用sp_rename进行更改时，不会改变syscomments中的创建语句，可以用sp_helptext进行查看。

3，视图的语句有这样几个限制：

1>,不能包含Compute [By]字句。

2>,不能直接使用Order by，但是允许用Top N...Order By。

3>,不能使用into[表]。

4>,不能使用临时表或表变量。

4，如果想要让视图具备可更新性质（可写），那需要有以下几点限制：

1>,可以直接利用instead of 触发器来实现视图的更新。

2>,视图中不能包含聚合函数，Group by，Top N,Distinct 语句。

3>,列中不能有很复杂的计算列(非常规算法)。

4>,结果列，至少有一列是由实际表字段查询得到的，不能所有列都是纯数据列（例如常量'a')。

5>,分区视图，即由Union或Union All串起来的若干表组成的视图其可更新情况限制比较复杂，因用得少，这里不作表述。

posted @ 2017-01-05 17:28 莫霏阅读(284) 评论(0) 编辑收藏举报

刷新页面返回顶部

莫霏

SQL-Server使用点滴(三-方法篇)

公告