数据库 SQL触发器实例讲解

《数据库 SQL触发器实例讲解》由会员分享，可在线阅读，更多相关《数据库 SQL触发器实例讲解（24页珍藏版）》请在装配图网上搜索。

1、SQL触发器实例讲解1SQL 触发器实例 1定义： 何为触发器？在 SQL Server 里面也就是对某一个表的一定的操作，触发某种条件，从 而执行的一段程序。触发器是一个特殊的存储过程。常见的触发器有三种：分别应用于 Insert , Update , Delete 事件。 我为什么要使用触发器？比如，这么两个表：Create Table Student（-学生表StudentID int primary key, -学号）-学生借书记录表-流水号Create Table BorrowRecord(BorrowRecord int identity(1,1),StudentIDBorrowD

2、ateReturnDAteint , datetime, Datetime,-学号-借出时间-归还时间）用到的功能有:1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关（也就是同时更改 借书记录表的学号）;2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。这时候可以用到触发器。对于 1，创建一个 Update 触发器：Create Trigger truStudentOn Student-在 Student 表中创建触发器for Update-为什么事件触发As-事件触发后所要做的事情if Update(StudentID)beginUpdate Borrow

3、RecordSet StudentID=i.StudentIDFrom BorrowRecord br , Deleted d ,Inserted i-Deleted 和 Inserted 临时表Where br.StudentID=d.StudentIDend理解触发器里面的两个临时的表：Deleted , Inserted。注意Deleted与Inserted分别 表示触发事件的表“旧的一条记录”和“新的一条记录”。一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：虚拟表 Inserted虚拟表 Deleted在表记录新增时修改时删除时存放新增的记录 存放用来更新的新记录

4、不存储记录不存储记录存放更新前的记录 存放被删除的记录一个Update的过程可以看作为：生成新的记录到Inserted表，复制旧的记录到Deleted 表，然后删除Student记录并写入新纪录。对于 2，创建一个 Delete 触发器Create trigger trdStudentOn Studentfor DeleteAsDelete BorrowRecordFrom BorrowRecord br , Delted dWhere br.StudentID=d.StudentID从这两个例子我们可以看到了触发器的关键：A.2个临时的表；B.触发机制。SQL触发器实例2/* 建立虚拟测试环

5、境，包含：表卷烟库存表，表卷烟销售表。 请大家注意跟踪这两个表的数据，体会触发器到底执行了什么业务逻辑，对数据有什么影响。 为了能更清晰的表述触发器的作用，表结构存在数据冗余，且不符合第三范式，这里特此说明。*/USE MasterGOIF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME = 卷烟库存表)DROP TABLE 卷烟库存表GOIF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME = 卷烟销售表)DROP TABLE 卷烟销售表GO-业

6、务规则：销售金额 = 销售数量 * 销售单价 业务规则。CREATE TABLE 卷烟销售表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,购货商 VARCHAR(40) NULL,销售数量 INT NULL,销售单价 MONEY NULL,销售金额 MONEY NULL)GO-业务规则：库存金额 = 库存数量 * 库存单价 业务规则。CREATE TABLE 卷烟库存表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,库存数量 INT NULL,库存单价 MONEY NULL,库存金额 MONEY NULL)GO-创建触发器，示例1/

7、*创建触发器T_INSERT_卷烟库存表,这个触发器较简单。说明： 每当卷烟库存表发生 INSERT 动作，则引发该触发器。触发器功能： 强制执行业务规则,保证插入的数据中,库存金额 = 库存数量 * 库存单价 注意：INSERTED、DELETED为系统表，不可创建、修改、删除，但可以调用。 重要： 这两个系统表的结构同插入数据的表的结构。*/IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME=T_INSERT 卷烟库存表)DROP TRIGGER T_INSERT_ 卷烟库存表GOCREATE TRIGGER T

