1、第 8 章 存储过程、触发器和游标第 8 章 存储过程、触发器和游标8.1存储过程存储过程8.2触发器触发器8.3游标游标 第 8 章 存储过程、触发器和游标8.1存存 储储 过过 程程8.1.1存储过程的概念和分类存储过程的概念和分类1.存储过程的概念存储过程的概念存储过程(Stored Procedure)是一组预先编译好的存储在服务器上的完成特定功能并且可以接受和返回用户提供的参数的 Tansact-SQL 语句的集合。存储过程是为了完成某一特定功能而编写的。第 8 章 存储过程、触发器和游标2.存储过程的分类存储过程的分类存储过程可以大大提高执行效率，增加复用性，减轻网络负担，安全性也
2、高。如果要修改存储过程，就要进入数据库中进行修改。1)系统存储过程系统存储过程(System Stored Procedure)以 sp_ 开头，如 sys.sp_addgroup(在当前数据库中创建一个组)。此类存储过程是 SQL Server 内置的存储过程，通常用来进行系统的各项设置、读取信息或执行相关管理工作。第 8 章 存储过程、触发器和游标例 8-1 查看 sc 表的约束。结果如图 8-1 所示。代码如下：exec sp_helpconstraint sc图 8-1系统存储过程第 8 章 存储过程、触发器和游标2)扩展存储过程扩展存储过程(Extended Stored Proce
3、dure)通常以 xp_ 开头，如 xp_logininfo(授予登录权限的 Windows 组和用户信息)。此类存储过程大多是用其他编程语言如 C+编写而成的，其内容并不是保存在 SQL Server 中，而是以 DLL 的形式单独存在的。例 8-2 执行以下存储过程将返回所有的 account name、type 和 privilege 等信息。代码如下：exec xp_logininfo;第 8 章 存储过程、触发器和游标例 8-3 查看有关 D:sq 文件夹的文件信息。显示结果如图 8-2 所示。代码如下：exec xp_cmdshell dir D:sq;图 8-2扩展存储过程第 8
4、 章 存储过程、触发器和游标3)用户自定义存储过程用户自定义存储过程(User-Defined Stored Procedure)是由用户设计的存储过程。其名称可以是任意组合 SQL Server 命令规则的字符组合，通常以“usp_”开头，避免以“sp_”或“xp_”开头，以免造成混淆。自定义的存储过程会被添加到所属数据库的存储过程中，并以对象的形式保存。第 8 章 存储过程、触发器和游标8.1.2创建存储过程创建存储过程1.使用使用 create procedure 语句创建存储过程语句创建存储过程1)语法格式第 8 章 存储过程、触发器和游标参数说明：(1)procedure_name
5、是要创建的存储过程的名称，它后面跟一个可选项 number，是一个整数，用来区别一组同名的存储过程，如 proc1、proc2 等。(2)parameter 用来声明存储过程的形式参数。在 create procedure 语句中，可以声明一个或多个参数。(3)data_type 是参数的数据类型。(4)varying 指定由 output 参数支持的结果集，仅应用于游标型参数。第 8 章 存储过程、触发器和游标(5)default 指定参数的缺省值。如果定义了缺省值，那么即使不给出参数值，则该存储过程仍能被调用。缺省值必须是常数或空值。(6)output 表明该参数是一个返回参数。用 out
6、put 参数可以向调用者返回信息。text 类型参数不能用作 output 参数。(7)recompile 指明 SQL Server 并不保存该存储过程的执行计划，该存储过程每执行一次都要重新编译。(8)encryption 表明 SQL Server 加密了 syscomments 表，该表的 text 字段是包含有create procedure 语句的存储过程文本，使用该关键字无法通过查看 syscomments 表来查看存储过程内容。第 8 章 存储过程、触发器和游标(9)for replication 表明仅当进行数据复制时过滤存储过程才被执行。for replication 与w
7、ith recompile 选项是互不兼容的。(10)as 指明该存储过程将要执行的动作。sql_statement 是包含在存储过程中的任何数量和类型的 SQL 语句。一个存储过程的大小最大值为 128 MB。用户定义的存储过程必须创建在当前数据库中。第 8 章 存储过程、触发器和游标2)存储过程的返回值(1)返回状态值(整数)。使用 return 语句，-99 0 为系统保留，存储过程成功执行时系统返回“0”，用户可以返回-99 0 之外的整数值来反映存储过程的运行状态。(2)返回参数值。使用 output 参数，执行存储过程时可以将值返回给 execute 语句中指定的变量。第 8 章
8、存储过程、触发器和游标 使用使用 SSMS 创建存储过程创建存储过程操作步骤如下：(1)在“对象资源管理器”中展开“数据库”节点，展开要创建存储过程的数据库。(2)展开“可编程性”节点，选择“存储过程”选项，右击鼠标弹出快捷菜单，选择“新建存储过程”命令，打开创建存储过程对话框，如图 8-3 所示。第 8 章 存储过程、触发器和游标图 8-3 SSMS 创建存储过程第 8 章 存储过程、触发器和游标(3)在右侧查询编辑器中出现存储过程的模板，显示了 create procedure 语句的框架，可以修改要创建的存储过程的名称，然后加入存储过程所包含的 T-SQL 语句即完成创建，如图 8-4
9、所示。第 8 章 存储过程、触发器和游标图 8-4定义存储过程模板第 8 章 存储过程、触发器和游标例 8-4 设有职工表 employees(empid,name,salary,dpid)和部门表 department(dpid,dpname,telephone,fax,manager)，要求：定义存储过程，根据输入的部门名称，返回该部门的员工的人数及平均工资。代码如下：第 8 章 存储过程、触发器和游标第 8 章 存储过程、触发器和游标8.1.3调用或执行存储过程调用或执行存储过程1.使用使用 execute 语句在查询编辑器中调用语句在查询编辑器中调用其语法格式如下：第 8 章 存储过程
10、、触发器和游标例 8-5 调用例 8-4 中定义的存储过程 samp，根据输入的部门名称，返回该部门的员工的人数及平均工资。代码如下：第 8 章 存储过程、触发器和游标执行结果如图 8-5 所示。图 8-5存储过程执行结果第 8 章 存储过程、触发器和游标2.在在 SSMS 中执行存储过程中执行存储过程操作步骤如下：(1)在“对象资源管理器”中展开“数据库”节点，再展开要执行存储过程的数据库。(2)展开“可编程性”节点，再展开“存储过程”选项，在用户定义的存储过程 samp上右击鼠标弹出快捷菜单，选择“执行存储过程”命令，打开“执行过程”对话框，如图8-6 所示。第 8 章 存储过程、触发器和
11、游标图 8-6执行存储过程第 8 章 存储过程、触发器和游标(3)在“执行过程”对话框输出参数为否的行后面的“值”处输入部门名称“办公室”，如图 8-7 所示。然后单击“确定”按钮，显示执行结果。第 8 章 存储过程、触发器和游标图 8-7“执行过程”对话框第 8 章 存储过程、触发器和游标8.2触发器触发器8.2.1触发器的概念和分类触发器的概念和分类1.触发器的概念触发器的概念触发器(Trigger)是针对某个表或视图所编写的特殊类型的存储过程，不能被直接调用执行，只有当该表或视图中的数据发生添加、更新或删除操作等事件时才自动触发，并执行触发器中所定义的相关操作。第 8 章 存储过程、触发
12、器和游标2.触发器的分类触发器的分类在 SQL Server 2008 R2 中，有 3 种类型的触发器：(1)DML 触发器是指在数据库中发生数据操作语言(DML)事件时启用的触发器。DML 事件即指在表或视图中修改数据的 insert、update、delete 语句。第 8 章 存储过程、触发器和游标根据 DML 触发器触发的方式不同又分为以下两种情况：After 触发器：它是在执行 insert、update、delete 语句操作之后执行触发器操作，主要是用于记录变更后的处理或检查，一旦发生错误，可以用 Rollback Transaction 语句来回滚本次事件。Instead o
13、f 触发器：它在执行 insert、update、delete 语句操作之前执行触发器本身所定义的操作。对于一个表或视图，只能定义一个 Instead of 触发器。第 8 章 存储过程、触发器和游标(2)DDL 触发器是指当服务器或数据库中发生数据定义语言(DDL)事件时启用的触发器。DDL 事件即指在表或索引中的 create、alter、drop 语句。(3)登录触发器是指当用户登录 SQL Server 实例建立会话时启用的触发器。第 8 章 存储过程、触发器和游标8.2.2创建触发器创建触发器1.使用使用 create trigger 语句创建触发器语句创建触发器其语法格式如下：第
14、8 章 存储过程、触发器和游标参数说明：(1)trigger_name 是触发器的名称。(2)table|view 是在其上创建触发器的表或视图。(3)with encryption 加密 syscomments 表中包含 create trigger 语句文本的条目。(4)after 指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。(5)instead of 指定执行触发器而不是执行触发器语句，从而替代触发语句的操作。第 8 章 存储过程、触发器和游标(6)insert,delete,update 指定在表或视图上执行哪些数据操作语句时将激活触发器的关键字。(7)wi
15、th append指定应该添加现有类型的其他触发器。(8)non for replication 表示当复制进程更改触发器所涉及的表时，不应执行该触发器。(9)as 是触发器要执行的操作。(10)sql_statement 是触发器的条件和操作。第 8 章 存储过程、触发器和游标2.在在 SSMS 中创建触发器中创建触发器操作步骤如下：(1)在“对象资源管理器”中展开“数据库”节点，在需要创建触发器的数据库中展开相应的表，找到“触发器”节点，在触发器图标或名称上右击鼠标弹出快捷菜单，选择“新建触发器”命令，如图 8-8 所示。第 8 章 存储过程、触发器和游标图 8-8“新建触发器”菜单第 8
16、 章 存储过程、触发器和游标(2)在“查询编辑器”工作界面，按触发器的格式显示编码，如图 8-9 所示。(3)用户根据需要修改触发器名称，添加触发器内容，输入触发器的编码。(4)单击“执行”按钮，当出现“命令已成功完成”的提示后，即完成创建。第 8 章 存储过程、触发器和游标图 8-9“查询编辑器”编辑窗口第 8 章 存储过程、触发器和游标例 8-6 在 categories 表中创建一个触发器，并插入一条记录验证触发器执行情况。代码如下：第 8 章 存储过程、触发器和游标选定“createend”之间的语句，单击“执行”按钮，成功创建触发器。展开“categories”表，再展开“触发器”，
17、可见 tg_catgories_insupd。插入一条记录：insert into categories values(008，军事类)触发器触发，给出提示信息“有记录被修改”，如图 8-10 所示。第 8 章 存储过程、触发器和游标图 8-10“触发器”触发给出提示信息第 8 章 存储过程、触发器和游标8.2.3修改触发器修改触发器1.使用使用 SSMS 修改触发器修改触发器操作步骤如下：(1)在“对象资源管理器”中展开“数据库”节点、“表”节点，再展开 categories 表和“触发器”节点。(2)在“TG_categories_insupd”上右击鼠标，在弹出的快捷菜单中选择“修改”命
18、令，调出查询编辑窗口。在“有记录被修改”后添加“或插入！”字样，如图 8-11 所示。(3)单击“执行”按钮，提示“命令已成功完成”，修改即完成。第 8 章 存储过程、触发器和游标图 8-11查询窗口修改触发器工作界面第 8 章 存储过程、触发器和游标2.使用使用 alter trigger 语句修改触发器语句修改触发器其语法格式如下(简化)：第 8 章 存储过程、触发器和游标8.2.4删除触发器删除触发器1.使用使用 drop trigger 语句删除触发器语句删除触发器语法如下：drop trigger trigger_name,.n例 8-7 删除 categories 表中的表触发器
19、tg_categories_indupd。代码如下：drop trigger tg_categories_indupd第 8 章 存储过程、触发器和游标2.在在 SSMS 中删除触发器中删除触发器操作步骤如下：在“对象资源管理器”中，依次展开“数据库”节点、“表”节点和“categories”节点，找到触发器TG_categories_indupd，右击鼠标，在弹出的快捷菜单中选择“删除”命令，在“删除对象”对话框中单击“确定”按钮，删除该触发器。第 8 章 存储过程、触发器和游标使用触发器可实现许多复杂的功能，但是要慎用，滥用会造成数据库及应用程序的维护困难。在数据库操作中，可以通过关系、触
20、发器、存储过程、应用程序等来实现数据操作，同时规则、约束、缺省值也是保证数据完整性的重要保障。如果过分依赖触发器，势必影响数据库的结构，同时增加维护的复杂程度。第 8 章 存储过程、触发器和游标8.3游标游标8.3.1游标的概念游标的概念在数据库开发过程中，常常会遇到这种情况，即从某一结果集中逐一地读取每一条记录。对应这样的操作，游标(Cursor)就是一种非常好的解决方案。第 8 章 存储过程、触发器和游标在数据库中，游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条 T-SQL 选择语句相关
21、联，因为游标由结果集和结果集中指向特定记录的游标位置组成，所以当决定对结果集进行操作时，必须声明一个指向该结果集的游标。第 8 章 存储过程、触发器和游标8.3.2创建游标创建游标使用游标有 3 个基本的步骤：声明游标、打开和使用游标以及关闭和释放游标。1.声明游标声明游标游标是定义在以 select 开始的数据集上的，可以将游标理解成一个定义在特定数据集上的指针，控制指针遍历数据集，或仅仅指向特定的行。游标分为游标类型和游标变量。第 8 章 存储过程、触发器和游标1)SQL-92 语法其语法格式如下：declare cursor_nameinsensitivescrollcursor for
22、 select_statementfor read only|updateof column_name,.n参数说明：(1)cursor_name 是指游标的名字。第 8 章 存储过程、触发器和游标(2)insensitive 表明 SQL Server 会将游标定义所选取出来的数据记录存放在一个临时表内(在 tempdb 数据库)，对该游标的读取操作皆由临时表来应答。当遇到以下情况时，游标将自动设定 insensitive 选项：在 select 语句中使用 distinct、group by、having、union 语句。使用 outer join。所选取的任意表没有索引。将实数值当做选
23、取的列。第 8 章 存储过程、触发器和游标(3)scroll 表明所有的提取操作(如 first、last、prior、next、relative、absollute)都可用，如果不使用该保留字，那么只能进行 next 提取操作。(4)select_statement 定义结果集的 select 语句。在游标中不能使用 compute、compute by、for browse、into 语句。(5)read only表明不允许游标内的数据被更新，尽管在缺省状态下游标是允许更新的。在 update 或 delete 语句的 where current of 子句中，不允许对该游标进行引用。(6
24、)updateof column_name,.n定义在游标中可被修改的列，如果不指出要更新的列，那么所有的列都将被更新。第 8 章 存储过程、触发器和游标2)Transact-SQL 扩展语法第 8 章 存储过程、触发器和游标2.打开和使用游标打开和使用游标第 8 章 存储过程、触发器和游标3.关闭和释放游标关闭和释放游标通过释放当前结果集并且解除定位游标的行上的游标锁定，可关闭一个开放的游标。(1)关闭游标的语法格式如下：第 8 章 存储过程、触发器和游标8.3.3游标在存储过程中的应用游标在存储过程中的应用例 8-11在 stud、sc 表中建立学生平均成绩排名游标，将成绩按降序存入游标，
25、然后使用 while 循环从游标中一条一条地取出来，实现排名。(1)创建成绩视图。代码如下：第 8 章 存储过程、触发器和游标(2)定义存储过程，并使用游标和循环语句。代码如下：第 8 章 存储过程、触发器和游标(3)创建并调用存储过程 proc_studscore。选中“create proceduredeallocate studscore”的代码部分，单击“分析”按钮，显示“命令已成功完成”。单击“执行”按钮，显示“命令已成功完成”，存储过程创建成功。展开“可编程性”，显示有 dbo.proc_studscore。然后，输入代码“exec proc_studscore”，并选中这一行代码，单击“执行”按钮，存储过程成功调用，显示结果。运行结果如图 8-12 所示。第 8 章 存储过程、触发器和游标图 8-12定义含游标的存储过程并调用
