《数据库数据存储过程》由会员分享，可在线阅读，更多相关《数据库数据存储过程（45页珍藏版）》请在金锄头文库上搜索。
1、数据库基础及应用,存储过程和触发器,存储过程和触发器是SQL语句和流程控制语句的集合。 存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其他强大的编程功能。 触发器是一种特殊的存储过程，和表的关系密切，当修改表的数据时自动执行，能有效地实现数据完整性。,1.1 存储过程概述,存储过程（Store Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带参数）来执行它。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。,
2、1.1.1存储过程的优点 使用SQL Server 2005中的存储过程而不使用存储在客户计算机本地的Transact-SQL程序的优势如下。（1）允许模块化程序设计只需创建过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。（2）允许更快执行如果某操作需要大量Transact-SQL代码或需重复执行，存储过程将比Transact-SQL批代码的执行要快。将在创建存储过程时对其进行分析和优化，并可在首次执行该过程后使用该过程的内存中版本。每次运行Transact-SQL语句时，都要从客户端重复发送，并且在
3、SQL Server每次执行这些语句时，都要对其进行编译和优化。,（3）减少网络流量一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。（4）可作为安全机制使用即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。 1.1.2 存储过程分类系统存储过程 用户自定义存储过程扩展存储过程,（1）系统存储过程,系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_，它们为检索系统表的信息提供了方便快捷的方法。尽管这些系统存储过程被放在mast
4、er数据库中，但是仍可以在其他数据库对其进行调用，在调用时不必在存储过程名前加上数据库名。而且在创建一个新的数据库时，一些系统存储过程会在新的数据库中自动创建。,（2）用户自定义存储过程,用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在本章中所涉及到的存储过程主要是指用户自定义存储过程。在SQL Server 2005中，存储过程有两种类型：Transact-SQL和CLR。Transact-SQL存储过程是指保存的Transact-SQL语句集合，可以接受和返回用户提供的参数。CLR存储过程是指对.NET Framework公共语言时方法的引用，可以
5、接受和返回用户提供的参数。它们在.NET Framework程序集中是作为类的公共静态方法实现的。用户自定义存储过程起名字不要以sp_作为后缀，和系统存储过程相同的前缀并不好，对于数据库而言是一个指示符，它将先在系统类别中定位，然后才在数据库中定位。使用这样的前缀不会造成特别的问题，但它会降低性能，并且造成潜在的混淆。,（3）扩展存储过程在SQL Server环境之外执行的动态链接库称为扩展存储过程，其前缀是xp_。使用时需要先加载到SQL Server系统中，并且按照使用存储过程的方法执行。,1.2 使用存储过程,创建一个存储过程有两种方法：一是使用图形创建存储过程，二是利用Transact
6、-SQL中命令Create Procedure。 在一个存储过程中，可以使用任何SQL语句，但是不包括下面的语句：Create Default，Create/Alter Procedure，Create Rule，Create/Alter Trigger，Create/Alter View。 当创建存储过程时，需要确定存储过程的3个组成部分： 所有的输入参数以及传递给调用者的输出参数； 被执行的针对数据库的操作语句，包括调用其他存储过程的语句； 返回给调用者的状态值，以指明调用是成功还是失败。,1.2.1 使用图形创建存储过程,SQL Server 2005提供了一种简单的方法-使用SQL S
7、erver Management Studio工具。操作步骤如下： 打开SQL Server Management Studio窗口，连接数据库引擎成功。 展开指定的服务器-MyDB-可编程性的节点。 从列表中选中存储过程右击-.选中新建存储过程，然后出现下图所示的CREATE PROCEDURE语句的模板，可以根据模板修改需要的存储过程。 修改完后，单击执行按钮即可创建一个存储过程。,1.2.2 使用语句创建存储过程,1语法格式使用T-SQL语句中的CREATE PROCEDURE命令创建存储过程前，应该考虑下列几个事项： 不能将CREATE PROCEDURE语句与其他SQL语句组合到单个
8、批处理中。 创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 存储过程是架构作用域内的对象，它们的名称必须遵守标识符规则。 只能在当前数据库中创建存储过程。,使用CREATE PROCEDURE创建存储过程的语法形式如下： CREATE PROCEDURE procedure_name ;number parameter data_typeVARYING=defaultOUTPUT,.n WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n ,用CREATE P
9、ROCEDURE创建存储过程的语法参数的意义如下：, procedure_name用于指定要创建的存储过程的名称，它后面跟一个可选项number，它是一个整数，它用来区别一组同名的存储过程。存储过程的命名必须符合命名规则，在一个数据库中或对其所有者而言，存储过程的名字必须惟一。, parameter是过程中的参数。在CREATE PROCEDURE语句中，可以声明一个或多个参数。当调用该存储过程时，用户必须给出所有的参数值，除非定义了参数的默认值。若参数的形式以parameter=value出现，则参数的次序可以不同，否则用户给出的参数值必须与参数列表中参数的顺序保持一致。若某一参数以para
10、meter=value形式给出，那么其他参数也必须以该形式给出。, data_type用于指定参数的数据类型。在存储过程中，所有的数据类型包括text和image都可以被用作参数。但是，游标cursor数据类型只能被用作OUTPUT参数。, VARYING用于指定作为输出OUTPUT参数支持的结果集，仅应用于游标参数。 Defaul用于指定参数的默认值。如果定义了默认值，那么即使不给出参数值，该存储过程仍能被调用。默认值必须是常数，或者是空值。 OUTPUT表明该参数是一个返回参数。用OUTPUT参数可以向调用这返回信息。Text类型参数不能用作OUTPUT参数。, RECOMPILE表明SQ
11、L Server不会保存该存储过程的执行计划。该存储过程、每执行一次都要重新编译。 ENCRYPTION 表示SQL Server加密了syscomments表，该表的text字段是包含CREATE PROCEDURE语句的存储过程文本，使用该关键字无法查看syscomments表中存储过程内容。 FOR REPLICATION选项指明了为复制创建的存储过程不能在订阅服务器上执行为，只有在创建过滤存储过程时（仅当进行数据复制时过滤存储过程才被执行），才使用该选项。FOR REPLICATION与WITH RECOMPILE选项是互不兼容的。, AS用于指定该存储过程要执行的操作。 sql_st
12、atement是存储过程中要包含的任意数目和类型的Transact-SQL语句。,创建存储过程的示例,【例1】在数据库“销售管理”中创建一个名为GetAllStudents的存储过程，将从表（Student）中返回所有学生的学号、姓名、年龄、性别、系。使用CREATE PROCEDURE的语句如下： CREATE PROCEDURE GetAllStudents AS select sno,sname,sage,ssex,sdept from student,【例2】查询学生的选课的信息，用到的表是学生表（Student）、选课表（sc）和课程表（course），返回的列包括学号（Sno）、姓
13、名（Sname）、课程代码（cno）和课程名称（Cname），存储过程名为Pro_GetAllINfo。 创建语句如下： CREATE PROC Pro_GetAllINfo AS select student.sno,sname,o,cname from student,course,sc where student.sno=sc.sno and o=o,存储过程的说明存储过程创建后，相关信息保存在sys.procedures系统表中。在定义一个存储过程前，最好先判断该对象是否存在，若存在，可先删除，然后重新定义该对象，例如在定义例2前，判断语句如下： if exists(select *
14、from sysobjects where name=Pro_GetAllInfo and type=P)drop proc Pro_GetAllInfo,1.3存储过程调用,可以使用EXECUTE语句来执行一个存储过程。如果存储过程是在一个批语句中的第一句，则可以不使用EXECUTE关键字。执行存储过程的Transact-SQL 语句如下： EXEC UTE return_status = procedure_name ;number | procedure_name_var parameter = value | variable OUTPUT | DEFAULT ， .n WITH RE
15、COMPILE ,其中各参数的意义为：, return_status是一个可选的整数变量，存放一个存储过程的返回状态。这个变量必须在用于EXECUTE语句中之前在一个批语句或存储过程中声明。 procedure_name指定要执行的过程。可以执行在另一个数据库中创建的一个过程，只要是其所有者或在该数据库中具有执行它的权限。如果在另一个SQL Server实例上有使用该服务器和在该数据库中执行该过程的权限，那么也可以执行该实例上的该过程。如果指定一个服务器名但不指定数据库名称，则SQL Server会在默认数据库中查找该过程。, number是一个可选的整数，用于分组同一名称的过程，使得它们可以
16、在单条DROP PROCEDURE语句中一起被删除。 procedure_name_var是一个局部变量的名称，它代表了一个存储过程的名称。 parameter指定过程的参数，在CREATE PROCEDURE语句中定义。 value是该参数对这个过程的值。如果不是用参数，则参数值必须以在CREATE PROCEDURE语句中定义的顺序提供。如果参数的值是一个对象的名称，则整个名称必须包含在单引号之间。如果参数的值是一个关键字，则关键字必须包含在一对双引号之间。, variable指定存储过程返回参数的变量。 OUTPUT指示存储过程返回一个参数。存储过程中的匹配参数还必须用OUTPUT关键字定义。如果OUTPUT参数是用来返回数据给调用它的批语句或过程的，则参数的值必须作为一个变量被返回（即parameter = variable），如果在CREATE PROCEDURE中没有指定OUTPUT参数，则不能为该参数指定OUTPUT选项。返回参数需要一个变量名称。在执行过程前，变量的数据类型必须声明并且赋值。返回参数可以是除text和image以外的任何数据类型。 DEFAULT 提供在过程定义中的默认值。如果过程的参数需要一个数值且没有指定默认值，而执行的时候没有为该参数指定参数，则会产生一个错误。,
