/**3.存储过程统计已'北京'冠名的出版社的图书信息*/IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_book')DROP PROCEDURE proc_bookGOCREATE PROCEDURE proc_book@bname varchar(50) ASSELECT * FROM Book WHERE PubComp like @bnameGO--执行存储过程exec proc_book '北京%'
/**4.存储过程统计某一时间段的借阅信息*/IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_borrow')DROP PROCEDURE proc_borrowGOCREATE PROCEDURE proc_borrow@beginDate datetime, --注意参数不能直接用函数赋值@endDate datetimeAS IF @beginDate IS NULL SET @beginDate=DateAdd(MM,-1,getDate()) IF @endDate IS NULL SET @endDate=getDate() SELECT * FROM Borrow WHERE LendDate BETWEEN @beginDate AND @endDateGO
/**5.存储过程实现插入借阅记录，输入参数：借书人ID、姓名、借书的名称，要求：（1）图书信息表Book对应的图书数量减1；（2）读者信息表Reader对应的读者已借书数量加1。如没有该借阅者的信息，则新加一条读者信息条记录；（3）向图书借阅表Borrow中添加一条借阅记录，借阅日期、应归还日期、实际归还日期都采用默认值。表结构参考第2章作业的简答题。*/IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_addreader')DROP PROCEDURE proc_addreaderGOCREATE PROCEDURE proc_addreader@RID varchar(50), --reader ID@Rname varchar(50), --reader name@Bname varchar(50) --borrow book nameASBEGIN TRANSACTIONDECLARE @errorSum intSET @errorSum=0UPDATE Book SET BCount=BCount-1 WHERE(BName=@Bname)SET @errorSum=@errorSum+@@errorIF EXISTS(SELECT * FROM Reader WHERE RName=@Rname)UPDATE Reader SET LendNum=LendNum+1 WHERE RName=@RnameELSEINSERT INTO Reader VALUES(@RID,@RName,1) SET @errorSum=@errorSum+@@errorDECLARE @BID varchar(50) SELECT @BID=BID FROM Book WHERE BName=@BnameINSERT INTO Borrow(RID,BID) VALUES (@RID,@BID)SET @errorSum=@errorSum+@@errorIF (@errorSum<>0)BEGIN print('失败了')ROLLBACK TRANSACTION END ELSE BEGIN print('成功了')COMMIT TRANSACTION上机练习：
------------------
USE MySchoolGOEXEC sp_columns Student --查看表Student中列的信息EXEC sp_help Student --查看表Student的所有信息EXEC sp_helpconstraint Student --查看表Student的约束
END GO
--------------------/*---创建存储过程----*/CREATE PROCEDURE usp_grade_subjectAS SELECT GradeName,SubjectName,ClassHour FROM Grade INNER JOIN Subject ON Grade.GradeId=Subject.GradeId ORDER BY Subject.GradeId,SubjectNoGO/*---调用执行存储过程---*/EXEC usp_grade_subject
--------------------------------CREATE PROCEDURE usp_query_subject @GradeName VARCHAR(50) = NULLAS IF @GradeName IS NULL SELECT GradeName,SubjectName,ClassHour FROM Grade LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId UNION SELECT GradeName,' ',SUM(ClassHour)FROM Grade LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId GROUP BY GradeName ELSE SELECT GradeName,SubjectName,ClassHour FROM Grade LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId CREATE PROCEDURE usp_query_subject @CourseNum INT OUTPUT, @HourNum INT OUTPUT, @GradeName VARCHAR(50)AS IF LEN(@GradeName) = 0 BEGIN PRINT '学期名称不能为空' RETURN END PRINT '---------学期课程信息如下------------' SELECT GradeName,SubjectName,ClassHour FROM Grade LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId WHERE GradeName=@GradeName SELECT @CourseNum=COUNT(0), @HourNum=SUM(ClassHour) FROM Grade INNER JOIN Subject ON Grade.GradeId=Subject.GradeId WHERE GradeName=@GradeNameGO
WHERE GradeName=@GradeName UNION SELECT GradeName,' ',SUM(ClassHour)FROM Grade LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId WHERE GradeName=@GradeName GROUP BY GradeNameGOCREATE PROCEDURE usp_insert_subject @SubjectNo int OUTPUT, @GradeId int OUTPUT,@GradeName varchar(50),@SubjectName varchar(50), @ClassHour int = 36AS DECLARE @errNum intSET @errNum = 0IF (LEN(RTRIM(@SubjectName))=0 OR LEN(RTRIM(@GradeName))=0)RETURN -1 BEGIN TRANSACTION IF NOT EXISTS(SELECT * FROM Grade WHERE GradeName = @GradeName) BEGININSERT INTO Grade (GradeName) VALUES (@GradeName)SET @errNum = @errNum + @@ERRORSELECT @GradeId=@@IDENTITY ENDELSESELECT @GradeId=GradeId FROM Grade WHERE GradeName = @GradeNameINSERT INTO Subject (SubjectName,ClassHour,GradeId) VALUES (@SubjectName,@ClassHour,@GradeId)SET @errNum = @errNum + @@ERRORSELECT @SubjectNo=@@IDENTITYIF (@errNum > 0) BEGINROLLBACK TRANSACTIONRETURN 0 ENDELSE BEGINCOMMIT TRANSACTIONRETURN 1 ENDGO--调用存储过程DECLARE @SubjectNo intDECLARE @GradeId intDECLARE @GradeName varchar(50)DECLARE @SubjectName varchar(50)DECLARE @ClassHour intDECLARE @rt intSET @GradeName = 'Y2'SET @SubjectName = 'Linux'SET @ClassHour = 10EXEC @rt=usp_insert_subject @SubjectNo OUTPUT,@GradeId OUTPUT,@GradeName,@SubjectName,@ClassHourIF (@rt = 1) BEGINPRINT '增加课程'+@SubjectName+'记录成功'PRINT '学期编号是' + CAST(@GradeId AS varchar(10)) + '，学期名称是' + @GradeNamePRINT '课程编号是' + CAST(@SubjectNo AS varchar(10)) + '，课程名称是' + @SubjectName ENDELSE if (@rt = 0)PRINT '增加课程记录失败！'ELSEPRINT '学期名称或课程名称不能为空，请重新执行！'GO
EXEC usp_query_subject 's2'
-----------------------------------------------------------------------
0 0
