分享
 
 
 

oracle 存储过程和触发器复制数据

王朝学院·作者佚名  2009-11-18
窄屏简体版  字體: |||超大  

一。存储过程的创建和使用

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_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;

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有