项目7Word文档下载推荐.docx

1、促成目标：二、工作任务利用存储过程实现如下功能：自动获取某雇员的个人信息并将之输出显示，包括姓名、性别、出生年月以及雇佣日期，并输出该雇员所做的订单明细表及订单的总金额。然后用雇员王孔若测试存储过程的功能。三、相关实践知识完成本项目的操作步骤如下：1先创建存储过程：打开查询分析器窗口，在窗口中输入以下Transact-SQL语句：CREATE PROCEDURE user_雇员订单信息name varchar（10）,order_count int output,total_money decimal outputAS-定义SQL server的变量，必须以开头命名变量，用DECLARE定义变

2、量DECLARE emp_Id int DECLARE sex char（2）DECLARE date1 datetime DECLARE date2 datetime-通过查询语句将字段的值赋值给变量SELECT emp_Id=雇员ID,sex=性别, date1=出生年月,date2=雇佣日期 FROM employee WHERE 姓名=name -用PRINT语句结合函数格式化输出name参数对应的雇员信息PRINT 姓名:+name+ 性别：+sex + 出生年月：+convert（char（4）,year（date1）+年+convert（char（2）,month（date1）+

3、月+convert（char（2）,day（date1）+日+ 雇用日期：+convert（char（4）,year（date2）+convert（char（2）,month（date2）+convert（char（2）,day（date2）+-输出name参数对应的雇员完成的订单明细SELECT customer.公司名称,customer.联系人姓名, product.产品名,P_order.数量,P_order.定货日期, employee.姓名, product.单价 FROM employee,customer,product,P_order WHERE employee.雇员ID=

4、 emp_Id and P_order.雇员ID=employee.雇员ID and P_order.产品ID=product.产品IDand P_order.客户ID=customer.客户ID -将name参数对应的雇员的订单数目以及订单总金额分别放到输出参数中SELECT order_count=count（*） , total_money=sum（P_order.数量*product.单价） FROM employee,product,P_order P_order.产品ID=product.产品ID 分析：存储过程类似于程序设计语言中的过程或函数，可以包括输入或输出参数，这里要三个参

5、数，一个参数用于传递要显示的雇员姓名，另两个参数用于统计出该雇员所做的订单数目以及订单总金额，并由输出参数返回其值。由于订单明细表具体信息分布在P_order表（订单表）、product（产品表）、customer表（客户表）中，故需要用select语句提取出这些表中的相关用信息。2选择菜单【查询】|【分析】或者按快捷键“Ctrl+F5”，进行程序分析，如果存在错误，那么查询分析器将会输出错误信息，然后要进行存储过程修改，反复进行这两个过程，直到没有错误。3选择菜单【查询】|【执行】或者按快捷键“F5”，如果语法不存在错误，则将会在数据库companyInfo中建立“user_雇员订单信息”存

6、储过程。此时，在SQL企业管理器中展开“数据库”文件夹，再展开companyInfo数据库，然后选中存储过程，在企业管理器右边的窗口中，会出现刚才创建的“user_雇员订单信息”存储过程，画横线部分，其类型是“用户”，说明是由用户创建的存储过程。4输入执行存储过程的代码：在SQL查询分析器窗口中，输入如下Transact-SQL代码：DECLARE count intDECLARE sum_money moneyEXEC user_雇员订单信息 ,count output,sum_money output订单数目是：+convert（varchar（10）,count）订单总金额是：+conv

7、ert（varchar（10）,sum_money） 5运行存储过程：按F5（或选择菜单【查询】|【运行】），输出结果分为网格和消息信息输出，其中用PRINT语句输出的信息在消息选项卡中，而SELECT语句的输出结果则通过网格形式输出。四、相关理论知识（一）存储过程的定义 存储过程是存储在服务器上的预编译好的Transact-SQL语句集。可以将存储过程类比为SQL Server提供的用户自定义函数，可以在后台或前台调用它们。实际上，存储过程是Transact-SQL对ANSI- 92SQL标准的扩充。它允许多个用户访问相同的代码。它提供了一种集中且一致的实现数据完整性逻辑的方法。存储过程用于

