oracle 存储过程和触发器复制数据

一。存储过程的创建和使用

1.创建程序包，并在程序中创建存储过程

create or replace
package ncs_icp_tj as
/*[e-mail]*/
/* todo 在此输入程序包声明 (类型, 异常错误, 方法等) */
/*根据备案主体id拷贝通过表备案数据到备案临时表，拷贝5张*/
procedure icp_pass_to_temp(
v_main_id in icp_gn_temp_baxx_zt.ztid%type,
v_lyd in icp_gn_temp_baxx_zt.sjxt_ztid%type,
v_in_hmd in icp_gn_temp_baxx_zt.in_hmd%type,
v_czlb in icp_gn_temp_baxx_zt.czlb%type,
v_bajd in icp_gn_temp_baxx_zt.bajd%type
);
end ncs_icp_tj;
2.创建程序包包体，并在程序中创建存储过程实现

create or replace
package body ncs_icp_tj as
/*根据备案主体id拷贝通过表备案数据到备案临时表，拷贝5张*/
procedure icp_pass_to_temp(
v_main_id in icp_gn_temp_baxx_zt.ztid%type,
v_lyd in icp_gn_temp_baxx_zt.sjxt_ztid%type,
v_in_hmd in icp_gn_temp_baxx_zt.in_hmd%type,
v_czlb in icp_gn_temp_baxx_zt.czlb%type,
v_bajd in icp_gn_temp_baxx_zt.bajd%type
)
is
v_lsh integer;
begin
select seq_icp_gn_temp_baxx_zt_ztid.nextval into v_lsh from dual;
if v_main_id is null or v_lyd is null or v_in_hmd is null or v_czlb is null or v_bajd is null then
raise_application_error(-20000, 'exsit null value in arguments.');
end if;
/*所有插入的查询条件为主体id*/
/* 插入主体*/
insert into icp_gn_temp_baxx_zt
(lsh, bbdw,ztid, sjxt_ztid, yhm_id, in_hmd, czlb, scbbsj, zjxgsj, dwmc, dwxz, tzz, zjlx, zjhm, shengid,
shiid, xianid, xxdz, zjzs,jylx, wzfzr, wzfzr_zjlx, wzfzr_zjhm, wzfzr_dhhm, wzfzr_sjhm,
wzfzr_dzyj, wzfzr_msn, wzfzr_qq, baxh, bajd, zsyxq, shr_xm,
shsj, bz, lryhlx, lr_yhm_id, bamm)
select
v_lsh, bbdw, v_main_id, sjxt_ztid, yhm_id, v_in_hmd/*是否在黑名单*/, v_czlb/*操作类别*/, scbbsj, zjxgsj, dwmc, dwxz, tzz, zjlx, zjhm, shengid,
shiid, xianid, xxdz, zjzs, jylx,wzfzr, wzfzr_zjlx, wzfzr_zjhm, wzfzr_dhhm, wzfzr_sjhm,
wzfzr_dzyj, wzfzr_msn, wzfzr_qq, baxh,v_bajd/*备案阶段*/, zsyxq, shr_xm,
shsj,bz, lryhlx, lr_yhm_id, bamm
from icp_gn_baxx_zt where id = v_main_id;
/*插入网站*/
insert into icp_gn_temp_baxx_wz
(lsh,bbdw,wzid, ztid, sjxt_wzid, scbbsj, xgsj, wzmc, syurl, wzfzr, wzfzr_zjlx, wzfzr_zjhm, wzfzr_dhhm, wzfzr_sjhm, wzfzr_dzyj, wzfzr_msn, wzfzr_qq, nrlx, fwnr, baxh, lryhlx, lr_yhm_id, bamm, bz,bajd)
select
v_lsh,bbdw,id, v_main_id, sjxt_wzid, scbbsj, xgsj, wzmc, syurl, wzfzr, wzfzr_zjlx, wzfzr_zjhm, wzfzr_dhhm, wzfzr_sjhm, wzfzr_dzyj, wzfzr_msn, wzfzr_qq, nrlx, fwnr, baxh, lryhlx, lr_yhm_id, bamm, bz,1
from icp_gn_baxx_wz
where ztid = v_main_id;
/*插入接入*/
insert into icp_gn_temp_baxx_jr
(lsh, bbdw,jrid, ztid, wzid, sjxt_jrid, ssisp, wzfb, wzjrfs, lryhlx, lr_yhm_id, bamm, bajd)
select
v_lsh, bbdw,id, v_main_id, wzid, sjxt_jrid, ssisp, wzfb, wzjrfs, lryhlx, lr_yhm_id, bamm,v_bajd
from icp_gn_baxx_jr
where ztid =v_main_id;
/*插入ip*/
insert into icp_gn_temp_baxx_iplb
(lsh,bbdw,ipid, ztid, wzid, jrid, sjxt_ipid, qsip, zzip)
select v_lsh, bbdw,id, v_main_id, wzid, jrid, sjxt_ipid, qsip, zzip
from icp_gn_baxx_iplb
where ztid = v_main_id;

