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

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

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

createorreplace
PACKAGENCS_ICP_TJAS
/*lfx@ncs-cyber.com.cn*/
/*TODO在此输入程序包声明(类型,异常错误,方法等)*/
/*根据备案主体ID拷贝通过表备案数据到备案临时表，拷贝5张*/
PROCEDUREICP_PASS_TO_TEMP(
v_main_idINicp_gn_temp_baxx_zt.ztid%TYPE,
v_lydINicp_gn_temp_baxx_zt.SJXT_ZTID%TYPE,
v_in_hmdINicp_gn_temp_baxx_zt.in_hmd%TYPE,
v_czlbINicp_gn_temp_baxx_zt.czlb%TYPE,
v_bajdINicp_gn_temp_baxx_zt.bajd%TYPE
);
ENDNCS_ICP_TJ;
2.创建程序包包体，并在程序中创建存储过程实现

createorreplace
PACKAGEBODYncs_icp_tjAS
/*根据备案主体ID拷贝通过表备案数据到备案临时表，拷贝5张*/
PROCEDUREICP_PASS_TO_TEMP(
v_main_idINicp_gn_temp_baxx_zt.ztid%TYPE,
v_lydINicp_gn_temp_baxx_zt.SJXT_ZTID%TYPE,
v_in_hmdINicp_gn_temp_baxx_zt.in_hmd%TYPE,
v_czlbINicp_gn_temp_baxx_zt.czlb%TYPE,
v_bajdINicp_gn_temp_baxx_zt.bajd%TYPE
)
IS
v_lshinteger;
BEGIN
selectSEQ_ICP_GN_TEMP_BAXX_ZT_ZTID.NEXTVALintov_lshfromdual;
IFv_main_idISNULLORv_lydISNULLORv_in_hmdISNULLORv_czlbISNULLORv_bajdISNULLTHEN
RAISE_APPLICATION_ERROR(-20000,"Exsitnullvalueinarguments.");
ENDIF;
/*所有插入的查询条件为主体ID*/
/*插入主体*/
INSERTINTOICP_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
FROMICP_GN_BAXX_ZTWHEREID=v_main_id;
/*插入网站*/
INSERTINTOICP_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
FROMICP_GN_BAXX_WZ
WHEREZTID=v_main_id;
/*插入接入*/
INSERTINTOICP_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
FROMICP_GN_BAXX_JR
WHEREZTID=v_main_id;
/*插入IP*/
INSERTINTOICP_GN_TEMP_BAXX_IPLB
(lsh,bbdw,IPID,ZTID,WZID,JRID,SJXT_IPID,QSIP,ZZIP)
SELECTv_lsh,bbdw,ID,v_main_id,WZID,JRID,SJXT_IPID,QSIP,ZZIP
FROMICP_GN_BAXX_IPLB
WHEREZTID=v_main_id;

/*插入域名*/
INSERTINTOICP_GN_TEMP_BAXX_YMLB
(lsh,bbdw,YMID,ZTID,WZID,SJXT_YMID,YM)
SELECT
v_lsh,bbdw,ID,v_main_id,WZID,SJXT_YMID,YM
FROMICP_GN_BAXX_YMLB
WHEREZTID=v_main_id;

ENDICP_PASS_TO_TEMP;

ENDncs_icp_tj;

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

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

二，触发器的创建。

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

createorreplace
TRIGGERTRIGGER_ICP_TEMP_ZT_INSERT
AFTERINSERTONICP_GN_TEMP_BAXX_ZT
FOREACHROW
BEGIN
insertintoICP_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;


createorreplaceTRIGGERTRIGGER_ICP_TEMP_WZ_INSERT
AFTERINSERTONICP_GN_TEMP_BAXX_WZ
FOREACHROW
BEGIN
insertintoICP_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;


createorreplace
TRIGGERTRIGGER_ICP_TEMP_JR_INSERT
AFTERINSERTONICP_GN_TEMP_BAXX_JR
FOREACHROW
BEGIN
insertintoICP_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;


createorreplace
TRIGGERTRIGGER_ICP_TEMP_IPLB_INSERT
AFTERINSERTONICP_GN_TEMP_BAXX_IPLB
FOREACHROW
BEGIN
insertintoICP_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表

createorreplace
TRIGGERTRIGGER_AAA_INSERT
AFTERINSERTONAAA
BEGIN
insertintoBBB(userid,username)
selectid,usernamefromAAA;
END;