資料庫實驗系列之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
  • Dapr Outbox 執行流程
    Dapr Outbox 是1.12中的功能。 本文只介紹Dapr Outbox 執行流程，Dapr Outbox基本用法請閱讀官方文檔 。本文中appID=order-processor，topic=orders 本文前提知識：熟悉Dapr狀態管理、Dapr發佈訂閱和Outbox 模式。 Outbo ...
  • Advanced .Net Debugging 7：托管堆與垃圾收集
    引言 在前幾章我們深度講解了單元測試和集成測試的基礎知識，這一章我們來講解一下代碼覆蓋率,代碼覆蓋率是單元測試運行的度量值,覆蓋率通常以百分比表示，用於衡量代碼被測試覆蓋的程度，幫助開發人員評估測試用例的質量和代碼的健壯性。常見的覆蓋率包括語句覆蓋率（Line Coverage）、分支覆蓋率（Bra ...
  • C#S7.NET實現西門子PLCDB塊數據採集的完整步驟
    前言 本文介紹瞭如何使用S7.NET庫實現對西門子PLC DB塊數據的讀寫，記錄了使用電腦模擬，模擬PLC，自至完成測試的詳細流程，並重點介紹了在這個過程中的易錯點，供參考。 用到的軟體： 1.Windows環境下鏈路層網路訪問的行業標準工具（WinPcap_4_1_3.exe）下載鏈接：http ...
  • WPF控制項：密碼框綁定MVVM
    從依賴倒置原則（Dependency Inversion Principle, DIP）到控制反轉（Inversion of Control, IoC）再到依賴註入（Dependency Injection, DI）的演進過程，我們可以理解為一種逐步抽象和解耦的設計思想。這種思想在C#等面向對象的編 ...
  • Python中的私有屬性與私有方法
    關於Python中的私有屬性和私有方法 Python對於類的成員沒有嚴格的訪問控制限制，這與其他面相對對象語言有區別。關於私有屬性和私有方法，有如下要點： 1、通常我們約定，兩個下劃線開頭的屬性是私有的（private）。其他為公共的（public）； 2、類內部可以訪問私有屬性（方法）； 3、類外 ...
  • C++ 訪問說明符詳解：封裝數據，控制訪問，提升安全性
    C++ 訪問說明符 訪問說明符是 C++ 中控制類成員（屬性和方法）可訪問性的關鍵字。它們用於封裝類數據並保護其免受意外修改或濫用。 三種訪問說明符： public：允許從類外部的任何地方訪問成員。 private：僅允許在類內部訪問成員。 protected：允許在類內部及其派生類中訪問成員。 示 ...
  • static_cast與dynamic_cast到底是什麼？
    寫這個隨筆說一下C++的static_cast和dynamic_cast用在子類與父類的指針轉換時的一些事宜。首先，【static_cast，dynamic_cast】【父類指針，子類指針】，兩兩一組，共有4種組合：用 static_cast 父類轉子類、用 static_cast 子類轉父類、使用 ...
  • C語言數據結構：雙向鏈表的增刪操作
    /******************************************************************************************************** * * * 設計雙向鏈表的介面 * * * * Copyright (c) 2023-2 ...
  • @ComponentScan註解的實現，Spring掃描包的過程
    相信接觸過spring做開發的小伙伴們一定使用過@ComponentScan註解 @ComponentScan("com.wangm.lifecycle") public class AppConfig { } @ComponentScan指定basePackage，將包下的類按照一定規則註冊成Be ...
  • opensips開啟python支持
    操作系統 ：CentOS 7.6_x64 opensips版本： 2.4.9 python版本：2.7.5 python作為腳本語言，使用起來很方便，查了下opensips的文檔，支持使用python腳本寫邏輯代碼。今天整理下CentOS7環境下opensips2.4.9的python模塊筆記及使用 ...
所有分類