目次
- 触发器:
- 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表中删除该部门的全部雇员。
- -- 创建表
- 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;
- /
复制代码
2、创建一个当删除部门时,删除该部门下的全部雇员的触发器
当任何时间从dept表中删除某个部门时,该触发器将从emp表中删除该部门的全部雇员。
注意:全部的以sysdba登录的账户都不能创建触发器,因此必要在创建的用户下面创建触发器- -- 创建部门表
- 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);
复制代码
- -- 创建触发器
- create or replace trigger del_dept
- before delete on dept
- for each row
- begin
- delete from emp where deptno = :old.deptno;
- end;
- /
复制代码
3、创建一个在account表插入记载之后,更新myevent数据表的触发器
创建了一个TRIG_INSERT的触发器,在向表account插入数据之后会向表myevent插入一组数据(表结构就不创建了…)- 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数据表中插入记载,存储登任命户的用户名和操作时间。- --创建表
- 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;
- /
复制代码
游标:
以下题目基于部门表和员工表:- -- 创建表
- 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)的雇员信息并输出
- 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;
- /
复制代码
2、员工工资上调20%,使用隐式游标输出上调工资的员工数目
把 emp 表中贩卖员(即salesman)的工资上调20%,然后使用隐式游标 SQL 的 %ROWCOUNT 属性输出上调工资的员工数目。- 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;
- /
复制代码
3、用表现游标和for语句检索出部门编号是30的雇员信息并输出
- 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;
- /
复制代码
4、声明游标检索员工信息,并使用%FOUND属性来判定
声明一个游标,用于检索指定员工编号的雇员信息,然后使用游标的%FOUND属性来判定是否检索到指定员工编号的雇员信息。- 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;
- /
复制代码
5、创建游标完成数据转移,将fruit表中单价大于10的记载放到fruitage表中
创建 fruit 表并插入数据- -- 创建水果表
- 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的字段一致,利用以下语句创建:- create table fruitage as select * from fruit where 2=3;
- -- 如果WHERE后面的条件为真,则复制表时把数据也一起复制。
- -- 不加默认会复制数据。
复制代码
创建游标,完成数据转移,将fruit表中,单价大于10的记载放到fruitage表中。- 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触发器和游标内容请搜索草根技术分享从前的文章或继承欣赏下面的相关文章希望大家以后多多支持草根技术分享! |