|
一。存储过程的创建和使用 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_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,v_main_id,v_in_hmd/是否在黑名单/,v_czlb/操作类别/,v_bajd/备案阶段/,BAMM
FROM ICP_GN_BAXX_ZT WHERE ID = v_main_id;
/插入网站/
INSERT INTO ICP_GN_TEMP_BAXX_WZ
(LSH,WZID,SJXT_WZID,XGSJ,WZMC,SYURL,WZFZR_DZYJ,NRLX,FWNR,BAMM,BAJD)
SELECT
v_lsh,id,1
FROM ICP_GN_BAXX_WZ
WHERE ZTID = v_main_id;
/插入接入/
INSERT INTO ICP_GN_TEMP_BAXX_JR
(lsh,bbdw,JRID,SJXT_JRID,SSISP,WZFB,WZJRFS,bajd)
SELECT
v_lsh,ID,v_bajd
FROM ICP_GN_BAXX_JR
WHERE ZTID =v_main_id;
/插入IP/
INSERT INTO ICP_GN_TEMP_BAXX_IPLB
(lsh,IPID,SJXT_IPID,QSIP,ZZIP)
SELECT v_lsh,ZZIP
FROM ICP_GN_BAXX_IPLB
WHERE ZTID = v_main_id; /插入域名/
INSERT INTO ICP_GN_TEMP_BAXX_YMLB
(lsh,YMID,SJXT_YMID,YM)
SELECT
v_lsh,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,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,LS_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,
WZFZR_SJHM,ls_id)
values( SEQ_ICP_GN_BAXX_XGLS_WZ_ID.NEXTVAL,
:new.LSH,:new.WZID,:new.SJXT_WZID,:new.WZMC,:new.SYURL,
:new.WZFZR_SJHM,:new.WZFZR_DZYJ,:new.NRLX,:new.FWNR,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,
WZFB,ls_id
)
values (SEQ_ICP_GN_BAXX_XGLS_JR_ID.NEXTVAL,
:new.lsh,:new.bbdw,:new.JRID,:new.SJXT_JRID,:new.SSISP,
:new.WZFB,:new.WZJRFS,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,ZZIP,ls_id
)
values( SEQ_ICP_GN_BAXX_XGLS_IPLB_ID.NEXTVAL,:new.IPID,: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; （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
