资源简介

(共31张PPT)
以程序方式处理学生信息管理数据表
任务8-1 SQL编程基础
目录
Contents
任务8-2 创建与使用存储过程
任务8-3 触发器
任务8-4 事务、锁的概念和应用
实训 以程序方式处理数据表的数据
PART 2
任务8-2 创建与使用存储过述
任务
assignments
（一）存储过程概述
（二）创建存储过程
（三）执行存储过程
（四）管理存储过程标
创建与使用存储过程
任务8-2
任务1-1 数据处理
【任务分析】
为了提高访问数据的速度与效率，可以使用存储过程管理数据库。
【课堂任务】
掌握存储过程的概念及应用。
存储过程的概念
存储过程的创建及管理
存储过程中参数的使用
存储过程的查看及删除
创建与使用存储过程
任务8-2
任务1-1 数据处理
（一）存储过程概述
1．什么是存储过程
存储过程(Stored Procedure)是在数据库中定义的完成特定功能的SQL语句集合，经编译后存储在数据库服务器上。存储过程可包含流程控制语句及各种SQL语句。存储过程与其他编程语言中的构造相似，它们可以接受输入参数并以输出参数的格式向调用程序返回单个或多个结果。
创建与使用存储过程
任务8-2
任务1-1 数据处理
2．使用存储过程的优点
（1）存储过程增强了SQL的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
（2）存储过程允许模块化程序设计。存储过程创建后，可以在程序中多次调用，而不必重新编写该存储过程的SQL语句，并且可以随时修改存储过程，毫不影响应用程序源代码。
（3）存储过程能实现较快的执行速度。默认情况下，在首次执行存储过程时将编译存储过程，并且创建一个执行计划，供以后的执行重复使用，系统可以用更少的时间来处理存储过程。
创建与使用存储过程
任务8-2
任务1-1 数据处理
（4）存储过程减少了服务器/客户端的网络流量。存储过程中的命令作为代码的单个批处理执行，当在客户计算机上调用存储过程时，网络中传送的只是该调用语句，从而可以显著减少服务器和客户端之间的网络流量。
（5）存储过程可作为一种安全机制来充分利用。多个用户和客户端程序可以通过存储过程对基础数据库对象执行操作，即使用户和程序对这些基础对象没有直接权限。
在通过网络调用存储过程中，只有存储过程的调用语句是可见的。因此，即使恶意用户截获网络传输信息，也无法看到存储过程本身涉及的表和数据库对象名称、嵌入的Transact-SQL语句及搜索关键数据等。
创建与使用存储过程
任务8-2
任务1-1 数据处理
（二）创建存储过程
在SQL Server系统中，可以使用SSMS管理工具和CREATE PROCEDURE语句创建存储过程。需要强调的是，必须具有CREATE PROCEDURE权限才能创建存储过程。
1．使用SSMS创建存储过程
【例8.15】 创建一个名称为Proc_Stur的存储过程，完成如下功能：在Students表中查询男生的Sno、Sex、Sage这几个字段的内容。
创建与使用存储过程
任务8-2
任务1-1 数据处理
使用SSMS创建存储过程的步骤如下。
（1）启动SSMS，在对象资源管理器中，连接到数据库引擎的实例并展开该实例。
（2）展开【数据库】|【grademanager】|【可编程性】节点，用鼠标右键单击【存储过程】节点，在快捷菜单中选择【存储过程】命令，如图8.1所示。
（3）在【查询编辑器】中出现存储过程编程模板，如图8.1所示。在此模板的基础上编写创建存储过程的语句。
创建与使用存储过程
任务8-2
任务1-1 数据处理
创建与使用存储过程
任务8-2
任务1-1 数据处理
2．使用CREATE PROCEDURE语句创建存储过程
语法格式如下。
CREATE PROCEDUREprocedure_name
[{@parameter data_type} [=default][OUTPUT][,…n]
AS sql_statement[…n]
参数说明如下。
（1）procedure_name：存储过程的名称。
（2）@parameter：过程中的参数。在CREATE PROCEDURE语句中可以声明一个或者多个参数。每个过程的参数仅用于该过程本身；其他过程中可以使用相同的参数名称。
创建与使用存储过程
任务8-2
任务1-1 数据处理
（3）data_type：参数的数据类型。
（4）default：参数的默认值。如果定义default值，则无需指定此参数的值即可执行过程。默认值必须是常量或者NULL。该常量值可以采用通配符的形式，也就是说，在将该参数传递到过程时使用LIKE关键字。
（5）OUTPUT：指明参数是输出参数。使用OUTPUT参数将值返回给过程的调用方。
（6）：要包含在过程中的一个或者多个Transact-SQL语句。
创建与使用存储过程
任务8-2
任务1-1 数据处理
【例8.16】 创建一个基本存储过程，从数据库grademanager的student表中检索出所有籍贯为“青岛”的学生的学号、姓名、班级号及家庭地址等信息。
USE grademanager
GO
CREATE PROCEDURE pro_学生信息
AS
SELECT sno,sname,classno,saddress
FROM student
WHERE saddressLIKE '%青岛%'
ORDER BY sno
GO
执行存储过程“pro_学生信息”，返回所有“青岛”籍的学生信息。
创建与使用存储过程
任务8-2
任务1-1 数据处理
3．使用存储过程参数
SQL Server的存储过程可以使用两种类型的参数：输入参数和输出参数。参数用于在存储过程以及应用程序之间交换数据。
输入参数允许用户将数据值传递到存储过程或者函数。
输出参数允许存储过程将数据值或者游标变量传递给用户。
每个存储过程向用户返回一个整数代码，如果存储过程没有明显设置返回代码的值，则返回代码为零。
（1）输入参数
输入参数，即在存储过程中有一个条件，在执行存储过程时为这个条件指定值，通过存储过程返回相应的信息。使用输入参数可以向同一存储过程多次查找数据库。
创建与使用存储过程
任务8-2
任务1-1 数据处理
【例8.17】 创建一个存储过程，用于返回grademanager数据库中“计算机2”班的所有学生信息。建立一个性别参数为同一存储过程指定不同的性别，来返回不同性别的学生信息。
USE grademanager
GO
CREATE PROCEDUREpro_学生_性别_信息
@性别 NVARCHAR(10)
AS
SELECT sno,sname,ssex,classname,header
FROM student A,class B
WHERE A.classno=B.classno
AND classname='计算机2'
AND A.ssex=@性别
GO
创建与使用存储过程
任务8-2
任务1-1 数据处理
“pro_学生_性别_信息”存储过程使用一个字符串型参数“@性别”来执行。执行带有输入参数的存储过程时，SQL Server提供了两种传递参数的方式。
① 按位置传递。这种方式是在执行过程的语句中，直接给出参数的值。当有多个参数时，给出参数的顺序与创建过程的语句中的参数一致，即参数传递的顺序就是参数定义的顺序。
创建与使用存储过程
任务8-2
任务1-1 数据处理
【例8.18】 使用按位置传递方式执行“pro_学生_性别_信息”存储过程。
EXEC pro_学生_性别_信息 '女'
② 通过参数名传递。这种方式是在执行存储过程的语句中，使用“参数名=参数值”的形式给出参数值。通过参数名传递的好处是，参数可以以任意顺序给出。
【例8.19】 使用参数传递方式执行“pro_学生_性别_信息”存储过程。
EXEC pro_学生_性别_信息 @性别='男'
使用上述两种传递参数的方式传递不同的参数并执行存储过程。
创建与使用存储过程
任务8-2
任务1-1 数据处理
（2）使用默认参数值
执行存储过程“pro_学生_性别_信息”时，如果没有指定参数，系统运行就会出错；如果希望不给出参数时也能够正确运行，则可以给参数设置默认值。
【例8.20】 设置“pro_学生_性别_信息”存储过程的状态参数默认值为“男”。
USE grademanager
GO
CREATE PROCEDURE pro_学生_性别_信息
@性别NVARCHAR(10)='男'
AS
SELECT sno,sname,ssex,classname,header
FROM student A,class B
WHERE A.classno=B.classno
AND classname='计算机2'
AND A.ssex=@性别
GO
为参数设置默认值以后，再执行存储过程时，就可以不指定具体的参数
创建与使用存储过程
任务8-2
任务1-1 数据处理
（3）输出参数
定义输出参数，可以从存储过程中返回一个或者多个值。要使用输出参数，就必须在CREATE PROCEDURE语句和EXECUTE语句中指定关键字OUTPUT。执行存储过程时，忽略OUTPUT关键字，存储过程仍然会执行但不返回值。
【例8.21】 创建一个名为pro_getteachername的存储过程。它使用两个参数，“@学生姓名”为输入参数，用于指定要查询的学生姓名，默认参数值为“徐红”；“@班主任”为输出参数，用来返回该班班主任的姓名。
创建与使用存储过程
任务8-2
任务1-1 数据处理
USE grademanager
GO
CREATE PROCEDUREpro_getteachername
@学生姓名NVARCHAR(20)='徐红',
@班主任 NVARCHAR(20) OUTPUT
AS
SELECT @班主任=B.header
FROM student A,class B
WHERE A.classno=B.classno
AND sname=@学生姓名
GO
为了接收某一存储过程的返回值，需要一个变量来存放返回参数的值，必须在该存储过程的调用语句中，为这个变量加上OUTPUT关键字来声明。
创建与使用存储过程
任务8-2
任务1-1 数据处理
（三）执行存储过程
1．使用SSMS执行存储过程
使用SSMS执行存储过程的步骤如下。
（1）启动SSMS，在对象资源管理器中，连接到数据库引擎的实例并展开该实例。
（2）展开【数据库】|【grademanager】|【可编程性】|【存储过程】节点，用鼠标右键单击要执行的存储过程（pro_学生信息），在快捷菜单中选择【执行存储过程】命令，如图8.6所示。
（3）打开执行该存储过程的窗口，单击【确定】按钮，该存储过程执行完毕，如图8.6所示。
创建与使用存储过程
任务8-2
任务1-1 数据处理
创建与使用存储过程
任务8-2
任务1-1 数据处理
2．使用EXECUTE语句执行存储过程
EXECUTE语句的语法格式如下。
[[EXEC[UTE]]
{
[@return_status=]
{procedure_name[;number]|@procedure_name_var}
[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}
[,…n]
下面执行任务8-2中创建的3个存储过程。因为前面创建的存储过程“pro_学生信息”中没有参数，所以可以直接使用EXEC语句来执行。
创建与使用存储过程
任务8-2
任务1-1 数据处理
使用带参数的存储过程，需要在执行过程中提供存储过程的参数值。可以使用两种方式来提供存储过程的参数值。
（1）直接方式。该方式在EXEC语句中直接为存储过程的参数提供数据值，并且这些数据值的数量和顺序与定义存储过程时，参数的数据和顺序相同。如果参数是字符类型或者日期类型，则还应该将这些参数值使用引号引起来。
例如，为前面创建的存储过程“pro_学生_性别_信息”提供一个字符串数据为“女”，具体执行情况如图8.3所示。
创建与使用存储过程
任务8-2
任务1-1 数据处理
创建与使用存储过程
任务8-2
任务1-1 数据处理
（2）间接方式。该方式是指在执行EXEC语句之前，声明参数并为这些参数赋值，然后在EXEC语句中引用这些已经获取数据值的参数。
例如，在EXEC语句执行存储过程“pro_学生_性别_信息”之前，使用DECLARE语句声明变量，然后使用SET语句为已声明的变量赋值。最后，在EXEC语句中引用变量作为存储过程的参数值，具体情况如图8.7所示。
创建与使用存储过程
任务8-2
任务1-1 数据处理
（四）管理存储过程
1．修改存储过程
使用ALTER PROCEDURE语句修改现有的存储过程，这与删除和重建存储过程不同，因为它仍保持存储过程的权限不发生变化。在使用ALTER PROCEDURE语句修改存储过程时，SQL Server会覆盖以前定义的存储过程。修改存储过程的基本语法格式如下。
ALTER PROCEDUREprocedure_name[;number]
[{@parameterdata_type} [=default][OUTPUT]
[,…n]
AS
sql_statement[…n]
创建与使用存储过程
任务8-2
任务1-1 数据处理
【例8.23】 修改“pro_学生信息”存储过程来返回所有“潍坊”的学生信息。
USE grademanager
GO
ALTER PROCEDURE pro_学生信息
AS SELECT sno,sname,classno,saddress
FROM student
WHERE saddressLIKE '%潍坊%'
ORDER BY sno
【例8.24】 执行“pro_学生信息”存储过程。
USE grademanager
GO
EXEC pro_学生信息
创建与使用存储过程
任务8-2
任务1-1 数据处理
2．删除存储过程
可使用DROPPROCEDURE语句从当前数据库中删除用户定义的存储过程。删除存储过程的语法格式如下。
DROP PROCEDURE{procedure}[,…n]
【例8.25】 删除“pro_学生信息”存储过程。
DROP PROC pro_学生信息
如果另一个存储过程调用某个已被删除的存储过程，则SQL Server将在执行调用进程时显示一条错误消息。
创建与使用存储过程
任务8-2
任务1-1 数据处理
3．查看存储过程
查看存储过程的定义信息，可以通过SSMS管理工具，也可以使用系统存储过程（sp_helptext）、系统函数（OBJECT_DEFINITION）和目录视图（sys.sql_modules）。
【例8.26】 使用系统存储过程sp_helptext查看“pro_学生_性别_信息”存储过程的定义文本信息，结果如图8.9所示。
USE grademanager
GO
EXCU sp_helptextpro_学生_性别_信息

展开更多......

收起↑