目錄
- 實驗9：存盤程序實驗
- 實驗10：觸發器實驗
實驗9：存盤程序實驗
自擬題目完成8個存盤程序的撰寫及除錯，熟練掌握存盤程序的使用，也可采用下圖中作業上的題目，
SQL陳述句代碼
--1.例1
use 學生作業管理資料庫;
select * from 學生表;
select * from 課程表;
select * from 學生作業表;
--先查看是否存在名字為student_course的存盤程序，如果有，洗掉
if exists (select name from sysobjects where name='student_course' and type='P')
drop procedure stuent_course;
--創建存盤程序
create procedure student_course
as
select 學生表.學號,姓名,課程名,作業1成績
from 學生表,課程表,學生作業表
where 學生表.學號=學生作業表.學號 and 課程表.課程號=學生作業表.課程號 and 姓名='張艷'
--執行存盤程序
execute student_course;
--帶輸入引數的存盤程序
create procedure student_course1
@Studentname varchar(10)
as
select 學生表.學號,姓名,課程名,作業1成績
from 學生表,課程表,學生作業表
where 學生表.學號=學生作業表.學號 and 課程表.課程號=學生作業表.課程號 and 姓名=@Studentname;
--呼叫student_course1
execute student_course1 '于蘭蘭';
--創建一個存盤程序用于向學生表中插入記錄
create procedure student_insert
@學號 int,@姓名 nvarchar(10),@性別 nvarchar(2),@專業班級 nvarchar(10),@出生日期 nvarchar(20),@聯系電話 nvarchar(20)
as
INSERT into 學生表
values(@學號,@姓名,@性別,@專業班級,@出生日期,@聯系電話);
--執行student_insert
execute student_insert '007','阿剛','男','電子06','2000-1-1','13333333333';
--創建存盤程序，若沒有給出學生姓名，則回傳所有學生情況
create procedure student_course2
@StudentName nvarchar(20)=null
as
if @StudentName is null
begin
select 學生表.學號,姓名,課程名,作業1成績
from 學生表,課程表,學生作業表
where 學生表.學號=學生作業表.學號 and 課程表.課程號=學生作業表.課程號
end
else
begin
select 學生表.學號,姓名,課程名,作業1成績
from 學生表,課程表,學生作業表
where 學生表.學號=學生作業表.學號 and 課程表.課程號=學生作業表.課程號 and 姓名=@Studentname;
end
execute student_course2 '張志國';
execute student_course2; --使用默認引數值
--使用輸出引數
CREATE procedure student_count
@CourseName varchar(20),
@StudentSum int output
as
select @StudentSum=COUNT(*)
from 課程表,學生作業表
where 課程表.課程號=學生作業表.課程號 and 課程名=@CourseName;
--對于帶有輸出引數的存盤程序，呼叫時需要定義相應的變數用于接收從存盤程序回傳的引數值
declare @StudentSum1 int;
execute student_count '資料結構',@StudentSum1 OUTPUT;
SELECT @StudentSum1 as 選資料結構的人數;
--創建一個存盤程序，輸出學生的基本情況
alter procedure student_query
@學號 int,@姓名 nvarchar(8) output,@性別 char(2) output
as
select @姓名=姓名,@性別=性別
from 學生表
where 學號=@學號;
--呼叫存盤程序，查看基本情況
declare @姓名1 nvarchar(20);
declare @性別1 char(2);
execute student_query '7',@姓名1 output,@性別1 output;
select @姓名1 as 學生姓名,@性別1 as 學生性別;
select * from 學生表;
--洗掉存盤程序是
--drop procedure 存盤程序名;
--market資料庫中
use market;
--存盤程序shanghai,查看上海客戶資訊
select * from Customers;
insert into Customers VALUES(3,'阿美','上海');
create procedure shanghai
as
select * from Customers
where City='上海'
execute shanghai;
--存盤程序Goods，查看指定商品資訊，商品編號作為輸入引數
select * from Goods;
insert into Goods values(1,'牙膏',2.5,'牙膏廠',400,'在售');
insert into Goods values(2,'牙刷',5,'牙刷廠',1200,'熱賣');
create procedure cunchuGoods
@商品編號 int
as
select * from Goods
where GoodID=@商品編號;
execute cunchuGoods @商品編號=2;
--存盤程序GoodsSum,查看指定客戶的所有訂單的訂貨總金額，客戶編號作為輸入引數，訂貨總金額作為輸出引數
select * from Orders;
insert into Orders values(1,1,1,2,5,'2020-1-1');
insert into Orders values(2,1,2,2,10,'2020-1-1');
insert into Orders values(3,2,2,2,10,'2020-1-1');
create procedure cunchuGoodsSum
@客戶編號 int,@訂貨總金額 float output
as
select @訂貨總金額=OrderSum from Orders
where Orders.CustomerID=@客戶編號;
declare @訂貨總金額 float; --切記勿忘宣告變數
execute cunchuGoodsSum 2,@訂貨總金額 output;
select @訂貨總金額 as '訂貨總金額';
--存盤程序insert_Goods,向Goods表中插入一條記錄
select * from Goods;
alter procedure insert_Goods
@商品編號 int,@商品名稱 nvarchar(20),@價格 float,@供貨商 nvarchar(20),@庫存量 int,@商品狀態 nvarchar(20)
as
insert into Goods(GoodID,Gname,Price,Provider,Stocks,Status)
values(@商品編號,@商品名稱,@價格,@供貨商,@庫存量,@商品狀態);
execute insert_Goods 3,'牙刷杯',10,'牙刷杯廠',5,'即將斷貨';
--創建存盤程序Goods_Orders1，查看任何指定貨品的訂單情況，包括訂單號，訂貨客戶姓名以及訂貨數量（使用輸入引數）
select * from Orders;
select * from Goods;
select * from Customers;
CREATE procedure Goods_Orders1
@指定貨品 nvarchar(20)
as
select OrderID,Cname,Quantity from Orders,Goods,Customers
where Orders.GoodID=Goods.GoodID and Orders.CustomerID=Customers.CustomerID AND Goods.Gname=@指定貨品;
EXECUTE Goods_Orders1 @指定貨品='牙膏';
--執行存盤程序，如果不給出引數則報錯，如果希望不輸入引數，即默認值，得到所有貨品訂單，則新建表Goods_Orders2
execute Goods_Orders1 @指定貨品; --報錯
create procedure Goods_Orders2
@指定貨品 nvarchar(20)=null
as
if @指定貨品 is null
begin
select OrderID,Cname,Quantity from Orders,Goods,Customers
where Orders.GoodID=Goods.GoodID and Orders.CustomerID=Customers.CustomerID
end
else
begin
select OrderID,Cname,Quantity from Orders,Goods,Customers
where Orders.GoodID=Goods.GoodID and Orders.CustomerID=Customers.CustomerID AND Goods.Gname=@指定貨品
end
execute Goods_Orders2;
execute Goods_Orders2 @指定貨品='牙刷';
--創建存盤程序Goods_OrderSum，來獲得某個貨品的訂單總額（使用輸入輸出引數）
alter PROCEDURE Goods_OrderSum
@貨品名稱 nvarchar(20),@訂單總額1 float output
as
select @訂單總額1=sum(OrderSum) from Orders,Goods
where Goods.GoodID=Orders.GoodID and Gname=@貨品名稱;
declare @訂單總額1 float;
execute Goods_OrderSum '牙膏',@訂單總額1 output;
select @訂單總額1 as '訂單總額';
實驗10：觸發器實驗
自擬題目完成5個觸發器的撰寫及除錯，熟練掌握觸發器的使用，也可采用下圖中作業上的題目，
SQL陳述句代碼
use 學生作業管理資料庫;
--例8 創建一個觸發器，當 學生表 中的記錄被更新時，顯示表中的所有記錄
create trigger student_change
on 學生表 after insert,update,delete
as
select * from 學生表;
--查看下變化
select * from 學生表;
insert into 學生表 values(1,'阿美','女','計科06','2002-1-1','13312313213');
--例9 在 學生表 中創建DELETE觸發器，實作對 學生表 和 學生作業表 的級聯洗掉
create trigger studentdelete on 學生表
after delete
as
delete from 學生作業表
where 學號 in
(select deleted.學號 from deleted);
--查看下變化
select * from 學生作業表;
insert into 學生作業表 values('K001',1,99,99,99);
select * from 學生作業表;
delete from 學生表 where 姓名='阿美';
select * from 學生作業表;
--例10 在學生作業表上創建insert 觸發器，當向學生作業表 中添加學生的選課記錄時，
--檢查該學生的學號是否存在，若不存在，則不能將記錄插入
create trigger sc_insert on 學生作業表
after insert
as
if(select count(*) from 學生表,inserted where 學生表.學號=inserted.學號)=0
begin
print '學號不存在，不能插入'
rollback transaction
end;
--查看效果
insert into 學生作業表 values('K001',1,99,99,99);
--例11 創建update觸發器，禁止對學生表 中學生的性別進行修改
create trigger student_update on 學生表
after update
as
if update(性別)
begin
print '禁止對學生學號修改'
rollback transaction
end;
--查看效果
select * from 學生表;
update 學生表 set 性別='男' where 性別='女' and 學號=7;
select * from 學生表;
--例12 在學生作業表上創建觸發器，當一次向學生作業表中添加多個記錄時，洗掉學號在學生表中不存在的記錄，
--從而保證資料的一致性，注意，不能在學生作業表中定義外鍵約束
create trigger sc_insert1 on 學生作業表
after insert
as
if(select count(*) from 學生表,inserted where inserted.學號=學生表.學號)<>@@ROWCOUNT
BEGIN
delete from 學生作業表
where 學號 not in (select 學號 from 學生表)
END;
--例13 在視圖上定義instead of 觸發器
select * from 學生表;
create view birth_view(學號,姓名,性別,生日,專業班級)
as
select 學號,姓名,性別,出生日期,專業班級
from 學生表;
create trigger birth_view_insert on birth_view
instead of insert
as
declare @學號 int
declare @姓名 varchar(20);
declare @性別 varchar(20);
declare @生日 varchar(20);
declare @專業班級 varchar(20);
select @學號=學號,@姓名=姓名,@性別=性別,@專業班級=專業班級
from inserted;
insert into 學生表(學號,姓名,性別,專業班級) values(@學號,@姓名,@性別,@專業班級);
--查看效果
insert into birth_view(學號,姓名,性別,專業班級) values(2,'阿紅','女','軟體04');
use market;
--第四章第五題（5）在Customers表上建立洗掉觸發器，實作Customers表和Orders表的級聯洗掉
select * from Customers;
select * from Orders;
create trigger customers_delete on Customers
after delete
as
delete from Orders
where CustomerID in (select deleted.CustomerID FROM DELETED);
--第四章第五題（6）在Orders表上建立插入觸發器，當向表中添加一條訂貨記錄時，若訂單中的商品狀態為即將斷貨（Status='即將斷貨'）
--則不能插入該條記錄
select * from Customers;
select * from Orders;
select * from Goods;
CREATE trigger orders_insert on Orders
AFTER INSERT
AS
if(select Status from Goods,inserted where Goods.GoodID=inserted.GoodID) in ('即將斷貨')
begin
print '即將斷貨，不能訂購'
rollback transaction
end;
--試試效果
insert into Orders values(4,3,3,2,20,'2020-2-2');
--第四章第五題（7）在Orders表上建立插入觸發器，當添加訂單時，減少Goods表中相應商品的庫存量
select * from Customers;
select * from Orders;
select * from Goods;
create trigger orders_insert1 on Orders
after insert
as
UPDATE Goods SET Stocks=Stocks-inserted.Quantity
FROM Goods,inserted
WHERE Goods.GoodID=inserted.GoodID;
--第四章第五題（8）在Orders表上建立觸發器，不允許對訂單日期進行修改
select * from Customers;
select * from Orders;
select * from Goods;
CREATE TRIGGER orders_create ON Orders
AFTER UPDATE
as
IF UPDATE(Date)
BEGIN
RAISERROR('不能手動修改',10,1)
ROLLBACK TRANSACTION
END;
--第四章第五題（9）建立觸發器，實作參照完整性約束，即若在Orders表中添加一條記錄時，則該訂單中的商品也必須在
--Goods表中存在，否則不許添加該記錄；
select * from Customers;
select * from Orders;
select * from Goods;
CREATE TRIGGER orders_create1 ON Orders
AFTER INSERT
AS
IF (SELECT COUNT(*) from Goods,inserted
WHERE Goods.GoodId=inserted.GoodID)=0
BEGIN
print '這種貨物不存在'
rollback transaction
END;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/2537.html
標籤：SQL Server