你好,游客 登录 注册 搜索
背景:
阅读新闻

数据库概论第四次实验 - GamesDuan的专栏

[日期:2013-04-19] 来源:  作者: [字体: ]

第四次实验内容

实验名称:存储过程和触发器

实验要求:要求学生熟悉对存储过程和触发器等基本操作,并知道其作用与意义,掌握存储过程和触发器的创建、修改、使用、删除等核心操作。

实验内容:参考实验参考书实验9实验10示例

(一)触发器实验原理及示例

有两个特殊表用在trigger语句中,即deletedinserted,这两张表采用检查一些数据更新的影响和为触发器动作设置条件,不能直接修改这两个表的内容。

deleted表:存储在deleteupdate语句执行时所影响的行的拷贝,在deleteupdate语句执行前被作用的行转移到deleted表中。

inserted表:存储在instertupdata语句执行时所影响的行的拷贝,在insertupdate语句执行期间,新行被同时加到inserted和触发器表中。

实际上update命令是删除后紧跟着插入,旧行首先拷贝到deleted表中,新行同时拷贝到inserted和触发器表中。

触发器仅在当前DB中生成,触发器有三种类型,即插入、删除和更新,另外插入和更新、删除和更新可以作为一种类型的触发器。

1、创建触发器

   语法:

Create trigger trigger_name 

  On table_name 

    For insert|update| delete 

  As sql_statement 

ex1create triger sales_insert on sales  

      For insert 

      As 

        If(select count(*) from titles,inserted 

        Where titles.title_id=inserted.tetle_id) 

           !=@@rowcount 

           Rollback 

  

ex2create trigger publishes_update on publishers 

     For update 

    As 

      if update(pub_id) and @@rowcount=1 

      Begin 

        Update tilles set  

           titles.pub_id=inserted.pub_id 

        From titles,deleted,inserted 

         Where deleted.pub_id=titles.pub_id  

      End 

2说明了若publishers表中的pub_id列被修改,同时修改titles表中的pub_id列。

ex3Create trigger ts on titleauthor 

      For delete as 

      Begin  

        Delete titles 

        From titles,deleted 

        Where deleted.title_id=titles.title_id 

        Delete titleauthor 

        From titleauthor,deletd 

        Where deleted.title_id=titleauthor.title.id 

        Print 'delete all' 

      end 

 ex4: 创建触发器,当输入某个同学选课成绩时,如果他是少数民族人,其成绩自动加5

CREATE TRIGGER sc_insert ON sc 

   FOR INSERT 

   AS 

    if exists (select * from inserted,student where inserted.sno=student.sno and student.Smz<>'汉族') 

update sc set sc.grade = sc.grade+5 

from inserted,student 

where inserted.sno=student.sno and student.Smz<>'汉族' and inserted.sno=sc.sno and inserted.cno=sc.cno 

GO 

 触发器常用于保证参照完整性。

2、删除触发器

    Drop triggertrigger_name

3、教学示例

(1)写一个允许用户一次只删除一条记录的触发器。

create trigger tr_emp 

on employee for delete as 

declare @row_cnt int 

select @row_cnt=count(*) from deleted 

if @row_cnt>1 

  begin  

   print '此删除操作可能回删除多条人事表数据!!!' 

   rollback transaction 

  end   

delete from employee 

where sex=''          /*结果:所影响的行数为0*/ 

4、实验内容

1)设置一个触发器,该触发器仅允许dbo用户可以删除employee表内数据的,否则出错。

2)设置一个针对employee表的触发器,当有人操作该列值时,触发器将自动将该操作者的名称和操作时间记录在一张表内,以便追踪。

3)级联更新:当更新employee表中emp_no列的值时,同时更新sales表中的sale_id列的值,并且一次只能更新一行。

4)对employee表写一个允许用户一次只删除一条记录的触发器。

5)对employee表写一个UPDATE触发器。当修改employee表的生日和雇佣日期时必须保证出生日期在雇佣日期之前,且年龄不小于25岁雇佣日期与出生日期必须间隔在20年之上

6)请使用游标和循环语句为sale_item表建立一个更新触发器updateSaleItem,当修改销售明细表中某个货品的数量或单价时自动修改销售主表中的相应定单的定单金额。

(二)存储过程实验原理及示例

存储过程是为了完成特定功能汇集而成的一组命名了的SQL语句集合,该集合编译后存放在SQL Server中,可根据实际情况重新编译,该过程可直接运行,也可以远程运行。