/*插入域名*/
insert into icp_gn_temp_baxx_ymlb
(lsh, bbdw,ymid, ztid, wzid, sjxt_ymid, ym)
select
v_lsh, bbdw,id, v_main_id, wzid, sjxt_ymid, ym
from icp_gn_baxx_ymlb
where ztid = v_main_id;

end icp_pass_to_temp;

end ncs_icp_tj;

3. 调用存储过程，call ncs_icp_tj.icp_pass_to_temp(5,1,0,2,17)

本存储过程的调用，实现了从5张通过表复制数据到5张临时表

二，触发器的创建。

1.行级触发器,没插入一条数据执行一次, 向临时表中加入数据时，执行此触发器，把临时表插入到临时表的数据复制的日志表中

create or replace
trigger trigger_icp_temp_zt_insert
after insert on icp_gn_temp_baxx_zt
for each row
begin
insert into icp_gn_baxx_xgls_zt
(id,lsh, bbdw,ls_id,ztid,sjxt_ztid, dwmc, dwxz, tzz, zjlx, zjhm, shengid,
shiid, xianid, xxdz, zjzs,jylx, wzfzr, wzfzr_zjlx, wzfzr_zjhm, wzfzr_dhhm, wzfzr_sjhm,
wzfzr_dzyj, wzfzr_msn, wzfzr_qq, baxh, shr_xm,
shsj, bz, lryhlx, lr_yhm_id, bamm)
values(　
seq_icp_gn_baxx_xgls_zt_id.nextval,:new.lsh, :new.bbdw,:new.czlb,:new.ztid,:new.sjxt_ztid, :new.dwmc, :new.dwxz, :new.tzz, :new.zjlx, :new.zjhm, :new.shengid,
:new.shiid, :new.xianid, :new.xxdz, :new.zjzs,:new.jylx, :new.wzfzr, :new.wzfzr_zjlx, :new.wzfzr_zjhm, :new.wzfzr_dhhm, :new.wzfzr_sjhm,
:new.wzfzr_dzyj, :new.wzfzr_msn, :new.wzfzr_qq, :new.baxh, :new.shr_xm,
:new.shsj, :new.bz, :new.lryhlx, :new.lr_yhm_id, :new.bamm);
end;


create or replace trigger trigger_icp_temp_wz_insert
after insert on icp_gn_temp_baxx_wz
for each row
begin
insert into icp_gn_baxx_xgls_wz
(id,
lsh,bbdw,wzid, ztid, sjxt_wzid, wzmc, syurl,wzfzr, wzfzr_zjlx, wzfzr_zjhm, wzfzr_dhhm,
wzfzr_sjhm, wzfzr_dzyj, wzfzr_msn, wzfzr_qq, nrlx, fwnr, baxh, lryhlx, lr_yhm_id,bz, ls_id)
values( seq_icp_gn_baxx_xgls_wz_id.nextval,
:new.lsh,:new.bbdw,:new.wzid, :new.ztid, :new.sjxt_wzid, :new.wzmc, :new.syurl,:new.wzfzr, :new.wzfzr_zjlx, :new.wzfzr_zjhm, :new.wzfzr_dhhm,
:new.wzfzr_sjhm, :new.wzfzr_dzyj, :new.wzfzr_msn, :new.wzfzr_qq, :new.nrlx, :new.fwnr, :new.baxh, :new.lryhlx, :new.lr_yhm_id,:new.bz,1);
end;


create or replace
trigger trigger_icp_temp_jr_insert
after insert on icp_gn_temp_baxx_jr
for each row
begin
insert into icp_gn_baxx_xgls_jr
(id,
lsh, bbdw,jrid, ztid, wzid, sjxt_jrid, ssisp,
wzfb, wzjrfs, lryhlx, lr_yhm_id, ls_id
)
values (seq_icp_gn_baxx_xgls_jr_id.nextval,
:new.lsh, :new.bbdw,:new.jrid, :new.ztid, :new.wzid, :new.sjxt_jrid, :new.ssisp,
:new.wzfb, :new.wzjrfs, :new.lryhlx, :new.lr_yhm_id,1);
end;


create or replace
trigger trigger_icp_temp_iplb_insert
after insert on icp_gn_temp_baxx_iplb
for each row
begin
insert into icp_gn_baxx_xgls_iplb
(id,
lsh,bbdw,ipid, ztid, wzid, jrid, sjxt_ipid, qsip, zzip, ls_id
)
values( seq_icp_gn_baxx_xgls_iplb_id.nextval,
:new.lsh,:new.bbdw,:new.ipid, :new.ztid, :new.wzid, :new.jrid, :new.sjxt_ipid, :new.qsip, :new.zzip,1);
end;

2.表级触发器 插入整个过程中，触发器只之行一次 ，当向aaa表中如入一条数据，将真个aaa表的数据复制bbb表

create or replace
trigger trigger_aaa_insert
after insert on aaa
begin
insert into bbb(userid, username)
select id, username from aaa;
end;