8、实现频繁使用的查询、业务规则、被其它过程使用的公共例行程序。（二）存储过程的分类1 系统提供的存储过程：在安装SQL Server 2000时，系统创建了很多系统存储过程。系统存储过程主要用于从系统表中获取信息。它们中的大部分可以在用户数据库中使用。系统存储过程的名字都以“sp_”为前缀。如sp_help存储过程功能为报告有关数据库对象（sysobjects表中列出的任何对象）、用户定义数据类型或 Microsoft SQL Server 所提供的数据类型的信息。在SQL查询分析器中输入代码“EXEC sp_help user_雇员订单信息”，执行后系统将输出该“user_雇员订单信息”存储过

9、程的所有信息，如存储过程名，类型，创建时间以及详细参数。2 用户定义的存储过程：用户定义的存储过程是由用户为完成某一特定功能而编写的存储过程。3 扩展存储过程：扩展存储过程是用来调用操作系统提供的功能。（三）存储过程的优点1 使用存储过程可以减少网络流量。这是因为存储过程存储在服务器上，并在服务器上运行。只有触发执行存储过程的命令和返回的结果才在网络上传输。所以，可以减少网络流量。客户端无需将数据库中的数据通过网络传输到本地进行计算，再将结果数据通过网络送到服务器。从而减少了网络流量。2 增强代码的重用性和共享性。一个存储过程是为了完成某一个特定功能而编写的一个模块，该模块可以被很多用户重用，

10、也可以被很多用户共享。所以，存储过程可以增强代码的重用性和共享性，加快应用的开发速度，提供开发的质量和效率。3 使用存储过程可以加快系统运行速度。第一次执行后的存储过程会在缓冲区中创建查询树，使得第二次执行时不用进行预编译，从而加快速度。4 使用存储过程保证安全性。因为可以不授予用户访问存储过程中涉及的表的权限，而只授予访问存储过程的权限。这样，既可以保证用户通过存储过程操纵数据库中的数据，又可以保证用户不能直接访问与存储过程相关的表，从而保证表中数据的安全性。（四）创建与管理存储过程1、利用TransactSQL语句创建和管理存储过程（1）创建存储过程CREATE PROCEDURE语句用于

11、从已测试过的SQL语句来实际创建一个存储过程。CREATE PROCEDURE语句的语法如下:CREATE PROCEDURE VARYING=默认值OUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONSql语句选项 说明salarySQL Server提供了两种方法传递参数。 按位置传送这种方法是在执行存储过程语句中，直接给出参数的传递值。当有多个参数时，值的顺序与创建存储过程语句中定义参数的顺序相一致。也就是说，参数传递的顺序就是参数定义的顺序。其格式是：EXECUTE 存储过程名 参数值.其中， “参数值”是传递给该输入参数的值。如

12、EXECUTE listEmployee 男,4000用于显示性别为男，薪水大于4000的雇员信息。 通过参数名传送在执行存储过程中，指出创建该存储过程语句中的参数名字和传递给它的值。其格式如下：EXECUTE 存储过程名 参数=值其中， “参数”是输入参数的名字， 值是传递给该输入参数的值。如：EXEC listEmployee salary=4000,sex=（5）创建带输出参数的存储过程我们可以从存储过程中返回一个或多个值。这是通过在创建存储过程的语句中定义输出参数来实现的。具体语法如下：参数名 数据类型=默认值 OUTPUT 参数名：是存储过程的输出参数名，必须以符号为前缀。 保留字O

13、UTPUT指明这是一个输出参数。值得注意的是，输出参数必须位于所有输入参数说明之后。【例7.3】如果在employee表中查找符合性别和薪水条件的雇员的详细信息。select * from employee where 性别=sex and 薪水【例7.4】在学生成绩库中创建存储过程proc_8_2，要求实现如下功能：输入学生学号，根据该学生所选课程的平均成绩显示提示信息，如果平均成绩在60分以上，则显示“此学生成绩合格！”，否则显示“此学生成绩不合格！”。Create proc proc_8_2sno char（8）declare savg tinyint -声明变量savg，用于存放学生平

