SQL高级第七章存储过程

作业：

*2.存储过程实现分页 @page为第几页 @row为每页多少行
*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME=&＃39;proc_page&＃39;)DROP PROCEDURE proc_page
GOCREATE PROCEDURE proc_page@page int,@row int =10AsDECLARE @total AS int SELECT @total=count(*) FROM BorrowIF @total<=(@page-1)*@rowprint(&＃39;总条数为&＃39;+convert(varchar(5),@total)+&＃39;请确认参数&＃39;)ELSESELECT TOP (@row) * FROM Borrow WHERE rid NOT IN(SELECT TOP ((@page-1)*@row) rid FROM Borrow)
GO
--执行语句
exec proc_page 1

/*
*3.存储过程统计已&＃39;北京&＃39;冠名的出版社的图书信息
*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME=&＃39;proc_book&＃39;)DROP PROCEDURE proc_book
GO
CREATE PROCEDURE proc_book
@bname varchar(50)ASSELECT * FROM Book WHERE PubComp like @bname
GO--执行存储过程
exec proc_book &＃39;北京%&＃39;

/*
*4.存储过程统计某一时间段的借阅信息
*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME=&＃39;proc_borrow&＃39;)DROP PROCEDURE proc_borrow
GO
CREATE PROCEDURE proc_borrow@beginDate datetime, --注意参数不能直接用函数赋值@endDate datetime
ASIF @beginDate IS NULLSET @beginDate=DateAdd(MM,-1,getDate())IF @endDate IS NULLSET @endDate=getDate()SELECT * FROM Borrow WHERE LendDate BETWEEN @beginDate AND @endDate
GO

/*
*5.存储过程实现插入借阅记录，
输入参数：借书人ID、姓名、借书的名称，要求：
（1）图书信息表Book对应的图书数量减1；
（2）读者信息表Reader对应的读者已借书数量加1。如没有该借阅者的信息，则新加一条读者信息条记录；
（3）向图书借阅表Borrow中添加一条借阅记录，借阅日期、应归还日期、实际归还日期都采用默认值。
表结构参考第2章作业的简答题。
*/IF EXISTS (SELECT * FROM sysobjects WHERE NAME=&＃39;proc_addreader&＃39;)DROP PROCEDURE proc_addreader
GOCREATE PROCEDURE proc_addreader@RID varchar(50), --reader ID@Rname varchar(50), --reader name@Bname varchar(50) --borrow book name
ASBEGIN 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(&＃39;失败了&＃39;)ROLLBACK TRANSACTION END ELSE BEGIN print(&＃39;成功了&＃39;)COMMIT TRANSACTION

上机练习：

------------------

USE MySchool
GO
EXEC sp_columns Student --查看表Student中列的信息
EXEC sp_help Student --查看表Student的所有信息
EXEC sp_helpconstraint Student --查看表Student的约束

END GO

--------------------

/*---创建存储过程----*/
CREATE PROCEDURE usp_grade_subject
AS SELECT GradeName,SubjectName,ClassHour FROM Grade INNER JOIN SubjectON Grade.GradeId=Subject.GradeIdORDER BY Subject.GradeId,SubjectNo
GO
/*---调用执行存储过程---*/
EXEC usp_grade_subject

--------------------------------

CREATE PROCEDURE usp_query_subject@GradeName VARCHAR(50) = NULL
AS IF @GradeName IS NULLSELECT GradeName,SubjectName,ClassHour FROM Grade LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId UNION SELECT GradeName,&＃39; &＃39;,SUM(ClassHour)FROM GradeLEFT JOIN Subject ON Grade.GradeId=Subject.GradeId GROUP BY GradeNameELSESELECT GradeName,SubjectName,ClassHour FROM GradeLEFT 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) = 0BEGINPRINT &＃39;学期名称不能为空&＃39;RETURNENDPRINT &＃39;---------学期课程信息如下------------&＃39;SELECT GradeName,SubjectName,ClassHour FROM Grade LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId WHERE GradeName=@GradeNameSELECT @CourseNum=COUNT(0), @HourNum=SUM(ClassHour)FROM GradeINNER JOIN Subject ON Grade.GradeId=Subject.GradeId WHERE GradeName=@GradeName
GO

WHERE GradeName=@GradeName UNION SELECT GradeName,&＃39; &＃39;,SUM(ClassHour)FROM Grade LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId WHERE GradeName=@GradeName GROUP BY GradeNameGO

CREATE PROCEDURE usp_insert_subject@SubjectNo int OUTPUT,@GradeId int OUTPUT,@GradeName varchar(50),@SubjectName varchar(50),@ClassHour int = 36
AS DECLARE @errNum intSET @errNum = 0IF (LEN(RTRIM(@SubjectName))=0 OR LEN(RTRIM(@GradeName))=0)RETURN -1BEGIN TRANSACTIONIF NOT EXISTS(SELECT * FROM Grade WHERE GradeName = @GradeName)BEGININSERT INTO Grade (GradeName) VALUES (@GradeName)SET @errNum = @errNum + @@ERRORSELECT @GradeId=@@IDENTITYENDELSESELECT @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 ENDELSEBEGINCOMMIT TRANSACTIONRETURN 1END
GO--调用存储过程
DECLARE @SubjectNo int
DECLARE @GradeId int
DECLARE @GradeName varchar(50)
DECLARE @SubjectName varchar(50)
DECLARE @ClassHour int
DECLARE @rt intSET @GradeName = &＃39;Y2&＃39;
SET @SubjectName = &＃39;Linux&＃39;
SET @ClassHour = 10
EXEC @rt=usp_insert_subject @SubjectNo OUTPUT,@GradeId OUTPUT,@GradeName,@SubjectName,@ClassHour
IF (@rt = 1)BEGINPRINT &＃39;增加课程&＃39;+@SubjectName+&＃39;记录成功&＃39;PRINT &＃39;学期编号是&＃39; + CAST(@GradeId AS varchar(10)) + &＃39;，学期名称是&＃39; + @GradeNamePRINT &＃39;课程编号是&＃39; + CAST(@SubjectNo AS varchar(10)) + &＃39;，课程名称是&＃39; + @SubjectNameEND
ELSE if (@rt = 0)PRINT &＃39;增加课程记录失败！&＃39;
ELSEPRINT &＃39;学期名称或课程名称不能为空，请重新执行！&＃39;
GO


EXEC usp_query_subject &＃39;s2&＃39;




-----------------------------------------------------------------------