资源简介

(共31张PPT)
第8章 存储过程
8.1 存储过程概述
执行速度快。
减少网络通信量。
规范程序设计。
提高系统安全性。
8.1.1 存储过程基本概念
它能够包含执行各种数据库操作的语句，并且可以调用其他的存储过程。
能够接受输入参数，并以输出参数的形式将多个数据值返回给调用程序（Calling Procedure）或批处理（Batch）。
向调用程序或批处理返回一个状态值，以表明成功或失败（以及失败的原因）。
8.1.2 存储过程的类型
系统存储过程
本地存储过程
临时存储过程
远程存储过程
扩展存储过程
8.1.3 存储过程的作用
通过本地存储、代码预编译和缓存技术实现高性能的数据操作。
通过通用编程结构和过程实现编程框架。
通过隔离和加密的方法提高数据库的安全性。
8.2 创建和执行存储过程
8.2.1 创建和执行不带参数的存储过程
创建存储过程语法格式如下：
CREATE PROC[EDURE] procedure_name [; number]
[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
【例8-1】
使用Transact-SQL语句在“学生选课”数据库中创建一个名为p_jiaoshi1的存储过程。该存储过程返回“教师”表中所有学历为“硕士研究生”的记录。
【例8-2】
使用Transact-SQL语句执行例8-1中创建的存储过程
例8-3
在SQL Server Management Studio查询分析器窗口中查看存储过程p_jiaoshi1的属性
8.2.2 创建和执行带输入参数的存储过程
创建带输入参数的存储过程
CREATE PROCEDURE procedure_name
@parameter_name datatype[=default]
[WITH ENCRYPTION]
[WITH RECOMPILE]
AS
Sql_statement
例8-4
使用Transact-SQL语句在“学生选课”数据库中创建一个名为p_jiaoshi2的存储过程。该存储过程能根据用户给定的学历值查询返回“教师”表中对应的记录。
8.2.2 创建和执行带输入参数的存储过程
执行带输入参数的存储过程，有两种方法：一种是使用参数名传递参数值；另一种是按位置传递参数值。
（1）使用参数名传递参数值
在执行存储过程的语句中，通过语句@parameter_name=value给参数的传递值。当存储过程含有多个输入参数时，参数值可以按任意顺序给定，对于允许空值和具有默认值的输入参数可以不给出参数的传递值。
【例8-5】
用参数名传递参数值的方法执行存储过程p_jiaoshi2，分别查询学历为“本科”和“博士研究生”的记录。
（2）按位置传递参数
在执行存储过程的语句中，不通过参数名传递参数值而直接给出参数的值。当存储过程含有多个输入参数时，采用这种方式传递值，给定参数值的顺序必须与存储过程中定义的输入变量的顺序一致。按位置传递参数时，也可以忽略空值和具有默认值的参数，但不能因此破坏输入参数的设定顺序。其语法格式如下：
[EXEC[UTE] procedure_name [value1,value2,…]
8.2.2 创建和执行带输入参数的存储过程
例8-6
用按位置传递参数值的方法执行存储过程p_jiaoshi2，分别查找学历为“本科”和“博士研究生”的记录。
8.2.3 创建和执行带输出参数的存储过程
如果需要从存储过程中返回一个或多个值，可以通过在创建存储过程的语句中定义输出参数来实现。定义输出参数，需要在CREATE PROCEDURE语句中定义参数时在参数名后面指定OUTPUT关键字。语法格式如下：
@parameter_name datatype[=default] OUTPUT
例8-7
创建存储过程p_jiaoshi3，要求能根据用户给定的学历值，统计出“教师”表的所有教师中，学历为该值的教师人数，并将结果以输出变量的形式返回给调用者。
例8-8
执行存储过程p_jiaoshi3，统计教师表中学历为“硕士研究生”的教师人数。
例8-9
在“学生选课”数据库中创建存储过程p_jiaoshi4，要求能根据用户给定的性别，统计“教师”表性别为该值的教师人数，并将结果以输出变量的形式返回给用户。
例8-10
执行存储过程p_jiaoshi4，统计教师表中性别为“男”的教师人数。
8.3 管理存储过程
8.3.1 查看存储过程
（1）sp_help用于显示存储过程的参数及其数据类型。sp_help语法格式如下：
sp_help [[@objname=]name]
（2）sp_helptext用于显示存储过程的定义语句。sp_helptext语法格式如下：
sp_helptext [@objname=]'name'
例8-11
查看存储过程p_jiaoshi3的信息
例8-12
查看存储过程p_jiaoshi3的定义语句
8.3.2 修改存储过程
修改存储过程是由ALTER PROCEDURE语句来完成的，其语法格式如下：
ALTER PROCEDURE procedure_name
@parameter_name datatype[=default][OUTPUT]
[WITH ENCRYPTION]
[WITH RECOMPILE]
AS
Sql_statement
例8-13
使用Transact-SQL语句修改存储过程p_jiaoshi1，根据用户提供的学历进行模糊查询，并要求加密。
例8-14
执行修改后的存储过程p_jiaoshi1，查询学历里有“研究生”的教师信息
8.3.3 删除存储过程
存储过程的删除是通过DROP PROCEDURE语句来实现的。其语法格式为：
DROP PROCEDURE name
参数name为要删除的存储过程的名字。
例8-15
使用Transact-SQL语句删除存储过程p_jiaoshi2。对应的SQL语句如下：
USE 学生选课
GO
DROP PROCEDURE p_jiaoshi2
GO
8.3.4 存储过程的重命名
可通过使用SQL Server Management Studio和Transact-SQL语句两种方法实现。
【例8-15】使用SQL Server Management Studio将存储过程p_jiaoshi1重命名为p_js1。
在SQL Server Management Studio的“对象资源管理器”中直接选择“学生选课”数据库，在展开的“可编程性”中，展开“存储过程”，找到需要修改的存储过程p_jiaoshi1，用鼠标右键单击存储过程p_jiaoshi1，在弹出的快捷菜单中选择“重命名”选项，输入存储过程的新名称p_js1即可。
8.3.4 存储过程的重命名
使用系统存储过程sp_rename来修改存储过程的名称.
语法格式：SP_RENAME 旧名称 新名称
【例8-16】使用Transact-SQL语句将存储过程p_jiaoshi3改名为p_js3。
对应的SQL语句如下：
SP_RENAME p_jiaoshi3 p_js3
GO
本章小结
存储过程是一段Transact-SQL语句，用来完成一个功能。
使用CREATE PROCEDURE创建存储过程。
执行存储过程EXECUTE procedure_name。
创建带输入输出参数的存储过程，需要在CREATE PROCEDURE语句中声明一个或多个变量作为参数
修改存储过程是由ALTER PROCEDURE语句来完成的。

展开更多......

收起↑