8、_INSERT_ 卷烟库存表ON 卷烟库存表FOR INSERTAS-提交事务处理BEGIN TRANSACTION-强制执行下列语句，保证业务规则UPDATE 卷烟库存表SET 库存金额 = 库存数量 * 库存单价WHERE 卷烟品牌 IN （SELECT 卷烟品牌 from INSERTED）COMMIT TRANSACTIONGO/*针对卷烟库存表，插入测试数据： 注意，第一条数据（红塔山新势力）中的数据符合业务规则， 第二条数据（红塔山人为峰）中，库存金额空，不符合业务规则， 第三条数据（云南映像）中，库存金额不等于库存数量乘以库存单价，不符合业务规则 第四条数据库存数量为 0。请注意

9、在插入数据后，检查卷烟库存表中的数据是否 库存金额 = 库存数量 * 库存单价。 */INSERT INTO 卷烟库存表（卷烟品牌,库存数量,库存单价,库存金额）SELECT 红塔山新势力,100,12,1200 UNION ALLSELECT 红塔山人为峰,100,22,NULL UNION ALLSELECT 云南映像,100,60,500 UNION ALLSELECT 玉溪,0,30,0GO-查询数据SELECT * FROM 卷烟库存表GO/*结果集RecordId 卷烟品牌 库存数量 库存单价 库存金额1 红塔山新势力 100 12.0000 1200.00002 红塔山人为峰 1

10、00 22.0000 2200.00003 云南映像 100 60.0000 6000.00004 玉溪 0 30.0000 .0000（所影响的行数为 4 行）*/-触发器示例2 /* 创建触发器T_INSERT_卷烟销售表,该触发器较复杂。 说明: 每当卷烟库存表发生 INSERT 动作，则引发该触发器。 触发器功能： 实现业务规则。业务规则: 如果销售的卷烟品牌不存在库存或者库存为零,则返回错误。 否则则自动减少卷烟库存表中对应品牌卷烟的库存数量和库存金额。*/IF EXISTS （SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME

11、 =T_INSERT 卷烟销售表）DROP TRIGGER T_INSERT_ 卷烟销售表GOCREATE TRIGGER T_INSERT_ 卷烟销售表ON 卷烟销售表FOR INSERTASBEGIN TRANSACTION-检查数据的合法性：销售的卷烟是否有库存,或者库存是否大于零IF NOT EXISTS （SELECT 库存数量FROM 卷烟库存表WHERE 卷烟品牌 IN （SELECT 卷烟品牌 FROM INSERTED）BEGIN-返回错误提示RAISERROR（错误！该卷烟不存在库存，不能销售。,16,1）-回滚事务ROLLBACKRETURNENDIF EXISTS （S

12、ELECT 库存数量FROM 卷烟库存表WHERE 卷烟品牌 IN （SELECT 卷烟品牌 FROM INSERTED） AND库存数量 = 0）BEGIN-返回错误提示RAISERROR（错误！该卷烟库存小于等于0不能销售。,16,1）-回滚事务ROLLBACK RETURN END-对合法的数据进行处理 -强制执行下列语句，保证业务规则UPDATE 卷烟销售表SET 销售金额 = 销售数量 * 销售单价WHERE 卷烟品牌 IN （SELECT 卷烟品牌 FROM INSERTED）DECLARE 卷烟品牌 VARCHAR（40）SET 卷烟品牌 = （SELECT 卷烟品牌 FROM

13、INSERTED）DECLARE 销售数量 MONEYSET 销售数量 = （SELECT 销售数量 FROM INSERTED）UPDATE 卷烟库存表SET 库存数量 = 库存数量 - 销售数量,库存金额 = （库存数量 - 销售数量）*库存单价WHERE 卷烟品牌 = 卷烟品牌COMMIT TRANSACTIONGO-请大家自行跟踪卷烟库存表和卷烟销售表的数据变化。-针对卷烟销售表，插入第一条测试数据，该数据是正常的。INSERT INTO 卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）SELECT 红塔山新势力,某购货商,10,12,1200GO-针对卷烟销售表，插入第二

