服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Oracle - 快速学习Oracle触发器和游标

快速学习Oracle触发器和游标

2021-08-21 22:07Xiu Yan Oracle

oracle触发器和游标是面试中常问到的知识点，今天就抽空通过实例代码给大家介绍Oracle触发器和游标的示例代码，需要的朋友参考下吧

触发器：

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

  创建一个dept_log数据表，并在其中定义两个字段（operate_tag varchar2(10),operate_time date），分别用来存储操作种类（插入，修改，删除）信息和操作日期。然后一个关于dept表的语句级触发器tri_dept，将用户对 dept 表的操作信息保存到dept_tag表中。

当任何时候从dept表中删除某个部门时，该触发器将从emp表中删除该部门的所有雇员。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 创建表
create table dept_log(
    operate_tag varchar2(10),
    operate_time date
);
create table dept(
    dname varchar2(20),
    dno number
);
 
-- 创建触发器
create or replace trigger tri_dept
before insert or update or delete
on dept
declare
    v_tag varchar2(10);
begin
    if inserting then
        v_tag:='插入';
    elsif updating then
        v_tag:='修改';
    elsif deleting then
        v_tag:='删除';
    end if;
    insert into dept_log values(v_tag, sysdate);
end tri_dept;
/

快速学习Oracle触发器和游标

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

当任何时候从dept表中删除某个部门时，该触发器将从emp表中删除该部门的所有雇员。

注意：所有的以sysdba登录的账户都不能创建触发器，因此需要在创建的用户下面创建触发器

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 创建部门表
create table dept(
    deptno number not null,
    dname varchar(20) not null
);
 
-- 创建员工表
create table emp(
    emp_no number not null,
    emp_name varchar(20) not null,
    job varchar(20) not null,
    sal number  not null,
    deptno number not null
);
 
--插入数据
insert into dept values(10, '部门1');
insert into dept values(20, '部门2');
insert into emp values(1001, '员工1', '工作1', 5000, 10);
insert into emp values(1002, '员工2', '工作2', 7200, 10);
insert into emp values(1003, '员工3', '工作3', 6000, 10);
insert into emp values(1004, '员工4', '工作4', 5000, 20);
insert into emp values(1005, '员工5', '工作5', 7000, 20);
?
1
2
3
4
5
6
7
8
-- 创建触发器
create or replace trigger del_dept
before delete on dept
for each row
begin
    delete from emp where deptno = :old.deptno;
end;
/

快速学习Oracle触发器和游标

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

创建了一个trig_insert的触发器，在向表account插入数据之后会向表myevent插入一组数据（表结构就不创建了…）

?
1
2
3
4
5
6
7
8
9
create or replace trigger trig_insert
after insert
on account
begin
    if inserting then
        insert into myevent values(1, 'after insert');
    end if;
end;
/

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

以dba 身份登录数据库，并创建一个名为db_log的数据表，用于记录登录用户的用户名和时间。
接着分别创建数据库启动和数据库关闭触发器，并向db_log数据表中插入记录，存储登录用户的用户名和操作时间。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--创建表
create table db_log(
    name varchar2(20),
    rtime timestamp
);
 
-- 创建触发器，用于记录用户登录
create or replace trigger trigger_startup
after startup
on database
begin
    insert into db_log values('user', sysdate);
end;
/
 
-- 创建触发器，用于记录用户退出
create or replace trigger trigger_shutdown
before shutdown
on database
begin
    insert into db_log values('xiuyan', sysdate);
end;
/

游标：

以下题目基于部门表和员工表：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 创建表
create table emp(
    empno number,
    ename varchar2(20),
    job varchar2(20),
    sal number,
    deptno number);
create table dept(
    deptno number,
    dname varchar2(20),
    loc varchar2(20));
    
-- 插入数据
insert into dept values(10,'account','new york');
insert into dept values(20,'salesman','chicago');
insert into dept values(30,'research','dallas');
insert into dept values(40,'operations','boston');
insert into emp values(1001,'mary','account',5000,10);
insert into emp values(2001,'smith','salesman',6000,20);
insert into emp values(3001,'kate','research',7000,30);

1、使用隐式游标和 for 语句检索出职务是销售员(salesman)的雇员信息并输出

