oracle创建dblink语句_多个Oracle数据库联合做统计分析

近期完成了一个精益管理项目（提升员工渗透率），主要用于统计各个单位在统计时间段内的业务渗透情况。从下面的日程计划表可以看出是一个持续改进的项目，其中核心支持数据就是本项目所要描述的多个Oracle数据库联合做统计分析，业务语言描述如下，代码也是几经修改，最终用于支撑完成项目。

统计期内有效承保的员工数占该单位员工总数的占比。有效承保是指统计期内有效保单保费累计大于100元的员工数；其中会员含会员及员工亲属，员工数以统计当期公司数据库更新的人数为准。会员是指员工及亲属。

业务语言描述起来很容易理解，但是由于我们所需的数据分布在不同的表中，而这些表又分布在不同的库中，所以其中涉及到连库查询，多表联合查询，创建临时表存放统计分析数据等一些列操作。下面拆开语句来挨个做解析：

一、创建DBLINK

/*跨库查询
create public database link dblink
       connect to totalplant identified by totalplant 
       using '(DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1521))
                )
                (CONNECT_DATA =
                  (SERVICE_NAME = prd.gdc)
                )
              )';
--语法解释：
create public database link DBLINK名字(自己随便起)
                  connect to 用户名 identified by 密码
                  using '(DESCRIPTION =
                            (ADDRESS_LIST =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = 要连接的数据库所在服务器的IP地址)(PORT = 1521))
                            )
                            (CONNECT_DATA =
                              (SERVICE_NAME = 要连接的数据库的在本地的服务名(即要连接的数据库的SID))
                            )
                          )';
*/
--创建DBLINK
create database link webinsured_link
connect to ****  identified by  ******
using '(DESCRIPTION =
        (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = **.***.*.*)(PORT = 0000)))
        (CONNECT_DATA =
        (SERVICE_NAME = *****) ))';

二、创建员工表，实时更新数据

--创建员工表 实时更新数据

CREATE TABLE SU_NWYG_LIST(
        ID NUMBER(10),
        NAME VARCHAR2(250),
        IDENTIFYNUMBER VARCHAR2(350),
        COM1 VARCHAR2(250),
        COM2 VARCHAR2(250),
        COM3 VARCHAR2(250),
        CHENGBAOINGFLAG NUMBER(6),
        CHENGBAOOLDFLAG NUMBER(6),
        CUSTOMER_POSITION VARCHAR2(250),
        CUSTOMER_TYPE NUMBER(10));
--插入数据   isvalid 1代表是否当前有效，0为已删除； falg i代表insert,u代表update，d代表delete；holdertype 1代表员工，2代表三产，3代表**员工。
insert into SU_NWYG_LIST(ID,NAME,IDENTIFYNUMBER,COM1,COM2,COM3)
select USERCODE,USERNAME ,IDENTIFYNO,CNAMESECOND,CNAMETHIRD,CNAMEFOURTH From nw_holder_info where isvalid = 1 and  holdertype = 1;

三、创建统计表，输出统计分析数据

--创建统计表
create table su_nwyg_sentou as
select IDENTIFYNUMBER,max(SUMPREMIUM) SUMPREMIUM from(
select * from(
select b.IDENTIFYNUMBER IDENTIFYNUMBER, sum(a.SUMPREMIUM) SUMPREMIUM From webpolicy@webinsured_link a
right join webinsured@webinsured_link b on a.policyno = b.policyno
where --a.STARTDATE > trunc(sysdate)
a.STARTDATE >= to_date('20190121','yyyymmdd') and a.STARTDATE <= to_date('20190127','yyyymmdd')
and a.riskcode  in ('1101','1205','1206','2401','6103','6107','6111','6113','2347','2125','2405','6118','6114','6115','6121','6210')
and b.IDENTIFYNUMBER in (select IDENTIFYNUMBER from su_nwyg_list)
and(a.policyno like '8%' 
or a.policyno in (
select policyno from SACUSTOMERVISITDATA@webinsured_link where STARTDATE >= to_date('20190121','yyyymmdd')  and STARTDATE <= to_date('20190127','yyyymmdd') and policyno like'2%' and BUSINESSPLATE ='2')
or a.policyno in (
select policyno from SACUSTOMERVISITDATA@webinsured_link where STARTDATE >= to_date('20190121','yyyymmdd')  and STARTDATE <= to_date('20190127','yyyymmdd') and policyno like'2%' and businessclassification='03' and businessclass='03'))
group by b.IDENTIFYNUMBER having sum(a.SUMPREMIUM) >=100
)
union all 
select * from(
select identifynumbercus IDENTIFYNUMBER,BISUMAMOUNT SUMPREMIUM from SaCustomerVisitData@webinsured_link 
where identifynumbercus is not null
and BUSINESSCLASS in ('04', '31')
and STARTDATE >= to_date('20190121','yyyymmdd') and STARTDATE <= to_date('20190127','yyyymmdd')
and identifynumbercus in (select IDENTIFYNUMBER from su_nwyg_list)
))
group by IDENTIFYNUMBER;
create index su_nwyg_sentou_IDENTIFYNUMBER  on su_nwyg_sentou (IDENTIFYNUMBER);
--更新员工总表中的 渗透标识
--   先将渗透标识置为0 未渗透
update su_nwyg_list set CHENGBAOINGFLAG = 0 ;
--   根据渗透清单，更新标识置为1 渗透
update su_nwyg_list set CHENGBAOINGFLAG = 1  where 
IDENTIFYNUMBER in (select IDENTIFYNUMBER from su_nwyg_sentou);
--删除历史一级单位渗透数
delete from su_nwyg_sentou_tongjicom1;
--插入全新一级单位渗透数
insert into su_nwyg_sentou_tongjicom1
select com1 ,count(*) n From su_nwyg_list where CHENGBAOINGFLAG=1 group by com1 order by 1 ;
--删除历史三级单位渗透数
delete from su_nwyg_sentou_tongjicom3;
--插入全新三级单位渗透数
insert into su_nwyg_sentou_tongjicom3
select com1 ,com2,com3 ,count(*) n From su_nwyg_list where CHENGBAOINGFLAG=1 group by com1 ,com2,com3 order by 1,2 ;
drop table su_nwyg_sentou ;
--输入一级、三级渗透数
select * From su_nwyg_sentou_tongjicom3  order by 1,2 ;
--删除员工数据表
drop table su_nwyg_list;

以上，输出结果：