14、条测试数据，该数据 销售金额 不等于 销售单价 * 销售数量 -触发器将自动更正数据，使 销售金额 等于 销售单价 * 销售数量。INSERT INTO 卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）SELECT 红塔山人为峰,某购货商,10,22,2000GO-针对卷烟销售表，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中找不到对应 -触发器将报错。INSERT INTO 卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）SELECT 红河 V8,某购货商,10,60,600GO/*结果集服务器：消息50000,级别16,状态1,过程T_INSERT 卷烟销售

15、表，行15 错误！该卷烟不存在库存，不能销售。*/-针对卷烟销售表，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中库存为 0。-触发器将报错。INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)SELECT 玉溪,某购货商,10,30,300GO/*结果集服务器：消息50000,级别16,状态1,过程T_INSERT 卷烟销售表，行29 错误！该卷烟库存小于等于 0，不能销售。*/-查询数据SELECT * FROM 卷烟库存表SELECT * FROM 卷烟销售表GO/*补充：1、本示例主要通过一个简单的业务规则实现来进行触发器使用的说明,具体的要根据

16、需要灵活 处理；2、关于触发器要理解并运用好 INSERTED , DELETED 两个系统表；3、本示例创建的触发器都是 FOR INSERT ,具体的语法可参考：Trigger 语法CREATE TRIGGER trigger_nameON table | view WITH ENCRYPTION -用于加密触发器 FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUM

17、NS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n sql_statement .n 4、关于触发器，还应该注意(1)、DELETE 触发器不能捕获 TRUNCATE TABLE 语句。 (2)、触发器中不允许以下 Transact-SQL 语句： ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE DROP DATABASE LOAD DATABASE LOAD LOG RECONFIGURE RESTORE DAT

18、ABASE RESTORE LOG(3)、触发器最多可以嵌套 32 层。*/-修改触发器-实质上，是将 CREATE TRIGGER . 修改为 ALTER TRIGGER .即可。-删除触发器DROP TRIGGER xxxGO-删除测试环境DROP TABLE 卷烟库存表GODROP TABLE 卷烟销售表GODROP TRIGGER T_INSERT_ 卷烟库存表GODROP TRIGGER T_INSERT_ 卷烟销售表GO# #触发器的基础知识和例子：create trigger tr_nameon table/viewfor | after | instead of update,

19、insert,deletewith encryptionas batch | if update (col_name) and|or update (col_name) 说明：1 tr_name ：触发器名称2 on table/view ：触发器所作用的表。一个触发器只能作用于一个表3 for 和 after ：同义4 after 与 instead of :sql 2000 新增项目 afrer 与 instead of 的区别After 在触发事件发生以后才被激活,只可以建立在表上Instead of 代替了相应的触发事件而被执行,既可以建立在表上也可以建立在视图上5 insert 、u

20、pdate 、delete ：激活触发器的三种操作，可以同时执行，也可选其一6 if update (col_name) ：表明所作的操作对指定列是否有影响，有影响，则激活触发器。此 外，因为 delete 操作只对行有影响，所以如果使用 delete 操作就不能用这条语句了(虽然使用也不出错，但是不能激活触发器，没 意义)。7 触发器执行时用到的两个特殊表： deleted ,inserteddeleted 和 inserted 可以说是一种特殊的临时表，是在进行激活触发器时由系统自动生成的， 其结构与触发器作用的表结构是一样的，只是存放 的数据有差异。续下面表格说明 deleted 与 i

21、nserted 数据的差异deleted 与 inserted 数据的差异Inserted存放进行 insert 和 update 操作后的数据Deleted存放进行 delete 和 update 操作前的数据注意：update操作相当于先进行delete再进行insert，所以在进行update操作时，修改前 的数据拷贝一条到 deleted 表中，修改后的数据在存到触发器作用的表的同时，也同时生成一条拷贝到insered表中接(一)-触发器示例2/*创建触发器T_INSERT 卷烟销售表,该触发器较复杂。说明: 每当卷烟库存表发生 INSERT 动作，则引发该触发器。触发器功能： 实现业

22、务规则。业务规则: 如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。 否则则自动减少卷烟库存表中对应品牌卷烟的库存数量和库存金额。*/IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME=T_INSERT 卷烟销售表)DROP TRIGGER T_INSERT_ 卷烟销售表GOCREATE TRIGGER T_INSERT_ 卷烟销售表ON 卷烟销售表FOR INSERTASBEGIN TRANSACTION-检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零IF NOT EXISTS （SELECT 库

23、存数量FROM 卷烟库存表WHERE 卷烟品牌 IN （SELECT 卷烟品牌 FROM INSERTED） ）BEGIN-返回错误提示RAISERROR（错误！该卷烟不存在库存，不能销售。,16,1）-回滚事务ROLLBACKRETURNENDIF EXISTS （SELECT 库存数量FROM 卷烟库存表WHERE 卷烟品牌 IN （SELECT 卷烟品牌 FROM INSERTED） AND库存数量 = 0）BEGIN-返回错误提示RAISERROR（错误！该卷烟库存小于等于0不能销售。,16,1）-回滚事务ROLLBACKRETURNEND-对合法的数据进行处理-强制执行下列语句，保证

