第7章 數據庫編程

?

答題要求：要求每題手寫答題并在答案右下角簽名，再拍照粘貼在每題下方。

1.?創(chuàng)建一個游標，逐行顯示選修了《高等數學》課程，成績>=90顯示為優(yōu)，90<成績<=80顯示為良的學生的姓名和成績等級。

???游標語句如下：

DECLARE @sName varchar(20), @score tinyint -- 聲明2個變量

?

DECLARE myCur CURSOR?FOR ? ? -- 定義游標

???SELECT studentName, score

???FROM Student a, Course b, Score c

???WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo

?????????AND courseName='高等數學'

???ORDER BY studentName

?

OPEN myCur ? -- 打開游標，游標指向游標集(查詢結果集)的第1個元組

??????

-- 輸出表頭信息

PRINT convert(char(10), '姓名')+convert(char(10), '成績等級')

PRINT replicate(' - ', 20)

FETCH myCur INTO @sName, @score ??--獲取第1個元組的值存入變量@sName, @score，游標下移

-- ?循環(huán)處理游標集中的每一個元組

WHILE ( @@FETCH_STATUS = 0)

BEGIN

IF @score>=90 ? ? ?-- 顯示變量@sName和成績等級“優(yōu)”

PRINT convert(char(10), @sName) + convert(char(10), '優(yōu)')

IF @score>=80 AND @score<90 ? -- 顯示變量@sName和成績等級“良”

PRINT convert(char(10), @sName) + convert(char(10), '良')

FETCH myCur INTO @sName, @score ?--獲取當前游標指向元組值, 游標下移

END

PRINT replicate('-' , 20) ????-- 輸出表格底線

CLOSE myCur ??????????????????-- 關閉游標

DEALLOCATE myCur ?????????????-- 釋放游標

?

?

?

2.?創(chuàng)建一個存儲過程，根據輸入的課程號統(tǒng)計該課程的選課人數和平均分，并將統(tǒng)計結果返回給調用者。

???存儲過程語句如下：

CREATE PROCEDURE proxkBycourseNo(@cNo char(3), @count?tinyint OUTPUT, @avg numeric(6, 2) OUTPUT)

AS

???????SELECT courseNo, @count=count(DISTINCT *), @avg=avg(score)

???????FROM Score

???????WHERE courseNo=@cNo

???GROUP BY courseNo

?

3.?創(chuàng)建滿足下述要求的觸發(fā)器，限制學生所在學院的取值范圍為{會計學院，信息管理系，金融學院，網絡空間與安全學院}

???觸發(fā)器語句如下：

CREATE TRIGGER instituteIns ???-- 創(chuàng)建插入類型的觸發(fā)器

ON Class ??????????????????????-- 觸發(fā)器作用的基本表

FOR INSERT ??????-- 觸發(fā)器的類型：觸發(fā)該觸發(fā)器被自動執(zhí)行的事件

AS

IF EXISTS (SELECT * FROM inserted WHERE institute?NOT IN ('會計學院', '信息管理系',

'金融學院','網絡空間與安全學院' ))

????????ROLLBACK ???-- 事務的回滾操作，即終止觸發(fā)該觸發(fā)器的插入操作

?

或者：

?

CREATE TRIGGER instituteUpt ???-- 創(chuàng)建修改類型的觸發(fā)器

ON Class ??????????????????????-- 觸發(fā)器作用的基本表

FOR UPDATE???????-- 觸發(fā)器的類型：觸發(fā)該觸發(fā)器被自動執(zhí)行的事件

AS

IF EXISTS (SELECT * FROM inserted WHERE institute?NOT IN ('會計學院', '信息管理系',

'金融學院','網絡空間與安全學院' ))

????????ROLLBACK ???-- 事務的回滾操作，即終止觸發(fā)該觸發(fā)器的插入操作

?

或者：

?

CREATE TRIGGER instituteUptIns ???-- 創(chuàng)建修改類型的觸發(fā)器

ON Class ?????????????????????????-- 觸發(fā)器作用的基本表

FOR UPDATE,?INSERT???????-- 觸發(fā)器的類型：觸發(fā)該觸發(fā)器被自動執(zhí)行的事件

AS

IF EXISTS (SELECT * FROM inserted WHERE institute?NOT IN ('會計學院', '信息管理系',

'金融學院','網絡空間與安全學院' ))

????????ROLLBACK ???-- 事務的回滾操作，即終止觸發(fā)該觸發(fā)器的插入操作

?

4.?編寫一個存儲過程實現(xiàn)下面的應用：從賬戶1轉指定數額的款項amount到賬戶2中。假設賬戶關系表為Account(Accountnum,Total)，其中，Accountnum表示賬戶賬號，數據類型為INT，Total表示賬戶余額，數據類型為FLOAT類型。

???存儲過程語句如下：

CREATE PROCEDURE TRANSFER(@inAccount int,@outAccount int,@amount float)

AS

????DECLARE @totalDepositOut float,@totalDepositIn float,@inAccount int

????BEGIN

-- 業(yè)務處理

--檢查轉出賬戶是否存在或是否余額不足

SELECT @totalDepositOut=total FROM Account WHERE Accountnum=@outAccount

IF @totalDepositOut<@amount OR @totalDepositOut IS NULL

??ROLLBACK

??RETURN

ENDIF

?

--檢查轉入賬戶是否存在

SELECT @totalDepositIn=total FROM Account WHERE Accountnum=@inAccount

IF @totalDepositIn IS NULL

??ROLLBACK

??RETURN

ENDIF

--修改轉出賬戶與轉入賬戶的賬戶余額

UPDATE Account SET total=total-@amount WHERE Accountnum=@outAccount

UPDATE Account SET total=total+@amount WHERE Accountnum=@inAccount

COMMIT

END

?