Oracle stored procedures and triggers replication data _oracle

One. Creation and use of stored procedures

1. Create the package and create the stored procedure in the program

Create or replace
PACKAGE NCS_ICP_TJ as
/*lfx@ncs-cyber.com.cn*/
/* TODO Enter Package declaration (type, exception error, method, etc.) here.
* * According to the record of the main ID copy through the table record data to the record temporary table, copy 5 Zhang * *
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 the package body and create the stored procedure implementation in the program

Create or replace
PACKAGE Body NCS_ICP_TJ as
* * According to the record of the main ID copy through the table record data to the record temporary table, copy 5 Zhang * *
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 are null or V_IN_HMD is null or v_czlb be null or V_BAJD is null THEN
Raise_application_error ( -20000, ' exsit null value in arguments. ');
End IF;
/* All inserted query criteria are subject id*/
/* Insert main BODY * *
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/* whether in the blacklist/, v_czlb/* operation category * *, 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/* filing Stage * *, ZSYXQ, SHR_XM,
SHSJ,BZ, LRYHLX, lr_yhm_id, BAMM
From icp_gn_baxx_zt WHERE ID = v_main_id;
/* INSERT WEB site * *
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_D Zyj, 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, WZF Zr_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 Access * *
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;
/* Insert 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 Domain Name * *
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. Invoke the stored procedure, call Ncs_icp_tj.icp_pass_to_temp (5,1,0,2,17)

A call to this stored procedure, which enables you to copy data from 5 sheets to 5 temporary tables

Second, the creation of triggers.

1. Row-level triggers that do not insert a single piece of data to execute once, when you add data to a temporary table, execute this trigger, and insert the temporary table into the data-replicated log table of the temporary table

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. Table-level triggers insert the entire process, the trigger only once, when the AAA table, such as a data, the whole AAA table of data copy BBB table

Create or replace
TRIGGER Trigger_aaa_insert
After INSERT on AAA
BEGIN
INSERT INTO BBB (userid, username)
Select ID, username from AAA;
End;