sql练习及存储实例Word文件下载.docx
《sql练习及存储实例Word文件下载.docx》由会员分享，可在线阅读，更多相关《sql练习及存储实例Word文件下载.docx（21页珍藏版）》请在冰点文库上搜索。
getUserList"
指定存储过程名
MyComm.CommandType=4'
表明这是一个存储过程
MyComm.Prepared=true'
要求将SQL命令先行编译
SetMyRst=MyComm.Execute
SetMyComm=Nothing
存储过程取得的记录集赋给MyRst，接下来，可以对MyRst进行操作。
在以上代码中，CommandType属性表明请求的类型，取值及说明如下：
-1表明CommandText参数的类型无法确定
1表明CommandText是一般的命令类型
2表明CommandText参数是一个存在的表名称
4表明CommandText参数是一个存储过程的名称
还可以通过Connection对象或Recordset对象调用存储过程，方法分别如下：
**通过Connection对象调用存储过程**
DIMMyConn,MyRst
SetMyConn=Server.CreateObject（"
ADODB.Connection"
MyConn.openMyConStr'
SetMyRst=MyConn.Execute（"
0,4）'
最后一个参断含义同CommandType
SetMyConn=Nothing
**通过Recordset对象调用存储过程**
DIMMyRst
SetMyRst=Server.CreateObject（"
ADODB.Recordset"
MyRst.open"
MyConStr,0,1,4
MyConStr是数据库连接字串,最后一个参断含义与CommandType相同
2.没有输入输出的存储过程
请看以下存储过程：
/*SP2*/
CREATEPROCEDUREdbo.delUserAll
deletefromdbo.[userinfo]
该存储过程删去userinfo表中的所有记录，没有任何输入及输出，调用方法与上面讲过的基本相同，只是不用取得记录集：
DIMMyComm
delUserAll"
MyComm.Execute'
此处不必再取得记录集
SetMyComm=Nothing
当然也可通过Connection对象或Recordset对象调用此类存储过程，不过建立Recordset对象是为了取得记录集，在没有返回记录集的情况下，还是利用Command对象吧。
3.有返回值的存储过程
在进行类似SP2的操作时，应充分利用SQLServer强大的事务处理功能，以维护数据的一致性。
并且，我们可能需要存储过程返回执行情况，为此，将SP2修改如下：
/*SP3*/
BEGINTRANSACTION
IF@@error=0
COMMITTRANSACTION
return1
ELSE
ROLLBACKTRANSACTION
return0
end
return
以上存储过程，在delete顺利执行时，返回1，否则返回0，并进行回滚操作。
为了在ASP中取得返回值，需要利用Parameters集合来声明参数：
**调用带有返回值的存储过程并取得返回值**
DIMMyComm,MyPara
声明返回值
SetMypara=MyComm.CreateParameter（"
RETURN"
2,4）
MyComm.Parameters.AppendMyPara
MyComm.Execute
取得返回值
DIMretValue
retValue=MyComm（0）'
或retValue=MyComm.Parameters（0）
在MyComm.CreateParameter（"
2,4）中，各参数的含义如下：
第一个参数（"
RETURE"
）为参数名。
参数名可以任意设定，但一般应与存储过程中声明的参数名相同。
此处是返回值，我习惯上设为"
；
第二个参数
（2），表明该参数的数据类型，具体的类型代码请参阅ADO参考，以下给出常用的类型代码：
adBigInt:
20;
adBinary:
128;
adBoolean:
11;
adChar:
129;
adDBTimeStamp:
135;
adEmpty:
0;
adInteger:
3;
adSmallInt:
2;
adTinyInt:
16;
adVarChar:
200;
对于返回值，只能取整形，且-1到-99为保留值；
第三个参数（4），表明参数的性质，此处4表明这是一个返回值。
此参数取值的说明如下：
0:
类型无法确定；
1:
输入参数；
2:
3：
输入或输出参数；
4:
返回值
以上给出的ASP代码，应该说是完整的代码，也即最复杂的代码，其实
可以简化为
MyComm.Parameters.AppendMyComm.CreateParameter（"
甚至还可以继续简化，稍后会做说明。
对于带参数的存储过程，只能使用Command对象调用（也有资料说可通过Connection对象或Recordset对象调用，但我没有试成过）。
4.有输入参数和输出参数的存储过程
返回值其实是一种特殊的输出参数。
在大多数情况下，我们用到的是同时有输入及输出参数的存储过程，比如我们想取得用户信息表中，某ID用户的用户名，这时候，有一个输入参数----用户ID，和一个输出参数----用户名。
实现这一功能的存储过程如下：
/*SP4*/
CREATEPROCEDUREdbo.getUserName
@UserIDint,
@UserNamevarchar（40）output
if@UserIDisnullreturn
select@UserName=username
fromdbo.[userinfo]
题目1
1、学校图书馆借书信息管理系统建立三个表：
学生信息表：
student
字段名称
数据类型
说明
stuID
char（10）
学生编号，主键
stuName
Varchar（10）
学生名称
major
Varchar（50）
专业
图书表：
book
BID
图书编号，主键
title
char（50）
书名
author
char（20）
作者
借书信息表：
borrow
borrowID
借书编号，主键
学生编号，外键
图书编号，外键
T_time
datetime
借书日期
B_time
还书日期
请编写SQL语句完成以下的功能：
1）查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期；
参考查询结果如下图所示：
2）查询所有借过图书的学生编号、学生名称、专业；
3）查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期；
4）查询目前借书但未归还图书的学生名称及未还图书数量；
附加：
建表语句:
USEmaster
GO
/*$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$*/
--检验数据库是否存在，如果为真，删除此数据库--
IFexists（SELECT*FROMsysdatabasesWHEREname='
BOOK'
DROPDATABASEBOOK
CREATEDATABASEBOOK
--建数据表--
USEBOOK
CREATETABLEstudent--学生信息表
（
stuIDCHAR（10）primarykey,--学生编号
stuNameCHAR（10）NOTNULL,--学生名称
majorCHAR（50）NOTNULL--专业
CREATETABLEbook--图书表
BIDCHAR（10）primarykey,--图书编号
titleCHAR（50）NOTNULL,--书名
authorCHAR（20）NOTNULL,--作者
CREATETABLEborrow--借书表
borrowIDCHAR（10）primarykey,--借书编号
stuIDCHAR（10）foreignkey（stuID）referencesstudent（stuID）,--学生编号
BIDCHAR（10）foreignkey（BID）referencesbook（BID）,--图书编号
T_timedatetimeNOTNULL,--借出日期
B_timedatetime--归还日期
--学生信息表中插入数据--
INSERTINTOstudent（stuID,stuName,major）VALUES（'
1001'
'
林林'
计算机'
1002'
白杨'
1003'
虎子'
英语'
1004'
北漂的雪'
工商管理'
1005'
五月'
数学'
--图书信息表中插入数据--
INSERTINTObook（BID,title,author）VALUES（'
B001'
人生若只如初见'
安意如'
B002'
入学那天遇见你'
晴空'
B003'
感谢折磨你的人'
如娜'
B004'
我不是教你诈'
刘庸'
B005'
英语四级'
白雪'
--借书信息表中插入数据--
INSERTINTOborrow（borrowID,stuID,BID,T_time,B_time）VALUES（'
T001'
2007-12-26'
null）
T002'
2008-1-5'
T003'
2007-10-8'
2007-12-25'
T004'
2007-12-16'
2008-1-7'
T005'
2007-12-22'
T006'
2008-1-6'
T007'
2007-9-11'
T008'
2007-12-10'
T009'
2007-10-16'
2007-12-18'
T010'
2007-9-15'
T011'
2007-12-28'
T012'
2007-12-30'
标准答案:
--1）查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期—
select学生编号=stuID,学生名称=（selectstuNamefromstudentwherestuID=borrow.stuID）,图书编号=BID,图书名称=（selecttitlefrombookwhereBID=borrow.BID）,借出日期=T_timefromborrowwherestuIDin（selectstuIDfromstudentwheremajor='
）andT_time>
'
2007-12-15'
andT_time<
2008-1-8'
--2）查询所有借过图书的学生编号、学生名称、专业--
select学生编号=stuID,学生名称=stuName,专业=majorfromstudentwherestuIDin（selectstuIDfromborrow）
--3）查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期--
select学生名称=（selectstuNamefromstudentwherestuID=borrow.stuID）,图书名称=（selecttitlefrombookwhereBID=borrow.BID）,借出日期=T_time,归还日期=B_timefromborrowwhereBIDin（selectBIDfrombookwhereauthor='
--4）查询目前借书但未归还图书的学生名称及未还图书数量--
select学生名称=（selectstuNamefromstudentwherestuID=borrow.stuID）,借书数量=count（*）fromborrowwhereB_timeisnullgroupbystuID
题目2
程序员工资表：
ProWage
ID
int
自动编号，主键
PName
Char（10）
程序员姓名
Wage
工资
创建一个存储过程，对程序员的工资进行分析，月薪1500到10000不等，如果有百分之五十的人薪水不到2000元，给所有人加薪，每次加100，再进行分析，直到有一半以上的人大于2000元为止，存储过程执行完后，最终加了多少钱？
例如：
如果有百分之五十的人薪水不到2000，给所有人加薪，每次加100元，直到有一半以上的人工资大于2000元，调用存储过程后的结果如图：
请编写T-SQL来实现如下功能：
1）创建存储过程，查询是否有一半程序员的工资在2200、3000、3500、4000、5000或6000元之上，如果不到分别每次给每个程序员加薪100元，至之一半程序员的工资达到2200，3000，3500，4000，5000或6000元。
2）创建存储过程，查询程序员平均工资在4500元，如果不到则每个程序员每次加200元，至到所有程序员平均工资达到4500元。
建表语句
Wage'
DROPDATABASEWage
CREATEDATABASEWage
USEWage
CREATETABLEProWage--程序员工资表
IDintidentity（1,1）primarykey,--工资编号
PNameCHAR（10）NOTNULL,--程序员姓名
WageintNOTNULL--工资
--插入数据--
INSERTINTOProWage（PName,Wage）VALUES（'
青鸟'
1900）
张三'
1200）
李四'
1800）
二月'
3500）
蓝天'
2780）
标准答案：
--1、创建存储过程--
ifexists（select*fromsysobjectswherename='
Sum_wage'
dropprocedureSum_wage
createprocedureSum_wage
@PWageint,
@AWageint,
@totalint
as
while（1=1）
begin
if（selectcount（*）fromProWage）>
2*（selectcount（*）fromProWagewhereWage>
=@PWage）
updateProWageset@total=@total+@AWage,Wage=Wage+@
升级会员 