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张临时表
申明：本内容来源于网络，仅代表作者个人观点，与本站立场无关，仅供您学习交流使用。其中可能有部分文章经过多次转载而造成文章内容缺失、错误或文章作者不详等问题，请您谅解。如有侵犯您的权利，请联系我们，本站会立即予以处理。
相关推荐：
OracleImpandExp(导入和导出)工具使用
oraclerman备份和还原恢复数据库
数据库连接池的基本原理