14、均成绩select savg=avg（成绩）from xscjwhere xscj.学号=sno -变量savg 存放学号为sno的学生平均 if savg=60 print “此学生成绩合格！”else print “此学生成绩不合格！go【例7.5】创建一个完成乘法运算的存储过程。CREATE PROCEDURE mathtutorm1 smallint,m2 smallint,result smallint OUTPUTSELECT result=m1*m2为了接收某一存储过程的返回值，在调用该存储过程的程序中，也必须声明作为输出的传递参数。这个输出传递参数声明为局部变量，用来存放返回参

15、数的值。EXECUTE返回状态=存储过程名 ;数字 参数名=参数值|变量OUTPUT|默认值,.nWITHRECOMPILE 存储过程名：是存储过程名字 参数名=参数值|变量：是输入参数传递值 参数名= 变量 OUTPUT是传递给该输出参数的变量。其中， 变量是用来存放返回参数的值。OUTPUT指明这是一个输出传递参数，与相应的存储过程中的输出参数相匹配。下面我们来执行刚刚创建的存储过程：DECLARE guess smallintSET guess=50EXECUTE mathtutor 5,6,guess OUTPUTSELECT 结果是: ,guess结果显示如下： 30【例7.10】执

16、行存储过程user_雇员订单信息 +convert（varchar（10）,sum_money）（6）修改存储过程创建了存储过程之后，不可避免地，根据不同的情况需要对它做出相应的修改。在修改之前，可以用一个系统存储过程，显示存储过程有关的代码，这个系统存储过程叫sp_helptext。假设存储过程未加密，这个系统存储过程将返回所有与存储过程有关的代码。这个命令的语法是：sp_helptext 这里 就是用户需要从中获取代码的存储过程的名字。如要显示本模块的代码，可以用“sp_helptext user_雇员订单信息”命令显示代码。修改存储过程用ALTER PROCEDURE语句,其语法如下:A

17、LTER PROCEDURE 其各选项的含义和CREATE PROCEDURE语句中的选项相同，读者可参考前面列出的内容。（7）删除存储过程删除存储过程是指删除由用户创建的存储过程。可以使用DROP PROC命令删除存储过程。DROP PROC 是要被删除的存储过程名字。注意：这种删除是永久的，所以，执行该操作时要慎重。2、利用企业管理器创建和管理存储过程在SQL企业管理器中展开“数据库”文件夹，再展开要编写存储过程的数据库，如companyInfo，然后右键单击存储过程，在弹出的菜单中选择【新建存储过程（S）】，将打开“新建存储过程”对话框， 输入完代码后，单击按钮“检查语法”，如果存在错误

18、，会弹出错误提示对话框，如果没有错误，则会弹出“语法检查成功”对话框，此时，先关闭此对话框，然后，单击“确定”按钮即可完成存储过程的创建。在存储过程窗口中将可以看到刚才创建的存储过程。（2）修改存储过程在SQL企业管理器中展开“数据库”文件夹，再展开要编写存储过程的数据库，如companyInfo，然后左键单击存储过程，然后在企业管理器中的右边窗口，双击要修改的存储过程，将会出现如图7-4所示的窗口，此时，修改存储过程的代码，接着先进行“检查语法”操作，语法检查成功后，单击“确定”按钮即可完成修改。（3）删除存储过程在SQL企业管理器中展开“数据库”文件夹，再展开要编写存储过程的数据库，如companyInfo，然后左键单击存储过程，接着在企业管理器中的右边窗口，右键单击要修改的存储过程，在弹出的菜单中选择“删除”菜单项，然后出现图7-5所示的窗口，接下来单击【全部除去（D）】按钮，即可将选中的存储过程从数据库中删除。复习思考题、作业题作业： 实践题：1-5下次课预 习要 点触发器实 施情况及分 析效果较好注：教案按授课次数或单元填写。重复课可不另填写教案。允许特色设计。