视图、存储过程、函数、游标与触发器

《视图、存储过程、函数、游标与触发器》由会员分享，可在线阅读，更多相关《视图、存储过程、函数、游标与触发器（67页珍藏版）》请在装配图网上搜索。

1、视图、存储过程、函数、游标与触发器 学习目标 掌握使用视图 掌握使用存储过程 掌握使用自定义标量函数、表值函数 掌握使用游标 掌握使用触发器 一、视图的定义 视图由SELECT查询语句所定义的一个虚拟表，是查看数据的一种非常有效的方式，同真实的数据表一样，视图也包含一系列带有名称的数据列和数据行，但视图与数据表又有很大的不同，视图中的数据并不真实的存在于数据库中。 1.1、视图的优点 简化查询 提供一种安全机制 视图掩码（对长的字段重新命名） 数据即时更新 1.2、视图的分类 标准视图：使用最频繁的视图，不存储任何数据，不占用任何存储空间 索引视图：拥有唯一群集索引的视图被称为索引视图，它存储

2、真实索引数据，占用一定的存储空间。 分区视图：现在用分区表进行替代 1.2.1、标准视图 -创建带有部门编号的emp视图 SELECT dbo.EMP.EMPNO, dbo.EMP.ENAME, dbo.EMP.JOB, dbo.EMP.MGR, dbo.EMP.HIREDATE, dbo.EMP.SAL, dbo.EMP.COMM, dbo.EMP.DEPTNO, dbo.DEPT.DNAME FROM dbo.EMP INNER JOIN dbo.DEPT ON dbo.EMP.DEPTNO = dbo.DEPT.DEPTNO -通过视图修改数据 update v_emp_with_dep

3、tname set sal=800 where empno=7369 注意: (1)可以修改基于两个或两个以上基表的视图，但是每次修改只能影响一个基表，不能同时修改。 (2)不能修改通过计算得到的列、有内置函数的列以及有聚合函数的列 1.2.2、索引视图 -创建各部门人数的视图 drop view v_countOfDept go create view v_countOfDept WITH SCHEMABINDING as SELECT EMP.deptno,count_big(*) empcount FROM dbo.EMP group by emp.deptno -创建聚合索引 CREA

4、TE UNIQUE CLUSTERED INDEX i_v_countOfDept_deptno ON v_countOfDept(deptno) 注意： （1）创建索引视图，必须拥有唯一聚合索引，如果创建聚合索引，带有聚合函数的基础视图必须使用WITH SCHEMABINDING ，group by以及count_big函数 （2）使用索引视图能提高数据库效率 （3）如果视图引用任何非确定性函数，则不能在视图上创建聚集索引 1.2.3、分区视图 -创建分区视图 -将表进行行分割，emp表分解为emp1和emp2 select * into emp1 from emp where empno=

5、7782 go CREATE VIEW v_emp_with_1_and_2 AS SELECT * FROM emp1 UNION ALL SELECT * FROM emp2 注意：分区视图在sqlserver2005中被分区表替代 1.3、在Management Studio中创建视图 二、存储过程 存储过程是数据库系统中封装的代码模块，它采用T-SQL语言来编写，经编译后存放在数据库服务器中，具有很好的可重用性，可用于高效地完成某些操作 存储过程可以充分利用服务器的高性能运算能力，无需把大量的结果集送往客户端进行处理，大大减少了网络数据传输的开销，提高了应用程序访问数据库的速度和效率

6、2.1、使用存储过程的存储过程的好处与特点 存储过程是已编译过的，并在服务器上注册和保存的代码模块，因而比一般的程序语句执行起来速度更快，同时减少了网络流量，节省了大量时间和数据流量。 存储过程可以使用控制语句和变量，并且在一个存储过程中可以调用其他存储过程，使得用户可以进行模块化程序设计，大大提高了用户设计程序的效率。 存储过程具有安全特性和所有权链接，可以执行所有的权限管理，用户可以被授予执行存储过程的权限，但不拥有直接对存储过程所引用对象的权限。 存储过程可以提高应用程序的安全性，防止SQL嵌入式攻击 存储过程可以允许代码绑定，引用当前不存在的对象，这些对象仅在存储过程执行时存在 2.2

