计算机原理 第9章 存储过程与触发器
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
存储过程的语法如下。
命令格式：
EXEC[UTE] 存储过程名
{ @字符串变量|[N]’SQL语句字符 串’}[+…n]
第十六页，共114页。
第9章 存储过程与触发器
9．5 修改存储过程
9．5．1 更改存储过程名称 [实例9.6] 将[实例9.3]所创建的名为pro_new存储过
程，修改成名为pro_存储过程。
第9章 存储过程与触发器
9．3存储过程创建 9．3．1使用企业管理器创建存储过程 [实例9.3] 在企业管理器中建立名为pro_new的存
储过程，并执行。 操作步骤：
第九页，共114页。
第9章 存储过程与触发器 归纳分析：
如果是查看或修改已有存储过程的定义， 则右击要查看或修改的存储过程，在弹出的快 捷菜单中，选择“属性”命令，则调出“存储 过程属性”窗口，在这里可以查看或修改存储 过程的定义。
第三页，共114页。
第9章 存储过程与触发器
存储过程具有如下优点：
1. 存储过程将一系列复杂的T-SQL代码封装在一起作为 数据库对象存放在数据库 中，用户使用时不必接触TSQL而仅需直接调用即可得到所需结果，简化了用户的操 作。
2. 存储过程已经被编译，执行时省去了编译与优化的 时间。另外，第一次从磁盘调用存储过程后，它将驻留在 内存中，下一次使用时可以直接从内存中调用，因此对于 需要多次调用的存储过程而言，速度明显加快。
3.在分布式查询中，调用存储过程的语句将比直接使用TSQL的语句少得多，这将大大减少网络流量。
4.通过适当的权限设置，可以使系统的安全性得到更有 效的保障。
第四页，共114页。
第9章 存储过程与触发器 9．2 存储过程分类
[实例9.2] 查看系统存储过程。 操作步骤：
第五页，共114页。
第9章 存储过程与触发器
归纳分析： SQL Server的存储过程分为三大类： 1．系统存储过程 系统存储过程是SQL Server内置的存储过程，
可以直接用于执行一些操作，用于进行系统管理、 登录管理、权限设置、数据库对象管理、查看数 据库信息、数据库复制等操作。系统存储过程存 放在master数据库中，以sp_作为前缀。在任何 数据库中无需用master限定就直接执行。
一个永久或临时存储过程。
命令格式： CREATE PROCEDURE 存储过程名 [WITH ENCRYPTION]
[WITH RECOMPILE]
AS
SQL 语句
命令说明： （1）WITH ENCRYPTION：对存储过程进行加密。
（2）WITH RECOMPILE：对存储过程重新编译。
第二十六页，共114页。
使用企业管理器修改存储过程名称的方法也类似于修 改表名称的方法，即展开指定的数据库节点，单击存储过 程，在右边的窗口中选中一个需要修改名称的存储过程右 击之，在弹出的快捷菜单中选择“重命名”。
第二十七页，共114页。
第9章 存储过程与触发器
9．6 删除存储过程 9．6．1使用企业管理器删除存储过程 [实例9.9] 使用企业管理器删除存储过程。
（2）WITH RECOMPILE：对存储过程重新编译。
第十四页，共114页。
第9章 存储过程与触发器
9．4 执行存储过程 [实例9.5] 分别执行[实例9.3]、[实例9.4]建立存
储过程。 操作步骤：
第十五页，共114页。
第9章 存储过程与触发器
归纳分析：
注意：执行存储过程时，区分大小写。 存储过程一旦编写好后，就可以调用执行。执行
sp_rename 原存储过程名，新存储过程名
修改存储过程名称的方法与修改表名称的方法类同， 读者可以参照执行。
使用企业管理器修改存储过程名称的方法也类似于 修改表名称的方法，即展开指定的数据库节点，单击存 储过程，在右边的窗口中选中一个需要修改名称的存储 过程右击之，在弹出的快捷菜单中选择“重命名”。
第二十一页，共114页。
第9章 存储过程与触发器 9．6 删除存储过程 9．6．1使用企业管理器删除存储过程 [实例9.9] 使用企业管理器删除存储过程。
操作步骤：
第二十二页，共114页。
第9章 存储过程与触发器
存储过程与触发器是SQL Server中的两类数 据库对象。它们都是由T-SQL语句编写而成的过 程，所不同的是存储过程是由用户根据需要调用 执行的，而触发器则是由某个动作(如删除或修 改一条记录)引发执行的。另外，存储过程可以 不依附于表而单独存在，而触发器则必须依附于 一个特定的表。它们与函数也不同，函数可以直 接通过函数名返回数值，其返回值可以直接在表 达式中使用，而存储过程与触发器则不能直接通 过其名称带回返回值，也不能直接在表达式中使 用。
第六页，共114页。
第9章 存储过程与触发器
2．临时存储过程
临时存储过程与临时表类似。是对用户定义的存储过程。 以#、或##开头的存储过程。无论用户在哪个数据库中创建，它 们都存放在临时数据库tempdb中。以#开头的存储过程是局部临 时存储过程，它仅能由其创建者本人在创建完后立即调用，一 旦该创建者断开与数据库的连接，局部临时存储过程立即被删 除。以##开头的存储过程是全局临时存储过程，它可以由创建 者本人和其他用户在创建完后共同调用，一旦创建者断开与数 据库的连接，则不再允许新的用户使用，而且等其他正在使用 该存储过程的用户使用完毕后，全局临时存储过程也被删除。
第一页，共114页。
第9章 存储过程与触发器
存储过程与触发器是SQL Server中的两类 数据库对象。它们都是由T-SQL语句编写而成的 过程，所不同的是存储过程是由用户根据需要 调用执行的，而触发器则是由某个动作(如删除 或修改一条记录)引发执行的。另外，存储过程 可以不依附于表而单独存在，而触发器则必须 依附于一个特定的表。它们与函数也不同，函 数可以直接通过函数名返回数值，其返回值可 以直接在表达式中使用，而存储过程与触发器 则不能直接通过其名称带回返回值，也不能直 接在表达式中使用。
第十八页，共114页。
第9章 存储过程与触发器
9．5．2 修改存储过程的参数与定义 1. 使用企业管理器修改存储过程
[实例9.7] 使用企业管理器修改存储过程[实例 9.4]，增加显示课程号的列。 操作步骤：
第十九页，共114页。
第9章 存储过程与触发器 2. 使用T-SQL语句修改存储过程 [实例9.8] 在[实例9.4]创建的存储过程，修改查
询记录按名次排列。 操作步骤：
第二十页，共114页。
第9章 存储过程与触发器
归纳分析：
修改已经创建的存储过程，可以不更改其用户的使用权限， 也不更改其名称，因此不会破坏其他相关程序的引用关系。 命令格式：
ALTER PROC[EDURE] 存储过程名 [WITH ENCRYPTION ]
[WITH RECOMPILE ] AS SQL 语句 命令说明： 其中各项语法含义与创建存储过程类似。
第9章 存储过程与触发器
学习目标： 通过本章学习，你能够学会： 1. 了解存储过程与触发器的特性
2. 了解存储过程与触发器的优点
3. 应熟练掌握使用企业管理器创建、修改、删除 和查看存储过程与与触发器的操作。
4. 应熟练掌握使用T-SQL语句创建、修改、删除和查看 存储过程，及如何执行存储过程与与触发器的操作。
第二十四页，共114页。
第9章 存储过程与触发器
归纳分析：
如果是查看或修改已有存储过程的定义， 则右击要查看或修改的存储过程，在弹出的快 捷菜单中，选择“属性”命令，则调出“存储 过程属性”窗口，在这里可以查看或修改存储 过程的定义。
第二十五页，共114页。
第9章 存储过程与触发器
归纳分析： 在SQL Server中，使用CREATE PROCEDURE语句创建
[ 实 例 9.4] 在 “ 学 生 ” 数 据 库 中 建 立 一 个 存 储 过 程 pro_new1，用于查询所有学生的学号和数学期中成绩、 计算机期中成绩，要求数学期中成绩和计算机期中成绩 分别列出。已知数学期中成绩的课程号是1002、计算机 期中成绩的课程号是2005。 操作步骤：
第十三页，共114页。
第十页，共114页。
第9章 存储过程与触发器
9．3．2 使用T-SQL语句创建存储过程
[实例9.4] 在“学生”数据库中建立一个存储过程pro_new1，用
于查询所有学生的学号和数学期中成绩、计算机期中成绩，要 求数学期中成绩和计算机期中 成绩分别列出。 已知数学期中成
绩的课程号是 1002、计算机期中
第七页，共114页。
第9章 存储过程与触发器 3. 扩展存储过程
是利用高级语言(如C语言)编写的存储过程，是 SQL Server可以动态装载并执行的动态链接库(DLL)。 扩展存储过程只能添加到master数据库中。本章不介 绍扩展存储过程有关内容，读者可以参考其他书籍有 关内容。
第八页，共114页。
成绩的课程号是2005。
操作步骤：
第十一页，共114页。
第9章 存储过程与触发器
归纳分析：
如果是查看或修改已有存储过程的定义，则右击 要查看或修改的存储过程，在弹出的快捷菜单中，选 择“属性”命令，则调出“存储过程属性”窗口，在 这里可以查看或修改存储过程的定义。
第十二页，共114页。
第9章 存储过程与触发器 9．3．2 使用T-SQL语句创建存储过程
第9章 存储过程与触发器
归纳分析： 只有数据库所有者和存储过程的所有者才有权修改存
储过程名称。不宜轻易修改存储过程名称，因为系统中许 多应用程序可能都要调用存储过程，如果修改了存储过程 的名称，则这些应用程序都要随之改动。 命令格式：
sp_rename 原存储过程名，新存储过程名
修改存储过程名称的方法与修改表名称的方法类同，读者 可以参照执行。
第二十三页，共114页。
第9章 存储过程与触发器
归纳分析：
SQL Server的存储过程分为三大类： 1．系统存储过程
系统存储过程是SQL Server内置的存储过程， 可以直接用于执行一些操作，用于进行系统管理、 登录管理、权限设置、数据库对象管理、查看数 据库信息、数据库复制等操作。系统存储过程存 放在master数据库中，以sp_作为前缀。在任何 数据库中无需用master限定就直接执行。
操作步骤： （1） 启动“查询分析器”，输入如下SQL语句：
sp_rename pro_new , pro_存储过程
（2）按“F5”键或单击工具栏“执行查询”图标。
第十七页，共114页。
第9章 存储过程与触发器
归纳分析： 只有数据库所有者和存储过程的所有者才有权修改存储过程
名称。不宜轻易修改存储过程名称，因为系统中许多应用程序 可能都要调用存储过程，如果修改了存储过程的名称，则这些 应用程序都要随之改动。 命令格wk.baidu.com：
第二页，共114页。
第9章 存储过程与触发器
9．1 存储过程概念
[实例9.1] 了解存储过程的执行过程，如图9.1所示。
调用存储过 程
（输入参数）
存储过 程
输出参数
执行动作 返回状态
值
归纳分析：
在SQL Server中定义的过程被称为存储过程。存储过程 是一组预先编译好的T-SQL代码，作为一个整体用于执行特 定的操作。存储过程属于数据库对象，它们存放在数据库中， 需要时用户可以调用。
第9章 存储过程与触发器
归纳分析：
在SQL Server中，使用CREATE PROCEDURE语句创建一 个永久或临时存储过程。
命令格式：
CREATE PROCEDURE 存储过程名
[WITH ENCRYPTION]
[WITH RECOMPILE]
AS
SQL 语句
命令说明： （1）WITH ENCRYPTION：对存储过程进行加密。
操作步骤：
第二十八页，共114页。
第9章 存储过程与触发器
9．6．2 使用T-SQL语言删除存储过程
[实例9.10] 删除Pro_new存储过程。 操作步骤：
（1） 启动“查询分析器”，输入如下SQL语句：
DROP PROCEDURE Pro_new （2）按“F5”键或单击工具栏“执行查询”图标。 归纳分析：
不再需要存储过程时，可以利用企业管理器或T-SQL语言删除。 命令格式：
DROP PROCEDURE 存储过程名称或存储过程组名称
注意：删除存储过程组时不必写出下标，即可将组中所有成员 全部删除。
第二十九页，共114页。
命令格式：
EXEC[UTE] 存储过程名
{ @字符串变量|[N]’SQL语句字符 串’}[+…n]
第十六页，共114页。
第9章 存储过程与触发器
9．5 修改存储过程
9．5．1 更改存储过程名称 [实例9.6] 将[实例9.3]所创建的名为pro_new存储过
程，修改成名为pro_存储过程。
第9章 存储过程与触发器
9．3存储过程创建 9．3．1使用企业管理器创建存储过程 [实例9.3] 在企业管理器中建立名为pro_new的存
储过程，并执行。 操作步骤：
第九页，共114页。
第9章 存储过程与触发器 归纳分析：
如果是查看或修改已有存储过程的定义， 则右击要查看或修改的存储过程，在弹出的快 捷菜单中，选择“属性”命令，则调出“存储 过程属性”窗口，在这里可以查看或修改存储 过程的定义。
第三页，共114页。
第9章 存储过程与触发器
存储过程具有如下优点：
1. 存储过程将一系列复杂的T-SQL代码封装在一起作为 数据库对象存放在数据库 中，用户使用时不必接触TSQL而仅需直接调用即可得到所需结果，简化了用户的操 作。
2. 存储过程已经被编译，执行时省去了编译与优化的 时间。另外，第一次从磁盘调用存储过程后，它将驻留在 内存中，下一次使用时可以直接从内存中调用，因此对于 需要多次调用的存储过程而言，速度明显加快。
3.在分布式查询中，调用存储过程的语句将比直接使用TSQL的语句少得多，这将大大减少网络流量。
4.通过适当的权限设置，可以使系统的安全性得到更有 效的保障。
第四页，共114页。
第9章 存储过程与触发器 9．2 存储过程分类
[实例9.2] 查看系统存储过程。 操作步骤：
第五页，共114页。
第9章 存储过程与触发器
归纳分析： SQL Server的存储过程分为三大类： 1．系统存储过程 系统存储过程是SQL Server内置的存储过程，
可以直接用于执行一些操作，用于进行系统管理、 登录管理、权限设置、数据库对象管理、查看数 据库信息、数据库复制等操作。系统存储过程存 放在master数据库中，以sp_作为前缀。在任何 数据库中无需用master限定就直接执行。
一个永久或临时存储过程。
命令格式： CREATE PROCEDURE 存储过程名 [WITH ENCRYPTION]
[WITH RECOMPILE]
AS
SQL 语句
命令说明： （1）WITH ENCRYPTION：对存储过程进行加密。
（2）WITH RECOMPILE：对存储过程重新编译。
第二十六页，共114页。
使用企业管理器修改存储过程名称的方法也类似于修 改表名称的方法，即展开指定的数据库节点，单击存储过 程，在右边的窗口中选中一个需要修改名称的存储过程右 击之，在弹出的快捷菜单中选择“重命名”。
第二十七页，共114页。
第9章 存储过程与触发器
9．6 删除存储过程 9．6．1使用企业管理器删除存储过程 [实例9.9] 使用企业管理器删除存储过程。
（2）WITH RECOMPILE：对存储过程重新编译。
第十四页，共114页。
第9章 存储过程与触发器
9．4 执行存储过程 [实例9.5] 分别执行[实例9.3]、[实例9.4]建立存
储过程。 操作步骤：
第十五页，共114页。
第9章 存储过程与触发器
归纳分析：
注意：执行存储过程时，区分大小写。 存储过程一旦编写好后，就可以调用执行。执行
sp_rename 原存储过程名，新存储过程名
修改存储过程名称的方法与修改表名称的方法类同， 读者可以参照执行。
使用企业管理器修改存储过程名称的方法也类似于 修改表名称的方法，即展开指定的数据库节点，单击存 储过程，在右边的窗口中选中一个需要修改名称的存储 过程右击之，在弹出的快捷菜单中选择“重命名”。
第二十一页，共114页。
第9章 存储过程与触发器 9．6 删除存储过程 9．6．1使用企业管理器删除存储过程 [实例9.9] 使用企业管理器删除存储过程。
操作步骤：
第二十二页，共114页。
第9章 存储过程与触发器
存储过程与触发器是SQL Server中的两类数 据库对象。它们都是由T-SQL语句编写而成的过 程，所不同的是存储过程是由用户根据需要调用 执行的，而触发器则是由某个动作(如删除或修 改一条记录)引发执行的。另外，存储过程可以 不依附于表而单独存在，而触发器则必须依附于 一个特定的表。它们与函数也不同，函数可以直 接通过函数名返回数值，其返回值可以直接在表 达式中使用，而存储过程与触发器则不能直接通 过其名称带回返回值，也不能直接在表达式中使 用。
第六页，共114页。
第9章 存储过程与触发器
2．临时存储过程
临时存储过程与临时表类似。是对用户定义的存储过程。 以#、或##开头的存储过程。无论用户在哪个数据库中创建，它 们都存放在临时数据库tempdb中。以#开头的存储过程是局部临 时存储过程，它仅能由其创建者本人在创建完后立即调用，一 旦该创建者断开与数据库的连接，局部临时存储过程立即被删 除。以##开头的存储过程是全局临时存储过程，它可以由创建 者本人和其他用户在创建完后共同调用，一旦创建者断开与数 据库的连接，则不再允许新的用户使用，而且等其他正在使用 该存储过程的用户使用完毕后，全局临时存储过程也被删除。
第一页，共114页。
第9章 存储过程与触发器
存储过程与触发器是SQL Server中的两类 数据库对象。它们都是由T-SQL语句编写而成的 过程，所不同的是存储过程是由用户根据需要 调用执行的，而触发器则是由某个动作(如删除 或修改一条记录)引发执行的。另外，存储过程 可以不依附于表而单独存在，而触发器则必须 依附于一个特定的表。它们与函数也不同，函 数可以直接通过函数名返回数值，其返回值可 以直接在表达式中使用，而存储过程与触发器 则不能直接通过其名称带回返回值，也不能直 接在表达式中使用。
第十八页，共114页。
第9章 存储过程与触发器
9．5．2 修改存储过程的参数与定义 1. 使用企业管理器修改存储过程
[实例9.7] 使用企业管理器修改存储过程[实例 9.4]，增加显示课程号的列。 操作步骤：
第十九页，共114页。
第9章 存储过程与触发器 2. 使用T-SQL语句修改存储过程 [实例9.8] 在[实例9.4]创建的存储过程，修改查
询记录按名次排列。 操作步骤：
第二十页，共114页。
第9章 存储过程与触发器
归纳分析：
修改已经创建的存储过程，可以不更改其用户的使用权限， 也不更改其名称，因此不会破坏其他相关程序的引用关系。 命令格式：
ALTER PROC[EDURE] 存储过程名 [WITH ENCRYPTION ]
[WITH RECOMPILE ] AS SQL 语句 命令说明： 其中各项语法含义与创建存储过程类似。
第9章 存储过程与触发器
学习目标： 通过本章学习，你能够学会： 1. 了解存储过程与触发器的特性
2. 了解存储过程与触发器的优点
3. 应熟练掌握使用企业管理器创建、修改、删除 和查看存储过程与与触发器的操作。
4. 应熟练掌握使用T-SQL语句创建、修改、删除和查看 存储过程，及如何执行存储过程与与触发器的操作。
第二十四页，共114页。
第9章 存储过程与触发器
归纳分析：
如果是查看或修改已有存储过程的定义， 则右击要查看或修改的存储过程，在弹出的快 捷菜单中，选择“属性”命令，则调出“存储 过程属性”窗口，在这里可以查看或修改存储 过程的定义。
第二十五页，共114页。
第9章 存储过程与触发器
归纳分析： 在SQL Server中，使用CREATE PROCEDURE语句创建
[ 实 例 9.4] 在 “ 学 生 ” 数 据 库 中 建 立 一 个 存 储 过 程 pro_new1，用于查询所有学生的学号和数学期中成绩、 计算机期中成绩，要求数学期中成绩和计算机期中成绩 分别列出。已知数学期中成绩的课程号是1002、计算机 期中成绩的课程号是2005。 操作步骤：
第十三页，共114页。
第十页，共114页。
第9章 存储过程与触发器
9．3．2 使用T-SQL语句创建存储过程
[实例9.4] 在“学生”数据库中建立一个存储过程pro_new1，用
于查询所有学生的学号和数学期中成绩、计算机期中成绩，要 求数学期中成绩和计算机期中 成绩分别列出。 已知数学期中成
绩的课程号是 1002、计算机期中
第七页，共114页。
第9章 存储过程与触发器 3. 扩展存储过程
是利用高级语言(如C语言)编写的存储过程，是 SQL Server可以动态装载并执行的动态链接库(DLL)。 扩展存储过程只能添加到master数据库中。本章不介 绍扩展存储过程有关内容，读者可以参考其他书籍有 关内容。
第八页，共114页。
成绩的课程号是2005。
操作步骤：
第十一页，共114页。
第9章 存储过程与触发器
归纳分析：
如果是查看或修改已有存储过程的定义，则右击 要查看或修改的存储过程，在弹出的快捷菜单中，选 择“属性”命令，则调出“存储过程属性”窗口，在 这里可以查看或修改存储过程的定义。
第十二页，共114页。
第9章 存储过程与触发器 9．3．2 使用T-SQL语句创建存储过程
第9章 存储过程与触发器
归纳分析： 只有数据库所有者和存储过程的所有者才有权修改存
储过程名称。不宜轻易修改存储过程名称，因为系统中许 多应用程序可能都要调用存储过程，如果修改了存储过程 的名称，则这些应用程序都要随之改动。 命令格式：
sp_rename 原存储过程名，新存储过程名
修改存储过程名称的方法与修改表名称的方法类同，读者 可以参照执行。
第二十三页，共114页。
第9章 存储过程与触发器
归纳分析：
SQL Server的存储过程分为三大类： 1．系统存储过程
系统存储过程是SQL Server内置的存储过程， 可以直接用于执行一些操作，用于进行系统管理、 登录管理、权限设置、数据库对象管理、查看数 据库信息、数据库复制等操作。系统存储过程存 放在master数据库中，以sp_作为前缀。在任何 数据库中无需用master限定就直接执行。
操作步骤： （1） 启动“查询分析器”，输入如下SQL语句：
sp_rename pro_new , pro_存储过程
（2）按“F5”键或单击工具栏“执行查询”图标。
第十七页，共114页。
第9章 存储过程与触发器
归纳分析： 只有数据库所有者和存储过程的所有者才有权修改存储过程
名称。不宜轻易修改存储过程名称，因为系统中许多应用程序 可能都要调用存储过程，如果修改了存储过程的名称，则这些 应用程序都要随之改动。 命令格wk.baidu.com：
第二页，共114页。
第9章 存储过程与触发器
9．1 存储过程概念
[实例9.1] 了解存储过程的执行过程，如图9.1所示。
调用存储过 程
（输入参数）
存储过 程
输出参数
执行动作 返回状态
值
归纳分析：
在SQL Server中定义的过程被称为存储过程。存储过程 是一组预先编译好的T-SQL代码，作为一个整体用于执行特 定的操作。存储过程属于数据库对象，它们存放在数据库中， 需要时用户可以调用。
第9章 存储过程与触发器
归纳分析：
在SQL Server中，使用CREATE PROCEDURE语句创建一 个永久或临时存储过程。
命令格式：
CREATE PROCEDURE 存储过程名
[WITH ENCRYPTION]
[WITH RECOMPILE]
AS
SQL 语句
命令说明： （1）WITH ENCRYPTION：对存储过程进行加密。
操作步骤：
第二十八页，共114页。
第9章 存储过程与触发器
9．6．2 使用T-SQL语言删除存储过程
[实例9.10] 删除Pro_new存储过程。 操作步骤：
（1） 启动“查询分析器”，输入如下SQL语句：
DROP PROCEDURE Pro_new （2）按“F5”键或单击工具栏“执行查询”图标。 归纳分析：
不再需要存储过程时，可以利用企业管理器或T-SQL语言删除。 命令格式：
DROP PROCEDURE 存储过程名称或存储过程组名称
注意：删除存储过程组时不必写出下标，即可将组中所有成员 全部删除。
第二十九页，共114页。