YY全世界

背景：项目管理系统(PMS)上线，客户为了方便使用，需要OA中登录名和密码在PMS,导出数据导入数据没有任何难度，难的是用户表的同步，两台数据库安装在不同的物理机上，这样也给我们同步造成了很大的麻烦，由于时间关系，将解决方案贴上来，大家如果有疑问或者好的建议，可以和我练习！
MSN:yaohao803@hotmail.com
mail:yaohao803@163.com

网络结构图如下：
插图



原理：
1.   在OA数据库中针对登录表建立新增，修改，删除的trigger，将异动后资料保存到OA中的临时表中。
2.   Pms数据库中建立db_link，通过db_link访问OA中的tempTable
3.   在pms数据库中建立procedure，用来访问OA数据库中的tempTable
4.   通过oracle中的job机制定时呼叫pms中的procedure
OA数据库中操作
请贵公司DBA在备份OA系统数据库后做如下操作
1.   如果为初次建立请使用procedure，初始化登录信息到临时表中，代码如下：
--oa初始化存储过程

create or replace procedure INIT_PMS_USER is

CURSOR cur IS select user_id,user_name,password,department_id

from oa.pub_users;

BEGIN

--执行循环操作

FOR cur_result in cur LOOP

begin

Insert into PMS_USER_LOG Values( HIBERNATE_SEQUENCE.Nextval, cur_result.user_id,cur_result.password,cur_result.user_name,cur_result.department_id,0,0,sysdate,'');

end;

END LOOP;

--循环结束

commit;

--提交

end INIT_PMS_USER;

/



请初始化执行该存储过程后，删除！





2.   建立临时表，以及查询视图代码如下：
create table PMS_USER_LOG

(

ID   NUMBER not null,

USERNO   VARCHAR2(255),

PASSWORD   VARCHAR2(255),

NAME   VARCHAR2(255),

DEPT_ID   VARCHAR2(255),

CTRL   NUMBER,

DISPOSAL   NUMBER,

DATETIME   DATE,

DISPOSALDATE DATE

)

--创建视图

CREATE OR REPLACE VIEW "OA"."OA_PMS_USER" ("ID","USERNO",

"PASSWORD","NAME","DEPT_ID","DEPT_NAME","CTRL","DISPOSAL") AS

select a.id,a.userno,a.password,a.name,c.organ_code dept_id,

c.organ_name dept_name,a.ctrl,a.disposal

from oa.pms_user_log a,oa.pub_stru b,oa.pub_organ c

where a.dept_id=b.stru_id and b.organ_id=c.organ_id and a.disposal=0 WITH READ ONLY;





3.   设定新增用户pms，并赋予查询视图("OA"."OA_PMS_USER")的权限，以及修改临时表(“OA”.”PMS_USER_LOG”)的权限，代码如下：
--设定权限(视图权限)

REVOKE SELECT

ON "OA"."OA_PMS_USER"

FROM "SRPM"

GRANT SELECT ON "OA"."OA_PMS_USER" TO "PMS" WITH GRANT OPTION

--表权限

REVOKE UPDATE

ON "OA"."PMS_USER_LOG"

FROM "PMS"

GRANT UPDATE ON "OA"."PMS_USER_LOG" TO "PMS" WITH GRANT OPTION





4.   建立所需队列，代码如下：
--建立SEQUENCE

CREATE SEQUENCE "OA"."HIBERNATE_SEQUENCE" INCREMENT BY 1

START WITH 11939 MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE

CACHE 20 NOORDER



5.   针对登录用户表建立新增，修改，删除的trigger，具体代码如下：
--建立触发器
--新增
Create or replace trigger pms_insert_user_log

after insert On pub_users

referencing

old as old_row

new as new_row

for each row

Begin

Insert into PMS_USER_LOG

Values( HIBERNATE_SEQUENCE.Nextval, :new_row.user_id,:new_row.password,:new_row.user_name,:new_row.department_id,0,0,sysdate,'');

End;

--修改

Create or replace trigger pms_update_user_log

after update On pub_users

referencing

old as old_row

new as new_row

for each row

Begin

Insert into PMS_USER_LOG

Values( HIBERNATE_SEQUENCE.Nextval, :new_row.user_id,:new_row.password,:new_row.user_name,:new_row.department_id,1,0,sysdate,'');

End;

--删除

Create or replace trigger pms_delete_user_log

after delete On pub_users

referencing

old as old_row

new as new_row

for each row

Begin

Insert into PMS_USER_LOG

Values( HIBERNATE_SEQUENCE.Nextval, :old_row.user_id,:old_row.password,:old_row.user_name,:old_row.department_id,2,0,sysdate,'');

End;





