導(dǎo)讀:1建站知識(shí)oracle 存儲(chǔ)過程和觸發(fā)器復(fù)制數(shù)據(jù)的代碼，需要的朋友可以參考下。網(wǎng)站建設(shè)建設(shè)網(wǎng)站。

一。存儲(chǔ)過程的創(chuàng)建和使用 1.創(chuàng)建程序包，并在程序中創(chuàng)建存儲(chǔ)過程 create or replace PACKAGE NCS_ICP_TJ AS /*lfx@ncs-cyber.com.cn*/ /* TODO 在此輸入程序包聲明 (類型, 異常錯(cuò)誤, 方法等) */ /*根據(jù)備案主體ID拷貝通過表備案數(shù)據(jù)到備案臨時(shí)表，拷貝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.創(chuàng)建程序包包體，并在程序中創(chuàng)建存儲(chǔ)過程實(shí)現(xiàn) create or replace PACKAGE BODY ncs_icp_tj AS /*根據(jù)備案主體ID拷貝通過表備案數(shù)據(jù)到備案臨時(shí)表，拷貝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_公司網(wǎng)站建設(shè)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個(gè)業(yè)網(wǎng)站建設(shè)公司, LRYHLX, LR_YHM_ID, BAMM FROM ICP_GN_BAXX_ZT WHERE ID = v_main_id; /*插入網(wǎng)站*/ 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, B如何seo優(yōu)化推廣網(wǎng)站AXH, 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. 調(diào)用存儲(chǔ)過程，call ncs_icp_tj.icp_pass_to_temp(5,1,0,2,17) 本存儲(chǔ)過程的調(diào)用，實(shí)現(xiàn)了從5張通過表復(fù)制數(shù)據(jù)到5張臨時(shí)表 二，觸發(fā)器的創(chuàng)建。 1.行級(jí)觸發(fā)器,沒插入一條數(shù)據(jù)執(zhí)行一次, 向臨時(shí)表中加入數(shù)據(jù)時(shí)，執(zhí)行此觸發(fā)器，把臨時(shí)表插入到臨時(shí)表的數(shù)據(jù)復(fù)制的日志表中 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.表級(jí)觸發(fā)器 插入整個(gè)過程中，觸發(fā)器只之行一次 ，當(dāng)向AAA表中如入一條數(shù)據(jù)，將真?zhèn)€AAA表的數(shù)據(jù)復(fù)制BBB表 create or replace TRIGGER TRIGGER_AAA_INSERT AFTER INSERT ON AAA BEGIN insert into BBB(userid, username) select id, username from AAA; END;相關(guān)網(wǎng)站建設(shè)建設(shè)網(wǎng)站。

關(guān)鍵詞標(biāo)簽: 存儲(chǔ)過程 觸發(fā)器