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;