• 售前

  • 售后

热门帖子
入门百科

快速学习Oracle触发器和游标

[复制链接]
luly靓 显示全部楼层 发表于 2021-8-14 15:20:34 |阅读模式 打印 上一主题 下一主题
目次


  • 触发器:

    • 1、 创建一个用于记载用户操作的触发器
    • 2、创建一个当删除部门时,删除该部门下的全部雇员的触发器
    • 3、创建一个在account表插入记载之后,更新myevent数据表的触发器
    • 4、创建一个用于记载登录 DBA 身份用户的用户名和时间的触发器

  • 游标:

    • 1、使用隐式游标和 for 语句检索出职务是贩卖员(salesman)的雇员信息并输出
    • 2、员工工资上调20%,使用隐式游标输出上调工资的员工数目
    • 3、用表现游标和for语句检索出部门编号是30的雇员信息并输出
    • 4、声明游标检索员工信息,并使用%FOUND属性来判定
    • 5、创建游标完成数据转移,将fruit表中单价大于10的记载放到fruitage表中


触发器:


1、 创建一个用于记载用户操作的触发器

  创建一个dept_log数据表,并在其中定义两个字段(operate_tag varchar2(10),operate_time date),分别用来存储操作种类(插入,修改,删除)信息和操作日期。然后一个关于dept表的语句级触发器tri_dept,将用户对 dept 表的操作信息生存到dept_tag表中。
当任何时间从dept表中删除某个部门时,该触发器将从emp表中删除该部门的全部雇员。
  1. -- 创建表
  2. create table dept_log(
  3.         operate_tag varchar2(10),
  4.         operate_time date
  5. );
  6. create table dept(
  7.         dname varchar2(20),
  8.         dno number
  9. );
  10. -- 创建触发器
  11. create or replace trigger tri_dept
  12. before insert or update or delete
  13. on dept
  14. declare
  15.         v_tag varchar2(10);
  16. begin
  17.         if inserting then
  18.                 v_tag:='插入';
  19.         elsif updating then
  20.                 v_tag:='修改';
  21.         elsif deleting then
  22.                 v_tag:='删除';
  23.         end if;
  24.         insert into dept_log values(v_tag, sysdate);
  25. end tri_dept;
  26. /
复制代码


2、创建一个当删除部门时,删除该部门下的全部雇员的触发器

当任何时间从dept表中删除某个部门时,该触发器将从emp表中删除该部门的全部雇员。
注意:全部的以sysdba登录的账户都不能创建触发器,因此必要在创建的用户下面创建触发器
  1. -- 创建部门表
  2. create table dept(
  3.     deptno number not null,
  4.     dname varchar(20) not null
  5. );
  6. -- 创建员工表
  7. create table emp(
  8.     emp_no number not null,
  9.     emp_name varchar(20) not null,
  10.     job varchar(20) not null,
  11.     sal number  not null,
  12.     deptno number not null
  13. );
  14. --插入数据
  15. insert into dept values(10, '部门1');
  16. insert into dept values(20, '部门2');
  17. insert into emp values(1001, '员工1', '工作1', 5000, 10);
  18. insert into emp values(1002, '员工2', '工作2', 7200, 10);
  19. insert into emp values(1003, '员工3', '工作3', 6000, 10);
  20. insert into emp values(1004, '员工4', '工作4', 5000, 20);
  21. insert into emp values(1005, '员工5', '工作5', 7000, 20);
复制代码
  1. -- 创建触发器
  2. create or replace trigger del_dept
  3. before delete on dept
  4. for each row
  5. begin
  6.         delete from emp where deptno = :old.deptno;
  7. end;
  8. /
复制代码



3、创建一个在account表插入记载之后,更新myevent数据表的触发器

创建了一个TRIG_INSERT的触发器,在向表account插入数据之后会向表myevent插入一组数据(表结构就不创建了…)
  1. create or replace trigger trig_insert
  2. after insert
  3. on account
  4. begin
  5.         if inserting then
  6.                 insert into myevent values(1, 'after insert');
  7.         end if;
  8. end;
  9. /
复制代码


4、创建一个用于记载登录 DBA 身份用户的用户名和时间的触发器

以DBA 身份登录数据库,并创建一个名为db_log的数据表,用于记载登任命户的用户名和时间。
接着分别创建数据库启动和数据库关闭触发器,并向db_log数据表中插入记载,存储登任命户的用户名和操作时间。
  1. --创建表
  2. create table db_log(
  3.         name varchar2(20),
  4.         rtime timestamp
  5. );
  6. -- 创建触发器,用于记录用户登录
  7. create or replace trigger trigger_startup
  8. after startup
  9. on database
  10. begin
  11.         insert into db_log values('user', sysdate);
  12. end;
  13. /
  14. -- 创建触发器,用于记录用户退出
  15. create or replace trigger trigger_shutdown
  16. before shutdown
  17. on database
  18. begin
  19.         insert into db_log values('xiuyan', sysdate);
  20. end;
  21. /
复制代码


游标:

