SQL Server2000实用教程第5章 Transact-SQL程序设计.ppt

使用存储过程的优点 模块化程序设计。将一个复杂程序通过多个彼此独立的存储过程组合而成，可以方便调试和维护。 SQL Server中的存储过程和其他编程语言中的子程序（过程和函数）一样，可以接受输入参数，也可以有返回值，而且还可以用输出参数将单个或多个值返回给调用它的存储过程或批处理中。 所以，各个存储过程不仅互相独立，彼此通信手段也非常灵活。 存储过程的分类 在SOL Server中存储过程分为两类： 即系统提供的存储过程和用户存储过程。 （1）系统存储过程。 系统存储过程是由SQL Server系统提供的存储过程，可以作为命令执行各种操作。 系统存储过程主要是从系统表中获得信息，从而为系统管理员管理SQL Server提供支持。 SQL Server中的许多管理工作是通过执行系统存储过程来完成的。许多系统信息也可通过执行系统存储过程而获得。 系统存储过程定义在系统数据库master中，其前缀是sp_。尽管系统存储过程被放在master数据库中，但是仍然可以在其他数据库中对其进行调用。在调用时不必在存储过程前加上数据库名。 在SQL Server 2000中有许多系统存储过程。 除了以sp_为前缀的系统存储过程，我们还常见到以xp_为前缀的存储过程，这种存储过程为扩展存储过程。 顾名思义，扩展存储过程用于扩展SQL Server的功能。它们是以动态链接库（DLL）形式存在的外部程序。 SQL Server允许开发人员使用其他编程语言（如C/C++）创建扩展存储过程，通过安装，它们可以直接在SQL Server的地址空间中运行。 在SQL Server 2000安装后，master数据库中已经存在许多扩展存储过程。例如，可以使用扩展存储过程xp_cmdshell来运行通常从命令提示符下执行的命令。以下语句通过查询分析器执行DOS环境下的DIR命令，显示C盘根目录下的所有文件名： xp_cmdshell ’ dir c: \*.* ’ 用户存储过程 用户存储过程是指在用户数据库中创建的存储过程。这种存储过程由用户创建，完成特定数据库操作任务。本节下面所涉及的存储过程主要是指用户存储过程。 用户存储过程一般不要使用sp_作为其名称前缀，因为如果用户存储过程和系统存储过程同名，将执行系统存储过程（SQL Server首先在master数据库中查找以sp_作前缀的存储过程）。 存储过程 如果在创建存储过程时，存储过程名的前面加上“#”，表示创建全局临时存储过程；在存储过程名的前面加上“# #”，表示创建局部临时存储过程。 局部临时过程只能由创建该过程的连接使用，即仅在创建它的会话中可用，全局临时存储过程可以在所有会话中使用。即所有用户均可以访问该过程。 它们都保存在tempdb数据库中，所以只有在tempdb数据库中具有创建存储过程权限的用户，才可以在该数据库中显式创建临时过程（不使用编号符命名）。 局部临时过程在当前会话结束时自动除去。全局临时过程在使用该过程的最后一个会话结束时除去。但通常也是在创建该过程的会话结束时。 存储过程的创建与执行 在SQL Server中，创建存储过程可以使用以下3种方法： 使用创建存储过程向导创建存储过程 使用企业管理器创建存储过程 使用CREATE PROCEDURE语句创建存储过程。 存储过程的创建与执行 一般情况下，创建存储过程的许可权归数据库的所有者，数据库的所有者可以把许可授权给其他用户。 存储过程的最大大小为 128 MB。 用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在 tempdb 中创建）。存储过程创建后其过程名存储在sysobjects系统表中，存储过程的文本存储在syscomments系统表中。可以在创建时对它加密以使其在该系统表中也不能正常显示。 存储过程的创建与执行 当创建存储过程时，需要确定存储过程的三个组成部分： 所有的输入参数以及传给调用者的输出参数； 对数据库操作的Transact-SQL语句，包括调用其他存储过程的语句。 返回给调用者的状态值，以指明调用是成功还是失败。 存储过程的创建与执行 在用户存储过程的定义中不能使用下列对象创建语句： CREATE VIEW、CREATE DEFAULT、CREATE RULE CREATE PROCEDURE、CREATE TRIGGER。 即在存储过程的创建中不能嵌套创建以上这些对象。 存储过程创建后，可以使用EXECUTE语句来执行（可以简写为EXEC）； 如果它是在一个批处理中的第一条语句，则关键字 EXECUTE（或EXEC）也可省略。 例 题 【例5.21】执行系统存储过程sp_help查看教学