Chapter9 存储过程的创建与使用.pptVIP

《数据库系统管理》 讲 师：张春娥 Email：zhang_chune@163.com 回 顾 上一章所讲的主要内容： 索引的概念； 索引的分类； 索引的优缺点； 索引的创建、查看、修改和删除； 第9章 存储过程的创建和使用 本章内容 本章目标 理解存储过程的概念、优点和分类； 掌握存储过程的创建； 掌握存储过程的执行方式，参数传递方式等； 掌握存储过程的查看、修改、重命名和删除； 了解存储过程组以及with recompile选项的使用 9.1 概述 9.1.1 存储过程（Stored Procedure） 存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。 用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 存储过程允许用户声明变量，也可以接收和输出参数、返回执行存储过程的状态值，以及嵌套调用等。 存储过程的运行过程。（如下） 存储过程的运行过程 首次运行存储过程时，它按以下方式进行： 该存储过程被划分成部件片断。 检查所引用的数据库中其它对象（表、视图等）的部件，确保引用的对象是存在的，也称为分解 分解完成之后，将过程名字存放在sysobjects表中，而过程的代码存放在syscomments表中。 然后编译，并且编译过程中将创建如何运行查询的蓝本。该蓝本也称为查询计划或查询树，查询树存放在sysProcedures表中。 存储过程再次运行时，读出查询计划并完全编译成过程计划，然后运行。 9.1 概述 9.1.2 存储过程的优点 利用SQL Server编写数据库应用程序基本方式： 1、本地客户计算机调用T-SQL编写程序，向SQL Server服务器发送命令，之后对结果进行处理； 2、SQL Server服务器上编写存储过程，客户计算机给定参数，调用存储过程，完成确定功能。 QS：哪一种方式更好？ 9.1 概述 9.1.2 存储过程的优点 更快的执行速度。存储过程是预编译的，而批处理的T-SQL语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。 减少网络流量，降低网络负担。应用程序通过调用语句就可以执行存储过程，不需要将大量T-SQL语句发送到服务器端。 实现了模块化编程，提高了程序的可移植性。 提供数据库的安全性。 9.1 概述 9.1.3 存储过程的分类 1、系统存储过程 主要存储在master数据库中，并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。 2、用户自定义存储过程 在用户数据库中创建的用户存储过程，完成特定数据库操作任务。 9.1 概述 9.1.3 存储过程的分类 1、系统存储过程 主要存储在master数据库中，并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。 2、用户自定义存储过程 Transact-SQL存储过程 CLR存储过程 9.1 概述 9.1.4 存储过程的创建规则 名字必须符合SQL Server命名规则； 在存储过程中不能有如下的SQL创建语句： Create Default、Create Procedure、Create Rule、Create Trigger 、 Create View。 引用的对象必须在创建存储过程前就存在； 可在过程中嵌套调用存储过程。 9.1 概述 9.1.4 存储过程的创建规则 在自己的存储过程中可以引用临时表，也可以创建局部临时表，但退出该存储过程时，所创建的局部临时表将消失； 存储过程最多能有255个参数，根据可用内存的不同，存储过程最大可达128MB； 若在存储过程中使用了Select *，而底层表中加入了新的列，新的列在过程运行时无法显示。 9.2 创建存储过程 存储过程的创建方式 使用SSMS来创建存储过程； 使用T-SQL语句来创建存储过程。 当创建存储过程时,需要确定其三个组成部分 输入参数以及输出参数； 被执行的针对数据库的操作语句； 返回给调用者的状态值； 9.2 创建存储过程 9.2.1 使用SSMS来创建存储过程 打开新建存储过程的代码编辑窗口。 使用“查询”-“指定模板参数的值”，打开“替换模板参数工具”。 例9-2-1 创建存储过程proc_ks，查询考生的部分信息（准考证号、姓名、性别、成绩）。 例9-2-1 创建存储过程proc_dw，完成向tb_dwxx表中添加一条记录，所添加记录的单位名称由输入参数来提供。（可判断是否已有该项记录） 9.2 创建存储过程 9.2.2 使用T-SQL语句来创建存储过程 注意事项： CREATE PROCEDURE语句不能与其他SQL语句在单个批处理中组合使用。 必须具有数