游标、触发器、存储过程实例

原创 2012年06月10日 20:03:07

实验八

一、游标部分:

实验名称:综合训练(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

   I@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表的生日和雇佣日期时必须保证出生日期在雇佣日期之前,且年龄不小于25岁雇佣日期与出生日期必须间隔在20年之上

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

数据库实验

实验二 实验名称:添加表记录(4课时) 一、实验目的 掌握修改表结构的基本方法,熟练使用INSERT语句为每张表追加表记录。 二、实验环境 采用Client/Server模式,学生为客户端,...

利用navicat创建存储过程、触发器和使用游标的简单实例

创建存储过程和触发器   1、建表 首先先建两张表(users表和number表),具体设计如下图:   2、存储过程 写一个存储过程,往users表中插入数据,创建过程如下:   代码如下:...
  • cqnuztq
  • cqnuztq
  • 2013年08月03日 21:34
  • 60529

利用navicat创建存储过程、触发器和使用游标的简单实例

创建存储过程和触发器   1、建表 首先先建两张表(users表和number表),具体设计如下图:   2、存储过程 写一个存储过程,往users...

数据库存储过程、触发器、游标

  • 2015年08月17日 10:41
  • 88KB
  • 下载

Mysql存储过程游标触发器

  • 2015年07月09日 21:15
  • 2KB
  • 下载

SQL使用DOS命令建库。建表,添加约束,标量值函数,存储过程,触发器,游标

这些代码是很久以前写的,不知怎么找出来了。贴在这里吧。主要是使用DOS建数据库。建表,添加约束,标量值函数,存储过程,触发器。 代码建库: 为了复习在SQL中使用DOS命令,开头便启用了高...
  • fengsji
  • fengsji
  • 2017年11月08日 17:32
  • 147
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:游标、触发器、存储过程实例
举报原因:
原因补充:

(最多只允许输入30个字)