最近项目中需要做一个数据同步功能,就是要将一个数据库里面的信息定时的同步到另外一个数据库中,这样的话就需要用到储存过程 触发器 定时器 和dblink.
以前没写过储存过程,恰好利用这次机会练练手,特写此博客,方便以后查阅:
序号 对象名称 类型 说明
1 synBaseDataTemp table 临时表
2 synBaseDataHistory table 同步历史记录
3 synBaseDataTemp_Sequence SEQUENCE 设置字段ID自增
4 tri_synBaseDataTemp TRIGGER 当临时表中有新增记录时,ID自动增加1
5 tri_基础数据表名 TRIGGER 当CCS基础数据表有增 删 改操作时,会自动触发在ECCS基础数据
表增加记录(共32个)
6 baseDataSynProc procedure 将临时表中记录的信息同步到ECCS中,同时将同步记录
增加到synBaseDataHistory中,并将同步的synBaseDataTemp信息删除
创建同步相关表:
[sql] view plaincopy
/*创建表synBaseDataTemp*/
CREATE TABLE synBaseDataTemp
(
id number(15,0) NOT NULL,
condition varchar2(300) not null,
tableName varchar2(40) not null,
operateType varchar2(20) not null,
operateDate date,
failFlag varchar2(1),
failTimes integer,
PRIMARY KEY (id)
);
comment on table synBaseDataTemp is '同步临时表';
comment on column synBaseDataTemp.id is '自增列,主键';
comment on column synBaseDataTemp.condition is '表主键条件';
comment on column synBaseDataTemp.tableName is '表名';
comment on column synBaseDataTemp.operateType is '操作类型';
comment on column synBaseDataTemp.operateDate is '操作时间';
comment on column synBaseDataTemp.failFlag is '失败标志,1失败';
comment on column synBaseDataTemp.failTimes is '失败次数';
/*创建表synHistory*/
CREATE TABLE synBaseDataHistory
(
id number(15,0) NOT NULL,
condition varchar2(300) not null,
tableName varchar2(40) not null,
operateType varchar2(20) not null,
operateDate date,
PRIMARY KEY (id)
);
comment on table synBaseDataHistory is '同步历史记录表';
comment on column synBaseDataHistory.id is '自增列,主键';
comment on column synBaseDataHistory.condition is '表主键条件';
comment on column synBaseDataHistory.tableName is '表名';
comment on column synBaseDataHistory.operateType is '操作类型';
comment on column synBaseDataHistory.operateDate is '操作时间';
/*为表synBaseDataTemp的ID创建自增列*/
--创建自动增长序列
CREATE SEQUENCE synBaseDataTemp_Sequence
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;
--创建触发器
CREATE OR REPLACE TRIGGER tri_synBaseDataTemp BEFORE
insert ON synBaseDataTemp FOR EACH ROW
begin
select synBaseDataTemp_Sequence.nextval into:New.id from dual;
end;
为每张表创建触发器,共32张表,以一张表作为实例:
[sql] view plaincopy
</pre><p> </p><pre class="sql" name="code">/*为表GGSYSTEM创建触发器,当有相关操作时,在synBaseDataTemp中存入相关信息*/
CREATE OR REPLACE TRIGGER tri_GGSYSTEM
AFTER INSERT or DELETE or UPDATE --指定触发时机触发
ON GGSYSTEM
REFERENCING NEW as new_value
FOR EACH ROW --说明创建的是行级触发器
BEGIN
--将修改前数据插入到临时表synBaseDataTemp,供存储过程调用。
if inserting then
INSERT INTO synBaseDataTemp
(condition, tableName, operateType, operateDate)
VALUES
('SYSTEMCODE=' || chr(39) || :new_value.systemCode || chr(39),
'GGSYSTEM',
'INSERT',
sysdate);
elsif updating then
INSERT INTO synBaseDataTemp
(condition, tableName, operateType, operateDate)
VALUES
('SYSTEMCODE=' || chr(39) || :new_value.systemCode || chr(39),
'GGSYSTEM',
'UPDATE',
sysdate);
elsif deleting then
INSERT INTO synBaseDataTemp
(condition, tableName, operateType, operateDate)
VALUES
('SYSTEMCODE=' || chr(39) ||
ld.systemCode || chr(39),
'GGSYSTEM',
'DELETE',
sysdate);
end if;
END;
创建触发器后,下面是存储过程:
[sql] view plaincopy
--包头
create or replace package baseDataSyn is
-- Author : zhengfazhen
-- Created : 2011-12-31
procedure baseDataSynProc; --从CCS同步到ECCS
end baseDataSyn;
--包体
CREATE OR REPLACE PACKAGE BODY baseDataSyn is
-- Author : zhengfazhen
-- Created : 2011-12-31
--同步 存储过程
PROCEDURE baseDataSynProc IS
v_id number(15, 0); --临时表ID
condition varchar2(400); --临时表主键条件
tableName varchar2(50); --临时表中存入的表名
operateType varchar2(10); --操作类型:insert update delete
insertSql varchar2(400); --插入sql
updateSql varchar2(2000); --更新sql
deleteSql varchar2(500); --删除sql
synLogSql varchar2(500); --历史记录sql
columStr varchar2(1000); --非主键列名字符串
gguserColum varchar2(500); --GGUSER非主键且除去(password,PASSWORDSETDATE,PASSWORDEXPIREDATE,LOCKSTATUS,UPDATEPWDIND,LOGINERRTIMES)字段
gguserSql varchar2(500); --为gguser赋值默认值SQL
failSql varchar2(500); --异常处理SQL
flag varchar2(1);
--查询临时表中所有记录
cursor c_synTemp is
select c.id, c.condition, c.tablename, c.operatetype, c.operatedate
from synBaseDataTemp c
where 1 = 1
order by operateDate ASC;
c_synTemp_row c_synTemp%rowtype;
--返回非主键列名
cursor c_columName(tableName varchar2) is
select uc.COLUMN_NAME
from user_tab_columns uc
where uc.COLUMN_NAME not in
(select col.column_name
from user_constraints con, user_cons_columns col
where con.constraint_name = col.constraint_name
and con.constraint_type = 'P'
and col.table_name = tableName)
and uc.TABLE_NAME = tableName;
c_row c_columName%rowtype;
BEGIN
for c_synTemp_row in c_synTemp loop
begin
v_id := c_synTemp_row.id;
condition := c_synTemp_row.condition;
tableName := c_synTemp_row.tableName;
operateType := c_synTemp_row.operateType;
flag := '1';
/*拼接返回的非主键列名start*/
for c_row in c_columName(tableName) loop
if flag = '1' then
columStr := c_row.COLUMN_NAME;
flag := '0';
else
columStr := columStr || ',' || c_row.COLUMN_NAME;
end if;
end loop;
/*拼接返回的非主键列名end*/
/*同义词 eccs_表名*/
insertSql := 'insert into eccs_' || tableName ||
' (select * from ' || tableName || ' where ' ||
condition || ')';
updateSql := 'update eccs_' || tableName || ' set
(' || columStr || ')=(select ' || columStr ||
' from ' || tableName || ' where ' || condition ||
') where ' || condition;
deleteSql := 'delete from eccs_' || tableName || ' where ' ||
condition;
synLogSql := 'insert into synBaseDataHistory (select st.id,st.condition,st.tablename,st.operatetype,st.operatedate from synBaseDataTemp st where id=:1)';
failSql := 'update synBaseDataTemp set failFlag=' || chr(39) || '1' ||
chr(39) || 'where id=' || v_id;
/*GGUSER表特殊处理*/
gguserSql := 'update eccs_' || tableName ||
' set LOCKSTATUS=' || chr(39) || '1' || chr(39) ||
', UPDATEPWDIND=' || chr(39) || '1' || chr(39) ||
', LOGINERRTIMES=' || chr(39) || '0' || chr(39) ||
' where ' || condition;
gguserColum := 'USERCNAME,USERTNAME,USERENAME,SEAL,COMPANYCODE,ISSUECOMPANY,ACCOUNTCODE,PHONE,MOBILE,' ||
'ADDRESS,POSTCODE,EMAIL,USERIND,LOGINSYSTEM,CREATORCODE,CREATETIME,UPDATERCODE,' ||
'UPDATETIME,VALIDIND,REMARK,FLAG,SEX,ALIASCNAME,ALIASTNAME,ALIASENAME,UWINITIAL';
if tableName = 'GGUSER' then
updateSql := 'update eccs_' || tableName ||
' set
(' || gguserColum ||
')=(select ' || gguserColum || ' from ' || tableName ||
' where ' || condition || ') where ' || condition;
end if;
if operateType = 'INSERT' then
EXECUTE IMMEDIATE insertSql; --同步数据
if tableName = 'GGUSER' then
EXECUTE IMMEDIATE gguserSql; --赋默认值
end if;
EXECUTE IMMEDIATE synLogSql
USING v_id; --插入历史记录表
delete from synBaseDataTemp where id = v_id; --删除临时表中的信息
commit;
elsif operateType = 'UPDATE' then
EXECUTE IMMEDIATE updateSql;
EXECUTE IMMEDIATE synLogSql
USING v_id;
delete from synBaseDataTemp where id = v_id;
commit;
elsif operateType = 'DELETE' then
EXECUTE IMMEDIATE deleteSql;
EXECUTE IMMEDIATE synLogSql
USING v_id;
delete from synBaseDataTemp where id = v_id;
commit;
end if;
exception when others then
rollback;
EXECUTE IMMEDIATE failSql;--更新异常标志
commit;
end;
end loop;
END baseDataSynProc;
end baseDataSyn;
创建job,定时执行储存过程:
[sql] view plaincopy
BEGIN
DBMS_SCHEDULER.CREATE_JOB(job_name => 'jobsynproc', --job名称，自己设
job_type => 'STORED_PROCEDURE', --类型为存储过程
job_action => 'ccs_synproc', --存储过程名称为proc
start_date => to_date('30-12-2011 00:00:00',
'dd-mm-yyyy hh24:mi:ss'), --开始执行时间
enabled => TRUE, --自动启用
auto_drop => false,
repeat_interval => 'FREQ=Monthly;Interval=1');
END;
在这之前,必须授权用户有增删改的权限,并且创建同义词,以便储存过程能顺利执行:
1.依照DBA用户登录执行sql命令：grant create synonym to 被授权用户;
2.如果你需要对某表可以修改等权限：需执行：grant select,insert,update on monitor_sys_log to 被授权用户;
3.用"被授权用户"登录创建同义词：create synonym monitor_sys_log(同义词名称) for 拥有表用户.monitor_sys_log（表名）;
分享到：
相关推荐
包装材料管理软件：Oracle二次开发_Oracle触发器与存储过程.docx 包装材料管理软件：Oracle二次开发_PL-SQL程序设计.docx 包装材料管理软件：Oracle二次开发_包装材料管理软件概述.docx 包装材料管理软件：Oracle二...
在Oracle数据库中，触发器（Triggers）是一种存储过程，它们自动执行，当特定的数据库事件发生时，如INSERT、UPDATE或DELETE操作。调试Oracle触发器是开发和维护数据库应用程序时的重要步骤，可以帮助我们找出潜在的...
本节将深入探讨“Oracle触发器与存储过程高级编程”的相关知识。 **触发器（Triggers）** 触发器是一种数据库对象，它在特定的数据库事件（如INSERT、UPDATE或DELETE）发生时自动执行。通过定义触发器，我们可以...
本资源包含与"ORACLE中触发器和存储过程介绍"相关的代码示例，帮助用户深入理解和实践这两种技术。 首先，我们来详细探讨触发器。触发器是一种数据库对象，它会在特定的数据库事件（如INSERT、UPDATE、DELETE）发生...
Oracle触发器是数据库对象之一，它在特定的数据库操作（如INSERT、UPDATE或DELETE）发生时自动执行。触发器主要用于实现复杂的业务规则和数据验证，它们可以扩展SQL的功能，允许在数据修改前后执行一系列的动作。...
这份"oracle笔记(存储过程函数触发器游标流程控制等)"涵盖了Oracle数据库管理中的关键知识点，包括但不限于以下几个方面： 1. **存储过程与函数**：存储过程是预编译的SQL语句集合，用于执行特定任务。它们可以提高...
综上所述，Oracle触发器是一种强大的工具，能够帮助开发者实现复杂的数据管理和维护任务。通过深入理解触发器的类型、事件和执行逻辑，可以更好地利用触发器来增强数据库系统的功能性和安全性。
本文将深入探讨如何利用Oracle触发器来实现这一功能，同时也会讲解背后的原理与实践步骤。 ### Oracle触发器与序列 在Oracle中，触发器是一种存储过程，它被定义为当特定事件（如数据的插入、更新或删除）发生时...
触发器可以看作是存储过程的一种特殊形式，当满足预定义条件时，它们会自动触发并运行。这使得数据库能够响应用户未直接调用的事件，从而增强数据完整性和业务规则的实施。 在Oracle中，触发器有以下几种类型： 1....
在Oracle中，触发器、过程和游标是数据库编程的重要组成部分，它们各自扮演着不可或缺的角色。 **触发器（Triggers）** 触发器是一种数据库对象，它在特定的数据库事件（如INSERT、UPDATE或DELETE）发生时自动执行...
3. **Oracle触发器**：触发器是一种特殊的存储过程，会在特定的数据库事件（如INSERT、UPDATE或DELETE）发生时自动执行。`V_InsertEquip.sql`可能包含一个插入触发器的示例，这种触发器在向特定表中插入新数据时被...
Oracle触发器是数据库管理系统Oracle中的一种重要特性，它允许开发者在特定的数据操作（如INSERT、UPDATE、DELETE）发生时执行自定义的逻辑。本章详细介绍了触发器的基础知识及其在数据库管理和信息系统中的应用。 ...
在Oracle数据库系统中，存储过程、触发器和定时器是三个关键的数据库管理工具，它们在数据处理和业务逻辑执行中扮演着重要角色。本文将详细介绍这三个概念，并结合实际例子来帮助理解它们的工作原理和应用。 1. **...
Oracle数据库中的存储过程和触发器是数据库管理中的重要组成部分，它们极大地...通过这样的实践，我们可以更好地理解Oracle存储过程和触发器的基本操作，以及如何解决可能出现的问题，从而提高数据库管理和维护的能力。
此外，存储过程、触发器和函数的语法也有区别。 2. **数据类型转换**： 两个系统中的数据类型存在差异。例如，MySQL的`VARCHAR2`对应Oracle的`VARCHAR2`，但MySQL的`TINYINT`在Oracle中可能是`NUMBER(3)`，`BLOB`...
其中，存储过程、函数和触发器是Oracle数据库中的重要组件，它们极大地增强了数据库的灵活性和性能。接下来，我们将深入探讨这三个核心概念及其应用。 1. 存储过程： 存储过程是一组预先编译的SQL语句，存储在...
Oracle触发器（Trigger）是一种特殊的存储过程，它的执行不是由程序调用，也不需要手动启动，而是由特定的数据库事件触发，比如当对一个表进行INSERT、DELETE或UPDATE操作时，就会激活触发器执行。触发器是数据库...
三、Oracle触发器 触发器是数据库级别的事件驱动程序，当特定的DML（INSERT、UPDATE、DELETE）操作发生时自动执行。常见的触发器类型有： 1. `BEFORE INSERT`：在插入数据前触发。 2. `AFTER INSERT`：在插入数据...