项目数据库存储过程

项目七公司管理数据库系统中存储过程的应用促成教学目标：1、能正确理解存储过程的概念2、会使用企业管理器和Transact-SQL语句管理存储过程3、会使用存储过程传递参数工作任务通过编写存储过程，获取指定雇员的个人信息并将之输出显示，包括姓名、性别、出生年月以及雇佣日期，以及该雇员所接收的订单明细表，并统计该雇员接收的订单数目以及订单总金额。一、存储过程的定义（1）SQLServer的存储过程类似于编程语言中的过程。将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQLServer服务器通过过程名来调用它们，这些过程（Procedure）就叫做存储过程。存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。存储过程的定义（2）存储过程定义包含两个主要组成部分：①过程名称及其参数的说明，包括所有的输入参数以及传给调用者的输出参数。②过程的主体：执行过程的针对数据库的操作语句（Transact-SQL语句），包括调用其它存储过程的语句。二、存储过程的特点允许模块化的程序设计更快的执行速度有效降低网络流量较好的安全机制三、存储过程的分类存储过程分为三类：系统提供的存储过程：系统提供的存储过程系统存储过程的名字都以“sp_”为前缀例：sp_bindefault，sp_bindrule，sp_help，sp_helpdb,sp_helpindex等用户定义的存储过程：用户定义的存储过程是由用户为完成某一特定功能而编写的存储过程。存储在创建时的数据库中。扩展存储过程：扩展存储过程是用来调用操作系统提供的功能。四、创建与管理存储过程使用三种方法创建存储过程：①使用创建存储过程向导创建存储过程。②利用SQLServer企业管理器创建存储过程。③使用CREATEPROCEDURE命令创建存储过程。1.使用向导创建存储过程在企业管理器中，选择工具菜单中的向导选项，选择“创建存储过程向导”，则出现欢迎使用创建存储过程向导对话框，根据图提示可完成创建存储过程。欢迎使用创建存储过程向导对话框选择数据库对话框选择数据库对象对话框完成创建存储过程向导对话框2.使用企业管理器创建存储过程（1）在企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程…”选项，（2）在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。选择新建存储过程对话框（1）创建存储过程（1）新建存储过程对话框创建存储过程（2）创建存储过程（3）3.利用Transact—SQL语句创建和理存储过程CREATEPROCEDURE语句用于从已测试过的SQL语句来实际创建一个存储过程。语法:CREATEPROC[EDURE]procedure_name[{@parameterdata_type}[=default][OUTPUT]][,...n]WITHENCRYPTIONASsql_statement[...n]【例1】创建所有的雇员的接收订单的明细信息的存储过程。createproclistOrderasselect姓名,产品名,数量,单价,数量*单价as金额fromemployeejoinp_orderonemployee.雇员ID=p_order.雇员IDjoinproductonproduct.产品ID=p_order.产品idorderby订货日期创建带输入参数的存储过程输入参数是指由调用程序向存储过程传递的参数。它们在创建存储过程语句中被定义，而在执行该存储过程中给出相应的变量值。语法:{@parameterdata_type}[=default]参数：@parameter：参数名，必须以@符号为前缀。data_type：参数的数据类型说明。default：如果执行存储过程时未提供该参数值，则使用默认值。【例2】创建指定姓名的雇员接收订单的明细信息的存储过程。createprocedurename_list_order@namevarchar(8)asselect姓名,产品名,数量,单价,数量*单价as金额fromemployeejoinp_orderonemployee.雇员ID=p_order.雇员IDjoinproductonproduct.产品ID=p_order.产品idWhere姓名=@nameorderby订货日期【例3】如果在employee表中查找符合性别和薪水条件的雇员的详细信息。CREATEPROCEDURElistEmployee@sexvarchar(2),@salarymoneyasselect*fromemployeewhere性别=@sexand薪水@salary【例4】在公司管理数据库中创建存储过程proc_cu，要求实现如下功能：输入公司的名称，查询该公司的订货情况，如果该公司没有订货，则显示“此公司没有订单！”，否则显示“此公司已下订单！”。CREATEPROCEDUREproc_pu@customer_namevarchar(20)ASifexists(select*fromp_orderwhere客户ID=(select客户IDfromcustomerwhere公司名称=@customer_name))print'此公司已下订单！'elseprint'此公司没有下订单！'【例5】在公司管理数据库中创建存储过程product_order，要求实现如下功能：根据产品名称，查询该产品的订货情况，如果该产品没有订货，则显示“此产品目前没有订单！”，否则显示订购该产品的订购明细表。【例5】程序清单CREATEPROCEDUREproduct_order(@productnamevarchar(20))ASdeclare@产品id_1intselect@产品id_1=产品idfromproductwhere产品名=@productnameifexists(select*fromp_orderwhere产品id=@产品id_1)beginSELECTproduct.产品名,p_order.数量,product.单价,product.库存量,p_order.订货日期FROMp_orderJOINproductONp_order.产品ID=product.产品IDwhereproduct.产品名=@productnameendelseprint@productname+'没有订单！'创建带输出参数的存储过程从存储过程中返回一个或多个值。语法：@参数名数据类型[=默认值]OUTPUT@参数名：存储过程的输出参数名，必须以@符号为前缀。数据类型：该参数的数据类型说明。OUTPUT：指明输出参数。注意，输出参数必须位于所有输入参数说明之后。【例7】创建一个指定产品的接受订单的总金额的存储过程。CreatePROCEDUREproduct_order_sum(@productnamevarchar(20),@sum_prointoutput)ASdeclare@产品id_1intselect@产品id_1=产品idfromproductwhere产品名=@productnameifexists(select*fromp_orderwhere产品id=@产品id_1)beginSELECT@sum_pro=sum(p_order.数量*product.单价)FROMp_orderJOINproductONp_order.产品ID=product.产品IDwhereproduct.产品名=@productnameendelseselect@sum_pro=0return@sum_pro使用WITHENCRYPTION选项对用户隐藏存储过程的文本。【例8】创建加密过程，使用sp_helptext系统存储过程获取关于加密过程的信息，然后尝试直接从syscomments表中获取关于该过程的信息。USEpubsGOCREATEPROCEDUREproduct_order_sum。。。。WITHENCRYPTIONAS。。。。GOexecsp_helptextproduct_order_sum消息：对象备注已加密。工作任务通过编写存储过程，雇员姓名由参数传递给该存储过程，自动获相应某雇员的个人信息并将之输出显示，包括姓名、性别、出生年月以及雇佣日期，以及该雇员所做的订单明细表，统计出该雇员所做的订单数目以及订单总金额，并由输出参数返回其值。程序清单（1）CREATEPROCEDUREuser_雇员订单信息@namevarchar(10),@order_countintoutput,@total_moneydecimaloutputAS--定义SQLserver的变量，必须以@开头命名变量，用DECLARE定义变量DECLARE@emp_IdintDECLARE@sexchar(2)DECLARE@date1datetimeDECLARE@date2datetime程序清单（2）--判断是否存在此雇员ifexists(SELECT*FROMemployeeWHERE姓名=@name)begin--通过查询语句将字段的值赋值给变量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))+'日'程序清单（3）--判断此雇员是否有订单ifexists(select*fromP_orderwhereP_order.雇员ID=@emp_Id)begin--输出@name参数对应的雇员完成的订单明细SELECTcustomer.公司名称,customer.联系人姓名,product.产品名,P_order.数量,P_order.订货日期,employee.姓名,product.单价FROMemployee,customer,product,P_orderWHEREemployee.雇员ID=@emp_IdandP_order.雇员ID=employee.雇员IDandP_order.产品ID=product.产品IDandP_order.客户ID=customer.客户ID程序清单（4）--将@name参数对应的雇员的订单数目以及订单总金额分别放到输出参数中SELECT@order_count=count(*),@total_money=sum(P_order.数量*product.单价)FROMemployee,product,P_orderWHEREP_order.雇员ID=employee.雇员IDandP_order.产品ID=product.产品IDandemployee.雇员ID=@emp_Idendelseprint'此雇员没有接受订单！'endelseprint'公司中没有此雇员！'5、执行存储过程语法：[EXEC[UTE]]{[@返回状态=]{过程名[;数字]|@存储过程变量}[[@参数=]{参数值|@变量[OUTPUT]|[默认值]][,…n]【例8】execcount_employee传递参数两种方法：按位置传送通过参数名传送（1）按位置传送在执行存储过程语句中，直接给出参数