SQL分組排序去反復的小實例。本站提示廣大學習愛好者：（SQL分組排序去反復的小實例）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL分組排序去反復的小實例正文
一。存儲進程的創立和應用
1.創立法式包，並在法式中創立存儲進程
create or replace
PACKAGE NCS_ICP_TJ AS
/*
[email protected]*/
/* 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;
END ICP_PASS_TO_TEMP;
END ncs_icp_tj;
3. 挪用存儲進程，call ncs_icp_tj.icp_pass_to_temp(5,1,0,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, 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.表級觸發器 拔出全部進程中，觸發器只之行一次 ，當向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;
