07存儲 上機


練習1:使用存儲過程查看表信息

[sql] view plain copy 在CODE上查看代碼片派生到我的代碼片
USE MySchool
GO
EXEC sp_columns Student --查看表Student中列的信息
EXEC sp_help Student --查看表Student的所有信息
EXEC sp_helpconstraint Student --查看表Student的約束

練習2:查詢獲得各學期課程信息

[sql] view plain copy 在CODE上查看代碼片派生到我的代碼片
/*---創建存儲過程----*/
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:用存儲過程查指定學期課程
[sql] view plain copy 在CODE上查看代碼片派生到我的代碼片
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 [email protected]
UNION
SELECT GradeName,' ',SUM(ClassHour)FROM Grade
LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId
WHERE [email protected]
GROUP BY GradeName
GO

EXEC usp_query_subject 's2'

練習4:存儲過程獲指定學期課程數

[sql] view plain copy 在CODE上查看代碼片派生到我的代碼片
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 [email protected]

SELECT @CourseNum=COUNT(0), @HourNum=SUM(ClassHour)
FROM Grade
INNER JOIN Subject ON Grade.GradeId=Subject.GradeId
WHERE [email protected]
GO

練習5:插入新課程記錄

[sql] view plain copy 在CODE上查看代碼片派生到我的代碼片
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 [email protected]+'記錄成功'
PRINT '學期編號是' + CAST(@GradeId AS varchar(10)) + ',學期名稱是' + @GradeName
PRINT '課程編號是' + CAST(@SubjectNo AS varchar(10)) + ',課程名稱是' + @SubjectName
END
ELSE if (@rt = 0)
PRINT '增加課程記錄失敗!'
ELSE
PRINT '學期名稱或課程名稱不能為空,請重新執行!'
GO

1.顯示圖書借閱記錄

[sql] view plain copy 在CODE上查看代碼片派生到我的代碼片
/*
*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)[email protected]
print('總條數為'+convert(varchar(5),@total)+'請確認參數')
ELSE
SELECT TOP (@row) * FROM Borrow WHERE rid NOT IN(SELECT TOP ((@page-1)[email protected]) rid FROM Borrow)
GO
--執行語句
exec proc_page 1

2.顯示圖書信息
[sql] view plain copy 在CODE上查看代碼片派生到我的代碼片
/*
*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.存儲過程顯示借閱次數
[sql] view plain copy 在CODE上查看代碼片派生到我的代碼片
/*
*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.存儲過程實現插入借閱記錄

[sql] view plain copy 在CODE上查看代碼片派生到我的代碼片
/*
*5.存儲過程實現插入借閱記錄,
輸入參數:借書人ID、姓名、借書的名稱,要求:
(1)圖書信息表Book對應的圖書數量減1;
(2)讀者信息表Reader對應的讀者已借書數量加1。如沒有該借閱者的信息,則新加一條讀者信息條記錄;
(3)向圖書借閱表Borrow中添加一條借閱記錄,借閱日期、應歸還日期、實際歸還日期都采用默認值。
表結構參考第2章作業的簡答題。
*/

IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_addreader')
DROP PROCEDURE proc_addreader
GO

CREATE PROCEDURE proc_addreader
@RID varchar(50), --reader ID
@Rname varchar(50), --reader name
@Bname varchar(50) --borrow book name
AS
BEGIN TRANSACTION
DECLARE @errorSum int
SET @errorSum=0
UPDATE Book SET BCount=BCount-1 WHERE([email protected])
SET @[email protected]+@@error
IF EXISTS(SELECT * FROM Reader WHERE [email protected])
UPDATE Reader SET LendNum=LendNum+1 WHERE [email protected]
ELSE
INSERT INTO Reader VALUES(@RID,@RName,1)
SET @[email protected]+@@error
DECLARE @BID varchar(50)
SELECT @BID=BID FROM Book WHERE [email protected]
INSERT INTO Borrow(RID,BID) VALUES (@RID,@BID)
SET @[email protected]+@@error

IF (@errorSum<>0)
BEGIN
print('失敗瞭')
ROLLBACK TRANSACTION
END
ELSE
BEGIN
print('成功瞭')
COMMIT TRANSACTION
END
GO

0 個評論

要回覆文章請先登錄註冊