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

一。存储过程的创建和使用 1.创建程序包,并在程序中创建存储过程 create or
replace PACKAGE NCS_ICP_TJ AS /*lfx@ncs-cyber.com.cn*/ /* 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_463.com,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;

相关文章