《SQLServer数据库应用与维护》-实验7-存储过程-参考答案

温州职业技术学院实验报告实验课程SQLServer数据库应用与维护实验项目实验七：存储过程实验地点明德楼607指导教师杜益虹班级信息1601学生姓名张楠学号16002080138教师评分日期2017-11-02

实验六T-SQL语言基础及应用一、实验目的掌握用户存储过程的创建操作。掌握用户存储过程的执行操作。掌握用户存储过程的删除操作。二、实验内容前提：还原上一次实验结果company数据库第一题【系统存储过程的使用】创建登录名aa1，调用sp_password函数设置登录名aa1的密码为‘aa123’EXECsp_passwordNULL,'aa123','aa1'查询员工人事表中有哪此约束。USEcompanyEXECsp_helpconstraint员工将‘company’数据库改名为‘销售’数据库EXECsp_renamedb'company','销售'查询视图'VIEW_CP_PRICE2000'的定义内容USE销售EXECsp_helptext'VIEW_CP_PRICE2000'第二题【存储过程基础练习】1、没有参数的存储过程：创建一个无参数存储过程p1并调用该存储过程，要求查询以下信息：客户编号、客户名称、产品编号、产品名称、价格，购买日期、购买数量。创建：createprocp1as beginselectdistinct客户.客户,客户名称,产品名称表.产品编号,产品名称,成本,销售明细表.订单日期,销售明细表.销售数量 from销售主表 innerjoin客户 on销售主表.客户号=客户.客户 innerjoin销售明细表 on销售主表.订单编号=销售明细表.订单编号 innerjoin产品名称表 on产品名称表.产品编号=销售明细表.产品编号 end调用：execp12、有输入参数的存储过程：创建一个带参数的存储过程p2并调用该存储过程,要求该存储过程根据传入的员工编号在员工人事表中查询该员工的信息。创建：createprocp2 @EmIDchar(5)asbeginselect* from员工 where员工编号=@EmIDend调用：execp2@EmID='E0001'3、有参数默认值的存储过程：创建一个带参数默认值的存储过程p3并调用该存储过程,要求该存储过程根据查询指定薪水范围的员工信息。创建：createprocp3 @salary1int=3000, @salary2int=6000asbeginselect* from员工 where薪水>=@salary1and薪水<=@salary2end调用：Execp3有输出参数的存储过程的创建：创建一个有输出参数的存储过程p4,该存储过程根据传入的员工姓名，查询该员工的编号创建：Createprocp4 @namevarchar(10),@ID_outvarchar(10)OUTPUTas select@ID_out=员工编号 from员工 where员工姓名=@namereturn调用：Declare@ID_savevarchar(10)Execp4@name='王成',@ID_out=@ID_saveOUTPUTPrint@ID_save有输出参数的存储过程的执行：根据指定的员工姓名，调用存储过程p4并在销售主表中查询该员工的所有订单编写。调用：Declare@ID_savevarchar(10)Execp4@name='王成',@ID_out=@ID_saveOUTPUT--Print@ID_saveselect*from销售主表where业务员编号=@ID_save第三题【存储过程提高题】对应于员工工资表：Wage字段名称数据类型备注IDInt自动编号，主键姓名Char（10）工资Float1、先建库表建表语句USEmasterGO/*********************建库*******************/--检验数据库是否存在，如果为真，删除此数据库--IFexists(SELECT*FROMsysdatabasesWHEREname='Wage')DROPDATABASEWageGOCREATEDATABASEWageGO--建数据表--USEWageGOCREATETABLEProWage--程序员工资表(IDintidentity(1,1)primarykey,--工资编号姓名CHAR(10)NOTNULL,--程序员姓名工资FloatNOTNULL--工资)GO--插入数据--INSERTINTOProWage(姓名,工资)VALUES('张立',1200)INSERTINTOProWage(姓名,工资)VALUES('李红',1800)INSERTINTOProWage(姓名,工资)VALUES('王平',3500)INSERTINTOProWage(姓名,工资)VALUES('蓝天',2780)INSERTINTOProWage(姓名,工资)VALUES('陈科',1900)2、请编写T-SQL来实现如下功能：（1）对员工的工资进行分析，如果有百分之五十的人薪水不到2000元，则给所有人加薪，每次加100，再进行分析，直到有一半以上的人大于2000元为止，最终加了多少钱？declare@aintdeclare@addMoneyintset@addMoney=0设置set@a=1While@a>0 BEGIN if(selectcount(*)fromProWagewhere工资>2000)*2>(selectcount(*)fromProWage) BEGIN print('最终增加了'+convert(varchar,@addMoney*5)+'元') Break END else BEGIN updateprowageset工资=工资+100 set@addMoney=@addMoney+100 END END（2）创建一个存储过程Sum_wage，对员工的工资进行分析，如果有百分之五十的人薪水不到X元，给所有人加薪，每次加100，再进行分析，直到有一半以上的人大于X元为止，最终加了多少钱？调用上述存储过程，查询是否有一半程序员的工资在2200、3000、3500、4000元之上，如果不到分别每次给每个程序员加薪100元，直至一半程序员的工资达到2200，3000，3500，4000元createprocSum_wage @Rule_Moneyint, @aint=1, @addMoneyint=0, @Rule_Numint=0as While@a>0 BEGIN if(selectcount(*)fromProWagewhere工资>@Rule_Money)*2>(selectcount(*)fromProWage) BEGIN print('只有'+convert(varchar,@Rule_Num)+'人到达'+'达到'+convert(varchar,@Rule_Money)+'元') print('达到'+convert(varchar,@Rule_Money)+'元的人数不足一半') print('最终增加了'+convert(varchar,@addMoney*5)+'元') Break END else BEGIN set@Rule_Num=(selectcount(*)fromProWagewhere工资>@Rule_Money) --保存到达要求的人数 updateprowageset工资=工资+100 set@addMoney=@addMoney+100 END END（3）创建存储过程avg_wage，查询程序员平均工资是否在Y元，如果不到Y元，则每个程序员每次加200元，直到所有程序员平均工资达到Y元。调用该存储过程createproc[dbo].[Avg_wage] @Rule_Moneyint, @aint=1, @addMoneyint=0, @avgSalaryint=0as set@avgSalary=(selectavg(工资)fromProWage) print('当前平均工资只有'+convert(varchar,@avgSalary)+'元') --输出一遍当前的工资 While@a>0 BEGIN if@avgSalary>@Rule_Money BEGIN break END else BEGIN updatepro