1、语法(创建)

create procedure procedure_name[;number] 

[[@parameter_name datatype[=default][output][,@parameter_name  

datatype [=default][output]]]] 

as 

   SQL-statements 

 说明:

   parameter_name:参数名 procedure可以不带参数

   number:一个可选的整数,用于将同名的过程分组,可以用drop procedure语句将它们一起删除。

   output:表示该参数为返回值,可以被调用者获取使用。

2、实例

ex1: 输入某个同学的学号,统计该同学的平均分

create procedure dbo.pro_student(@sno char(6)) 

as 

begin 

  declare @sname char(10), @cname char(20) 

   declare @grade tinyint,@sum int 

      declare @aver numeric(6,2),@count tinyint 

      select @sum=0, @count=0 

      declare get_grade cursor for  

      select sname,cname,grade from dbo.sc a,dbo.student b,dbo.course c  

      where b.sno=@sno and a.sno=b.sno and a.cno=c.cno 

      open get_grade 

      fetch get_grade into @sname,@cname,@grade 

      while (@@fetch_status=0) 

      begin 

         select @sname,@cname,@grade 

        select @sum=@sum+@grade 

        select @count=@count+1 

        fetch get_grade into @sname,@cname,@grade 

      end 

   close get_grade 

   deallocate get_grade 

   if @count=0 

      select 0 

   else 

     select @sum/@count 

   end 

   go 

   grant execute on dbo.pro_student to studuser1  

go 

3、存储过程的修改

alter procedure procedure_name[;number] 

[[@parameter_name datatype[=default][output][,@parameter_name  

datatype [=default][output]]]] 

as 

     SQL-statements 

 4、教学示例

1)模糊查询

create procedure  sp_empname @E_name varchar(10) as 

select a.emp_name,a.dept,b.tot_amt 

from employee a inner join sales b 

on a.emp_no=b.sale_id  

where a.emp_name like @E_name 

go 

 

exec sp_empname '%' 

2)利用存储过程计算出E0014业务员的销售总金额。

create procedure  sp_saletot @E_no char(5),@p_tot int output as 

select @p_tot=sum(tot_amt) 

from sales 

where sale_id=@E_no  

go 

 

declare @tot_amt int 

exec sp_saletot E0014, @tot_amt output 

select @tot_amt 

5、实验内容

1)利用存储过程,给employee表添加一条业务部门员工的信息。

2)利用存储过程从employeesalescustomer表的连接中返回所有业务员的姓名、客户姓名、销售金额。

3)利用存储过程查找刘刚的员工编号、订单编号、销售金额。

4)利用存储过程查找姓并且职称为职员的员工的员工编号、订单编号、销售金额。

5)利用存储过程计算出订单编号为10003的订单的销售金额。

 

***本次实验内容较难,希望大家认真准备***

附:游标使用方法使用游标必须经历五个步骤:

    定义游标:delare

    打开游标:open

    逐行提取游标集中的行:fetch

    关闭游标:close

    释放游标:Deallocate

1、语法:

declare cursor_name cursor for SQL查询 

[for [read only|update {of column-name list}] 

    缺省update 

open curser_name 

fetch curser_name [into 变量表

当游标移至尾部,不可以再读取游标,必须关闭游标然后重新打开游标。 

可以通过检查全局变量@@fetch_status来判断是否已读完游标集中所有行。 

Close curser_name 

Deallocate curser_name. 

2、删除游标集中当前行

Delete from 表名 where current of curser_name

从游标中删除一行后,游标定位于被删除的游标之后的一行,必须再用fetch得到该行。

3、更新游标集中当前行

update 表名 set 列名=表达式 [,列名=表达式]
where current of curser_name

4、教学示例

1)利用游标选取业务部门的员工编号和姓名,并执行游标。

DECLARE cur_emp SCROLL cursor FOR 

SELECT emp_no,emo_name 

FROM employee 

WHERE dept=业务 

ORDER BY emp_no 

 

执行: 

OPEN cur_emp 

SELECT  cursor打开状态=@@ERROR 

SELECT  cursor内数据条数=@@CURSOR_ROWS 

 

FETCH NEXT FROM cur_emp 

SELECT cursor读取状态=@@CURSOR_STATUS 





收藏 推荐 打印 | 录入:admin | 阅读:
相关新闻