SQL_server基础教程(第二版) 教学课件 作者 董翔英第9章.ppt

《SQL_server基础教程(第二版) 教学课件 作者 董翔英第9章.ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、第九章存储过程与触发器9.1存储过程.9.2触发器9.3常用的系统存储过程将T-SQL查询转化为存储过程是提高SQLServer2008服务器性能的最佳方法之一，因为存储过程是在服务器端运行，所以执行速度快，而且存储过程方便用户查询，提高数据使用效率。9.1.1存储过程的作用9.1存储过程当一个存储过程创建时即被翻译成可执行的系统代码保存在系统表内，当作是数据库的对象之一，一般用户只要执行存储过程，并提供存储过程所需的参数，就可以得到需要的查询结果，而不用接触到具体的SQL命令。存储过程的作用有:（１）执行速度快。存储过程第一次执行时进行编译并驻留在高速缓存中，以后再执行

2、时，只需从高速缓存中调用已编译好的二进制代码，对于经常被执行且功能固定的查询需求，存储过程将大大节省SQLServer2008执行时间。（２）减少网络流量。存储过程存储在服务器上并在服务器上执行，网络上只传送存储过程执行的最终数据，大大减少了网络流量。（３）作为一种安全机制。通过设置用户只可能使用存储过程访问数据，限制用户不能直接操作数据库中的敏感数据，以保障数据的安全性。（４）屏蔽T-SQL命令，提供交互查询的客户接口，增加数据库应用的方便性。1.内建存储过程内建存储过程由系统提供，在安装SQLServer2008后自动装入，定义在系统数据master中，其存储过程名前

3、缀是sp_。9.1.2存储过程的类型SQLServer2008为了管理上的方便，提供了大量内建存储过程让系统管理员直接使用，以便获得一些系统信息，或直接得到某些特定的功能，如sp_help存储过程可以检索系统表的信息，sp_helpdb存储过程可以显示数据库对象信息，sp_adduser存储过程可以显示新建用户信息。内建存储过程又分目录存储过程、系统存储过程、复制存储过程、事务存储过程和扩充存储过程。（１）目录存储过程：用来显示数据库对象的信息，如数据库内建哪些表、表内哪些列。常用的有sp_helpdb、sp_help、sp_helptext等存储过程。（２）系统存储过程

4、：用来处理SQLServer2008的系统管理工作，如新建用户、设置选项、设置密码等。常用的有sp_adduser、sp_dboption、sp_password等存储过程。（３）复制存储过程：用来处理有关数据库复制方面的事务。（４）事务存储过程：用来进行创建系统工作，如安排数据库复制、安排工作执行的时间或系统警告信息等.（５）扩充存储过程：用于加载和执行DLL动态链接库，扩充存储过程名称通常以“xp_”为前缀。2.本地存储过程本地存储过程是在用户数据库中创建的存储过程，用以完成特定的数据库操作任务，也称为用户存储过程。通常数据库所有者和ddl_admin角色成员可以创建

5、本地存储过程，数据库所有者可以授权其他用户创建本地存储过程。本地存储过程通常只能应用于创建它的数据库，并且存储过程名不能使用sp_前缀，以免系统误解。（１）永久存储过程：在当前数据库中创建，存储过程名存储于当前数据库的系统表sysobject中，文本保存在数据库的系统注释表中。本地存储过程可分为永久性存储过程或临时存储过程。（２）临时存储过程：如果在存储过程名称之前加上“#”或“##”前缀，代表此存储过程是一临时保存的存储过程，这些存储过程创建在tempdb数据库中，与所有临时对象一样在SQLServer2008结束运行时会被删除。临时存储过程有两种，“#”表示局部临时存

6、储程，这种存储过程只能由创建它的用户使用，其它用户无法使用，该存储过程的拥有者也无法将其执行权限下放给其他用户，当拥有者和SQLServer2008结束连接时，该存储过程即自动被删除。“##”表示全局临时存储过程，这种存储过程可以为其他所有用户使用，拥有者不能限定别人使用它，当最后一个用户结束连接时，该存储过程即自动被删除。9.1.3创建存储过程1.命令方式创建存储过程使用CREATEPROCEDURE语句创建存储过程，其语法格式为：CREATEPROCEDUREprocedure_name[;number]/*定义过程名*/[{@parameter_namedata_t

7、ype}[VARYING][=default][OUTPUT]][,…n]/*参数声明*/[WITH{RECOMPILE

8、ENCRYPTION

9、RECOMPILE,ENCRYPTION}]/*定义存储过程的处理方式*/[FORREPLICATION]ASsql_statement/*执行的操作*/参数说明如下：1）procedure_name为存储过程名，用户存储过程名不能以sp_开头，临时过程名前加“#”或“##”。2）number为一组存储过程中的成员编号，该组所有成员拥有共同的存储过程名，可以用一条DROPPROCEDU