Copy data using oracle stored procedures and triggers

I. Creation and use of stored procedures

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

Create or replace
PACKAGE NCS_ICP_TJ
/* Lfx@ncs-cyber.com.cn */
/* TODO enter the package Declaration (type, Exception error, method, etc.) here )*/
/* Copy data from the form to the temporary form based on the ICP filing subject ID, and copy 5 copies */
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 a program package and create a stored procedure in the program.

Create or replace
Package body ncs_icp_tj
/* Copy data from the form to the temporary form based on the ICP filing subject ID, and copy 5 copies */
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;
/* All inserted query conditions are subject IDs */
/* Insert the subject */
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/* 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 a website */
Insert into ICP_GN_TEMP_BAXX_WZ
(LSH, BBDW, WZID, ZTID, SJXT_WZID, SCBBSJ, XGSJ, WZMC, SYURL, WZFZR, plural, cosine, WZFZR_MSN, cosine, 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, numeric, WZFZR_MSN, numeric, 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. call the stored procedure, call ncs_icp_tj.icp_pass_to_temp (, 17)

This stored procedure is called to replicate data from five tables to five temporary tables.

2. Create a trigger.

1. A Row-level trigger that does not insert a data record for execution once. When adding data to a temporary table, execute this trigger to insert the temporary table to the Data Replication 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. During the whole process of table-Level Trigger insertion, the trigger only has one row. When a piece of data is inserted into the AAA table, the data in the AAA table is copied to the 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;