?
1
2
3
4
5
6
7
8
9
begin
    for emp_record in(select empno, ename, sal from emp where job='salesman')
    loop
        dbms_output.put('雇员编号：'||emp_record.empno);
        dbms_output.put('; 雇员名称：'||emp_record.ename);
        dbms_output.put_line('; 雇员编号：'||emp_record.sal);
    end loop;
end;
/

快速学习Oracle触发器和游标

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

把 emp 表中销售员（即salesman）的工资上调20%，然后使用隐式游标 sql 的 %rowcount 属性输出上调工资的员工数量。

?
1
2
3
4
5
6
7
8
9
begin
    update emp set sal=sal*(1+0.2) where job='salesman';
    if sql%notfound then
        dbms_output.put_line('没有雇员需要上调工资');
    else
        dbms_output.put_line('有'|| sql%rowcount ||'个雇员需要上调工资');
    end if;
end;
/

快速学习Oracle触发器和游标

3、用显示游标和for语句检索出部门编号是30的雇员信息并输出

?
1
2
3
4
5
6
7
8
9
10
11
12
declare
    cursor cur_emp is
    select * from emp where deptno = 30;
begin  
    for emp_record in cur_emp
    loop
        dbms_output.put('雇员编号：'||emp_record.empno);
        dbms_output.put('; 雇员名称：'||emp_record.ename);
        dbms_output.put_line('; 雇员职务：'||emp_record.job);
    end loop;
end;
/

快速学习Oracle触发器和游标

4、声明游标检索员工信息，并使用%found属性来判断

声明一个游标，用于检索指定员工编号的雇员信息，然后使用游标的%found属性来判断是否检索到指定员工编号的雇员信息。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
declare
    v_ename varchar2(50);
    v_job varchar2(50);
    cursor cur_emp is
    select ename, job from emp where empno = &empno;
begin
    open cur_emp;
    fetch cur_emp into v_ename, v_job;
    if cur_emp%found then
        dbms_output.put('雇员编号：'||v_ename ||',职务是：'||v_job );
    else
        dbms_output.put('无数据记录');
    end if;
end;
/

快速学习Oracle触发器和游标

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

创建 fruit 表并插入数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建水果表
create table fruit(
    f_id    varchar2(10)    not null,
    f_name  varchar2(255)   not null,
    f_price  number (8,2)   not null
);
 
--插入数据
insert into fruit values  ('a1', 'apple',5.2);
insert into fruit values ('b1','blackberry', 10.2);
insert into fruit values ('bs1','orange', 11.2);
insert into fruit values('bs2','melon',8.2);
insert into fruit values ('t1','banana', 10.3);
insert into fruit values ('t2','grape', 5.3);
insert into fruit values ('o2','coconut', 9.2);

创建表fruitage，表fruitage和表fruit的字段一致，利用以下语句创建：

?
1
2
3
create table fruitage as select * from fruit where 2=3;
-- 如果where后面的条件为真，则复制表时把数据也一起复制。
-- 不加默认会复制数据。

创建游标，完成数据转移，将fruit表中，单价大于10的记录放到fruitage表中。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
declare
    v_id fruit.f_id %type;
    v_name fruit.f_name %type;
    v_price fruit.f_price %type;
    cursor frt_cur is
    select f_id, f_name, f_price from fruit where f_price>10;
begin
    open frt_cur;
    loop
        fetch frt_cur into v_id, v_name, v_price;
            if frt_cur%found then
                insert into fruitage values(v_id, v_name, v_price);
            else
                dbms_output.put_line('已取出所有数据，共有'||frt_cur%rowcount||'条记录');
            exit;
            end if;
    end loop;
    close frt_cur;
end;
/

快速学习Oracle触发器和游标

到此这篇关于oracle触发器和游标练习题的文章就介绍到这了,更多相关oracle触发器和游标内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/weixin_43819566/article/details/117931790

  • 触发器
  • 游标
  • Oracle

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    1033 　 1455 　 12154 　 10231 　 12514 　 54013 　 数据库系统 　 快照 　 分区策略 　 数据库集 　 异常关闭 　 Oracle9iPL 　 SQL编程 　 日期函数 　 SQL性能优化 　 并行查询 　 紧急故障 　 完全卸载 　 Win2k 　 IMP 　 分区管理 　 密码文件 　 AutoUpgrade 　 异地备份 　 交叉表 　 加载数据库错误 　 areasQueries 　 逻辑备份 　 SH文件 　 数据文件 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图