实验八
一、游标部分：
实验名称：综合训练（2课时）
1、 请使用游标和循环语句为sale_item表建立一个更新触发器updateSaleItem，当修改销售明细表中某个货品的数量或单价时自动修改销售主表中的相应定单的定单金额。
create trigger updateSaleItem on sale_item
for update
as
if update(qty) or update(unit_price)
begin
declare @order_no int,@prod_id char(5)
declare cur_SaleItem cursor for
select order_no,prod_id from deleted
open cur_SaleItem
begin transaction
fetch cur_SaleItem into @order_no,@prod_id
while(@@fetch_status=0)
begin
update sales
set tot_amt=tot_amt-deleted.qty*deleted.unit_price+
inserted.qty*inserted.unit_price
from inserted,deleted
where sales.order_no=inserted.order_no
and inserted.order_no=deleted.order_no
and sales.order_no=@order_no
and inserted.prod_id=deleted.prod_id
and inserted.prod_id=@prod_id
fetch cur_SaleItem into @order_no,@prod_id
end
commit tran
close cur_SaleItem
deallocate cur_SaleItem
end
2、 请使用游标和循环语句编写一个存储过程proSearchCustomer，根据客户编号，查询该客户的名称、地址以及所有与该客户有关的销售记录，销售记录按商品分组输出。
if exists(select name from sysobjects
where name='proSearchCustomer' and type='p')
drop procedure proSearchCustomer
go
create procedure proSearchCustomer @cust_id char(5)
as
begin
declare @cust_name char(20),@cust_addr char(40)
select @cust_name=Cust_name,@cust_addr=addr from Customer
where Cust_id=@cust_id
if len(@cust_name)>0
begin
select @cust_name 'Customer',@cust_addr 'Address'
Declare cust_cursor cursor for
Select c.Prod_name,sum(b.Qty*b.Unit_price)
from Sales a,Sale_item b,Product c
where a.Cust_id=@cust_id and a.Order_no=b.Order_no
and b.Prod_id=c.Prod_id
group by c.Prod_name
declare @prod_name char(20),@tot_amt numeric(9,2)
open cust_cursor
fetch cust_cursor into @prod_name,@tot_amt
while(@@fetch_status=0)
begin
select @Prod_name' Prodname: ',@tot_amt 'Tot_amt'
fetch cust_cursor into @prod_name,@tot_amt
end
Close cust_cursor
Deallocate cust_cursor
end
else
select '没有找到该客户' '错误'
End
实验五
实验名称：游标的建立与使用（2课时）
1、 利用游标查找所有女业务员的基本情况。
declare @emp_no char(5),@emp_name char(10),@dept char(4)
declare @title char(6),@salary int
declare mycur cursor for
select emp_no,emp_name,dept,title,salary
from employee
where sex='f' and emp_no in (
select sale_id
from sales )
open mycur
fetch mycur into
@emp_no,@emp_name,@dept,@title,@salary
while(@@fetch_status=0)
begin
select @emp_no,@emp_name,@dept,@title,@salary
fetch mycur into
@emp_no,@emp_name,@dept,@title,@salary
end
close mycur
deallocate mycur
2、 创建一游标，逐行显示表customer.的记录，并且用WHILE结构来测试游标的函数@@FETCH_STATUS的返回值
declare @cust_id char(5),@cust_name char(20),
@addr char(40)
declare @tel_no char(10),@zip char(6)
declare mycur cursor for
select *
from customer
open mycur
fetch mycur into
@cust_id ,@cust_name ,@addr,@tel_no,@zip
while(@@fetch_status=0)
begin
select @cust_id ,@cust_name ,@addr ,@tel_no,@zip
fetch mycur into
@cust_id ,@cust_name ,@addr ,@tel_no,@zip
end
close mycur
deallocate mycur
二、存储过程部分
实验七
实验名称：存储过程建立与调用（含带参存储过程的建立与调用）（2课时）
1、 利用存储过程，给employee表添加一条业务部门员工的信息。
create procedure proEmployeeIns
@emp_no char(5),@emp_name char(10) ,@sex char(1) ,
@dept char(4) ,@title char(6), @date_hired datetime ,
@birthday datetime, @salary int ,@addr char(50)
as
insert into employee
(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)
values(@emp_no,@emp_name,@sex,@dept,@title,@date_hired,@birthday,@salary,@addr)
exec proEmployeeIns 'e0029','刘红','M','业务','经理','2001-12-09',null,3500,'江西南昌'
2、 利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。
create procedure proSearchSales as
select a.emp_name,b.cust_name,c.tot_amt
from employee a,customer b,sales c
where a.emp_no=c.sale_id and b.cust_id=c.cust_id
3、 利用存储过程查找“刘刚”的员工编号、订单编号、销售金额。
create procedure proSearchByName @emp_name char(10)as
select a.emp_name,a.emp_no,b.order_no,b.tot_amt
from employee a,sales b
where a.emp_no=b.sale_id and a.emp_name =@emp_name
4、 利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。
首先将销售主表的金额清0
update sales set tot_amt=0
将销售明细表的值填入到销售主表中
update sales set tot_amt=ordSum
from (select order_no,sum(qty*unit_price) ordSum
from sale_item
group by order_no) a
where sales.order_no=a.order_no
create procedure proSearchEmployee
as
begin
declare @qty smallint,@price numeric(7,2),@sum numeric(10,2)
declare @orderNo int
declare @empName char(10),@oldEmpNo char(5),@newEmpNo char(5)
/*查找员工的定单记录主表*/
declare empCur cursor for
select a.emp_name,a.emp_no,b.order_no
from employee a,sales b
where a.emp_no=b.sale_id and a.emp_name like '刘%'
and a.title='职员'
order by emp_no
open empCur
fetch empCur into @empName,@oldEmpNo,@orderNo
while (@@fetch_status=0)
begin
set @sum=0
set @newEmpNo=@oldEmpNo
/*将定单明细表数据进行汇总*/
declare myCur cursor for
select qty,unit_price
from sale_item
where order_no=@orderNo
open myCur
fetch myCur into @qty,@price
while (@@fetch_status=0)
begin
set @sum=@sum+@qty*@price
fetch myCur into @qty,@price
end
close myCur
deallocate myCur
select @empName 销售员, @orderNo 定单编号,@sum 订单总金额
fetch empCur into @empName,@oldEmpNo,@orderNo
if @oldEmpNO<>@newEmpNo
begin
set @newEmpNo=@oldEmpNo
end
end
close empCur
deallocate empCur
end
5、 利用存储过程计算出订单编号为10003的订单的销售金额。
create procedure proTotamt @order_no char(5) as
select order_no,sum(qty*unit_price)
from sale_item
where order_no=@order_no
group by order_no
create procedure proTotamt @order_no char(5)
as
begin
declare @qty smallint,@price numeric(7,2),@sum numeric(10,2)
/*查找定单的客户和销售员*/
select cust_name,emp_name,order_date
from employee a,customer b,sales c
where order_no=@order_no and b.cust_id=c.cust_id
and emp_no=sale_id
/*定义游标*/
declare myCur cursor for
select qty,unit_price
from sale_item
where order_no=@order_no
open myCur
fetch myCur into @qty,@price
set @sum=0
while (@@fetch_status=0)
begin
set @sum=@sum+@qty*@price
fetch myCur into @qty,@price
end
close myCur
deallocate myCur
select @order_no 定单号, @sum 订单总金额
End
设有三个关系：
S(S#,SNAME,AGE,SEX，Total)
SC(S#,C#,GRADE)
C(C#,CNAME,TEACHER)
使用存储过程统计每个同学的总分，总分统计方法：凡是60分以下的，成绩提高10%，将统计后的总分填入到S表中
Create procedure proComputerTotal
As
Begin
Declare @sno char(5), @oldSno char(5)
Declare @grade tinyint, @tot_amt numeric(9,2)
Declare myCur Cursor for
Select s#,grade from sc
Order by s#
Open myCur
Fetch myCur into @ sno, @grade
While (@@fetch_status=0)
Begin
Set @ oldSno=@ sno
Set @tot_amt = 0
While (@ oldSno=@ sno and @@fetch_status=0)
Begin
If @grade<60
Set @grade=@grade*1.1
Set @tot_amt=@tot_amt+@grade
Fetch myCur into @ sno, @grade
End
Update S set Total=@tot_amt
Where S#=@ oldSno
Select @ oldSno, @tot_amt
End
Close myCur
Deallocate myCur
End
三、触发器
实验九
实验名称：触发器的建立与使用（2课时）
1、 设置一个触发器，该触发器仅允许“dbo”用户可以删除employee表内数据的，否则出错。
create trigger EmploteeDelete on employee
for delete
as
if exists (select * from deleted)
begin
if user!='dbo'
rollback
end
2、 设置一个针对employee表的触发器，当有人操作该列值时，触发器将自动将该操作者的名称和操作时间记录在一张表内，以便追踪。
create table TraceEmployee
( userid char(10) not null,
OperateDate datetime not null,
OperateType char(10) not null,
constraint TraceEmployeePK
primary key(userid,OperateDate))
create trigger EmploteeInsert on employee
for insertas
if exists (select * from inserted)
insert into TraceEmployee values(
user,getdate(),'insert')
create trigger EmploteeDelete on employee
for deleteas
if exists (select * from deleted)
insert into TraceEmployee values(
user,getdate(),'delete')
create trigger EmploteeUpdate on employee
for updateas
if exists (select * from deleted)
insert into TraceEmployee values(
user,getdate(),'update')
3、 级联更新：当更新employee表中emp_no列的值时，同时更新sales表中的sale_id列的值，并且一次只能更新一行。
create trigger EmploteeUpdate on employee
for update
as
declare @oldEmpNo char(5),@newEmpNo char(5)
if (select count(*) from inserted)>1
rollback
else
begin
if update(emp_no)
begin
select @oldEmpNo=emp_no from deleted
select @newEmpNo=emp_no from inserted
update sales set sale_id=@newEmpNo
where sale_id=@oldEmpNo
end
end
4、 对employee表写一个UPDATE触发器。
当修改employee表的生日和雇佣日期时必须保证出生日期在雇佣日期之前，且年龄不小于２５岁雇佣日期与出生日期必须间隔在２０年之上
alter trigger EmploteeUpdate on employee
for update
as
declare @birthday datetime,@date_hired datetime
if (update(birthday) or update(date_hired))
begin
declare getCur cursor for
select birthday, date_hired
from inserted
open getCur
fetch getCur into @birthday, @date_hired
while (@@fetch_status=0)
begin
if @date_hired<=@birthday
rollback
else
if year(@date_hired)-year(@birthday) <20
rollback
else
if year(getdate())-year(@birthday) <25
rollback
fetch getCur into @birthday, @date_hired
end
close getCur
deallocate getCur
end