SQL-sever自学教程-存储过程PPT资料.ppt

1、允许模块化程序设计。只需创建过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。允许更快执行。如果某操作需要大量 Transact-SQL 代码或需重复执行，存储过程将比 Transact-SQL 批代码的执行要快。将在创建存储过程时对其进行分析和优化，并可在首次执行该过程后使用该过程的内存中版本。每次运行 Transact-SQL 语句时，都要从客户端重复发送，并且在 SQL Server 每次执行这些语句时，都要对其进行编译和优化。减少网络流量。一个需要数百行 Transact-SQL 代码的操作由一

2、条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。可作为安全机制使用。即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。,2022年10月2日星期日,5,存储过程的类型,SQL Server 存储过程是用 Transact-SQL 语句 CREATE PROCEDURE 创建的，并可用 ALTER PROCEDURE 语句进行修改。存储过程定义包含两个主要组成部分：过程名称及其参数的说明，以及过程的主体（其中包含执行过程操作的 Transact-SQL 语句）。在Microsoft SQL Server 2000关系型数据库管理系统中，支持5种类

3、型的存储过程：系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。不同类型的存储过程具有不同的作用。,2022年10月2日星期日,6,1）系统存储过程在SQL Server中的许多管理工作是通过执行系统存储过程来完成的。系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作，还可以作为样本存储过程指导用户如何编写有效的存储过程。系统存储过程创建和保存在master数据库中，都以sp_为名称的前缀。允许系统管理员执行修改系统表的数据库管理任务。系统存储过程是SQL Server系统自带的（分为9类），具有执行系统存储过程权限的用户，可在master数据库之外直接调用（即：

4、可在任意一个数据库中执行）。一般情况下，系统存储过程执行成功返回0值，若有错误发生返回非0值。,2022年10月2日星期日,7,2）本地存储过程（用户存储过程）指创建在每个用户自己数据库中的存储过程，即：由用户根据实际问题的需要所创建的存储过程。这种存储过程主要在应用程序中使用，虽然存储过程的名称前不能有sp_为名称的前缀，但是在实际应用中应该使用有意义的名称。在以后的数据库开发过程中，涉及的存储过程都是本地存储过程，可以完成特定的任务。,2022年10月2日星期日,8,3）临时存储过程临时存储过程首先是本地存储过程。SQL Server 支持两种临时过程：局部临时过程和全局临时过程。如果存储

5、过程的名称前面有一个符号“#”，那么把该存储过程称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。局部临时过程只能由创建该过程的连接使用，在当前会话结束时自动除去。如果存储过程的名称前面有两个符号“#”，那么把该存储过程称为全局临时存储过程，这种存储过程可以在所有用户会话中使用。全局临时过程则可由所有连接使用，在使用该过程的最后一个会话结束时除去。通常是在创建该过程的会话结束时。,2022年10月2日星期日,9,4）远程存储过程指从远程服务器上调用的存储过程，或是从连接到另外一个服务器上的客户机上调用的存储过程。远程存储过程实际上就是非本地服务器上的存储过程。5）扩展存储过程扩展存储

6、过程是以动态链接库（dll）形式存在的外部程序。SQL Server自身带了大量的扩展存储过程安装在master数据库中，扩展存储过程与普通存储过程执行方法相同。,2022年10月2日星期日,10,创建存储过程,创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。用户存储过程可以使用T-SQL语句CREATE PROCEDURE或企业管理器来创建。在创建一个存储过程之前，应注意以下几个问题：l 在存储过程中不能包含CREATE VIEW、CREATE DEFAULT、CREATE RULE、CREATE PROCEDURE和 CREATE T

