本文最初由security9968发布于security9968的csdn博客，禁止任何形式的剽窃行为
转载原创文章请注明，转载自：security的博客
实验9：存储过程实验
自拟题目完成8个存储过程的编写及调试，熟练掌握存储过程的使用。也可采用下图中作业上的题目。
SQL语句代码
use 学生作业管理数据库;
select * from 学生表;
select * from 课程表;
select * from 学生作业表;
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;
execute student_course1 '于兰兰';
create procedure student_insert
@学号 int,@姓名 nvarchar(10),@性别 nvarchar(2),@专业班级 nvarchar(10),@出生日期 nvarchar(20),@联系电话 nvarchar(20)
as
INSERT into 学生表
values(@学号,@姓名,@性别,@专业班级,@出生日期,@联系电话);
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 学生表;
use market;
select * from Customers;
insert into Customers VALUES(3,'阿美','上海');
create procedure shanghai
as
select * from Customers
where City='上海'
execute shanghai;
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;
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 '订货总金额';
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,'即将断货';
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 @指定货品='牙膏';
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 @指定货品='牙刷';
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 学生作业管理数据库;
create trigger student_change
on 学生表 after insert,update,delete
as
select * from 学生表;
select * from 学生表;
insert into 学生表 values(1,'阿美','女','计科06','2002-1-1','13312313213');
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 学生作业表;
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);
create trigger student_update on 学生表
after update
as
if update(性别)
begin
print '禁止对学生学号修改'
rollback transaction
end;
select * from 学生表;
update 学生表 set 性别='男' where 性别='女' and 学号=7;
select * from 学生表;
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;
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;
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);
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');
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;
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;
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;
本文最初由security9968发布于security9968的csdn博客，禁止任何形式的剽窃行为
转载原创文章请注明，转载自：security的博客
