Oracle stored procedures and trigger data copying Code. For more information, see.
Oracle stored procedures and trigger data copying Code. For more information, see.
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;
