《数据库基础与项目实训教程——基于SQLServer 教学课件 ppt 作者 虞江峰第8章》由会员分享，可在线阅读，更多相关《数据库基础与项目实训教程——基于SQLServer 教学课件 ppt 作者 虞江峰第8章（62页珍藏版）》请在金锄头文库上搜索。
1、第8章 存储过程和触发器,知识导航,学习目标,了解： 存储过程的概念及优点 触发器的概念和类型 掌握： 存储过程的创建、调用和管理方法 触发器的创建和使用方法,存储过程和触发器,8.1 存储过程概述 8.2 存储过程的操作 8.3 触发器概述 8.4 触发器的操作,8.1 存储过程概述,任务描述：存储过程是T-SQL语句和控制流语句组成的集合，是最高级别的应用程序。了解存储过程的基本概念，对于后续相关内容的学习，非常重要。 任务目标：了解存储过程的基本概念及优点。,8.1 存储过程概述,8.1.1 存储过程的定义与类型 存储过程主要分为三种类型： （1）系统存储过程（System Stored
2、 Procedure） 由数据库系统自身创建，其名称附有前缀sp_，存储在master数 据库中，用于管理SQL Server和显示数据库和用户信息 （2）扩展存储过程（Extended Stored Procedure） 属于动态链接库，其名称以xp_为前缀，存储过程只能添加到 master数据库中 （3）用户自定义存储过程 由用户根据对某一个特定功能的需要，在用户数据库中自行创 建并执行用户指定的任务。,8.1 存储过程概述,8.1.2 存储过程的优点 （1）模块化编程 存储过程的创建是为了完成一个特定的功能，由此创建的程序模块存储在数据库中，可被多个用户重复调用和共享，并可独立于应用程序
3、代码。存储过程的使用，大大增加了代码的重用性和共享性，提高了应用程序开发的质量和效率。 （2）提高执行效率，加快运行速度 存储过程在创建时已经在服务器上进行了编译并加以优化，在存储过程第一次被执行时，服务器会将其保留在高速缓存中，当以后再次调用该存储过程时，不再需要经历装载编译等过程，可以立即执行，大大提升了运行速度，改善了系统性能。 （3）降低网络流量 存储过程代码直接存储于数据库中，当对其进行调用时，只需要一条调用语句，不会产生大 量T-SQL语句的代码流量，从而降低网络流量。 （4）提供安全机制 通过向用户授予对存储过程的访问权限，允许用户单独执行存储过程，而不给予其直接访问 存储过程所
4、包含的数据库对象（表、试图）的权限，增强了安全性控制。,8.2 存储过程的操作,任务描述：在广播电视大学“RtvuStu”数据库中创建存储过程，实现对存储过程的调用和管理。 任务目标：掌握存储过程的创建方法，能够对存储过程进行管理和调用。,8.2 存储过程的操作,8.2.1 存储过程的创建 SQL Server中，可使用三种方法创建存储过程： （1）使用企业管理器、 （2）使用向导和使用T-SQL语句中的Create （3）Procedure语句。 在创建存储过程时，不可以使用Create Default、Create Rule、 Create Trigger、Create View语句。,8
5、.2 存储过程的操作,1. 使用企业管理器创建存储过程 1）在桌面单击“开始”“程序”“Microsoft SQL Server”“企业管理器”命令，打开企业管理器，选择服务器。 2）展开需要创建存储过程所在的数据库，单击“存储过程”，如图8-1所示，图右边框列出的是该数据库中的存储过程。,图8-1 企业管理器中的存储过程,8.2 存储过程的操作,使用企业管理器创建存储过程 3）右键单击“存储过程”，或在右边框中任意存储过程上单击右键，在弹出的快捷菜单中选择“新建存储过程”命令，出现“存储过程属性新建存储过程”对话框，如图8-2所示。 4）将图8-2对话框中的“OWNER.PROCEDURE
6、NAME”用需要创建的存储过程的名称代替，再将需要实现功能的SQL语句代码依次输入，编辑存储过程，如图8-3所示。 5）输入完成后，单击“检查语法”按钮，检查输入的SQL语句代码是否正确。 6）检查通过后，单击“确定”按钮，保存存储过程，完成创建,图8-2 新建存储过程 图8-3 新建存储过程中的执行代码,7）存储过程创建完成后，用右键单击该存储过程，在弹出的快捷菜单中选择“所有任务”“管理权限”命令，在弹出的对象属性中，对其使用权限进行设置，如图8-4所示。,图8-4 存储过程的对象属性,8.2 存储过程的操作,2. 使用向导创建存储过程 使用SQL Server提供的向导来创建存储过程，可
7、以根据系统提示 完成操作，步骤如下： 1）打开企业管理器，展开相应的服务器和数据库“RtvuStu”。 2）在菜单栏中选择“工具”“向导”命令，如图8-5所示，弹出“选择向导”对话框，如图8-6所示。,图8-5 向导菜单 图8-6 “选择向导”对话框,8.2 存储过程的操作,2. 使用向导创建存储过程 3）在“选择向导”对话框中，单击“数据库”选项，在展开的列表中，选择“创建存储过程向导”选项，单击“确定”按钮，弹出如图8-7所示的“创建存储过程向导之欢迎”对话框。 4）在“创建存储过程向导之欢迎”对话框中单击“下一步”按钮，弹出“创建存储过程向导之选择数据库”对话框，如图8-8所示。,图8-
8、7 “创建存储过程向导”对话框 图8-8 “选择数据库”对话框,8.2 存储过程的操作,5）在“创建存储过程向导之选择数据库”对话框中，选择本次创建的存储过程所在的数据库，单击“下一步”按钮，弹出“创建存储过程向导之选择存储过程”对话框，如图8-9所示，该对话框中显示了前面选择的数据库中所有的用户表名称，并有三个包含复选框的列，分别代表三种操作：插入、删除和更新。用户可以根据需要设置一个或多个操作。 6）完成操作后，单击“下一步”按钮，弹出“正在完成创建存储过程向导”对话框，单击“完成”按钮，存储过程创建成功，如图8-10所示。,图8-9 “选择存储过程”窗口 图8-10 “正在完成创建存储过
9、程”对话框,8.2 存储过程的操作,7）还可以通过单击图8-10中的“编辑”按钮，进入“编辑存储过程属性”对话框，如图8-11所示。在该窗口上对存储过程进行重新命名，在选择字段中选中的列名称将会被此存储过程使用。 8）要编辑存储过程，可以通过单击“编辑存储过程属性”对话框下方的“编辑SQL”按钮，打开“编辑存储过程SQL”窗口，如图8-12所示，在该窗口内检查存储过程的T-SQL程序代码，在编辑区域内直接编辑代码，完成后，单击“分析”按钮检查语法错误。单击“确定”按钮返回如图8-10所示的“正在完成创建存储过程”对话框。单击“完成”按钮，存储过程创建成功。,图8-11 “编辑存储过程属性”对话
10、框 图8-12 “编辑存储过程SQL”窗口,8.2 存储过程的操作,2. 3使用Create Procedure 语句创建存储过程 使用T-SQL语句中的Create Procedure命令可以直接创建存储 过程。但在使用该命令创建存储过程前，应注意以下几点： 1）Create Procedure语句不能和其他T-SQL语句在同一个批处理中。 2）创建存储过程的权限默认属于数据库拥有者，该拥有者可以将此权限授予其他用户。 3）存储过程是数据库对象，其名称必须遵守标识符命名规则。 4）只能在当前数据库中创建存储过程。,8.2 存储过程的操作,用Create Procedure创建存储过程的语法形
11、式如下： Create Procedure procedure_name Parameter Data_type Varying=DefaultOutput ,.n With Recompile|Encryption|Recompile,Encryption For Replication As Sql_statement .n ,8.2 存储过程的操作,参数说明如下。 1）Procedure_name：指定要创建的存储过程的名称。 2）Parameter：过程中的参数。在 Create Procedure 语句中可以声明一个或多个参数。 3）Data_type：用于指定参数的数据类型。 4）
12、Varying：用于指定作为输出Output参数支持的结果集，仅用于游标参数。 5）Default：用于指定参数的默认值。一旦定义了默认值，即使不给出参数值，该存储过程依然可以被调用。 6）Output：表明该参数是一个返回参数。 7）Recompile：表示该存储过程在运行时将重新编译。 8）Sql_statement：是存储过程中要包含的任意数目和类型的T-SQL语句。,8.2 存储过程的操作,【例8-1】 创建一个存储过程，命名为getCouseName，该存储过程可获取所有“Course”表中的课程名。 在查询分析器中输入如下语句： Use RtvuStu Go Create proc
13、 getCouseName As Select Cname from Course GO,8.2 存储过程的操作,8.2.2 存储过程的调用 在SQL Server数据中，通过使用T-SQL语句中的Execute命令来执行 存储过程，但是当存储过程是批处理的第一条语句时，那么不使用 Execute关键字也可以执行存储过程。 执行存储过程的语法如下： Execute Procedure_name parameter = value | variableOutput I Default ,.n,8.2 存储过程的操作,参数说明如下。 1）Procedure_name：用于指定要执行的存储过程。 2
14、）parameter：是在创建过程时定义的过程参数。调用者向存储过程所传递的参数值由value参数或variable变量提供。 3）variableOutput用于保存存储过程中传递的参数值或返回参数的变量。若使用Output关键字，则表示该参数是一个输出参数，目的是在调用批处理或过程的其他语句中使用其返回值，参数的值必须作为一个变量被返回（即parameter=variable）。 4）Default：使用存储过程提供的默认值。 在执行存储过程时，尽量少用可选参数，否则会影响系统的性能。可以在调用存储过程时传递参数给它，将参数名称的前缀用标示，如 auName。,8.2 存储过程的操作,【例
15、8-2】 执行存储过程getCouseName。 在查询分析器中输入如下语句： Use RtvuStu Go Exec getCouseName Go,【例8-3】 创建存储过程getDetailByName，通过输入参数学生姓名，选出该学生的基本信息，对不存在此学生姓名的输入值，必须作检测，打印信息“不存在此学生”。 在查询分析器中创建存储过程语句如下： Use RtvuStu Go Create proc getDetailByName name nvarchar(10) As If exists Select * From Student Where 姓名= name Begin Sel
16、ect * From Student Where 姓名= name End Else Begin Print 不存在此学生！ End Go 执行该存储过程： Exec getDetailByName name=章山,8.2 存储过程的操作,8.2.3 存储过程的管理 1查看存储过程 通过企业管理器查看存储过程步骤如下： 1）打开企业管理器，展开相应的服务器和数据库“RtvuStu”。 2）选择“存储过程”，在窗口右侧显示当前数据库中所有的存储过程。 3）选择要查看的存储过程，双击或通过右键单击后，选择“属性”选项，弹出“存储过程属性”对话框，在该对话框中查看当前存储过程的T-SQL源代码，如图8-13所示。,8.2 存储过程的操作,图8-13 “存储过程属性”对话框,还可以在查询分析器中使用sp_help、sp_helptext、sp_de