24、业务规则UPDATE 卷烟销售表SET 销售金额 = 销售数量 * 销售单价WHERE 卷烟品牌 IN （SELECT 卷烟品牌 FROM INSERTED）DECLARE 卷烟品牌 VARCHAR（40）SET 卷烟品牌 = （SELECT 卷烟品牌 FROM INSERTED）DECLARE 销售数量 MONEYSET 销售数量 = （SELECT 销售数量 FROM INSERTED）UPDATE 卷烟库存表SET 库存数量 = 库存数量 - 销售数量,库存金额 = （库存数量 - 销售数量）*库存单价WHERE 卷烟品牌 = 卷烟品牌COMMIT TRANSACTIONGO-请大家自行

25、跟踪卷烟库存表和卷烟销售表的数据变化。-针对卷烟销售表，插入第一条测试数据，该数据是正常的。INSERT INTO 卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）SELECT 红塔山新势力,某购货商,10,12,1200GO-针对卷烟销售表，插入第二条测试数据，该数据 销售金额 不等于 销售单价 * 销售 数量。-触发器将自动更正数据，使 销售金额 等于 销售单价 * 销售数量。INSERT INTO 卷烟销售表（卷烟品牌,购货商,销售数量,销售单价,销售金额）SELECT 红塔山人为峰,某购货商,10,22,2000GOSQL 拥有很多可用于计数和计算的内建函数。函数的语法内建

26、 SQL 函数的语法是：SELECT function（歹U ） FROM 表函数的类型在SQL中，基本的函数类型和种类有若干种。函数的基本类型是 Aggregate 函数 Scalar 函数合计函数(Aggregate functions)Aggregate 函数的操作面向一系列的值，并返回一个单一的值。注释：如果在 SELECT 语句的项目列表中的众多其它表达式中使用 SELECT 语句，则这个 SELECT 必须使用 GROUP BY 语句！Persons table （在大部分的例子中使用过）NameAgeAdams, John38Bush, George33Carter, Thoma

27、s28MS Access 中的合计函数函数描述AVG(column)返回某列的平均值COUNT(column)返回某列的行数（不包括NULL值）COUNT(*)返回被选行数FIRST(column)返回在指定的域中第一个记录的值LAST(column)返回在指定的域中最后一个记录的值MAX(column)返回某列的最高值MIN(column)返回某列的最低值STDEV(column)STDEVP(column)SUM(column)返回某列的总和VAR(column)VARP(column)在SQL Server中的合计函数函数描述AVG(column)返回某列的行数BINARY_CHECKS

28、UMCHECKSUMCHECKSUM_AGGCOUNT(column)返回某列的行数（不包括NULL值）COUNT(*)返回被选行数COUNT(DISTINCT column)返回相异结果的数目FIRST(column)返回在指定的域中第一个记录的值（SQLServer2000不支持）LAST(column)返回在指定的域中最后一个记录的值（SQLServer2000不支持）MAX(column)返回某列的最高值MIN(column)返回某列的最低值STDEV(column)STDEVP(column)SUM(column)返回某列的总和VAR(column)VARP(column)SQLSe

29、rver 游标简介与使用说明游标(Cursor )是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了 在结果集中一次以行或者多行前进或向后浏览数据的能力。我们可以把游标当作一个指针，它可 以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。1. 游标的组成游标包含两个部分：一个是游标结果集、一个是游标位置。游标结果集：定义该游标得SELECT语句返回的行的集合。游标位置：指向这个结果集某一行的当前指针。2. 游标的分类游标共有3类：API服务器游标、Transaction-SQL游标和API客户端游标。其中前两种游标都是运行在服务器上的，所以又叫做服务器游标。API服务

30、器游标API服务器游标主要应用在服务上，当客户端的应用程序调用API游标函数时，服务器会对 API 函数进行处理。使用 API 函数和方法可以实现如下功能：(1) 打开一个连接。(2) 设置定义游标特征的特性或属性，API自动将游标影射到每个结果集。(3) 执行一个或多个Transaction-SQL语句。(4) 使用API函数或方法提取结果集中的行。API服务器游标包含以下四种：静态游标、动态游标、只进游标、键集驱动游标(Primary key)静态游标的完整结果集将打开游标时建立的结果集存储在临时表中，(静态游标始终是只 读的)。静态游标具有以下特点：总是按照打开游标时的原样显示结果集；不

