快速学习Oracle触发器和游标

• 触发器：
  
  
  
  • 1、 创建一个用于记载用户操作的触发器
    
  • 2、创建一个当删除部门时，删除该部门下的全部雇员的触发器
    
  • 3、创建一个在account表插入记载之后，更新myevent数据表的触发器
    
  • 4、创建一个用于记载登录 DBA 身份用户的用户名和时间的触发器
    
  
  
• 游标：
  
  
  
  • 1、使用隐式游标和 for 语句检索出职务是贩卖员(salesman)的雇员信息并输出
    
  • 2、员工工资上调20%，使用隐式游标输出上调工资的员工数目
    
  • 3、用表现游标和for语句检索出部门编号是30的雇员信息并输出
    
  • 4、声明游标检索员工信息，并使用%FOUND属性来判定
    
  • 5、创建游标完成数据转移，将fruit表中单价大于10的记载放到fruitage表中
    
  
  

-- 创建表
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;
/
复制代码

-- 创建部门表
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;
/
复制代码

create or replace trigger trig_insert
after insert
on account
begin
        if inserting then
                insert into myevent values(1, 'after insert');
        end if;
end;
/
复制代码

--创建表
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);
复制代码

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;
/
复制代码

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;
/
复制代码

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;
/
复制代码

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;
/
复制代码

-- 创建水果表
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);
复制代码

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

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;
/
复制代码