数据库系统与应用-(7)课件.ppt
- 【下载声明】
1. 本站全部试题类文档,若标题没写含答案,则无答案;标题注明含答案的文档,主观题也可能无答案。请谨慎下单,一旦售出,不予退换。
2. 本站全部PPT文档均不含视频和音频,PPT中出现的音频或视频标识(或文字)仅表示流程,实际无音频或视频文件。请谨慎下单,一旦售出,不予退换。
3. 本页资料《数据库系统与应用-(7)课件.ppt》由用户(三亚风情)主动上传,其收益全归该用户。163文库仅提供信息存储空间,仅对该用户上传内容的表现方式做保护处理,对上传内容本身不做任何修改或编辑。 若此文所含内容侵犯了您的版权或隐私,请立即通知163文库(点击联系客服),我们立即给予删除!
4. 请根据预览情况,自愿下载本文。本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
5. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007及以上版本和PDF阅读器,压缩文件请下载最新的WinRAR软件解压。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 应用 课件
- 资源描述:
-
1、第7章存储过程、触发器及用户自定义函数第第7章存储过程、触发器及章存储过程、触发器及 用户自定义函数用户自定义函数7.1 存储过程存储过程7.2 触发器的概念触发器的概念 7.3 用户自定义函数用户自定义函数 习题习题7第7章存储过程、触发器及用户自定义函数7.1存存 储储 过过 程程7.1.1存储过程概述存储过程概述1.存储过程的定义存储过程的定义SQL Server的存储过程类似于其他编程语言中的函数,是在数据库服务器上创建、运行的程序和过程。这些过程可由应用程序、数据完整性规则或触发器调用,是独立于表之外的数据库对象。存储过程包含一组经常执行的、逻辑完整的SQL语句,它们被保存在数据库中
2、,并由SQL Server服务器通过过程名来调用。第7章存储过程、触发器及用户自定义函数存储过程在首次运行时被编译和优化,相关信息就保存在高速缓存中,下次调用时可以直接执行,从而加快了运行速度。存储过程可用来进行查询操作,类似于SELECT语句,用于检索数据;还可以对数据进行操作,类似于INSERT、UPDATA和DELETE语句,用于插入、更新和删除数据。存储过程可带有输入和输出参数,应用起来比较灵活。第7章存储过程、触发器及用户自定义函数2.存储过程的类型存储过程的类型SQL Serve中存储过程分为五类:系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。1)系统存储过
3、程SQL Server中的许多管理活动是通过一种被称为系统存储过程的特殊过程执行的。系统存储过程用于管理SQL Server和显示有关数据库和用户的信息。系统存储过程在master数据库中创建并存储,带有sp_前缀。建议用户不要创建以sp_为前缀的存储过程。第7章存储过程、触发器及用户自定义函数2)本地存储过程本地存储过程是用户在用户数据库中编写的存储过程。3)临时存储过程SQL Server支持两种临时存储过程:本地临时存储过程和全局临时存储过程。其中,在过程名的前面带有#符号的表示是本地临时存储过程;而带有#符号的表示是全局临时存储过程。与临时表类似,SQL Server 关闭后,这些临时
4、存储过程将不存在。4)远程存储过程远程存储过程是SQL Server的早期功能。仅限于在远程SQL Server上执行远程存储过程。第7章存储过程、触发器及用户自定义函数5)扩展存储过程扩展存储过程是SQL Server可以动态装载并执行的动态链接库(DLL),以前缀“xp_”标识。3.存储过程的优点存储过程的优点存储过程相对于本地SQL语句,具有执行速度快、安全性高、网络流量小、可编程性强等优点。第7章存储过程、触发器及用户自定义函数1)存储过程具有较高的执行性能存储过程存储在数据库中只需创建一次,以后可在程序中调用该存储过程任意次。存储过程是预编译的,首次运行时,查询优化器会对其进行分析、
5、优化,并给出存在系统表中的执行计划,以后执行时无需再次进行预编译,从而提高了执行性能。第7章存储过程、触发器及用户自定义函数2)存储过程能加强数据库的安全性在SQL Server中,可以只允许用户通过存储过程来查询、插入、更新和删除数据库中的表及数据,而不可以直接对表中数据进行操作,从而保障了数据库中数据的安全性。给不同的存储过程强制不同的安全性,以实现基于函数的表的访问。第7章存储过程、触发器及用户自定义函数3)存储过程能够减少网络流量存储过程存放在服务器上,并在服务器上运行,一条执行过程代码的单独语句就可实现调用,在客户端和服务器间传送的只是该调用语句,而不需要在网络中发送所有相关的源代码
6、和中间过程结果,所以其执行时要比直接使用SQL语句快,减少了网络负载。第7章存储过程、触发器及用户自定义函数4)存储过程具有可编程性在SQL Server中,用户可用流程控制语句编写存储过程。在高级语言,如在Visual C+中可同样创建、修改和执行存储过程,这样就在客户端软件和SQL Server间提供了最佳解决方案。第7章存储过程、触发器及用户自定义函数4.系统存储过程和扩展存储过程系统存储过程和扩展存储过程1)系统存储过程SQL Server的系统表中存放了大量关于数据库对象的信息,大部分是数字数据,难以阅读和修改。SQL Server提供了许多系统存储过程,使用户很容易从系统表中查询修
7、改数据。系统存储过程以“sp_”开头,在master数据库中创建,拥有者是系统管理员。运行系统存储过程时,数据库服务器首先在当前数据库中查找,若未找到,再到master数据库中查找。SQL Server提供了九大类约七百多个系统存储过程。下面是几个常用的系统存储过程:第7章存储过程、触发器及用户自定义函数sp_databases:列出数据库服务器上所有可用的数据库,用于检索数据库名。sp_tables:列出当前数据库中可用的表。sp_indexes:返回指定表的索引信息。sp_stored_procedures:列出当前数据库中可用的存储过程。sp_help:用于检索数据库中对象(表、视图、存
8、储过程等)的信息,包括生成日期、列名、外部关键字等。sp_helptext:用于从系统表syscomments中检索生成的数据库对象(表、视图、存储过程等)的SQL命令。第7章存储过程、触发器及用户自定义函数sp_depends:用于显示和存储过程相关的数据库对象。例例7-1 用sp_addtype 建立用户自定义数据类型。EXEC sp_addtype text_type,datetime2)扩展存储过程扩展存储过程用于扩展SQL Server的功能,使用户可以使用其他编程语言中的命令。扩展存储过程以“xp_”开头,是存放在动态链接库(DLL)中的C+代码。下面介绍几个扩展存储过程。第7章存
9、储过程、触发器及用户自定义函数xp_cmdshell:从命令行提示符下执行DOS命令。xp_sscanf:将数据从字符串读入每个格式参数所给定的变量位置。xp_logininfo:报告账户、账户类型、账户的特权级别、账户的映射登录名和账户访问SQL Server的权限路径。例例7-2 使用扩展存储过程xp_cmdshell从命令行提示符下执行DOS命令,列出当前目录下所有文件。执行结果如图7-1所示。EXEC master.xp_cmdshell dir *.*第7章存储过程、触发器及用户自定义函数图7-1 执行扩展存储的过程第7章存储过程、触发器及用户自定义函数7.1.2创建简单存储过程创建
10、简单存储过程1.创建简单存储过程创建简单存储过程在SQL Server中,可以使用三种方法创建存储过程:使用创建存储过程向导、SQL Server企业管理器和T-SQL语句中的CREATE PROCEDURE命令。1)使用创建存储过程向导创建存储过程 在企业管理器中,选择“工具”“向导”“创建存储过程向导”根据提示可完成存储过程的创建。如图7-2所示。第7章存储过程、触发器及用户自定义函数图7-2 使用向导创建存储过程第7章存储过程、触发器及用户自定义函数2)使用SQL Server 企业管理器创建存储过程 在SQL Server企业管理器中,选择指定的服务器和数据库,用右键单击要创建存储过程
11、的数据库,在弹出的快捷菜单中选择“新建”选项,再选择下一级菜单中的“存储过程”选项;或者用右键单击存储过程图标,从弹出的快捷菜单中选择“新建存储过程”选项。均会弹出创建存储过程对话框。在文本框中可以输入创建存储过程的T_SQL语句。如图7-3所示。第7章存储过程、触发器及用户自定义函数图7-3 通过企业管理器创建存储过程第7章存储过程、触发器及用户自定义函数3)使用CREATE PROCEDURE命令创建存储过程使用CREATE PROCEDORE命令创建存储过程的语法形式如下:CREATE PROCEDURE procedure_name;number parameter data_type
12、 VARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FORREPLICATION AS sql_statement .n 第7章存储过程、触发器及用户自定义函数其中,procedure_name:用于指定要创建的存储过程的名称。过程名必须符合标识符规则,且对于数据库及其所有者必须唯一。要创建局部临时存储过程,可以在 procedure_name 前面加一个编号符(#procedure_name),要创建全局临时存储过程,可以在 procedure_name 前面加两个编号符(#procedure_na
13、me)。第7章存储过程、触发器及用户自定义函数number:该参数是可选的整数,它用来对同名的存储过程分组,以便用一条 DROP PROCEDURE 语句即可将同组的过程一起删除。parameter:存储过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值)。存储过程最多可以有 2100 个参数。使用符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身;相同的参数名称可以用在其他过程中。默认情况下,参数只能代替常量,而不能用于代替表名、列名或其他数据库对象
14、的名称。第7章存储过程、触发器及用户自定义函数data_type:用于指定参数的数据类型。所有数据类型(包括 text、ntext 和 image)均可以用作存储过程的参数。不过,cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor,也必须同时指定 VARYING 和 OUTPUT 关键字。VARYING:用于指定作为输出OUTPUT参数支持的结果集,仅适用于游标参数。default:用于指定参数的默认值。如果定义了默认值,不必指定该参数的值即可执行该存储过程。默认值必须是常量或 NULL。如果存储过程将对该参数使用 LIKE 关键字,那么默认值中可以包含通配
15、符(%、_、和)。第7章存储过程、触发器及用户自定义函数OUTPUT:表明该参数是一个返回参数。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。RECOMPILE:表明 SQL Server 不会保存该存储过程的执行计划,每次执行时需重新翻译。ENCRYPTION:表示 SQL Server 加密了 syscomments 表中text字段保存的是CREATE PROCEDURE 语句的文本内容。sql_statement:是存储过程中要包含的SQL 语句。第7章存储过程、触发器及用户自定义函数创建了存储过程后可以执行该存储
16、过程命令如下:EXEC procedure_name下面举例说明存储过程的建立过程。例例7-3 在pubs库中创建存储过程my_proc1,使其返回国家为USA的记录。CREATE PROC my_proc1ASSELECT *from publishersWHERE country=USA在查询分析器中运行存储过程 EXEC my_proc1,结果如图7-4所示。第7章存储过程、触发器及用户自定义函数图7-4 执行存储过程示意图第7章存储过程、触发器及用户自定义函数例例7-4在pubs库中创建存储过程,从四个表的连接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数
17、。具体命令如下:USE pubsGOCREATE PROCEDURE au_info_allASSELECT au_lname,au_fname,title,pub_name第7章存储过程、触发器及用户自定义函数FROM authors a INNER JOIN titleauthor taON a.au_id=ta.au_id INNER JOIN titles tON t.title_id=ta.title_id INNER JOIN publishers pON t.pub_id=p.pub_idGO在查询分析器中运行存储过程au_info_all,其结果如图7-5所示。第7章存储过程、
18、触发器及用户自定义函数图7-5 执行存储过程示意图第7章存储过程、触发器及用户自定义函数创建存储过程前,应该考虑下列几个事项:(1)不能将 CREATE PROCEDURE语句与其他SQL语句组合到单个批处理中。(2)创建存储过程的权限默认属于数据库所有者,该所有者可将此权限授予其他用户。(3)存储过程是数据库对象,其名称必须遵守标识符规则。(4)只能在当前数据库中创建存储过程。(5)一个存储过程的最大长度为128 MB,存储过程可以嵌套,最多嵌套32层。第7章存储过程、触发器及用户自定义函数2.浏览存储过程的信息浏览存储过程的信息存储过程被创建之后,它的名字就被存储在系统表sysobject
19、s中,它的源代码存放在系统表syscomments中。可以使用企业管理器或系统存储过程来查看用户创建的存储过程。第7章存储过程、触发器及用户自定义函数1)使用企业管理器查看用户创建的存储过程 在企业管理器中,打开指定的服务器和数据库项,选择要查看存储过程的数据库,单击存储过程文件夹,此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程,从弹出的快捷菜单中选择属性选项,或双击该存储过程,此时便可以看到存储过程的源代码。如图7-6所示。第7章存储过程、触发器及用户自定义函数图7-6 查看存储过程示意图第7章存储过程、触发器及用户自定义函数2)使用系统存储过程来查看用户创建的存储
20、过程 也可以使用SQL Server提供的系统存储过程来查看用户存储过程,其语法形式如下:sp_help:用于显示存储过程的参数及其数据类型。sp_help objname=name其中,参数name为要查看的存储过程的名称。sp_helptext:用于显示存储过程的源代码。sp_helptext objname=name其中,参数name为要查看的存储过程的名称。第7章存储过程、触发器及用户自定义函数sp_depends:用于显示和存储过程相关的数据库对象。sp_depends objname=object其中,参数object为要查看依赖关系的存储过程的名称。sp_stored_proce
21、dures:用于返回当前数据库中的存储过程列表。例例7-5 查看pubs 库中所有存储过程。在查询分析器中输入如下命令:EXEC sp_stored_procedures其运行结果如图7-7所示。第7章存储过程、触发器及用户自定义函数图7-7 查看所有存储过程示意图第7章存储过程、触发器及用户自定义函数例例7-6 查看存储过程my_proc1的相关代码信息。在查询分析器中输入如下命令:EXEC sp_helptext my_proc1运行结果如图7-8所示。第7章存储过程、触发器及用户自定义函数图7-8 查看存储过程相关代码示意图第7章存储过程、触发器及用户自定义函数7.1.3创建和执行含参数
22、存储过程创建和执行含参数存储过程存储过程能够带有一些参数,这不仅增加了灵活性,还扩展了存储过程的功能。存储过程的参数分输入参数和输出参数,可以是任意数据类型。1.创建含参数的存储过程创建含参数的存储过程输入参数是向存储过程传递的参数,在创建存储过程时定义,在执行存储过程时需要给出相应的值。输出参数从存储过程中返回的一个或多个值。参数是一个内存局部变量,存放在内存中。第7章存储过程、触发器及用户自定义函数例例7-7创建带简单输入参数的存储过程,返回pubs数据库中authors表指定作者的信息。在查询分析器中输入如下命令:CREATE PROC my_proc2lastname varchar(
23、40),firstname varchar(20)ASSELCEC *from authorsWHERE au_fname=firstname and au_lname=lastname 第7章存储过程、触发器及用户自定义函数例例7-8创建带有通配符参数的简单存储过程,下面的存储过程从四个表的连接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配,如果没有提供参数,则使用预设的默认值。在查询分析器中输入如下命令:USE pubsGOCREATE PROCEDURE au_info2 lastname varchar(30)=D%,firstname va
24、rchar(18)=%第7章存储过程、触发器及用户自定义函数AS SELECT au_lname,au_fname,title,pub_nameFROM authors a INNER JOIN titleauthor ta ON a.au_id=ta.au_id INNER JOIN titles t ON t.title_id=ta.title_id INNER JOIN publishers p ON t.pub_id=p.pub_idWHERE au_fname LIKE firstname AND au_lname LIKE lastnameGO第7章存储过程、触发器及用户自定义函数
25、例例7-9 创建带有输出参数的存储过程,在pubs数据库中的表titles中创建存储过程my_proc3,返回指定书名的书价。在查询分析器中输入如下命令:CREATE PROC my_proc3 title varchar(40)=%,price money output第7章存储过程、触发器及用户自定义函数AS SELECT titleFROM titlesWHERE title=titleSELECT price=priceFROM titlesWHERE title=title2.执行含参数存储过程执行含参数存储过程执行带有输入参数的存储过程时,可以用参数名传送参数值,也可按参数位置传送
展开阅读全文