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