資料庫實驗系列之3存儲過程和觸發器實驗（存儲過程和觸發器）

来源:https://www.cnblogs.com/wanggang9968/archive/2020/05/18/12908747.html
-Advertisement-
Play Games

"TOC" 實驗9：存儲過程實驗 自擬題目完成8個存儲過程的編寫及調試，熟練掌握存儲過程的使用。也可採用下圖中作業上的題目。 SQL語句代碼 實驗10：觸發器實驗 自擬題目完成5個觸發器的編寫及調試，熟練掌握觸發器的使用。也可採用下圖中作業上的題目。 SQL語句代碼 ...


目錄

實驗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;

在這裡插入圖片描述


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Shell腳本關於迴圈的一些總結
    不管是哪一門電腦語言，迴圈都是不可繞開的一個話題，Shell 當然也不是例外。下麵總結一些 Shell 腳本里常用的迴圈相關的知識點，新手朋友可以參考。 for 迴圈 Shell 腳本里最簡單的迴圈當屬 迴圈，有編程基礎的朋友應該都有使用過 for 迴圈。最簡單的 for 迴圈如下所示，你只需將變 ...
  • Linux 計劃任務摘要
    Linux計劃任務中對應的時間含義及指令 指令 : --linux定時任務 crontab -e # 創建自己的一個任務調度，此時會進入到vi編輯界面，來編寫我們要調度的任務 crontab -l # 列出定時的任務 時間對應關係 : 55 7 * * * csh -c "/home/dpower/ ...
  • 安裝和使用ArchLiunx超詳細教程
    安裝系統 一、下載介質 https://www.archlinux.org/download/ 二、啟動 ISO 到Live 環境 此步驟由很多種方式： 製作ISO為U盤啟動工具，可以使用 Ultra ISO 或 大白菜 有Linux/Unix系統的，可以硬碟寫入Grub，製作啟動項 三、安裝前的準 ...
  • Redis學習筆記（十） 客戶端
    Redis伺服器是典型的一對多伺服器程式：一個伺服器可以與多個客戶端建立網路連接，每個客戶端可以向伺服器發送命令請求，而伺服器則接收並處理客戶端發送的命令請求，並向客戶端返回命令回覆。 通過使用由I/O多路復用技術實現的文件事件處理器，Redis伺服器使用單線程單進程的方式處理命令請求，並於多個客戶 ...
  • 【漫畫】CAS原理分析！無鎖原子類也能解決併發問題！
    本文來源於微信公眾號【胖滾豬學編程】、轉載請註明出處 在漫畫併發編程系統博文中，我們講了N篇關於鎖的知識，確實，鎖是解決併發問題的萬能鑰匙，可是併發問題只有鎖能解決嗎？今天要出場一個大BOSS：CAS無鎖演算法，可謂是併發編程核心中的核心！ 溫故 首先我們再回顧一下原子性問題的原因，參考 "【漫畫】J ...
  • SQL子查詢的感悟
    今天在聽陳華軍老師的課時；感觸頗多。其中講到“不同執行計劃的選擇（子查詢）”這一欄。我們在平時工作也經常要用到子查詢。有哪些思路來優化這種子查詢呢？ 例如我們今天實驗的表結構 表T1 有10000條記錄;併在id欄位創建btree索引 表T2 有1000條記錄 postgres=# create t ...
  • 寶塔部署war包項目，出現SQLException: Access denied for user
    新公司使用的是寶塔來部署項目，war包。在部署運行時遇到了SQLException: Access denied for user 'xxx@xxxxx' (using password:yes) 重裝mysql無果之後。就只能慢慢解決，分享下我的解決過程。 # 一. 錯誤原因 正常來講，使用nav ...
  • mysql索引解析
    1、基本概念 數據讀寫性能主要是IO次數，單次從磁碟讀取單位是頁，即便只讀取一行記錄，從磁碟中也是會讀取一頁的（）單頁讀取代價高，一般都會進行預讀） （1）扇區是磁碟的最小存儲單元 （2）塊是文件系統的最小存儲單元,比如你保存一個記事本，即使只輸入一個字元，也要占用4KB的存儲，這就是最小存儲的意思 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類