31、反映数据库中作的 任何修改，也不反映对结果集行的列值所作的更改；不显示打开游标后在数据库中新插入的行； 组成结果集的行被其他用户更新，新的数据值不会显示在静态游标中；但是静态游标会显示打开 游标以后从数据库中删除的行。动态游标与静态游标相反，当滚动游标时动态游标反映结果集中的所有更改。结果集中的 行数据值、顺序和成员每次提取时都会改变。只进游标不支持滚动，它只支持游标从头到尾顺序提取数据行。注意：只进游标也反映对 结果集所做的所有更改。键集驱动游标同时具有静态游标和动态游标的特点。当打开游标时，该游标中的成员以及 行的顺序是固定的，键集在游标打开时也会存储到临时工作表中，对非键集列的数据值的更

32、改在 用户游标滚动的时候可以看见，在游标打开以后对数据库中插入的行是不可见的，除非关闭重新 打开游标。Transaction-SQL 游标该游标是基于 Declare Cursor 语法，主要用于 Transaction-SQL 脚本、存储过程以及 触发器中。Transaction-SQL游标在服务器处理由客户端发送到服务器的Transaction-SQL 语句。在存储过程或触发器中使用Transaction-SQL游标的过程为：(1) 声明Transaction-SQL变量包含游标返回的数据。为每个结果集列声明一个变量。 声明足够大的变量来保存列返回的值，并声明变量的类型为可从数据类型隐式转

33、换得到的数据类 型。(2) 使用Declare Cursor语句将Transaction-SQL游标与Select语句相关联。还可 以利用 Declare Cursor 定义游标的只读、只进等特性。(3) 使用Open语句执行Select语句填充游标。(4) 使用Fetch Into语句提取单个行，并将每列中得数据移至指定的变量中。注意：其 他 Transaction-SQL 语句可以引用那些变量来访问提取的数据值。 Transaction-SQL 游标不 支持提取行块。(5) 使用Close语句结束游标的使用。注意：关闭游标以后，该游标还是存在，可以使 用 Open 命令打开继续使用，只有调

34、用 Deallocate 语句才会完全释放。客户端游标该游标将使用默认结果集把整个结果集高速缓存在客户端上，所有的游标操作都在客户端 的高速缓存中进行。注意：客户端游标只支持只进和静态游标。不支持其他游标。3.游标的生命周期游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放 游标。声明游标是为游标指定获取数据时所使用的Select语句，声明游标并不会检索任何数据， 它只是为游标指明了相应的 Select 语句。Declare 游标名称 Cursor 参数声明游标的参数(1) Local与Global:Local表示游标的作用于仅仅限于其所在的存储过程、触发器 以及批

35、处理中、执行完毕以后游标自动释放。Global表示的是该游标作用域是整个会话层。由 连接执行的任何存储过程、批处理等都可以引用该游标名称，仅在断开连接时隐性释放。(2) Forward_only与Scroll:前者表示为只进游标，后者表示为可以随意定位。默 认为前者。(3) Static、Keyset与Dynamic:第一个表示定义一个游标，其数据存放到一个 临时表内，对游标的所有请求都从临时表中应答，因此，对该游标进行提取操作时返回的数据不 反映对基表所作的修改，并且该游标不允许修改。Keyset表示的是，当游标打开时，键集驱动 游标中行的身份与顺序是固定的，并把其放到临时表中。Dynami

36、c表示的是滚动游标时，动态 游标反映对结果集内所有数据的更改。(4) Read_only、Scroll_Locks 与 Optimistic:第一个表示的是只读游标，第二 个表示的是在使用的游标结果集数据上放置锁，当行读取到游标中然后对它们进行修改时，数据 库将锁定这些行，以保证数据的一致性。Optimistic的含义是游标将数据读取以后，如果这些 数据被更新了，则通过游标定位进行的更新与删除操作将不会成功。标准游标:Declare MyCursor CursorFor Select * From Master_Goods只读游标Declare MyCusror CursorFor Selec

37、t * From Master_GoodsFor Read Only可更新游标Declare MyCusror CursorFor Select * From Master_GoodsFor UpDate打开游标使用 Open 语句用于打开 Transaction-SQL 服务器游标，执行 Open 语句的过 程中就是按照 Select 语句进行填充数据，打开游标以后游标位置在第一行。打开游标全局游标：Open Global MyCursor局部游标：Open MyCursor读取游标数据：在打开游标以后，使用Fetch语句从Transaction-SQL服务器游标中检 索特定的一行。使用 F

