第6章 游标和触发器上机01Word文档格式.docx
- 文档编号:17624345
- 上传时间:2022-12-07
- 格式:DOCX
- 页数:20
- 大小:492.09KB
第6章 游标和触发器上机01Word文档格式.docx
《第6章 游标和触发器上机01Word文档格式.docx》由会员分享,可在线阅读,更多相关《第6章 游标和触发器上机01Word文档格式.docx(20页珍藏版)》请在冰豆网上搜索。
图2.6.1学生分数表tb_score的数据结构
实现思路
(1)创建过程,命名为proc_upd_rank,在其中定义基于tb_score表的可更新游标,用于根据总分更新rank字段,如图2.6.2所示。
图2.6.2创建使用游标的proc_upd_rank过程
(2)执行proc_upd_rank过程,实现更新rank字段并求名次的功能,如图2.6.3所示。
图2.6.3执行proc_upd_rank过程并显示结果
练习部分
(一)
假定存在一张商品销售表tb_emp_sell,其数据结构如图2.6.4所示。
要求通过此表生成一张如图2.6.5所示的报表。
(注意:
商品编号与雇员编号都是可变的。
)
图2.6.4商品销售表tb_emp_sell数据结构
图2.6.5生成报表的数据结构
(1)创建过程proc_emp_sell_report,在其中使用游标动态构造SQL语句,创建一张临时表,该表用于存放查询的结果。
CREATEORREPLACEPROCEDUREproc_emp_sell_report
AS
BEGIN
DECLARE
v_tempINT;
--定义临时变量
v_dyna_sqlVARCHAR2(5000);
--定义变量存放动态SQL语句
v_sellquantityINT;
--定义变量用于保存销量
--查询获得名为emp_sell_report的记录个数赋给v_temp变量
SELECTCOUNT(*)INTOv_tempFROMtab
WHEREtname=upper('
emp_sell_report'
);
--如果大于零,表示存在emp_sell_report表,则删除
IFv_temp>
0THEN
EXECUTEIMMEDIATE'
DROPTABLEemp_sell_report'
;
ENDIF;
--构造创建表的SQL语句
v_dyna_sql:
='
createtableemp_sell_report(empnovarchar2(10),'
--关于商品编号的游标FOR循环
FORemp_sell_rowIN(SELECTDISTINCTshopidFROMtb_emp_sell)LOOP
=v_dyna_sql||emp_sell_row.shopid||'
number
(2),'
ENDLOOP;
--去掉其后多余的逗号
=substr(v_dyna_sql,0,length(v_dyna_sql)-1)||'
)'
--执行动态SQL语句,创建emp_sell_report表
EXECUTEIMMEDIATEv_dyna_sql;
--关于雇员编号的游标FOR循环
FORempno_rowIN(SELECTDISTINCTempnoFROMtb_emp_sell)LOOP
--构造插入表数据的动态SQL语句
insertintoemp_sell_reportvalues('
'
||
empno_row.empno||'
'
FORshopid_rowIN(SELECTDISTINCTshopidFROMtb_emp_sell)LOOP
--求出指定雇员,指定商品编号的销量
SELECTCOUNT(*)INTOv_tempFROMtb_emp_sell
WHEREempno=empno_row.empnoANDshopid=shopid_row.shopid;
0THEN--存在对应雇员编号、商品编号的销量
--求出销量
SELECTsellquantityINTOv_sellquantityFROMtb_emp_sell
ELSE--否则,置零
v_sellquantity:
=0;
=v_dyna_sql||v_sellquantity||'
--退出循环,去掉后面多余的逗号
=substr(v_dyna_sql,0,length(v_dyna_sql)-1)||'
--执行插入操作
END;
(2)执行过程proc_emp_sell_report,出现权限不足的错误提示,如图2.6.6所示。
图2.6.6在过程中使用EXECUTEIMMEDIATE语句提示权限不足
出现错误提示的原因是在过程中使用了EXECUTEIMMEDIATE语句。
可以在创建存储过程时指定authid为current_user,表示存储过程执行时,以该存储过程的创建者的身份来验证存取权限,即:
CREATEORREPLACEPROCEDUREproc_emp_sell_reportAUTHIDCURRENT_USER。
再次执行proc_emp_sell_report过程,如图2.6.7所示。
图2.6.7成功执行proc_emp_sell_report
(3)执行proc_emp_sell_report过程后,会生成emp_sell_report表,此时可以直接查询该表,运行结果如图2.6.5所示。
练习部分
(二)
假定存在一张房屋出租表tb_rent,其数据结构如图2.6.8所示。
要求编写过程proc_query_rent,用于根据房源区、室、厅、租金信息进行联合查询。
该过程有5个IN参数(分别为param_region、param_room、param_hall、param_rentMin、param_rentMax)和一个OUT参数。
由于该过程必须返回查询的结果集,所以需要使用SYS_REFCURSOR游标类型来实现。
现规定该过程的调用方式如下:
(1)查询“汉阳区”的租金在800到1000之间的房源信息(其他条件不限),其调用格式为:
callproc_query_rent(‘汉阳区’,null,null,800,1000,v_sys_refcursor)
(2)查询2室1厅、租金在1000以内的房源信息,其调用格式为:
callproc_query_rent(null,2,1,1000,null,v_sys_refcursor)
(3)查询2室1厅的房源信息,其调用格式为:
callproc_query_rent(null,2,1,null,null,v_sys_refcursor)
图2.6.8房屋出租表tb_rent数据结构
(1)创建过程proc_query_rent,使用SYS_REFCURSOR游标类型来返回结果集,如图2.6.9所示。
图2.6.9创建proc_query_rent过程
(2)测试此过程,如图2.6.10所示。
图2.6.10测试proc_query_rent过程
阶段2
使用触发器实现自动编号
在SQLServer数据库中,如果将字段属性设置成IDENTITY,则该字段值由SQLServer在插入数据时自动填充,但Oracle中没有此功能。
现要求通过触发器来实现自动编号功能。
(1)创建序列,命名为stu_seq,如图2.6.11所示。
图2.6.11创建stu_seq序列
(2)创建一张表,命名为tb_stud,如图2.6.12所示。
图2.6.12创建tb_stud表
(3)在tb_stud表中创建行级触发器以实现自动编号,代码如图2.6.13所示。
图2.6.13在表tb_stud上创建行级触发器实现自动编号
(4)在tb_stud表中插入数据并测试运行结果,如图2.6.14所示。
图2.6.14向tb_stud表插入数据并测试运行结果
SQLServer数据库中支持计算列,而Oracle数据库不支持计算列,但可以通过触发器实现。
现要求创建一个用于存储学生成绩信息的表,并通过触发器生成“总分”计算列和“最优科目”计算列。
(1)创建一张表,命名为tb_stud_score,如图2.6.15所示。
图2.6.15创建tb_stud_score表
(2)在tb_stud_score表中创建行级触发器,命名为tr_stud_score,如图2.6.16所示。
图2.6.16创建行级触发器
(3)向表tb_stud_score中插入数据,并测试运行结果,如图2.6.17所示。
图2.6.17向表中插入数据并测试运行结果
使用触发器限制雇员工资不能超过所在部门的最高工资。
(1)一般情况下,在编写DML触发器时,触发器代码不能从触发器所对应的基表中读取数据,但可以通过在DML触发器中使用PRAGMAAUTONOMOUS_TRANSACTION命令声明自治事务的方式来实现。
以下将创建触发器tr_emp_sal,用于实现在向emp表中更新雇员信息时雇员工资不能超过所在部门的最高工资,如图2.6.18所示。
图2.6.18创建tr_emp_sal行自治事务触发器
(2)更新emp表中的数据,测试运行结果,如图2.6.19所示。
图2.6.19更新emp表中的数据并测试运行结果
练习部分(三)
使用登录触发器,限制用户只能在工作时间段内(如9:
00到17:
00之间)登录Oracle服务器,并写入登录失败错误日志。
(1)创建用于写入日志的表tb_login_log,如图2.6.20所示。
图2.6.20创建用于写入日志的表
(2)创建登录触发器,获取当前时间。
如果不在正常工作时间内,则写入日志,并抛出异常,如图2.6.21所示。
图2.6.21创建登录触发器限制用户登录
(3)测试此登录触发器,如图2.6.22所示。
图2.6.22测试登录触发器
练习部分(四)
使用触发器实现对dept表DML的审计。
(1)创建用于存储DML审计信息的表tb_audit_emp_dml,如图2.6.23所示。
图2.6.23创建用于审计的表tb_audit_emp_dml
(2)创建触发器tr_audit_emp_dml,实现对dept表DML的审计,如图2.6.24所示。
图2.6.24创建用于实现审计的触发器
(3)对tb_audit_emp_dml表进行操作,并测试运行结果,如图2.6.25所示。
图2.6.25测试运行结果
上机作业
使用触发器实现参照完整性。
参照完整性是指若两个表之间具有主从关系,当删除主表数据时,必须确保相关的从表数据已经被删除;
当修改主表的主键列数据时,必须确保相关从表数据已经被修改。
现要求通过触发器实现:
(1)当在dept部门表中删除一个部门时,其emp表中该部门的所有雇员级联删除。
(2)当修改dept部门表中每个部门的部门编号时,其emp表中对应的部门编号级联更新。
可以在dept表中创建一个DML行级触发器,在该触发器中判断操作类型。
如果是删除操作,则根据部门编号删除emp表中该部门记录;
如果是更新操作,则更新emp表中对应的部门编号。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第6章 游标和触发器上机01 游标 触发器 上机 01