實
驗
報
告
課程名稱：
SQL
Server
數(shù)據(jù)庫基礎(chǔ)
任課教師：
池宗琳
實驗名稱：
存儲過程
年級、專業(yè)：
2018級電子信息工程
學(xué)
號：
20181060093
姓
名：
馬
信
日期：
2019
年
月
日
云南大學(xué)
信息學(xué)院
一、實驗?zāi)康?、掌握使用SELECT語句實現(xiàn)對數(shù)據(jù)庫的簡單查詢
2、掌握使用SELECT語句實現(xiàn)對數(shù)據(jù)庫的多表鏈接查詢和子查詢
二、實驗內(nèi)容、方法、步驟、結(jié)果與分析
完成以下各題功能，保存或記錄實現(xiàn)各題功能的Transact-SQL語句。
1.在數(shù)據(jù)庫HrSystem中創(chuàng)建存儲過程avg._wage,用于求所有員工的平均工資，并通過輸出參數(shù)返回該平均工資。要求在創(chuàng)建存儲過程之前要首先判斷該存儲過程是否已經(jīng)存在，如果存在，則將其刪除。
USE
Hrsystem
GO
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
＇avg_wage＇)
DROP
PROC
avg_wage
GO
CREATE
PROC
avg_wage
@AVWAGE
AS
FLOAT
AS
SELECT
@AVWAGE
=
AVG(Wage)
FROM
Employees
@AVWAGE
GO
2.執(zhí)行第1題創(chuàng)建的存儲過程avg_
wage,打印員工平均工資。
USE
Hrsystem
GO
DECLARE
@avg
AS
FLOAT
EXEC
avg_wage
@avg
3.在數(shù)據(jù)庫HrSystem中創(chuàng)建存儲過程max_
wage,根據(jù)指定的部門名稱(輸人參數(shù))返回該部門的最高工資(輸出參數(shù))。要求在創(chuàng)建存儲過程之前要首先判斷該存儲過程是否已經(jīng)存在，如果存在，則將其刪除。
USE
Hrsystem
GO
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
＇max_wage＇)
DROP
PROC
avg_wage
GO
CREATE
PROC
max_wage
@Dename
varchar(20),@MAX_wage
FLOAT
OUTPUT
AS
SELECT
@MAX_wage
=
MAX(Wage)
FROM
Employees
WHERE
Dep_id
IN(SELECT
Dep_id
FROM
Departments
WHERE
Dep_name
=
@Dename)
GROUP
BY
Dep_id
4.執(zhí)行第3題創(chuàng)建的存儲過程max
wage,指定部門為“財務(wù)部”，打印該類部門的最高工資。
USE
Hrsystem
GO
DECLARE
@MAX_wage
FLOAT
EXEC
max_wage
＇財務(wù)部＇,@MAX_wage
OUTPUT
@MAX_wage
5.刪除存儲過程avg_
wage和I
max_
wage。
USE
Hrsystem
GO
DROP
PROCEDURE
max_wage
GO
DROP
PROCEDURE
avg_wage
(二)觸發(fā)器
創(chuàng)建一個“學(xué)生信息”數(shù)據(jù)庫，包含“學(xué)生基本信息”表、“專業(yè)”表和“系”表，各表包含的字段如下。
“學(xué)生基本信息”表:學(xué)號;姓名;性別;班級;出生日期;專業(yè)編號。
“專業(yè)”表:專業(yè)編號;專業(yè)名稱;系編號。
“系”
表:系編號;系名稱;系簡介。
各字段類型按其實際含義自行定義，輸人-
-些數(shù)據(jù)，要求數(shù)據(jù)要有代表性。
以下操作要求全部在SQL
Server
Management
Studio
中完成，保存或記錄實現(xiàn)各題功能的Transcat-SQL語句（包括測試相應(yīng)觸發(fā)器是否生效的相關(guān)語句及測試結(jié)果)。
1.在“專業(yè)”表上創(chuàng)建一個INSERT觸發(fā)器“TRG1”。當(dāng)發(fā)生插入專業(yè)表操作時，將顯示插入的記錄。
USE
學(xué)生信息
GO
CREATE
TRIGGER
TRG1
ON
專業(yè)
FOR
INSERT
AS
DECLARE
@depid
INT
DECLARE
@depname
varchar(50)
DECLARE
@number
INT
SELECT
@depid
=
專業(yè)編號
FROM
inserted
SELECT
@number
=
系編號
FROM
inserted
SELECT
@depname
=
專業(yè)名稱
FROM
inserted
PRINT(＇系名：＇+STR(@depid)+＇專業(yè)名：＇+STR(@depname)+＇系的編號：＇+str(@number))
INSERT
INTO
專業(yè)
(專業(yè)編號,專業(yè)名稱,系編號)
VALUES(@depid,@depname,@number)
2.在“專業(yè)”表上創(chuàng)建一個DELETE觸發(fā)器“TRG2”，當(dāng)發(fā)生刪除操作時，將給出警告、列出刪除的記錄并撤銷刪除。
USE
學(xué)生信息
GO
CREATE
TRIGGER
TRG2
ON
專業(yè)
FOR
DELETE
AS
PRINT(＇警告！禁止刪除＇)
ROLLBACK
TRANSACTION
3.在“專業(yè)”表上創(chuàng)建一個UPDTAE觸發(fā)器“TRG3”，當(dāng)發(fā)生更新“專業(yè)名稱”字段的操作時，給出警告并撤銷更新
USE
學(xué)生信息
GO
CREATE
TRIGGER
TRG3
ON
專業(yè)
FOR
UPDATE
AS
DECLARE
@temp_proid
INT
DECLARE
@temp_xiid
INT
DECLARE
@temp_porna
varchar(50)
SELECT
@temp_porna
=
專業(yè)名稱
FROM
inserted
IF
@temp_porna
IS
not
NULL
BEGIN
PRINT(＇禁止修改專業(yè)名稱＇)
ROLLBACK
TRANSACTION
END
ELSE
BEGIN
SELECT
@temp_porna
=
專業(yè)名稱
FROM
deleted
SELECT
@temp_xiid
=
系編號
FROM
deleted
SELECT
@temp_proid
=
專業(yè)編號
FROM
deleted
UPDATE
專業(yè)
SET
專業(yè)編號
=
@temp_proid,系編號
=
@temp_xiid
WHERE
專業(yè)名稱
=
@temp_porna
END
4.在“學(xué)生基本信息”表上創(chuàng)建一
一個更新觸發(fā)器“TRG4“，當(dāng)發(fā)生更新“學(xué)號”或“姓名”字段的操作時給出警告，并撤銷更新。
USE
學(xué)生信息
GO
CREATE
TRIGGER
TRG4
ON
學(xué)生基本信息
FOR
UPDATE
AS
DECLARE
@temp_stunum
char(11)
DECLARE
@temp_name
char(10)
DECLARE
@temp_gender
BIT
DECLARE
@temp_class
varchar(10)
DECLARE
@temp_date
DATETIME
DECLARE
@temp_proID
INT
SELECT
@temp_name
=
姓名
FROM
inserted
SELECT
@temp_stunum
=
學(xué)號
FROM
inserted
IF
@temp_name
IS
NOT
NULL
OR
@temp_stunum
IS
NOT
NULL
BEGIN
PRINT(＇禁止修改學(xué)號或者姓名＇)
ROLLBACK
TRANSACTION
END
ELSE
BEGIN
SELECT
@temp_stunum
=
學(xué)號
FROM
deleted
SELECT
@temp_name
=
姓名
FROM
deleted
SELECT
@temp_gender
=
性別
FROM
inserted
SELECT
@temp_class
=
班級
FROM
inserted
SELECT
@temp_date
=
出生日期
FROM
inserted
SELECT
@temp_proID
=
專業(yè)編號
FROM
inserted
UPDATE
學(xué)生基本信息
SET
性別
=
@temp_gender,班級
=
@temp_class,出生日期
=
@temp_date,專業(yè)編號
=
@temp_proID
WHERE
學(xué)號
=
@temp_stunum
END
5.刪除以
上各題創(chuàng)建的所有觸發(fā)器。做好“學(xué)生信息”數(shù)據(jù)庫的備份，以備第10章、第章上機(jī)操作時使用。
USE
學(xué)生信息
GO
DROP
TRIGGER
TRG1
DROP
TRIGGER
TRG2
DROP
TRIGGER
TRG3
DROP
TRIGGER
TRG4
三、實驗小結(jié)【對自己而言，通過實驗學(xué)到的關(guān)鍵技術(shù)方法】
掌握了觸發(fā)器的一些基本方法：
1.創(chuàng)建觸發(fā)器
2.分清了觸發(fā)器的種類，但是還是需要深入了解dml觸發(fā)器中三個種類觸發(fā)器的不同。
3.了解了觸發(fā)器在我們實際操作中的作用
4.
點擊咨詢