7、RIGGER这几个创建数据库对象的语句。l 创建存储过程的权限默认属于数据库所有者，该权限可以授予其他人。l 存储过程是数据库对象，它的命名应符合标识符的命名规则。l 存储过程只能创建在当前数据库中。,2022年10月2日星期日,11,当创建好存储过程之后，系统检查其中语句的正确性。语法检查之后，系统将存储过程的名称存储在当前数据库的系统表sysobjects中，将存储过程的文本储存在当前数据库的系统表syscomments中；如果碰到语法错误，那么存储过程创建失败。权限：CREATE PROCEDURE 的权限默认授予 sysadmin 固定服务器角色成员和 db_owner 和 db_dd

8、ladmin 固定数据库角色成员。sysadmin 固定服务器角色成员和 db_owner 固定数据库角色成员可以将 CREATE PROCEDURE 权限转让给其他用户。执行存储过程的权限授予过程的所有者，该所有者可以为其它数据库用户设置执行权限。创建存储过程的方法：1、使用T-SQL语句创建存储过程2、使用企业管理器创建存储过程3、使用向导创建存储过程,2022年10月2日星期日,12,1使用T-SQL语句创建存储过程 创建存储过程使用CREATE PROC EDURE语句。,【语法格式】CREATE PROC EDURE procedure_name;number parameter d

9、ata_type VARYING=default OUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement.n,2022年10月2日星期日,13,其中：procedure_name为存储过程名。parameter为存储过程中的输入、输出参数。data_type为参数的数据类型。default为参数指定的默认值。WITH子名为指定一些选项。RECOMPILE表明SQL Server 不会缓存该过程的计划，该过程将在每次运行时重新编译。ENCRYPTION表示SQL Serve

10、r加密syscomments表中包含CREATE PROCEDURE 语句文本的条目。n表示此过程可以包含多条 T-SQL 语句。sql_statement 在存储过程中要执行的T-SQL语句。,2022年10月2日星期日,14,一个简单存储过程的创建和执行【示例】使用带有复杂 SELECT 语句的简单过程。（该存储过程不使用任何参数。）CREATE PROCEDURE au_info_allASSELECT au_lname,au_fname,title,pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id=ta.au_id

11、 INNER JOIN titles t ON t.title_id=ta.title_id INNER JOIN publishers p ON t.pub_id=p.pub_idGOau_info_all 存储过程可以通过以下方法执行：EXECUTE au_info_all（或）EXEC au_info_all如果该过程是批处理中的第一条语句，则可使用：au_info_all,2022年10月2日星期日,15,说明：建议使用下面的方式建立存储过程。首先检验在sysobjects表中是否存在名字相同的存储过程的记录，如果有，则先删除原有的存储过程，再创建与之同名的存储过程。USE pubsI

12、F EXISTS（SELECT name FROM sysobjects WHERE name=au_info_all AND type=P）DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allASSELECT au_lname,au_fname,title,pub_name FROM 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

13、ON t.pub_id=p.pub_idGO,2022年10月2日星期日,16,创建带参数的存储过程在创建和使用存储过程时，其参数是非常重要的。存储过程通过参数来与调用它的程序通信。在程序中调用存储过程时，可以通过输入参数将数据传给存储过程，存储过程可以通过输出参数和返回值将数据返回给调用它的程序。一个存储过程中最多可以有1024个参数。存储过程的参数在创建时应在CREATE PROCEDURE和AS关键字之间定义，每个参数都要指定参数名和数据类型。参数名必须以符号为前缀。用户可以为参数指定默认值，如果调用存储过程时，不为输入参数传入数据，系统就会将默认值传给输入参数。如果是输出参数应用OUT

14、PUT关键字描述，各个参数定义之间用逗号隔开。【语法】parameter_name data_type=defaultOUTPUT,2022年10月2日星期日,17,【示例】使用带有参数的简单过程下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。USE pubsIF EXISTS（SELECT name FROM sysobjects WHERE name=au_info AND type=P）DROP PROCEDURE au_infoGOUSE pubsGOCREATE PROCEDURE au_info lastn

15、ame varchar（40）,firstname varchar（20）AS SELECT au_lname,au_fname,title,pub_name FROM 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_id WHERE au_fname=firstname AND au_lname=lastnameGO,2022年10月2日星期日,18,在程序中