• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    oracle 存储过程和触发器复制数据
    一。存储过程的创建和使用

    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;
    您可能感兴趣的文章:
    • Oracle 查询死锁并解锁的终极处理方法
    • 在命令行下进行Oracle用户解锁的语句
    • oracle修改scott密码与解锁的方法详解
    • ORACLE 如何查询被锁定表及如何解锁释放session
    • oracle查询锁表与解锁情况提供解决方案
    • Oracle 添加用户并赋权,修改密码,解锁,删除用户的方法
    • Oracle 跨库 查询 复制表数据 分布式查询介绍
    • oracle复制表结构和复制表数据语句分享
    • Oracle数据库集复制方法浅议
    • oracle查询锁表及解锁,修改表字段名与复制表结构和数据的方法
    上一篇:Oracle中sys和system的区别小结
    下一篇:Oracle 多行记录合并/连接/聚合字符串的几种方法
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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