第七章上机与课后

练习1：使用存储过程查看表信息

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

练习2：查询获得各学期课程信息

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

练习3：用存储过程查指定学期课程

CREATE PROCEDURE usp_query_subject
  @GradeName VARCHAR(50) = NULL
AS
  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
    WHERE GradeName=@GradeName
    UNION
    SELECT GradeName,' ',SUM(ClassHour)FROM Grade
    LEFT JOIN Subject  ON Grade.GradeId=Subject.GradeId
    WHERE GradeName=@GradeName
    GROUP BY GradeName
GO

EXEC usp_query_subject 's2'

练习4：存储过程获指定学期课程数

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=@GradeName
GO

练习5：插入新课程记录
 
CREATE PROCEDURE usp_insert_subject
    @SubjectNo int OUTPUT,
    @GradeId int OUTPUT,
 @GradeName varchar(50),
 @SubjectName varchar(50),
    @ClassHour int = 36
AS
    DECLARE @errNum int
 SET @errNum = 0
 
 IF (LEN(RTRIM(@SubjectName))=0 OR LEN(RTRIM(@GradeName))=0)
  RETURN -1

    BEGIN TRANSACTION
    IF NOT EXISTS(SELECT * FROM Grade WHERE GradeName = @GradeName)
   BEGIN
  INSERT INTO Grade (GradeName) VALUES (@GradeName)
  SET @errNum = @errNum + @@ERROR
  SELECT @GradeId=@@IDENTITY
   END
 ELSE
  SELECT @GradeId=GradeId FROM Grade WHERE GradeName = @GradeName

 INSERT INTO Subject (SubjectName,ClassHour,GradeId)
  VALUES (@SubjectName,@ClassHour,@GradeId)
 SET @errNum = @errNum + @@ERROR

 SELECT @SubjectNo=@@IDENTITY

 IF (@errNum > 0)
   BEGIN
  ROLLBACK TRANSACTION
  RETURN 0
   END
 ELSE
   BEGIN
  COMMIT TRANSACTION
  RETURN 1
   END
GO

--调用存储过程
DECLARE @SubjectNo int
DECLARE @GradeId int
DECLARE @GradeName varchar(50)
DECLARE @SubjectName varchar(50)
DECLARE @ClassHour int
DECLARE @rt int

SET @GradeName = 'Y2'
SET @SubjectName = 'Linux'
SET @ClassHour = 10
EXEC @rt=usp_insert_subject @SubjectNo OUTPUT,@GradeId OUTPUT,@GradeName,@SubjectName,@ClassHour
IF (@rt = 1)
  BEGIN
 PRINT '增加课程'+@SubjectName+'记录成功'
 PRINT '学期编号是' + CAST(@GradeId AS varchar(10)) + '，学期名称是' + @GradeName
 PRINT '课程编号是' + CAST(@SubjectNo AS varchar(10)) + '，课程名称是' + @SubjectName
  END
ELSE if (@rt = 0)
 PRINT '增加课程记录失败！'
ELSE
 PRINT '学期名称或课程名称不能为空，请重新执行！'
GO

1.显示图书借阅记录

 
/*
*2.存储过程实现分页 @page为第几页 @row为每页多少行
*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_page')
 DROP PROCEDURE proc_page
GO

CREATE PROCEDURE proc_page
 @page int,
 @row int =10
  As
    DECLARE @total AS int
    SELECT @total=count(*) FROM Borrow
 IF @total<=(@page-1)*@row
   print('总条数为'+convert(varchar(5),@total)+'请确认参数')
 ELSE
  SELECT TOP (@row)  * FROM Borrow  WHERE rid NOT IN(SELECT TOP ((@page-1)*@row) rid FROM Borrow)
GO
--执行语句
exec proc_page 1
2.显示图书信息
 
/*
*3.存储过程统计已'北京'冠名的出版社的图书信息
*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_book')
 DROP PROCEDURE proc_book
GO
CREATE PROCEDURE proc_book
@bname varchar(50)
 AS
 SELECT * FROM Book WHERE PubComp like @bname
GO

--执行存储过程
exec proc_book '北京%'
3.存储过程显示借阅次数

/*
*4.存储过程统计某一时间段的借阅信息
*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_borrow')
 DROP PROCEDURE proc_borrow
GO
CREATE PROCEDURE proc_borrow
 @beginDate datetime,  --注意参数不能直接用函数赋值
 @endDate datetime
AS
  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 @endDate
GO
4.存储过程实现插入借阅记
1./* 
2.*5.存储过程实现插入借阅记录， 
3.输入参数：借书人ID、姓名、借书的名称，要求： 
4.（1）图书信息表Book对应的图书数量减1； 
5.（2）读者信息表Reader对应的读者已借书数量加1。如没有该借阅者的信息，则新加一条读者信息条记录； 
6.（3）向图书借阅表Borrow中添加一条借阅记录，借阅日期、应归还日期、实际归还日期都采用默认值。 
7.表结构参考第2章作业的简答题。 
8.*/ 
9. 
10.IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_addreader') 
11.    DROP PROCEDURE proc_addreader 
12.GO 
13. 
14.CREATE PROCEDURE proc_addreader 
15.    @RID varchar(50), --reader ID 
16.    @Rname varchar(50), --reader name 
17.    @Bname varchar(50)  --borrow book name 
18.AS 
19.    BEGIN TRANSACTION 
20.    DECLARE @errorSum int 
21.    SET @errorSum=0 
22.    UPDATE Book SET BCount=BCount-1 WHERE(BName=@Bname) 
23.    SET @errorSum=@errorSum+@@error 
24.    IF EXISTS(SELECT * FROM Reader WHERE RName=@Rname) 
25.        UPDATE  Reader SET LendNum=LendNum+1 WHERE RName=@Rname 
26.    ELSE 
27.        INSERT INTO Reader VALUES(@RID,@RName,1)  
28.    SET @errorSum=@errorSum+@@error 
29.    DECLARE @BID varchar(50) 
30.    SELECT @BID=BID FROM Book WHERE BName=@Bname 
31.    INSERT INTO Borrow(RID,BID) VALUES (@RID,@BID) 
32.    SET @errorSum=@errorSum+@@error 
33. 
34.    IF (@errorSum<>0) 
35.        BEGIN  
36.            print('失败了') 
37.            ROLLBACK TRANSACTION  
38.        END  
39.    ELSE  
40.        BEGIN  
41.            print('成功了') 
42.            COMMIT TRANSACTION 
43.        END  
44.GO