7、、存储过程的分类 用户自定义的存储过程：最主要的存储过程 系统存储过程：sp_前缀，系统预定义 扩展存储过程：保存在DLL动态链接库中并从动态链接库中执行的C+程序代码，用于扩展SQLSERVER2005性能，以字符xp_开头，通常与其它系统存储过程一起使用通过程序集调用。 2.3、存储过程的设计规则 CREATE PROCEDURE 定义本身可包括除下列 CREATE 语句以外的任何数量和类型的 SQL 语句，存储过程中的任意地方都不能使用下列语句： CREATE RULE CREATE DEFAULT CREATE FUNCTION CREATE TRIGGER CREATE PROCED

8、URE CREATE VIEW USE DATABASE 2.4、创建存储过程的语法 CREATE PROC | PROCEDURE schema_name. procedure_name parameter type_schema_name. data_type OUTPUT ,.n AS 2.5 创建和使用存储过程 -创建不带参数的存储过程 if exists(select name from sysobjects where name=pro_name and type=p) drop procedure pro_name Go create procedure pro_name as

9、declare v_name varchar(10),v_sal decimal(10,2) begin begin try select v_name=ename,v_sal=sal from emp where empno=7369 if v_sal2500 print 工资超过2500 else print 工资少于2500 end try begin catch print 错误号:+cast(error as varchar(10) print 错误内容:+error_message() end catch end -使用存储过程 exec pro_name 注意：Sysobject

10、s: 系统中的所有对象，包括：表、存储过程、触发器等等 Type =p 类型为存储过程，其他如：s系统对象，u用户表，v变量,tr触发器等等 -创建带输入参数的存储过程 if exists(select name from sysobjects where name=pro_name and type=p) drop procedure pro_name Go create procedure pro_name vempno int as declare v_name varchar(10),v_sal decimal(10,2) begin begin try select v_name=e

11、name,v_sal=sal from emp where empno=vempno if v_sal2500 print 工资超过2500 else print 工资少于2500 end try begin catch print 错误号:+cast(error as varchar(10) print 错误内容:+error_message() end catch end -使用存储过程 pro_name 7369 -创建带输出参数的存储过程 if exists(select name from sysobjects where name=pro_name and type=p) drop

12、 procedure pro_name Go create procedure pro_name vempno int,v_name varchar(10) output,v_sal decimal(10,2) out as begin begin try select v_name=ename,v_sal=sal from emp where empno=vempno end try begin catch print 错误号:+cast(error as varchar(10) print 错误内容:+error_message() end catch end -使用存储过程 declar

13、e v_name varchar(10) ,v_sal decimal(10,2) exec pro_name 7369 ,v_name output,v_sal output if v_sal2500 print 工资超过2500 else print 工资少于2500 go 2.6 存储过程的执行过程和重编译 存储过程在第 1 次执行时，要经过语法分析、解析、编译和执行共 4 个阶段，当添加了新的索引或更新了某些列数值之后，存储过程将不自动执行优化，可以强制在下次启动服务器前重编译该存储过程，以更新原有的执行计划。 可以有三种方式实现： -1使用sp_recompile系统存储过程 exe

14、c sp_recompile hh -2创建存储过程时增加with recompile选项 ALTER procedure dbo.hh with recompile as begin select count(*) count from aa end -3 执行重新编译 exec hh with recompile 2.7 加密存储过程 -使用with encryption语句对存储过程进行加密 ALTER procedure dbo.hh with recompile,encryption as begin select count(*) count from aa end 2.8 使用扩

15、展存储过程 使用外围配置器配置，如下图： 敲入 exec xp_cmdshell dir c:“ 结果是列出c盘的具体内容 相干的还有几个都是以xp_开头，具体可以查询msdn文档。 -比如使用xp_fileexist判断文件是否存在 use master declare ret int exec xp_fileexist e:tempweb.config,ret output print ret 三、用户自定义函数 用户自定义函数是接受参数、执行操作（例如复杂计算）并将操作结果以值的形式返回的子程序。返回值可以是单个标量值或结果集。 SQL Server 2005支持3种类型的Transac

16、t-SQL用户自定义函数：标量函数、内嵌表值函数和多语句表值函数。 在SQL Server中使用用户自定义函数有以下优点： 允许模块化程序设计。 执行速度更快。 减少网络流量。 3.1 创建用户自定义函数 （1）标量函数 标量型函数返回一个确定类型的标量值其返回值类型为除TEXT、 NTEXT、 IMAGE、CURSOR、 TIMESTAMP、table 外的其它数据类型。函数体语句定义在BEGIN-END语句内，其中包含了可以返回值的Transact-SQL 命令 创建标量函数的语法如下： CREATE FUNCTION schema_name. function_name ( parame

17、ter_name AS type_schema_name. parameter_data_type = default ,.n ) RETURNS return_data_type WITH ,.n AS BEGIN function_body RETURN scalar_expression END 其中各参数的含义如下。 schema_name 用户自定义函数所属的架构的名称。 function_name 用户自定义函数的名称。 parameter_name 用户自定义函数的参数。 type_schema_name. parameter_data_type 参数的数据类型及其所属的架构，前

18、者为可选项。 = default 参数的默认值。 return_data_type 用户自定义标量函数的返回值。 function_body 函数体，指定一系列定义函数值的Transact-SQL语句。 scalar_expression 指定标量函数返回的标量值。 指定函数将具有以下一个或多个选项,共有两个： ENCRYPTION 指示数据库引擎对包含CREATE FUNCTION语句文本的目录视图列进行加密。 SCHEMABINDING 指定将函数绑定到其引用的数据库对象。 创建和使用标量函数 -创建函数 if exists(select name from sysobjects wher

19、e name=get_salary_by_deptno) drop function get_salary_by_deptno go create function get_salary_by_deptno (v_dept_no int) Returns int as begin declare v_sum int select v_sum=sum(sal) from emp where deptno = v_dept_no if error0 begin return -1 -其中返回-1表示函数出错了 end return v_sum end -使用函数 print dbo.get_sal

20、ary_by_deptno(10) （2）内嵌表值函数 内联表值型函数以表的形式返回一个返回值，即它返回的是一个表内联表值型函数没有由BEGIN-END 语句括起来的函数体。其返回的表由一个位于RETURN 子句中的SELECT 命令段从数据库中筛选出来。内联表值型函数功能相当于一个参数化的视图。 创建内嵌表值函数的语法如下： CREATE FUNCTION schema_name. function_name ( parameter_name AS type_schema_name. parameter_data_type = default ,.n ) RETURNS TABLE WITH

21、 ,.n AS RETURN ( select_stmt ) 其中各参数的含义如下。 schema_name、function_name、parameter_name、 type_schema_name. parameter_data_type、 = default 、的含义与标量函数中的各参数相同。 TABLE 指定表值函数的返回值为表。 select_stmt 定义内嵌表值函数的返回值的单个SELECT语句。 使用内嵌表值函数： drop function f_get_emp go CREATE FUNCTION f_get_emp() RETURNS table as RETURN (

22、select * from emp ) 注意：返回 table 数据类型的用户定义函数实际上是视图 ，可以对返回的数据直接更新，并影响基表。 （3）多语句表值函数 多声明表值型函数可以看作标量型和内联表值型函数的结合体。它的返回值是一个表，但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值型函数的不足。 创建多语句表值函数的语法如下： CREATE FUNCTION schema_name. function_name ( parameter_name AS ty

23、pe_schema_name. parameter_data_type = default ,.n ) RETURNS return_variable TABLE WITH ,.n AS BEGIN function_body RETURN END 其中各参数的含义如下。 schema_name、function_name、parameter_name、 type_schema_name. parameter_data_type、 = default 、的含义与标量函数中的各参数相同。 TABLE 指定表值函数的返回值为表。在多语句表值函数中，return_variable是TABLE变量，用

24、于存储和汇总应作为函数值返回的行。 定义表数据类型。表声明包含列定义和列约束（或表约束）。 function_body 函数体，指定一系列定义函数值的Transact-SQL语句。 使用多语句表值函数： drop function f_get_emp go CREATE FUNCTION f_get_emp() RETURNS abc table(empno int) as begin insert into abc select empno from emp return end select * from dbo.f_get_emp() 3.2 修改删除用户自定义函数 使用ALTER FU

25、NCTION语句可以修改用户自定义函数的定义，ALTER FUNCTION语句的语法及参数与CREATE FUNCTION的语法及参数类似。 使用DROP FUNCTION语句可以从当前数据库中删除一个或多个用户自定义函数。 3.3 自定义函数与存储过程的区别 存储过程与函数主要区别于返回值方面： （1）存储过程可以使用output参数返回多个值，而函数只可以返回一个值，不可以使用output参数 （2）关于返回结果集方面的区别： 存储过程中虽然没有return语句，但是也可以返回结果集。 比如：create procedure sp1 as begin select name, fid_fk

26、 from table_1 print 111 select testname ,fid from table_2 end 这个结果，可以被被使用：insert into table_3(name, fid_fk) exec sp1 对于自定义函数，它必须指定定义为返回值为table类型的数据v，并且在代码中显式的向该table v中insert；或者，只是制定返回值为table类型，不指定return 的对象变量，直接return 该表。即： create function fn1() returns table as return select fid, testname from tab

27、le_2 或者 create function fn1() returns v table (fid int primary key not null, testname nchar(10) as begin insert into v select fid,testname from table_2 End -使用 insert into table3 select * from fn1() 它们的区别： （1）前者是返回查询的结果集合，只是作为一系列功能的副产品；而后者，就是为了返回值而创建的。 （2）前者返回值不能被直接引用；而是必须被使用为exec sp1或者 insert into

28、table 的形式使用。后者返回值可以被直接引用。 四、游标 在数据库中，游标是一个十分重要的概念。 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。 当决定对结果集进行处理时，必须声明一个指向该结果集的游标。 4.1、游标的分类 MS SQL SERVER 支持三种类型的游标：Transact_SQL 游标，API 服务器游标和客户游标。 其中Transact_SQL 游标就是我们同样使用的游标 Transact_SQL 游标是由DECLARE CURSOR 语法定义、主要用在Transact_SQL 脚本、存储过程和触发器中。 4.2、 Transact_SQL 游标

29、 每一个游标必须有四个组成部分这四个关键部分必须符合下面的顺序： 1.DECLARE 游标 2.OPEN 游标 3.从一个游标中FETCH 信息 4.CLOSE 或DEALLOCATE 游标 通常我们使用DECLARE 来声明一个游标声明一个游标主要包括以下主要内容： 游标名字 数据来源（表和列） 选取条件 属性（仅读或可修改） 其语法格式如下： DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n 其中： cursor_name

30、指游标的名字。 INSENSITIVE 表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。 SCROLL 表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。由此可见，SCROLL 极大地增加了提取数据的

31、灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再重开游标。 select_statement 是定义结果集的SELECT 语句。 READ ONLY 表明不允许游标内的数据被更新尽管在缺省状态下游标是允许更新的。 UPDATE OF column_name,n 定义在游标中可被修改的列，如果不指出要更新的列，那么所有的列都将被更新。 -下面是一个游标的例子 -1 DECLARE tempcursor CURSOR FOR select sal,comm from emp where sal1000 declare sal decimal(10,2),comm int begin -2

32、 open tempcursor; -3 fetch next from tempcursor into sal,comm print 工资为:+cast(sal as varchar(10) print 补助为:+cast(comm as varchar(10) -4 close tempcursor DEALLOCATE tempcursor End -在这里例子中明显有一处缺陷，就是只能取到一条记录 -下面增加循环和判断 DECLARE tempcursor CURSOR FOR select sal,comm from emp where sal1000 declare sal dec

33、imal(10,2),comm int begin open tempcursor; fetch next from tempcursor into sal,comm while fetch_status=0 begin print 工资为:+cast(sal as varchar(10) print 补助为:+cast(comm as varchar(10) fetch next from tempcursor into sal,comm end close tempcursor DEALLOCATE tempcursor End -注意： fetch_status是一个游标专用的系统函数，

34、用于表示是否取到值 五、触发器 Microsoft SQL Server 2005系统提供了两种强制业务逻辑和数据完整性的机制，即约束技术和触发器技术。 5.1、触发器的作用 触发器是一种特殊的存储过程，它在特定语言事件发生时自动执行，通常用于实现强制业务规则和数据完整性。 触发器支持嵌套，最多可以嵌套32层 5.2、 触发器的分类 DML触发器： DML触发器是当数据库服务器中发生数据操作语言（DML）事件时要执行的操作。DML事件包括对表或视图发出的UPDATE、INSERT或DELETE语句 AFTER触发器：指SQL语句成功执行之后触发 INSTEAD OF触发器。 DDL触发器 5.

35、2.1、 DML AFTER触发器 DML AFTER触发器是当数据库服务器中发生数据操作语言（DML）事件时要执行的操作，成功执行之后触发。 特别注意：SQLServer触发器中可以拥有事务回滚功能，当有错误发生时，执行rollback，这样导致DML失效。 5.2.1.1、DML AFTER触发器的语法 创建DML触发器的语法格式为： CREATE TRIGGER schema_name . trigger_name ON table FOR | AFTER INSERT , UPDATE , DELETE AS sql_statement 其中各参数的含义如下。 （1）schema_na

36、me DML触发器所属架构的名称。 （2）trigger_name 触发器的名称。 （3）table 对其执行DML触发器的表。 （4）AFTER 指定DML触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被激发。 如果写FOR则默认为AFTER （5） INSERT , UPDATE , DELETE 指定激活触发器的数据修改语句。必须至少指定一个选项。在触发器定义中允许使用上述选项的任意顺序组合。 （6）sql_statement 指定触发器所执行的T-SQL语句。 5.2.1.2 使触发器无效或重新有效 1使DML触发器无效 在有些情况下，用户希望暂停触发器的作用，但并不删除它，

37、这时就可以通过DISABLE TRIGGER语句使触发器无效，语法格式如下： DISABLE TRIGGER schema . trigger_name ,.n | ALL ON table 2使DML触发器重新有效 要使DML触发器重新有效，可使用ENABLE TRIGGER语句，语法格式如下： ENABLE TRIGGER schema_name . trigger_name ,.n | ALL ON table 5.2.1.3 删除DML触发器 当不再需要某个触发器时，可以将其删除。 删除了触发器后，它所基于的表和数据不会受到影响。删除表将自动删除其上的所有触发器。 删除触发器的语法格式

38、如下： DROP TRIGGER 触发器名 也可以使用SQL Server Management Studio删除触发器。 5.2.1.4、触发器应用 drop trigger my_triger Go -建立一个触发器，只有在上班时间才可以修改emp表 create trigger my_triger on emp for insert,update,delete as begin -注意：星期天是，星期六是 if (datepart(weekday,getdate() in (7,1) or (datepart(Hour,getdate() not between 8 and 18) be

39、gin raiserror(不是上班时间,不能修改emp表,16,1) rollback end End 注意：（1）当用户操作违背触发器时，可以使用T-SQL语言(如：print)或raiseerror命令来向用户显示出错信息。 （2） raiseerror命令可以通过error捕捉到出错信息。 5.2.1.5、 Inserted表和Deleted表 SQL Server 2005为每个DML触发器都创建了两个专用临时表：Inserted表和Deleted表。这两个表的结构总是与被该触发器作用的表的结构相同，触发器执行完成后，与该触发器相关的这两个表也会被删除。 Inserted表存放由于执

40、行INSERT或UPDATE语句而要向表中插入的所有行。 Deleted表存放由于执行DELETE或UPDATE语句而要从表中删除的所有行。 5.2.1.6、一个类似于行级触发器的应用 SQLServer2005没有行级触发器，因为微软认为没有这个必要，完全可以通过deleted表和inserted表的配合来完成行级检测。 见备注中的代码 5.2.2、 DML INSTAND OF触发器 视图上不允许定义after触发器，只能定义instand of触发器，其语法格式如下： CREATE TRIGGER schema_name . trigger_name On view INSTAND OF

41、 INSERT , UPDATE , DELETE AS sql_statement 5.2.2.1、 DML instead of触发器的应用 create view v_emp as select * from emp go drop trigger my_triger_for_view Go -建立一个触发器，只有在上班时间才可以修改emp表 create trigger my_triger_for_view on v_emp instead of insert,update,delete as begin -注意：星期天是，星期六是 if (datepart(weekday,getda

42、te() in (7,1) or (datepart(Hour,getdate() not between 8 and 18) begin raiserror(不是上班时间,不能修改emp表,16,1) rollback end end 5.2.3、 DDL触发器 发生DDL语句的时候触发，其语法格式如下： CREATE TRIGGER trigger_name ON ALL SERVER | DATABASE FOR | AFTER event_type,. AS sql_statement 注意： 常用的事件如：CREATE_TABLE,DROP_TABLE 等 更多的内容请查阅联机帮助

43、5.2.3.1、 DDL触发器的应用 在这个例子中建立一个表，用以存放使用DDL的过程 并且我们需要使用eventdata ( )函数来进行捕捉 见备注中的例子 课程小结 本课是SQLServer后台编程的全部内容，主要包括视图、自定义存储过程、自定义函数、使用游标与触发器。 课后习题 填空题 1视图是由 SELECT 语句定义的一个虚拟表，在 SQL server2005 系统中，视图分为 、 、_3类。 2通过视图修改数据表时，数据表中不能包含 函数，且 SELECT 语句中不能 子句。 3 存储过程是 SQLServer2005 中封装的 ，包括 3 种类型，分别是 、 、 。 4 存储

44、过程有多种调用方式，其中比较常用的是使用 语句。 5 存储过程在第 1 次执行时，要经过 、 、 和 共 4 个阶段，当添加了新的索引或更新了某些列数值之后，存储过程将不自动执行优化，直到下一次 SQL server 2005 服务器重新启动后再运行该存储过程时为止，可以强制在下次启动服务器前 该存储过程，以更新原有的执行计划。 6 可以使用 语句来加密存储过程，防止未授权用户通过 SELECT 语句查看该存储过程程序代码。 7 系统存储过程常以 _ 开头，扩展存储过程则以 开头。 8 触发器是一种特殊类型的 ，但触发器与它又明显不同：触发器不是通过 EXEC 命令调用，而是在 时被激活。 9

45、 触发器分为 和 两种基本类型，其中前者是针对_事件被激活，后者是针对 事件被激活。 10当用户操作违背触发器时，可以使用 语言或 命令来向用户显示出错信息。 11当一个触发器临时不使用时，可以将其 ，当需要再次使用该触发器时，可以将其 。 12 函数可提高程序的重用性和系统的易用性， SQL Server 2005 提供了两大类的函数： 和 。 13在 SQL Server 2005 中，用户自定义函数分为两大类：标量值函数和表值函数，对于其中任何一种， 又可分为内联标量值（表值）函数或 函数 选择题选择题 1 在以下各种视图中，会占用一定存储空间的是（ ）。 A 标准视图 B。 索引视图

46、C。 分区视图 2. 通过视图修改数据表时，下列说法不正确的是（ ）。 。人在利用视图向数据表插入数据时，必须确保视图中没有定义的列允许为空值，否则系统提示出错信息 B 通过视图，用户可以更新数据表中的任何列值。 C 用户只能访问视图中定义的字段，对于视图中没有定义的数据表中的字段，用户不能通过视图访问。 D 通过视图修改数据表时，数据表中不能包含聚合函数，且 SELECT 语句中不能包含 GEOUP BY 子句。 3 关于存储过程，下列说法正确的是（ ）。 A 存储过程存放在用户的客户机上，使用时直接调用即可 B 存储过程中可以使用任意的 Transact 一 SQL 语句 C 存储过程可以

47、使用控制流语句和变量，且在一个存储过程中可以调用其他的存储过程。 D 存储过程在每次执行时，都要经过语法分析、解析、编译和执行共 4 个阶段。 4 关于触发器，下列说法错误的是（ ）。 A 触发器是一种特殊类型的存储过程。 B DDL 触发器和 DML 触发器可以通过 CREATETRIGGER 语句来创建，都是为了响应事件而被激活。 C 触发器可以同步数据库中的相关数据表，进行级联更改。 D DDL 触发器包括 INSERT 触发器 、 UPDATE 触发器、 DELETE 触发器等基本触发器。 5 可以响应 INSERT 语句的触发器是（ ）。 A INSERT 触发器 B。 DELETE

48、 触发器 C。 UPDATE 触发器 D。 DDL 触发器 简答题简答题 1 简述存储过程和触发器的基本功能和特点 2 简述存储过程和触发器的基本类型。 3 如何理解存储过程的执行原理 4 函数和存储过程有什么区别？ 上机操作题上机操作题 1.利用 Management studio 为emp表创建视图，使普通职工只能查询自己的薪水信息，而不能访问其他信息 2.从控制台打印下列信息(用签套的循环，外层循环控制行，内层循环控制*号的个数以及出现的样式) * * * * * * * 3. 用存储过程实现：输入一个大于0的整数n， 返回1到n的整数和 4. 利用游标从dept表打印出所有的部门名字

49、5创建一个能根据输入日期来确定是本年度第几周的用户自定义函数。 答案 填空题 1标准视图 、 索引视图 、_分区视图_ 2聚合 、 group by 3代码模块，用户定义存储过程、系统存储过程、扩展存储过程 4exec 5语法分析、解析、编译、执行、重编译 6with encryption 7Sp、xp 8存储过程，特定语言事件发生 9DDL、DML、DDL、DML 10T-SQL、raiseerror 11禁用、启用 12系统内置函数、用户自定义函数 13多语句表值 选择题 1b 2. b 3C 4D 5A 简答题简答题 1 简述存储过程和触发器的基本功能和特点 答案：存储过程是SQLSER

50、VER2005系统中封装的代码模块，经编译后存放在数据库服务器中，具有很好的可重用性，可用于高效地完成某些特定操作。 它的特点如下： （1）存储过程是已编译过的，并在服务器上注册和保存的代码模块，因而比一般的程序语句执行起来速度更快，同时减少了网络流量。 （2）存储过程可以使用控制流语句和变量，并且在一个存储过程中可以调用其他存储过程，使得用户可以进行模块化程序设计，大大提高了用户设计程序的效率。 （3）存储过程具有安全性和所有权链接，可以执行所有的权限管理 （4）存储过程可以提高应用程序的安全性，防止SQL嵌入式攻击 （5）存储过程可以允许代码绑定，引用当前不存在的对象，这些对象仅在存储过程

51、执行时存在 触发器时一种特殊类型的存储过程，但触发器与存储过程又明显不同：触发器不是通过EXEC命令调用，而是在某个指定事件执行时被激活。触发器通常可以执行一定的业务规则，它和SQLSERVER2005所提供的约束技术一起，保证了数据库中数据的完整性、一致性和有效性。 它的特点如下： （1）触发器时自动进行的，可以在一定条件下自动被激活 （2）触发器可以同步数据库中的相关数据表，进行级联更新 （3）触发器可以实现更复杂的安全检查 （4）触发器可以实现数据库的管理任务，例如DDL触发器，可在DDL语句后被激活，以实现统一的数据库管理策略。 2 简述存储过程和触发器的基本类型。 答案：存储过程包括

52、：用户自定义的存储过程：最主要的存储过程 系统存储过程：sp_前缀，系统预定义 扩展存储过程：保存在DLL动态链接库中并从动态链接库中执行的C+程序代码，用于扩展SQLSERVER2005性能，以字符xp_开头，通常与其它系统存储过程一起使用通过程序集调用。 触发器包括： DML触发器： DML触发器是当数据库服务器中发生数据操作语言（DML）事件时要执行的操作。DML事件包括对表或视图发出的UPDATE、INSERT或DELETE语句 AFTER触发器：指SQL语句成功执行之后触发 INSTEAD OF触发器。 DDL触发器：当系统使用DDL指令时触发。 3 如何理解存储过程的执行原理 答案

53、：存储过程在第1次执行时，需要经过语法分析、解析、编译和执行共4个阶段。 语法分析阶段：创建存储过程时，系统检查其语句的正确性与否。如果出错，系统会提示错误信息，并宣告创建失败；如果顺利通过检查，则系统自动将该存储过程保存在当前数据库的sql_modules视图中，以备调用 解析阶段：又成为延迟阶段。指查询分析器从sql_modules目录视图中，读取该存储过程的脚本 编译阶段：分析存储过程并生成存储过程执行计划的过程。执行计划时描述存储过程执行最快的方法，其生成过程取决于表的数据量、表的索引特征、WHERE 子句使用的条件以及是否使用了UNION、GROUP BY 、ORDERBY子句等。查

54、询优化器在分析完存储过程的这些因素后，将生成的执行计划置于高速缓冲存储区中，该缓冲区是一个内存区域。 执行阶段：执行驻留在高速缓冲存储区中的存储过程的执行计划。 存储过程在之后的运行中，只需要执行即可，大大的提高了效率，除非执行时增加了with recompile选项。 4 函数和存储过程有什么区别？ 答案：存储过程与函数主要区别于返回值方面： （1）存储过程可以使用output参数返回多个值，而函数只可以返回一个值，不可以使用output参数 （2）关于返回结果集方面的区别： 存储过程中虽然没有return语句，但是也可以返回结果集。 比如：create procedure sp1 as b

55、egin select name, fid_fk from table_1 print 111 select testname ,fid from table_2 end 这个结果，可以被被使用：insert into table_3(name, fid_fk) exec sp1 对于自定义函数，它必须指定定义为返回值为table类型的数据t，并且在代码中显式的向该table t中insert；或者，只是制定返回值为table类型，不指定return 的对象变量，直接return 该表。即： create function fn1() returns table as return selec

56、t fid, testname from table_2 或者 create function fn1() returns v table (fid int primary key not null, testname nchar(10) as begin insert into v select fid,testname from table_2 end 它们的区别： （1）前者是返回查询的结果集合，只是作为一系列功能的副产品；而后者，就是为了返回值而创建的。 （2）前者返回值不能被直接引用；而是必须被使用为exec sp1或者 insert into table 的形式使用。后者返回值可以被直接引用。 操作题答案： 见备注