项目7Word文档下载推荐.docx
《项目7Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《项目7Word文档下载推荐.docx（17页珍藏版）》请在冰豆网上搜索。
促成目标：
二、工作任务
利用存储过程实现如下功能：
自动获取某雇员的个人信息并将之输出显示，包括姓名、性别、出生年月以及雇佣日期，并输出该雇员所做的订单明细表及订单的总金额。
然后用雇员'
王孔若'
测试存储过程的功能。
三、相关实践知识
完成本项目的操作步骤如下：
1．先创建存储过程：
打开查询分析器窗口，在窗口中输入以下Transact-SQL语句：
CREATEPROCEDUREuser_雇员订单信息
@namevarchar（10）,
@order_countintoutput,
@total_moneydecimaloutput
AS
--定义SQLserver的变量，必须以@开头命名变量，用DECLARE定义变量
DECLARE@emp_Idint
DECLARE@sexchar
（2）
DECLARE@date1datetime
DECLARE@date2datetime
--通过查询语句将字段的值赋值给变量
SELECT@emp_Id=雇员ID,@sex=性别,@date1=出生年月,@date2=雇佣日期
FROMemployeeWHERE姓名=@name
--用PRINT语句结合函数格式化输出@name参数对应的雇员信息
PRINT'
姓名:
'
+@name+'
性别：
+@sex
+'
出生年月：
+convert（char（4）,year（@date1））+'
年'
+convert（char
（2）,month（@date1））+'
月'
+convert（char
（2）,day（@date1））+'
日'
+'
雇用日期：
+convert（char（4）,year（@date2））+'
+convert（char
（2）,month（@date2））+'
+convert（char
（2）,day（@date2））+'
--输出@name参数对应的雇员完成的订单明细
SELECTcustomer.公司名称,customer.联系人姓名,
product.产品名,P_order.数量,P_order.定货日期,
employee.姓名,
product.单价
FROMemployee,customer,product,P_order
WHEREemployee.雇员ID=@emp_Idand
P_order.雇员ID=employee.雇员IDand
P_order.产品ID=product.产品IDand
P_order.客户ID=customer.客户ID
--将@name参数对应的雇员的订单数目以及订单总金额分别放到输出参数中
SELECT@order_count=count（*）,@total_money=sum（P_order.数量*product.单价）
FROMemployee,product,P_order
P_order.产品ID=product.产品ID
分析：
存储过程类似于程序设计语言中的过程或函数，可以包括输入或输出参数，这里要三个参数，一个参数用于传递要显示的雇员姓名，另两个参数用于统计出该雇员所做的订单数目以及订单总金额，并由输出参数返回其值。
由于订单明细表具体信息分布在P_order表（订单表）、product（产品表）、customer表（客户表）中，故需要用select语句提取出这些表中的相关用信息。
2．选择菜单【查询】|【分析】或者按快捷键“Ctrl+F5”，进行程序分析，如果存在错误，那么查询分析器将会输出错误信息，然后要进行存储过程修改，反复进行这两个过程，直到没有错误。
3．选择菜单【查询】|【执行】或者按快捷键“F5”，如果语法不存在错误，则将会在数据库companyInfo中建立“user_雇员订单信息”存储过程。
此时，在SQL企业管理器中展开“数据库”文件夹，再展开companyInfo数据库，然后选中存储过程，在企业管理器右边的窗口中，会出现刚才创建的“user_雇员订单信息”存储过程，画横线部分，其类型是“用户”，说明是由用户创建的存储过程。
4．输入执行存储过程的代码：
在SQL查询分析器窗口中，输入如下Transact-SQL代码：
DECLARE@countint
DECLARE@sum_moneymoney
EXECuser_雇员订单信息'
@countoutput,@sum_moneyoutput
订单数目是：
+convert（varchar（10）,@count）
订单总金额是：
+convert（varchar（10）,@sum_money）
5．运行存储过程：
按F5（或选择菜单【查询】|【运行】），输出结果分为网格和消息信息输出，其中用PRINT语句输出的信息在消息选项卡中，而SELECT语句的输出结果则通过网格形式输出。
四、相关理论知识
（一）存储过程的定义
存储过程是存储在服务器上的预编译好的Transact-SQL语句集。
可以将存储过程类比为SQLServer提供的用户自定义函数，可以在后台或前台调用它们。
实际上，存储过程是Transact-SQL对ANSI-92SQL标准的扩充。
它允许多个用户访问相同的代码。
它提供了一种集中且一致的实现数据完整性逻辑的方法。
存储过程用于实现频繁使用的查询、业务规则、被其它过程使用的公共例行程序。
（二）存储过程的分类
1．系统提供的存储过程：
在安装SQLServer2000时，系统创建了很多系统存储过程。
系统存储过程主要用于从系统表中获取信息。
它们中的大部分可以在用户数据库中使用。
系统存储过程的名字都以“sp_”为前缀。
如sp_help存储过程功能为报告有关数据库对象（sysobjects表中列出的任何对象）、用户定义数据类型或Microsoft®
SQLServer™所提供的数据类型的信息。
在SQL查询分析器中输入代码“EXECsp_helpuser_雇员订单信息”，执行后系统将输出该“user_雇员订单信息”存储过程的所有信息，如存储过程名，类型，创建时间以及详细参数。
2．用户定义的存储过程：
用户定义的存储过程是由用户为完成某一特定功能而编写的存储过程。
3．扩展存储过程：
扩展存储过程是用来调用操作系统提供的功能。
（三）存储过程的优点
1．使用存储过程可以减少网络流量。
这是因为存储过程存储在服务器上，并在服务器上运行。
只有触发执行存储过程的命令和返回的结果才在网络上传输。
所以，可以减少网络流量。
客户端无需将数据库中的数据通过网络传输到本地进行计算，再将结果数据通过网络送到服务器。
从而减少了网络流量。
2．增强代码的重用性和共享性。
一个存储过程是为了完成某一个特定功能而编写的一个模块，该模块可以被很多用户重用，也可以被很多用户共享。
所以，存储过程可以增强代码的重用性和共享性，加快应用的开发速度，提供开发的质量和效率。
3．使用存储过程可以加快系统运行速度。
第一次执行后的存储过程会在缓冲区中创建查询树，使得第二次执行时不用进行预编译，从而加快速度。
4．使用存储过程保证安全性。
因为可以不授予用户访问存储过程中涉及的表的权限，而只授予访问存储过程的权限。
这样，既可以保证用户通过存储过程操纵数据库中的数据，又可以保证用户不能直接访问与存储过程相关的表，从而保证表中数据的安全性。
（四）创建与管理存储过程
1、利用Transact—SQL语句创建和管理存储过程
（1）创建存储过程
CREATEPROCEDURE语句用于从已测试过的SQL语句来实际创建一个存储过程。
CREATEPROCEDURE语句的语法如下:
CREATEPROCEDURE<
存储过程名称>
{<
@参数变量>
<
数据类型>
}[VARYING][=默认值][OUTPUT]][,...n]
[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
Sql语句
选项
说明
<
创建的存储过程的名字。
向存储过程传递参数。
参数数据类型
[VARYING]
当你把游标作为参数返回时，要指定该选项。
这个选项告诉SQLServer对于返回游标的行集合将会发生改变
[=默认值]
这个选项用于指定特定参数的缺省值。
[OUTPUT]
输出参数。
当过程执行完成后，该参数值能被返回到正在执行的过程里。
[，⋯n]
指明可以在一个存储过程中指定多个参数。
WITHRECOMPILE
强制SQLServer在每一次执行存储过程时都重新编译。
WITHENCRYPTION
强制SQLServer对存储在系统备注表中的存储过程文本进行加密
WITHRECOMPILE，ENCRYPTION
这一选项强制SQLServer重新编译和加密存储过程
AS
表明存储过程的定义将要开始
Sql语句
组成存储过程的不同语句
如本模块中的代码：
--定义SQLserver的变量，必须以@开头命名变量，用declare定义变量
……
（2）创建步骤
●写SQL语句
如：
查看雇员的个数
SELECTCOUNT（*）FROMemployee
●测试SQL语句
执行这些SQL语句。
确认符合要求。
若得到所需结果,则创建存储过程.
●定义该存储过程。
如果发现符合要求，则按照存储过程的语法.
CREATEPROCcount_employee
●执行过程
执行存储过程，验证正确性。
EXECcount_employee【例7.1】要在employee表中查找所有雇员的详细信息，可以创建如下的存储过程：
CREATEPROCEDURElist_employee
SELECT*FROMemployeeORDERBY雇佣日期
执行这几行代码，将产生一个存储过程。
（3）执行存储过程
执行存储过程的完整语法如下：
[EXEC[UTE]]
{
[@返回状态=]
{过程名[;
数字]|@存储过程变量
}
[[@参数=]{参数值|@变量[OUTPUT]|[默认值]]
[,...n]
[WITHRECOMPILE]
每个参数的使用下面会详细讲解。
最简单的执行方式是：
EXEC存储过程名
如刚才创建的count_employee存储过程例子，要执行该存储过程只要用“EXECcount_employee”即可
（4）创建带输入参数的存储过程
输入参数是指由调用程序向存储过程传递的参数。
它们在创建存储过程语句中被定义，而在执行该存储过程中给出相应的变量值。
具体语法如下:
@参数名数据类型[=默认值]
其中，
@参数名：
是存储过程的输入参数名，必须以@符号为前缀。
当执行该存储过程时，应该向输入参数提供相应的值。
数据类型：
是该参数的数据类型说明，它可以是系统提供的数据类型，也可以是用户定义的数据类型。
默认值：
如果执行存储过程时未提供该参数值，则使用默认值。
【例7.1】设计[某班某门课程成绩表]，按学号排序。
createprocedure某班某门课程成绩表
@班名varchar（20）,@课程名nchar（16）
as
select学号,姓名,课程名称,分数
from教学成绩表视图
where课程名称=@课程名and班级=@班名
orderby学号
【例7.2】创建名为listEmployee的存储过程，其功能是：
在employee表中查找符合性别和薪水条件的雇员的详细信息。
其代码如下：
CREATEPROCEDURElistEmployee
@sexvarchar
（2）,
@salarymoney
SELECT*FROMemployeeWHERE性别=@sexand薪水>
@salary
SQLServer提供了两种方法传递参数。
●按位置传送
这种方法是在执行存储过程语句中，直接给出参数的传递值。
当有多个参数时，值的顺序与创建存储过程语句中定义参数的顺序相一致。
也就是说，参数传递的顺序就是参数定义的顺序。
其格式是：
[EXECUTE]存储过程名[参数值...]
其中，“参数值”是传递给该输入参数的值。
如
EXECUTElistEmployee'
男'
4000
用于显示性别为男，薪水大于4000的雇员信息。
●通过参数名传送
在执行存储过程中，指出创建该存储过程语句中的参数名字和传递给它的值。
其格式如下：
[EXECUTE]存储过程名[@参数=值]
其中，“@参数”是输入参数的名字，值是传递给该输入参数的值。
如：
EXEClistEmployee@salary=4000,@sex='
（5）创建带输出参数的存储过程
我们可以从存储过程中返回一个或多个值。
这是通过在创建存储过程的语句中定义输出参数来实现的。
具体语法如下：
@参数名数据类型[=默认值]OUTPUT
@参数名：
是存储过程的输出参数名，必须以@符号为前缀。
保留字OUTPUT指明这是一个输出参数。
值得注意的是，输出参数必须位于所有输入参数说明之后。
【例7.3】如果在employee表中查找符合性别和薪水条件的雇员的详细信息。
select*
fromemployee
where性别=@sexand薪水>
【例7.4】在学生成绩库中创建存储过程proc_8_2，要求实现如下功能：
输入学生学号，根据该学生所选课程的平均成绩显示提示信息，如果平均成绩在60分以上，则显示“此学生成绩合格！
”，否则显示“此学生成绩不合格！
”。
Createprocproc_8_2
@snochar（8）
declare@savgtinyint
--声明变量@savg，用于存放学生平均成绩
select@savg=avg（成绩）
fromxscj
wherexscj.学号=@sno
--变量@savg存放学号为@sno的学生平均
if@savg>
=60
print“此学生成绩合格！
”
else
print“此学生成绩不合格！
go
【例7.5】创建一个完成乘法运算的存储过程。
CREATEPROCEDUREmathtutor
@m1smallint,
@m2smallint,
@resultsmallintOUTPUT
SELECT@result=@m1*@m2
为了接收某一存储过程的返回值，在调用该存储过程的程序中，也必须声明作为输出的传递参数。
这个输出传递参数声明为局部变量，用来存放返回参数的值。
[[EXECUTE]
[@返回状态=]
{存储过程名[;
数字]}
[[@参数名=]{参数值|@变量[OUTPUT]|[默认值]}][,...n]
[WITHRECOMPILE]
存储过程名：
是存储过程名字
[@参数名=]{参数值|@变量}：
是输入参数传递值
[@参数名=]@变量OUTPUT是传递给该输出参数的变量。
其中，@变量是用来存放返回参数的值。
OUTPUT指明这是一个输出传递参数，与相应的存储过程中的输出参数相匹配。
下面我们来执行刚刚创建的存储过程：
DECLARE@guesssmallint
SET@guess=50
EXECUTEmathtutor5,6,@guessOUTPUT
SELECT'
结果是:
'
@guess
结果显示如下：
30
【例7.10】执行存储过程user_雇员订单信息
+convert（varchar（10）,@sum_money）
（6）修改存储过程
创建了存储过程之后，不可避免地，根据不同的情况需要对它做出相应的修改。
在修改之前，可以用一个系统存储过程，显示存储过程有关的代码，这个系统存储过程叫sp_helptext。
假设存储过程未加密，这个系统存储过程将返回所有与存储过程有关的代码。
这个命令的语法是：
sp_helptext<
存储过程名>
这里<
就是用户需要从中获取代码的存储过程的名字。
如要显示本模块的代码，可以用“sp_helptextuser_雇员订单信息”命令显示代码。
修改存储过程用ALTERPROCEDURE语句,其语法如下:
ALTERPROCEDURE<
其各选项的含义和CREATEPROCEDURE语句中的选项相同，读者可参考前面列出的内容。
（7）删除存储过程
删除存储过程是指删除由用户创建的存储过程。
可以使用DROPPROC命令删除存储过程。
DROPPROC<
是要被删除的存储过程名字。
注意：
这种删除是永久的，所以，执行该操作时要慎重。
2、利用企业管理器创建和管理存储过程
在SQL企业管理器中展开“数据库”文件夹，再展开要编写存储过程的数据库，如companyInfo，然后右键单击存储过程，在弹出的菜单中选择【新建存储过程（S）…】，将打开“新建存储过程”对话框，
输入完代码后，单击按钮“检查语法”，如果存在错误，会弹出错误提示对话框，如果没有错误，则会弹出“语法检查成功”对话框，此时，先关闭此对话框，然后，单击“确定”按钮即可完成存储过程的创建。
在存储过程窗口中将可以看到刚才创建的存储过程。
（2）修改存储过程
在SQL企业管理器中展开“数据库”文件夹，再展开要编写存储过程的数据库，如companyInfo，然后左键单击存储过程，然后在企业管理器中的右边窗口，双击要修改的存储过程，将会出现如图7-4所示的窗口，此时，修改存储过程的代码，接着先进行“检查语法”操作，语法检查成功后，单击“确定”按钮即可完成修改。
（3）删除存储过程
在SQL企业管理器中展开“数据库”文件夹，再展开要编写存储过程的数据库，如companyInfo，然后左键单击存储过程，接着在企业管理器中的右边窗口，右键单击要修改的存储过程，在弹出的菜单中选择“删除”菜单项，然后出现图7-5所示的窗口，接下来单击【全部除去（D）】按钮，即可将选中的存储过程从数据库中删除。
复习思考题、作业题
作业：
实践题：
1-5
下次课
预习
要点
触发器
实施
情况及
分析
效果较好
注：
教案按授课次数或单元填写。
重复课可不另填写教案。
允许特色设计。
升级会员 