第10章存储过程概论
存储过程PPT教学课件
PPT教学课件
谢谢观看
Thank You For Watching
3
111.Biblioteka 存储过程综述优点： 1、安全机制：只给用户访问存储过程的权限，而不授予用户访问表
和视图的权限。 2、改良了执行性能：在第一次执行后，会在SQL server的缓冲区中
创建查询树，以后执行无需编译。 3、减少网络流量：存储过程存在于服务器上，调用时，只需传递执
行存储过程的执行命令和返回结果。 4、模块化的程序设计：增强了代码的可重用性，提高了开发效率。
11.1 存储过程综述
1、存储过程概念 存储过程是一种数据库对象，是为了实现某个特定任务，将一组预编
译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用。存储 过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中以 便以后调用，这样可以提高代码的执行效率。 存储过程同其它编程语言中的过程相似，有如下特点： 接收输入参数并以输出参数的形式将多个值返回至调用过程或批处理。 包含执行数据库操作（包括调用其它过程）的编程语句。 向调用过程或批处理返回状态值，以表明成功或失败以及失败原因。
第10章 存储过程
第10章存储过程存储过程是SQL语句和可选控制流语句的预编译集合，它以一个名称存储并作为一个单元处理。
本章介绍存储过程的创建、执行、修改和删除等。
10.1 概述存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其他强大的编程功能。
存储过程可以使对数据库的管理，以及显示关于数据库及其用户信息的工作容易得多。
存储过程可包含程序流、逻辑以及对数据库的查询。
它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。
可以出于任何使用SQL语句的目的来使用存储过程，它具有以下优点：可以在单个存储过程中执行一系列SQL语句。
可以从自己的存储过程内引用其他存储过程，这可以简化一系列复杂语句。
存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快，且能减少网络通信的负担。
10.2创建存储过程要使用存储过程，首先要创建一个存储过程。
可以使用Transact-SQL 语言的CREATE PROCEDURE语句，也可以使用企业管理器或者存储过程创建向导来完成。
1．使用CREATE PROCEDURE语句创建存储过程CREATE PROCEDURE语句的语法格式为：CREATE PROC[EDURE ] procedure_name [; number][ {@parameter data_type}[VARYING ][ = default][OUTPUT]][,…n][WITH{RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}][FOR REPLICATION]AS sql_statement […n ]其中各参数含义如下：procedure_name新存储过程的名称。
number 是可选的整数，用来对同名的过程分组，以便用一条DROP PROCEDURE语句即可将同组的过程一起除去。
例如，名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。
第10章 存储过程
例
• 执行存储过程“增加成绩”，并查看执行结果。 USE xs GO EXEC 增加成绩 SELECT * FROM 学生 GO
10.4 存储过程的参数
为了提高存储过程的灵活性，SQL Server 2000支持在存 储过程中使用参数。 存储过程的参数分为输入参数和输出参数两种类型，输入 参数用于向存储过程中带入数据，而输出参数则能将存储 过程中的数据返回到调用程序。 在定义存储过程时,可以同时指定参数,格式如下： @参数名 数据类型 [=默认值] [OUTPUT][, ... n] 如果参数后面使用OUTPUT关键字，则表明它是输出参数。
例1
• 创建存储过程“增加成绩”，将表“成绩”中 所有学生的成绩增加10%。 USE xs GO CREATE PROCEDURE 增加成绩 AS UPDATE sc SET grade = grade + grade*0.1 GO
在创建一个存储过程时，如果已经存在同名的存储过程， 例2 USE xs --如果存在名称为“增加成绩”的存储过程，则将其删 除 IF EXISTS (SELECT name FROM sysobjects WHERE name = '增加成绩' AND type = 'P') DROP PROCEDURE 增加成绩 GO CREATE PROCEDURE 增加成绩 AS UPDATE sc SET grade = grade + grade*0.1 GO
参数说明
• 默认值：参数的默认值。如果定义了默认值， 不必指定该参数的值即可执行存储过程。默认 值必须是常量或NULL。如果要在存储过程中 对该参数使用LIKE关键字，那么默认值中可以 包含通配符（%、_、[]和[^]）。 • OUTPUT：表明参数是返回参数。该选项的值 可以返回给EXEC[UTE]。使用OUTPUT参数可 将信息返回给调用过程。text、ntext和image参 数可用作OUTPUT参数。使用OUTPUT关键字 的输出参数可以是游标占位符。
第10章 存储过程
2. 提高执行效率，改善系统系能
存储过程比一般的SQL语句执行速度快。存储过程在创建 时已被编译，每次执行时不必再编译，而SQL语句每次执行 都需要编译。另外，存储过程已在服务器注册。存储过程具 有安全特性（例如权限）和所有权链接，以及可以附加到它 们的证书。用户可以被授予权限来执行存储过程而不必直接 对存储过程中引用的对象具有权限。
说明： 1) 该语句可以创建永久存储过程，也可以创建一个在一个 会话过程中临时使用的局部存储过程，名称前加一个#；还可 以创建一个在所有会话中临时使用的全局存储过程，名称前加 两个##。
10.2.1 创建存储过程
2) 分组号：整数，可作为同名过程分组的后缀序号，如 Ts1，Ts2可定义属于一组，同组的过程可以用一条DROP PROCEDURE删除命令全部删除掉。 3) @形参变量：过程中的参数。在CREATE PROCEDURE过程中可以声明一个或多个参数。必须在执行 过程时提供每个所声明参数的值（除非定义了该参数的默认 值）。 4) 数据类型：参数的数据类型。所有数据类型均可以用 作存储过程的参数。不过，CURSOR数据类型只能用于 OUTPUT参数。如果指定的数据类型为CURSOR，也必须同 时指定VARYING和OUTPUT关键字。 5) VARYING：指定作为输出参数支持的结果集（由存储 过程动态构造，内容可以变化）。仅适用于游标参数。 6) 默认值：参数的默认值。如果定义了默认值，不必指 定该参数的值即可执行过程。默认值必须是常量或NULL。
CREATE PROCEDURE ProcBookCate
@读者编号 char(6), @类别 nvarchar(20) OUTPUT
AS SELECT @类别 = BookCate FROM BookCategory, Lending, Book WHERE UserId = @读者编号 AND BookCategory.BkCateId = Book.BkCateId AND Lending .BookId = Book .BookId
第10章存储过程与触发器
10.1 存储过程概述
存储过程具有以下优点
➢ (1)模块化编程。 ➢ (2)快速执行。 ➢ (3)减少网络通信量。 ➢ (4)提供安全机制。 ➢ (5)保证操作一致性。
1010..22 存储存过程储的创过建与使程用 的创建与使用
存储过程创建
1. 使用企业管理器创建存储过程
10.2.1 创建存储过程
10.2.5 存储过程参数与状态值
2. 返回存储过程的状态 (1)用RETURN语句定义返回值 存储过程可以返回整型状态值，表示过程是否成功执行，
或者过程失败的原因。 如果存储过程没有显式设置返回代码的值，则SQL Server
返回代码为 0，表示成功执行；若返回-1~ -99之间的整数， 表示没有成功执行。也可以使用RETURN语句，用大于0 或小于-99的整数来定义自己的返回状态值，以表示不同 的执行结果。
order_num FROM employee e INNER JOIN sell_order s ON
e.employee_id=s.employee_id JOIN goods g ON g.goods_id=s.goods_id JOIN goods_classification gc ON gc.classification_id=g.classification_id WHERE employee_name LIKE @employee_name
10.2.2 执行存储过程
例如，执行例10-1的存储过程goods_info
在SQL查询分析器中输入命令： EXEC goods_info
运行的结果：
修改存储过程 10.2 存储过程的创建与使用
1. 使用企业管理器修改存储过程 2. 使用ALTER PROCEDURE语句修改存储过程
数据库管理与应用存储过程概述
存储过程概述什么是存储过程？存程是命名的T-SQL句的集合.它支持任何T-SQL言，包括流程控制言、DDL、DML和DCL等。
此外可以支持参数化的定内容，接受入参数并以出参数的格式向用程或批理返回多个。
使用存程可以提高性能。
例如，如果一个有五行文本，但同有5000个用同，必会增加网通信流，造成堵塞。
使网速度大大减慢。
如何减少网通信流呢？我可以将句放在存程里，存在服器上，用在只需要在客端行一条运行存程句即可，提高了用找速度。
使用存程大多是运用在重复性的操作中〔例如每个学生的成信息〕，到达一次开屡次使用，降低程序重复开。
此外使用存程可以加速整个运行的效率。
存程建后，就会将定内容存在数据中。
除第一次使用之外，其余情况可以直接从程序存中取得已行的程序，免去再一次。
使用存程能化数据管理。
当需要修改，只需要在服器上修改一次即可，不需要在所有客机器上行修改。
此外，使用存程可以增数据平安性。
用程序可以在没有象的限下，配合存程的控制行有限度地存取，以防止敏感数据〔如学生成表的成〕被任意与修改。
用户自定义存储过程创立存程分系存程和用自自定存程两大，本任主要介自定存程的建和行。
1〕建和行不参数的存程最容易生成和使用的存程是返回果集而不需要任何参数。
如所有学生成。
建不参数的存程法：CREATEPROCEDURE存程名AST-SQL句行不参数的存程只需要行代“EXEC存程名〞即可。
〔2〕建和行入参数的存程接受入参数，返回数据集的存程，种存程最常用，一般配合前端用程序行用，通指定入参数SELECT句将行果以数据集的方式返回前端用程序。
如根据学生的学号，某个学生的考成。
在定入参数，可出缺省。
建入参数存程法：CREATEPROCEDURE存程名〔参数1,⋯,参数n〕AST-SQL句参数使用方法如下：@参数名数据型[=默]行入参数的存程，需将入参数。
例如，将“学号〞存程。
如果不，默是缺省〔如果有缺省〕。
执行带参数的存储过程语法：EXEC存储过程名@参数名=值在执行存储过程时指定参数名称允许按任意顺序提供参数。
第10章-存储过程和触发器
第10章存储过程和触发器【学习目标】本章将要学习存储过程和触发器的基本概念、作用和基本操作。
本章学习要点：◆存储过程的概念、作用、分类；◆存储过程的创建、查看、修改和执行；◆触发器的主要作用、类型；◆inserted表和deleted表的作用和使用；◆触发器的创建方法、查看、修改和执行。
【学习导航】存储过程（Store Procedure）和触发器（Trigger）是SQL Server 数据库系统重要的数据库对象，在以SQL Server 2005 为后台数据库创建的应用程序中具有重要的应用价值。
本章主要内容见图10-1所示的学习导航。
图10-1 本章内容学习导航10.1 存储过程概述Transact-SQL语言是应用程序与SQL Server数据库之间的主要编程接口，大量的时间将花费在Transact-SQL 语句和应用程序代码上。
在很多情况下，许多代码被重复使用多次，每次都输入相同的代码不但繁琐，更由于在客户机上的大量命令语句逐条向SQL Server 发送，将降低系统运行效率。
因此，SQL Server提供了一种方法，它将一些固定的操作集中起来由SQL Server数据库服务器来完成，应用程序只需调用它的名称，就可实现某个特定任务，这种方法就是存储过程。
下面将详细介绍存储过程的概念、特点、创建、执行等内容。
10.1.1 存储过程的概念SQL SERVER 中T-SQL语言为了实现特定任务而将一些需要多次调用的固定的操作编写成子程序并集中以一个存储单元的形式存储在服务器上，由SQL Server数据库服务器通过子程序名来调用它们，这些子程序就是存储过程。
存储过程是一种数据库对象，存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行，具有很强的编程功能。
存储过程可以使用EXECUTE语句来运行。
在SQL Server中使用存储过程而不使用存储在客户端计算机本地的T-SQL程序有以下几个方面的好处。
存储过程和触发器课件
触发器的主要作用是用于在数据表上 自动执行一系列操作，以确保数据的 完整性和一致性。
触发器的分类与触发事件
分类
根据触发时机，触发器可分为INSERT触 发器、UPDATE触发器和DELETE触发器 。
VS
触发事件
在执行INSERT、UPDATE或DELETE操作 时，触发器会自动执行。
触发器的优缺点
作用
存储过程可以用于封装数据库操作，简化复杂的数据库操作 ，提高数据访问的效率，减少网络流量，提高数据安全性等 。
存储过程的分类
系统存储过程
系统存储过程以sp_作为前缀，主 要用于管理系统数据库的存储过 程。
自定义存储过程
用户自定义存储过程是由用户根 据自己的需求编写的存储过程。
存储过程的优缺点
触发器参数
用于传递给触发器的值或变量。
触发器返回值
触发器执行完毕后返回的值或结果。
触发器的使用示例
• 示例1：创建一个在插入操作时触发的触发器，将新插入的 记录的ID复制到另一个表中。
触发器的使用示例
AFTER INSERT ON table1
CREATE TRIGGER after_insert_example
可维护性差：随着业务逻辑的 变更，可能需要修改多个触发
器，维护成本较高。
05
触发器的创建与使用
创建触发器的基本语法
• CREATE TRIGGER 触发器名称
创建触发器的基本语法
ON 表名 FOR EACH ROW WHEN 条件
创建触发器的基本语法
BEGIN 触发器逻辑 END;
触发器的参数与返回值
存储过程和触发器课件
• 存储过程概述 • 存储过程的创建与使用 • 存储过程的调试与优化 • 触发器概述 • 触发器的创建与使用 • 触发器的调试与优化
数据库原理第10章 存储过程和触发器
Hale Waihona Puke 10.1.2 创建和执行存储过程【例3】带多个输入参数并有默认值的存储过程：查询某个学生某门课程的考试成绩，课程的默认值为“java”。 CREATE PROCEDURE p_StudentGrade3 @sname char(10), @cname char(20) = 'Java' AS SELECT Sname, Cname, Grade FROM Student s INNER JOIN SC ON s.Sno = SC.sno INNER JOIN Course c ON o = o WHERE sname = @sname AND cname = @cname
由用户自行创建的存储过程，可以输入参数、向客户端返回表格或结果、消息等，也可以返回输出函数。
10.1.2 创建和执行存储过程
1.创建存储过程 CREATE PROC[EDURE] 存储过程名 [ { @参数名 数据类型 } [ = default ] [OUTPUT] ] [ , … n ] AS SQL语句 [ … n ] 2.执行存储过程 EXEC [ UTE ] 存储过程名 [实参 [, OUTPUT] [, … n] ]
存储过程功能
接受输入参数并以输出参数的形式将多个值返回给调用者。 包含执行数据库操作的语句。 将查询语句执行结果返回到客户端内存中。
存储过程分类
存储过程类型 系统存储过程 扩展存储过程 用户自定义存储过程
系统存储过程存储以SP_为前缀,是由SQL Server2008自己创建、管理和使用的一种特殊的存储过程，不能对其进行修改或删除。如Sp_helpdb、Sp_renamedb等。
数据库基础与实践技术 （SQL Server 2008）
第10章 存储过程
<
>
10. 10.4.2
查看存储过程
第 十 章 存 储 过 程
SP_helptext查看定义存储过程的T SQL语句。 SP_helptext查看定义存储过程的T-SQL语句。 查看定义存储过程的 语句
图10-8 选择新建存储过程对话框
图10-9 选择新建存储过程对话框
图10-10 新建存储过程对话框
图10-11 设置权限对话框
使用T-SQL语句创建存储过程
创建存储过程的T-SQL语句的语法为：
第 十 章 存 储 过 程
CREATE PROC[EDURE] procedure_name[;number] [ { @parameter data_type}[VARYING] [=default] [OUTPUT] ] [,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
10.2.1 使用T-SQL语句创建存储过程
【例10-1】 创建一个存储过程Battery_order，查看“Battery”货品 的所有订货情况。
Use Market Go
第 十 章 存 储 过 程
Create Procedure Battery_Order As Select OrderID,LastName,Quantity,OrderDate,OrderSum From Customers join Orders on Customers.CustomerID=Orders.CustomerID Where Orders.GoodsName=“Battery” Go
第十章 存储过程和触发器(清华大学)ppt
Page 8
10．2．3 带输入参数的存储过程 1．建立存储过程 一个存储过程可以带一个或多个参数，输入参数是
指由调用程序向存储过程传递的参数，它们在创建 存储过程语句中被定义，在执行存储过程中给出相 应的参数值。 声明带输入参数的存储过程的语法格式如下： CREATE PROCEDURE 存储过程名 @参数名 数据类型[=默认值] [，…n]
[WITH ENCRYPTION]
[WITH RECOMPILE]
AS SQL 语句
SQL Server实用教程
23.-05.2020
Page 9
其中“@参数名”和定义局部变量一样，必须以符号@为 前缀，要指定数据类型，多个参数定义要用“，”隔开。 在执行存储过程时该参数将由指定的参数值来代替，如果 执行时未提供该参数的参数值，则使用时须定义默认值 （默认值可以是常量或空值null），否则将产生错误。
SQL Server实用教程
23.-05.2020
Page 3
10．1．3 存储过程的类型 （1）系统存储过程。 （2）本地存储过程。 （3）临时存储过程。 （4）远程存储过程。 （5）扩展存储过程。
SQL Server实用教程
23.-05.2020
Page 4
10.2 建立和执行存储过程
简单存储过程类似于将一组SQL语句起个名字，然后就可 以在需要时反复调用。复杂一些的则要有输入和输出参数。
23.-05.2020
Page 10
CREATE PROCEDURE stu_cj1 @name char(10),@cname char(16)
AS SELECT xs.学号,姓名,课程名,成绩
FROM xs INNER JOIN cj ON xs.学号=cj.学号INNER JOIN kc ON cj.课程号=kc.课程号
第10章 存储过程
版权所有：中国信息大学信息工程学院计算机系 严芬 版权所有：
• 语法如下：
EXEC procedure_name [value1,value2…]
版权所有：中国信息大学信息工程学院计算机系 严芬 版权所有：
【例】用按位置传递参数的方法执行存储过程 p_StudentPara ，分别查找班级代码为20010051 和20010059的学生记录。 EXEC p_StudentPara ‘20010051’ EXEC p_StudentPara ‘20010051’
• 注意：存储过程是SQL语句和部分流控语句的预 编译集合，存储过程被进行了编译和优化。当存 储过程第一次执行时，SQL Server为其产生查询 计划并将其保留在内存中，这样以后在调用该存 储过程时就不必再进行编译，这能在一定程度上 改善系统的性能。
版权所有：中国信息大学信息工程学院计算机系 严芬 版权所有：
第10章 存储过程 10章
概述
• 问题
要把某完成功能的SQL做成类似C语言的函数， 供需要时调用，如何做？
• 什么是存储过程？
是一组被编译在一起的T-SQL语句的集合，它 们被集合在一起以完成一个特定的任务。
版权所有：中国信息大学信息工程学院计算机系 严芬 版权所有：
• 存储过程的分类
– 系统存储过程。 – 扩展存储过程（提供从SQL Server到外部程序 的接口，以便进行各种维护活动）。 – 用户自定义的存储过程。
版权所有：中国信息大学信息工程学院计算机系 严芬 版权所有：
【练习】创建存储过程Cou_credit，要求能根据用 户给定的学分值，统计满足该学分值的课程数目， 并把它返回给调用程序。 2 如要统计2个学分的课程门数，该如何调用上述存 储过程。 【练习】创建名为mod_credit的存储过程，能修改 用户指定课程号的课程学分值，修改值也由用户 指定。
存储过程概述
存储过程概述在大型数据库系统中，存储过程和触发器具有很重要的作用。
无论是存储过程还是触发器，都是SQL 语句和流程控制语句的集合。
就本质而言，触发器也是一种存储过程。
存储过程在运算时生成执行方式，所以，以后对其再运行时其执行速度很快。
S QL Server 2000 不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。
12.1.1 存储过程的概念存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库。
中用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
在SQL Server 的系列版本中存储过程分为两类：系统提供的存储过程和用户自定义存储过程。
系统过程主要存储在master 数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。
通过系统存储过程，MS SQL Server 中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。
尽管这些系统存储过程被放在master 数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。
而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。
用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。
在本章中所涉及到的存储过程主要是指用户自定义存储过程。
12.1.2 存储过程的优点当利用MS SQL Server 创建一个应用程序时，Transaction-SQL 是一种主要的编程语言。
若运用Transaction-SQL 来进行编程，有两种方法。
其一是，在本地存储Transaction- SQL 程序，并创建应用程序向SQL Server 发送命令来对结果进行处理。
其二是，可以把部分用Transaction-SQL 编写的程序作为存储过程存储在SQL Server 中，并创建应用程序来调用存储过程，对数据结果进行处理存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者确定；返回状态值给调用者，指明调用是成功或是失败；包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。
第10章存储过程
第10章存储过程关键词：存储过程创建存储过程执行存储过程存储过程的参数查看存储过程修改存储过程删除存储过程学习要求：本章主要阐述了存储过程的创建和使用方法。
并且全面地、系统地介绍了存储过程的概念、存储过程的优点；系统存储过程的特点及用途；创建存储过程的方法；查看、修改和删除存储过程的方法。
重点分析了存储过程的创建和使用方法。
学习和掌握本章，是对SQL Server 2000数据库的灵活运用。
10.1 概述１、存储过程——是为实现某个特定任务而编写的一段代码。
２、存储过程的特点：● 可以包含一条或多条Transact-SQL语句。
● 可以接受输入参数并可以返回输出值。
● 一个存储过程可以调用另一个存储过程。
● 会返回执行情况的状态代码给调用它的程序。
３、存储过程的优点：● 实现模块化编程。
一个存储过程可以被多个用户共享和重用。
● 加快程序的运行速度。
第一次执行后的存储过程会在缓冲区中创建查询树，使得第二次执行时不用进行预编译。
● 可以减少网络流量。
存储过程存储在服务器上，只有触发执行存储过程的命令和返回结果才在网络上传输。
● 可以提高数据库安全性。
可以只授予用户访问存储过程的权限，而不授予其直接修改数据表的权限。
３、存储过程的分类：● 系统存储过程。
由系统自动创建，主要存储在master数据库中，一般以sp_为前缀。
系统存储过程完成的功能主要是从系统表中获取信息。
● 用户自定义存储过程。
由用户创建并能完成某一特定功能的存储过程。
10.2 创建存储过程１、使用CREATE PROCEDURE语句创建存储过程格式：CREATE PROC[EDURE ] procedure_name [；number][ {@parameter data_type}[VARYING ][ = default][OUTPUT]][，…n][WITH{RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}][FOR REPLICATION]AS sql_statement […n]其中各参数含义如下：● procedure_name：新存储过程的名称。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第10章存储过程关键词：存储过程创建存储过程执行存储过程存储过程的参数查看存储过程修改存储过程删除存储过程学习要求：本章主要阐述了存储过程的创建和使用方法。
并且全面地、系统地介绍了存储过程的概念、存储过程的优点；系统存储过程的特点及用途；创建存储过程的方法；查看、修改和删除存储过程的方法。
重点分析了存储过程的创建和使用方法。
学习和掌握本章，是对SQL Server 2000数据库的灵活运用。
10.1 概述１、存储过程——是为实现某个特定任务而编写的一段代码。
２、存储过程的特点：● 可以包含一条或多条Transact-SQL语句。
● 可以接受输入参数并可以返回输出值。
● 一个存储过程可以调用另一个存储过程。
● 会返回执行情况的状态代码给调用它的程序。
３、存储过程的优点：● 实现模块化编程。
一个存储过程可以被多个用户共享和重用。
● 加快程序的运行速度。
第一次执行后的存储过程会在缓冲区中创建查询树，使得第二次执行时不用进行预编译。
● 可以减少网络流量。
存储过程存储在服务器上，只有触发执行存储过程的命令和返回结果才在网络上传输。
● 可以提高数据库安全性。
可以只授予用户访问存储过程的权限，而不授予其直接修改数据表的权限。
３、存储过程的分类：● 系统存储过程。
由系统自动创建，主要存储在master数据库中，一般以sp_为前缀。
系统存储过程完成的功能主要是从系统表中获取信息。
● 用户自定义存储过程。
由用户创建并能完成某一特定功能的存储过程。
10.2 创建存储过程１、使用CREATE PROCEDURE语句创建存储过程格式：CREATE PROC[EDURE ] procedure_name [； number][ {@parameter data_type}[VARYING ][ = default][OUTPUT]][，…n][WITH{RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}][FOR REPLICATION]AS sql_statement […n ]其中各参数含义如下：● procedure_name：新存储过程的名称。
● number：对同名的过程分组。
● @parameter：过程中的参数。
可以声明一个或多个参数。
存储过程最多可以有2100个参数。
● data_type：参数的数据类型。
● VARYING：指定作为输出参数支持的结果集(由存储过程动态构造，内容可以变化)。
仅适用于游标参数。
● default：参数的默认值。
如果定义了默认值，不必指定该参数的值即可执行过程。
默认值必须是常量或NULL。
● OUTPUT：表明参数是返回参数。
该选项的值可以返回给EXE[UTE]。
使用OUTPUT参数可将信息返回给调用过程。
● RECOMPILE：表明SQL Server不会缓存该过程的计划，该过程将在运行时重新编译。
● ENCRYPTION：表示SQL Server加密syscomments表中包含CREATE PROCEDURE语句文本的条目。
● FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。
● sql_statement：过程中要包含的任意数目和类型的Transact-SQL语句。
但有一些限制。
创建存储过程时应该注意下面几点：● 存储过程的最大大小为128MB。
● 用户定义的存储过程只能在当前数据库中创建(临时过程除外，临时过程总是在tempdb中创建)。
● 在单个批处理中，CREATE PROCEDURE语句不能与其他Transact-SQL语句组合使用。
● 存储过程可以嵌套使用，在一个存储过程中可以调用其他的存储过程。
嵌套的最大深度不能超过32层。
● 存储过程如果创建了临时表，则该临时表只能用于该存储过程，而且当存储过程执行完毕后，临时表自动被删除。
● 创建存储过程时，“sq_statement”不能包含下面的Transact-SQL语句：SET SHOWPLAN_TEXT、SET SHOWMAN_ALL、CREATE VIEW、CREATE DEFAULT、CREATE RULE、CREATE PROCEDURE和CREATE TRIGGER。
例1：创建用于检索所有学生的成绩记录的存储过程stud_degree。
USE school--判断stud_degree存储过程是否存在，若存在，则删除IF EXISTS (SELECT name FROM sysobjectsWHERE name = ’stud_degree’ AND type =’P’)DROP PROCEDURE stud_degreeGOUSE schoolGO--创建存储过程stud_degreeCREATE PROCEDURE stud_degree /* 无参过程 */ASSELECT student.sno，student.sname，ame，score.degreeFROM student，course，scoreWHERE student.sno=score.sno AND o=oORDER BY student.snoGO通过下述SQL语句执行该存储过程：USE school--判断stud_degree存储过程是否存在，若存在，则执行它IF EXISTS (SELECT name FROM sysobjectsWHERE name = ’stud_degree’ AND type =’P’)EXEC stud_degreeGO例2：创建一个带有参数的存储过程stu_info，该存储过程根据传入的学生编号，在student表中查询此学生的信息。
USE school--判断stud_info存储过程是否存在，若存在，则删除IF EXISTS (SELECT name FROM sysobjectsWHERE name = ’stud_info’ AND type =’P’)DROP PROCEDURE stud_infoGOUSE schoolGO--创建存储过程stud_infoCREATE PROCEDURE stud_info@s_no char(5) /* 有参过程·形参 */ASSELECT *FROM studentWHERE sno=@s_noGO通过下述SQL语句执行该存储过程：USE school--判断stud_info存储过程是否存在，若存在，则执行它IF EXISTS (SELECT name FROM sysobjectsWHERE name = ’stud_info’ AND type =’P’)EXEC stud_info ‘105’/*实参*/GO例3：创建一个带有参数的存储过程stu_age，该存储过程根据传入的学生编号，在student表中计算此学生的年龄，并根据程序的执行结果返回不同的值，程序执行成功，返回整数0，如果执行出错，则返回错误号。
USE school--判断stud_age存储过程是否存在，若存在，则删除IF EXISTS (SELECT name FROM sysobjectsWHERE name = ’stud_age’ AND type =’P’)DROP PROCEDURE stud_ageGOUSE schoolGO--创建存储过程stud_ageCREATE PROCEDURE stud_age@s_no char(5), /* 有参过程 */@age int OUTPUT /* 返回参数 */AS-- 定义并初始化局部变量，用于保存返回值DECLARE @errorvalue intSET @errorvalue=0-- 求此学生的年龄SELECT @age=YEAR(GETDATE())-YEAR(sbirthday)FROM studentWHERE sno=@s_no-- 根据程序的执行结果返回不同的值IF (@@ERROR<>0)SET @errorvalue=@@ERRORRETURN @errorvalue /* 带回结果值 */GO该过程的调用在后面介绍。
２、使用企业管理器创建存储过程使用企业管理器创建存储过程的操作步骤如下：（1）打开企业管理器，展开服务器组，并展开相应的服务器。
（2）打开“数据库”文件夹，并打开要创建存储过程的数据库。
（3）选择“存储过程”选项，右击鼠标，执行“新建存储过程”命令，打开创建存储过程对话框。
（4）在“文本”列表框中显示了CREATE PROCEDURE语句的框架，可以修改要创建的存储过程的名称，然后加入存储过程所包含的SQL语句。
（5）单击“检查语法”按钮可以检查创建存储过程的SQL语句的语法是否正确。
（6）如果要将其设置为下次创建存储过程的模板，可单击“另存为模板”按钮。
（7）完成后，单击“确定”按钮即可创建一个存储过程，如下图10-1所示。
图10-1存储过程属性界面３、使用向导创建存储过程使用向导创建一个存储过程insert_table8_1，对应的操作步骤如下：（1）在企业管理器中，执行“工具”下拉菜单中的“向导”命令，打开“选择向导”对话框。
（2）在“数据库”文件夹选择“创建存储过程”向导，单击“确定”按钮，出现创建存储过程向导欢迎对话框。
单击“下一步”按钮。
（3）在出现“选择数据库”对话框中，选择数据库后，单击“下一步”按钮，如下图10-2所示。
图10-2选择向导界面（3）在出现“选择数据库”对话框中，选择数据库后，单击“下一步”按钮，如下图10-3所示。
图10-3创建存储过程向导1（4）在出现“选择存储过程”对话框中列出了所有表，以及可以对表进行的插入、删除和更新操作。
可以通过选中每个表对应的复选框来确定要对表进行的操作。
例如，选择table8表后面的“插入”、“删除”、“更新”栏中的复选框。
单击“下一步”按钮，如下图10-4所示。
图10-4创建存储过程向导2（5）出现“正在完成创建存储过程向导”对话框。
若单击“完成”按钮，即可完成存储过程的创建，如下图10-5所示。
图10-5创建存储过程向导3（6）单击“编辑”按钮，打开“编辑存储过程属性”对话框，可编辑存储过程，如下图10-6所示。
图10-6编辑存储过程属性（7）单击“编辑SQL”按钮，即可打开“编辑存储过程SQL”对话框，其中的列表框显示了创建该存储过程的Transact-SQL语句，可以在已有的Transact-SQL语句的基础上进行编辑，可以单击“分析”按钮来执行语法检查，如下图10-7所示。
图10-7编辑存储过程SQL（8）单击“确定”按钮，返回到前面的“正在完成创建存储过程向导”对话框。