38、etch 操作，可以使游标移动到下一个记录，并将游标返回的每个列得数 据分别赋值给声明的本地变量。Fetch Next | Prior | First | Last | Absolute n | Relative n From MyCursorInto GoodsID,GoodsName其中：Next表示返回结果集中当前行的下一行记录，如果第一次读取则返回第一行。 默认的读取选项为 NextPrior 表示返回结果集中当前行的前一行记录，如果第一次读取则没有行返回， 并且把游标置于第一行之前。First表示返回结果集中的第一行，并且将其作为当前行。Last表示返回结果集中的最后一行，并且将其作

39、为当前行。Absolute n如果n为正数，则返回从游标头开始的第n行，并且返回行变成 新的当前行。如果n为负，则返回从游标末尾开始的第n行，并且返回行为新的当前行，如果n 为 0，则返回当前行。Relative n 如果 n 为正数，则返回从当前行开始的第 n 行，如果 n 为负,则 返回从当前行之前的第n行，如果为0,则返回当前行。关闭游标调用的是Close语句，方式如下：Close Global MyCursorCloseMyCursor释放游标调用的是 Deallocate 语句，方法如下： Deallocate GlboalMyCursor Deallocate MyCursor游标

40、实例:Declare MyCusror Cursor ScrollFor Select * From Master_Goods Order By GoodsIDOpen MyCursorFetch next From MyCursorInto GoodsCode,GoodsNameWhile(Fetch_Status = 0)BeginBeginSelect GoodsCode = Convert(Char(20),GoodsCode)Select GoodsName = Convert(Char(20),GoodsName)PRINT GoodsCode + : + GoodsNameEnd

41、Fetch next From MyCursorInto GoodsCode,GoodsNameEndClose MyCursorDeallocate MyCursor修改当前游标的数据方法如下:UpDate Master_Goods Set GoodsName = yangyang8848 Where CurrentOf MyCursor;删除当前游标行数据的方法如下:Delete From Master_Goods Where Current Of MyCursorSelect CURSOR_ROWS 可以得到当前游标中存在的数据行数。注意：此变量为一 个连接上的全局变量，因此只对应最后一

42、次打开的游标。SQLServer 存储过程简介与使用方法Sql Server 的存储过程是一个被命名的存储在服务器上的 Transacation-Sql 语句集合 , 是封装重复性工作的一种方法,它支持用户声明的变量、条件执行和其他强大的编程功能。存储过程相对于其他的数据库访问方法有以下的优点： （1）重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。（2）提高性能。存储过程在创建的时候就进行了编译，将来使用的时候不用再重新编 译。一般的SQL语句每执行一次就需要编译一次，所以使用存储过程提高了效率。（3）减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称

43、 以及参数就可以了，因此降低了网络传输的数据量。（4）安全性。参数化的存储过程可以防止SQL注入式的攻击，而且可以将Grant、 Deny 以及 Revoke 权限应用于存储过程。存储过程一共分为了三类：用户定义的存储过程、扩展存储过程以及系统存储过程。其中，用户定义的存储过程又分为Transaction-SQL和CLR两种类型。Transaction-SQL 存储过程是指保存的 Transaction-SQL 语句集合，可以接受和返回用 户提供的参数。CLR存储过程是指对.Net Framework公共语言运行时（CLR）方法的引用，可以接受和返 回用户提供的参数。他们在.Net Frame

44、work程序集中是作为类的公共静态方法实现的。（本 文就不作介绍了）创建存储过程的语句如下：田日Codeschema_name: 代表的是存储过程所属的架构的名称例如：Create Schema yangyang8848GoCreate Proc yangyang8848.AllGoodsAs Select * From Master_GoodsGo执行：Exec AllGoods发生错误。执行： Exec yangyang8848.AllGoods 正确执行。；Number: 用于对同名过程进行分组的可选整数。使用一个 DROP PROCEDURE 语 句可将这些分组过程一起删除。例如：Cr

45、eate Proc S1 ;1ASSelect * From Master_GoodsGoCreate Proc S1 ;2AsSelect * From Master_LocationGo创建完毕了两个存储过程。它们在同一个组S1里，如果执行Exec S1则存储过程默 认执行 Exec S1 ;1 。如果我们想得到所有据点信息则需要执行 Exec S1 ;2。当我们要删除 存储过程的时候，只能执行 Drop Exec S1 则该组内所有的存储过程被删除。 parameter: 存储过程中的参数，除非将参数定义的时候有默认值或者将参数设置为 等于另一个参数，否则用户必须在调用存储过程的时候为参