PMS数据库中操作
1.   由于不是一台物理机，所以与OA系统建立DB_LINK,具体代码如下(请确定建立db_link的用户名密码有权访问视图<"OA"."OA_PMS_USER">并且能够修改临时表<“OA”.”PMS_USER_LOG”>)：
--方式1

Create public database link DB_oa connect to “请询问OA系统的DBA(用户名)” identified by “请询问OA系统的DBA(密码)” using 'oa';

--方式2

create public database link db_oa connect to “请询问OA系统的DBA(用户名)” identified by “请询问OA系统的DBA(密码)” using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=oa)))';

2.   建立读取临时表，修改pms系统资料的存储过程：
create or replace procedure pms_get_user_log is

empid number;

userid number;

org_id number;

data_count number;

CURSOR cur IS select * from oa.OA_PMS_USER@DB_oa;

BEGIN

--执行循环操作

FOR cur_result in cur LOOP

begin

--查找是否在pms系统中存在部门信息

select count(*) into data_count from jdpj_waf_organization t where t.code=cur_result.dept_id;

if data_count>0 then

  select t.id into org_id from jdpj_waf_organization t where t.code=cur_result.dept_id;

  if cur_result.CTRL=0 then

  select HIBERNATE_SEQUENCE.nextval into empid from dual;

  --新增员工信息表

  insert into Jdpj_Waf_Employee(ID,NAME,Org_Id)values(empid,cur_result.NAME,org_id);

  --新增登录信息表

  insert into Jdpj_Waf_User(Id,Emp_Id,Name,Password,Register_Date)values(HIBERNATE_SEQUENCE.NEXTVAL,empid,cur_result.USERNO,cur_result.PASSWORD,sysdate);

  elsif cur_result.CTRL=1 then

  SELECT emp_id into empid FROM Jdpj_Waf_User where Name=cur_result.USERNO;

  update Jdpj_Waf_User set password=cur_result.PASSWORD where name=cur_result.USERNO;

  update Jdpj_Waf_Employee set name=cur_result.NAME,org_id=org_id where id=empid;

  else

  SELECT emp_id into empid FROM Jdpj_Waf_User where Name=cur_result.USERNO;

  delete from Jdpj_Waf_User where emp_id=empid;

  delete from jdpj_waf_employee where id=empid;

  end if;

  --修改状态为已经处理

  update oa.pms_user_log@DB_oa set DISPOSAL=1,DISPOSALDATE=sysdate where id=cur_result.id;

else

  select HIBERNATE_SEQUENCE.nextval into org_id from dual;

  --todo:请在上线时期将parent_id改成对应的未知部门id

  insert into jdpj_waf_organization(id,name,parent_id,type,sort_index,code,shortening)values(org_id,cur_result.DEPT_NAME,'8800','department',0,cur_result.DEPT_ID,cur_result.DEPT_NAME);

  if cur_result.CTRL=0 then

  select HIBERNATE_SEQUENCE.nextval into empid from dual;

  --新增员工信息表

  insert into Jdpj_Waf_Employee(ID,NAME,Org_Id)values(empid,cur_result.NAME,org_id);

  --新增登录信息表

  insert into Jdpj_Waf_User(Id,Emp_Id,Name,Password,Register_Date)values(HIBERNATE_SEQUENCE.NEXTVAL,empid,cur_result.USERNO,cur_result.PASSWORD,sysdate);

  elsif cur_result.CTRL=1 then

  SELECT emp_id into empid FROM Jdpj_Waf_User where Name=cur_result.USERNO;

  update Jdpj_Waf_User set password=cur_result.PASSWORD where name=cur_result.USERNO;

  update Jdpj_Waf_Employee set name=cur_result.NAME,org_id=org_id where id=empid;

  else

  SELECT emp_id into empid FROM Jdpj_Waf_User where Name=cur_result.USERNO;

  delete from Jdpj_Waf_User where emp_id=empid;

  delete from jdpj_waf_employee where id=empid;

  end if;

  --修改状态为已经处理

  update oa.pms_user_log@DB_oa set DISPOSAL=1,DISPOSALDATE=sysdate where id=cur_result.id;

end if;

exception  

  when   no_data_found   then  

  dbms_output.put_line('error');

end;

END LOOP;

--循环结束

commit;

--提交

end pms_get_user_log;

/

3.   打开oracle命令中心，建立job任务，代码如下：
--修改job队列为100
alter system set job_queue_processes=100;
--建立job任务，每小时执行一次
begin
dbms_job.submit(:job1,'pms_get_user_log;',to_date('14-09-2007 1:00:00', 'dd-mm-yyyy hh24:mi:ss'),'sysdate+1/24');
end;
/
使用SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM DBA_JOBS;查询job是否建立成功


4.   为实现PMS系统与OA系统用户登录表同步，需要在数据库中增加如下操作