双表同步数据的oracle package使用触发器实现

package

create or replace package PKG_YYY is
      procedure EMP_INSERT;
      procedure EMP_SELECT;
      procedure EMP_DELETE;
      procedure EMP_UPDATE;
end PKG_YYY;

package body

create or replace package body PKG_YYY is

--增加数据

procedure EMP_INSERT is
 
 cursor cursor_insert is
  select * from emp03;
  v_all cursor_insert%rowtype;
 
begin
  open cursor_insert;
  loop
   fetch cursor_insert into v_all;
   exit when cursor_insert%notfound;
   insert into emp03 values(v_all.EMPLOYEE_ID,v_all.LAST_NAME,v_all.SALARY,v_all.DEPARTMENT_ID,v_all.HIRE_DATE);
   dbms_output.put_line('插入成功！');
   commit;
  end loop;
 
  close cursor_insert;
 
end EMP_INSERT;

--更新工资低于 3000 的员工工资调为 3000
procedure EMP_UPDATE is
  cursor cursor_update is
  select employee_id,salary from emp01;
  v_id emp03.employee_id%type;
  v_sal emp03.salary%type;
begin
  open cursor_update;
    loop
      fetch cursor_update into v_id,v_sal;
      exit when cursor_update%notfound;
      if v_sal <3000 then
         update emp03 set salary =3000 where employee_id=v_id;
         dbms_output.put_line('员工：'||v_id||'工资已经更新');
      end if;
      commit;
    end loop;
    dbms_output.put_line('一共有'||cursor_update%rowcount||'条数据被调正');
    close cursor_update;
end EMP_UPDATE;
 
--查询前 10 名员工的信息。
procedure EMP_SELECT is
 
 cursor cursor_select is
 select last_name,salary from emp03
   where rownum<11 order by salary;
  v_name emp03.last_name%type;
  v_sal  emp03.salary%type;        
begin
  open cursor_select;
  fetch cursor_select into v_name, v_sal;
   while cursor_select %found loop
        dbms_output.put_line(v_name||':'||v_sal);
        fetch cursor_select into v_name, v_sal;
        commit;
   end loop;
  close  cursor_select;
 
end EMP_SELECT;

--删除数据
procedure EMP_DELETE is
 
 cursor cursor_delete is
  select employee_id from emp03;
  v_id emp03.employee_id%type;
  e_deleteid_exception exception;
 pragma exception_init(e_deleteid_exception,-2292);
begin
  open cursor_delete;
 
     fetch cursor_delete into v_id;
     delete from employees
     where employee_id=100;
     
exception
 when e_deleteid_exception then dbms_output.put_line('违反完整性约束条件,不可删除此用户！');  
  close cursor_delete;
  commit;
 
end EMP_DELETE;
 

end PKG_YYY;

 触发器

create or replace trigger A_TRIGGER
     after
     insert or update or delete on emp03
     for each row
begin
  if inserting then
      insert into emp04(employee_id,last_name,salary,department_id,hire_date)
      values(:new.employee_id,:new.last_name,:new.salary,:new.department_id,:new.hire_date);
  elsif updating then
       update emp04 set salary =:new.salary
       where employee_id=:new.employee_id;
  elsif deleting then
       delete from emp04 where employee_id= :new.employee_id;
  end if;
end A_TRIGGER;

 测试数据添加

declare
  v_1 emp03.employee_id%type :=50;
  v_2  emp03.last_name%type :='bill';
  v_3  emp03.salary%type :=12340.00;
  v_4 emp03.department_id%type :=20;
 
begin
  insert into emp03(employee_id,last_name,salary,department_id,hire_date)
  values(v_1,v_2,v_3,v_4,sysdate);
end;
 

测试 结果