快速学习Oracle触发器和游标

目录

• 触发器：
• 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_deptbefore insert or update or deleteon deptdeclare	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_deptbefore delete on deptfor each rowbegin	delete From emp where deptno = :old.deptno;
    end;
    /

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

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

create or replace trigger trig_insertafter inserton accountbegin	if inserting then		insert into myevent values(1, 'after insert');
    	end if;
    end;
    /

@L_777_15@

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

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

--创建表create table db_log(	name varchar2(20),	rtime timestamp);
    -- 创建触发器，用于记录用户登录create or replace trigger trigger_startupafter startupon databasebegin	insert into db_log values('user', sysdate);
    end;
    /-- 创建触发器，用于记录用户退出create or replace trigger trigger_shutdownbefore shutdownon databasebegin	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;
    /

@L_406_19@

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触发器和游标内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

• Oracle中触发器示例详解
• 详解oracle中通过触发器记录每个语句影响总行数
• Oracle触发器trigger详解
• Oracle触发器用法实例详解
• oracle监控某表变动触发器例子(监控增,删,改)
• Oracle创建主键自增表(sql语句实现)及触发器应用
• Oracle中游标Cursor基本用法详解
• Oracle存储过程游标用法分析
• Oracle显示游标的使用及游标for循环

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！