46、数赋值。存储过程最多有 2100 个参数。例如：Create Proc yangyang8848.OneGoodsGoodsCode varchar(10)AsSelect * From Master_Goods Where GoodsCode = GoodsCodeGo调用的代码：Declare Code varchar(10)Set Code = 0004Exec yangyang8848.OneGoods Code在参数的后边加入 Output 表明该参数为输出参数。Create Proc yangyang8848.OneGoodsGoodsCode2 varchar(10) outpu

47、t,GoodsCode varchar(10) = 0011AsSelect * From Master_Goods Where GoodsCode = GoodsCodeSet GoodsCode2 = 0005Go调用方法：Declare VV2 varchar(10)Exec yangyang8848.OneGoods Code out注意：如果存储过程的两个参数一个有默认值一个没有，那么我们要把有默认值得放在后 边，不然会出问题哦细心的朋友，可能看到上边的语句有一些不同，比如，存储过程用的是output，而调用语 句用的是out。我要告诉您，两者是一样的。RECOMPILE：指示数据库

48、引擎不缓存该过程的计划，该过程在运行时编译。如果指定了 FOR REPLICATION，则不能使用此选项。对于CLR存储过程，不能指定RECOMPILE。这个说一个非常好用的函数 OBJECT_ID ：返回架构范围内对象的数据库对象标识号。例如：我们创建存储过程时，可以如下写代码If Object_ID(yangyang8848.OneGoods) Is Not NullDrop Proc yangyang8848.OneGoodsGoCreate Proc yangyang8848.OneGoodsGoodsCode2 varchar(10) out,GoodsCode varchar(10

49、) = 0011AsSelect * From Master_Goods Where GoodsCode = GoodsCodeSet GoodsCode2 = 0005Go针对于上边的这个存储过程，我们调用以下 SQL 查询Select definition From sys.sql_modulesWhere object_id = Object_ID(yangyang8848.OneGoods);我们是可以查到结果的。可是如果我们对该存储过程加入 ENCRYPTION 那么你将无法看到任何结果If Object_ID(yangyang8848.OneGoods) Is Not NullDr

50、op Proc yangyang8848.OneGoodsGoCreate Proc yangyang8848.OneGoodsGoodsCode2 varchar(10) out,GoodsCode varchar(10) = 0011With EncryptionAsSelect * From Master_Goods Where GoodsCode = GoodsCodeSet GoodsCode2 = 0005Go然后我们查询 sys.sql_modules目录视图，将返回给你 Null。然后我们执行以下 SQL: Exec sp_helptext yangyang8848.OneGo

51、ods你将得到以下结果：The text for object ya ngya ng8848. On eGoods is en crypted.说到这里你应该明白了，参数 ENCRYPTION ：是一种加密的功能， 将 CREATE PROCEDURE 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 2005 的任 何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。 但是，可通过 DAC 端口访问系统表的特权用户或直接访问数据库文件的特权用户可使用此文 本。此外，能够向服务器进程附加调试器的用户可在运行时从内存中检索已解密的过程。前两天写

52、了一篇关于游标的介绍文章 ，下边写一个例子，将游标与存储过程一起使用上：If Object_ID(dbo.GetMasterGoods) Is Not NullDrop Proc dbo.GetMasterGoodsGoCreate Proc GetMasterGoodsMyCursor Cursor Varying OutputWith EncryptionAsSet MyCursor = CursorForSelect GoodsCode,GoodsName From Master_GoodsOpen MyCursorGo-下边建立另外一个存储过程，用于遍历游标输出结果Create Pro

53、c GetAllGoodsIDAndNameAsDeclare GoodsCode varchar(18)Declare GoodsName nvarchar(20)Declare MasterGoodsCursor CursorExec GetMasterGoods MasterGoodsCursor outFetch Next From MasterGoodsCursorInTo GoodsCode,GoodsNameWhile(Fetch_Status = 0)BeginBeginPrint GoodsCode + : + GoodsNameEndFetch Next From MasterGoodsCursorInTo GoodsCode,GoodsNameEndClose MasterGoodsCursorDeallocate MasterGoodsCursorGo最后执行 Exec GetAllGoodsIDAndName 结果为以下内容品 0003品 0004123123品 0006品 0007品 0008品 0009品 0010品 0011品 0012品 0013品 0014000300040005000600070008000900100011001200130014