以下题目基于部门表和员工表:
  1. -- 创建表
  2. create table emp(
  3.         empno number,
  4.         ename varchar2(20),
  5.         job varchar2(20),
  6.         sal number,
  7.         deptno number);
  8. create table dept(
  9.         deptno number,
  10.         dname varchar2(20),
  11.         loc varchar2(20));
  12.        
  13. -- 插入数据
  14. insert into dept values(10,'account','new york');
  15. insert into dept values(20,'salesman','chicago');
  16. insert into dept values(30,'research','dallas');
  17. insert into dept values(40,'operations','boston');
  18. insert into emp values(1001,'mary','account',5000,10);
  19. insert into emp values(2001,'smith','salesman',6000,20);
  20. insert into emp values(3001,'kate','research',7000,30);
复制代码


1、使用隐式游标和 for 语句检索出职务是贩卖员(salesman)的雇员信息并输出
  1. begin
  2.         for emp_record in(select empno, ename, sal from emp where job='salesman')
  3.         loop
  4.                 dbms_output.put('雇员编号:'||emp_record.empno);
  5.                 dbms_output.put('; 雇员名称:'||emp_record.ename);
  6.                 dbms_output.put_line('; 雇员编号:'||emp_record.sal);
  7.         end loop;
  8. end;
  9. /
复制代码



2、员工工资上调20%,使用隐式游标输出上调工资的员工数目

把 emp 表中贩卖员(即salesman)的工资上调20%,然后使用隐式游标 SQL 的 %ROWCOUNT 属性输出上调工资的员工数目。
  1. begin
  2.         update emp set sal=sal*(1+0.2) where job='salesman';
  3.         if sql%notfound then
  4.                 dbms_output.put_line('没有雇员需要上调工资');
  5.         else
  6.                 dbms_output.put_line('有'|| sql%rowcount ||'个雇员需要上调工资');
  7.         end if;
  8. end;
  9. /
复制代码



3、用表现游标和for语句检索出部门编号是30的雇员信息并输出
  1. declare
  2.         cursor cur_emp is
  3.         select * from emp where deptno = 30;
  4. begin       
  5.         for emp_record in cur_emp
  6.         loop
  7.                 dbms_output.put('雇员编号:'||emp_record.empno);
  8.                 dbms_output.put('; 雇员名称:'||emp_record.ename);
  9.                 dbms_output.put_line('; 雇员职务:'||emp_record.job);
  10.         end loop;
  11. end;
  12. /
复制代码



4、声明游标检索员工信息,并使用%FOUND属性来判定

声明一个游标,用于检索指定员工编号的雇员信息,然后使用游标的%FOUND属性来判定是否检索到指定员工编号的雇员信息。
  1. declare
  2.         v_ename varchar2(50);
  3.         v_job varchar2(50);
  4.         cursor cur_emp is
  5.         select ename, job from emp where empno = &empno;
  6. begin
  7.         open cur_emp;
  8.         fetch cur_emp into v_ename, v_job;
  9.         if cur_emp%found then
  10.                 dbms_output.put('雇员编号:'||v_ename ||',职务是:'||v_job );
  11.         else
  12.                 dbms_output.put('无数据记录');
  13.         end if;
  14. end;
  15. /
复制代码



5、创建游标完成数据转移,将fruit表中单价大于10的记载放到fruitage表中

创建 fruit 表并插入数据
  1. -- 创建水果表
  2. create table fruit(
  3.         f_id    varchar2(10)    not null,
  4.         f_name  varchar2(255)          not null,
  5.         f_price  number (8,2)          not null
  6. );
  7. --插入数据
  8. insert into fruit values  ('a1', 'apple',5.2);
  9. insert into fruit values ('b1','blackberry', 10.2);
  10. insert into fruit values ('bs1','orange', 11.2);
  11. insert into fruit values('bs2','melon',8.2);
  12. insert into fruit values ('t1','banana', 10.3);
  13. insert into fruit values ('t2','grape', 5.3);
  14. insert into fruit values ('o2','coconut', 9.2);
复制代码
创建表fruitage,表fruitage和表fruit的字段一致,利用以下语句创建:
  1. create table fruitage as select * from fruit where 2=3;
  2. -- 如果WHERE后面的条件为真,则复制表时把数据也一起复制。
  3. -- 不加默认会复制数据。
复制代码
创建游标,完成数据转移,将fruit表中,单价大于10的记载放到fruitage表中。
  1. declare
  2.         v_id fruit.f_id %TYPE;
  3.         v_name fruit.f_name %TYPE;
  4.         v_price fruit.f_price %TYPE;
  5.         cursor frt_cur is
  6.         select f_id, f_name, f_price from fruit where f_price>10;
  7. begin
  8.         open frt_cur;
  9.         loop
  10.                 fetch frt_cur into v_id, v_name, v_price;
  11.                         if frt_cur%found then
  12.                                 insert into fruitage values(v_id, v_name, v_price);
  13.                         else
  14.                                 dbms_output.put_line('已取出所有数据,共有'||frt_cur%ROWCOUNT||'条记录');
  15.                         exit;
  16.                         end if;
  17.         end loop;
  18.         close frt_cur;
  19. end;
  20. /
复制代码

到此这篇关于Oracle触发器和游标练习题的文章就先容到这了,更多相关Oracle触发器和游标内容请搜索草根技术分享从前的文章或继承欣赏下面的相关文章希望大家以后多多支持草根技术分享!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作