SQL 第七章

-第七章上机一  
USE MySchool  
GO  
EXEC sp_columns Student  --查看表Student中列的信息  
EXEC sp_help Student  --查看表Student的所有信息  
EXEC sp_helpconstraint Student --查看表Student的约束  
  
--上机二  
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   
  
--上机三  
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'  
  
--上机四  
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  
  
--上机五  
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