C/C++教程

数据库-视图(从其他服务器获取视图或者表内容,copy到自己数据库作为新的视图)

本文主要是介绍数据库-视图(从其他服务器获取视图或者表内容,copy到自己数据库作为新的视图),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

当我们数据库非常多,分布在不同的服务器上,此时有一个系统需要使用其他系统的数据库时,这个时候,直接用数据库视图比建库建表建视图方便的多,给个示例,记录下:

SELECT t.resourceid,
t.resourcename,
t.resourcealias,
t.resourcetype,
t.resourceurl,
t.groupid,
t.requesttype,
t.resourcedes,
t.resourceproxyurl
FROM dblink('dbname=resourceregister_cp hostaddr=192.9.30.27 port=5432 user=postgres password=postgres'::text, 'select distinct serviceid as resourceid,servicename as resourcename,servicealias as resourcealias,servicetype as resourcetype,serverurl as resourceurl, rcr.resourcegroupid as groupid ,''get'' as requesttype,description as resourcedes, serverurl_out as resourceproxyurl
from dbms_data_service ds inner join dbms_rea_catalog_resource rcr on ds.serviceid = rcr.resourceid
inner join share_resource sr on sr.sourceid=ds.serviceid and sr.audit_status=''1''
union all
select distinct interfaceid as resourceid,interfacename as resourcename,interfacealias as resourcealias,interfacetype as resourcetype,interfaceurl as resourceurl, rcr.resourcegroupid as groupid ,''get'' as requesttype,interfacedescription as resourcedes,interfaceproxyurl as resourceproxyurl
from dbms_data_interface di inner join dbms_rea_catalog_resource rcr on di.interfaceid = rcr.resourceid
inner join share_resource sr on sr.sourceid=di.interfaceid and sr.audit_status=''1''
union all
select distinct productid as resourceid,productname as resourcename,productalias as resourcealias,producttype as resourcetype,producturl as resourceurl ,rcr.resourcegroupid as groupid ,''get'' as requesttype,productdescription as resourcedes,productproxyurl as resourceproxyurl
from dbms_data_product dp inner join dbms_rea_catalog_resource rcr on dp.productid = rcr.resourceid
inner join share_resource sr on sr.sourceid=dp.productid and sr.audit_status=''1''
union all
select distinct fileid as resourceid,name as resourcename, alias as resourcealias,resourcetype as resourcetype,serverpath as resourceurl,rcr.resourcegroupid as groupid ,''get'' as requesttype,'''' as resourcedes,serverproxypath as resourceproxyurl
from dbms_unstru_file uf inner join dbms_rea_catalog_resource rcr on uf.fileid = rcr.resourceid
inner join share_resource sr on sr.sourceid=uf.fileid and sr.audit_status=''1'''::text) t(resourceid character varying, resourcename character varying, resourcealias character varying, resourcetype character varying, resourceurl character varying, groupid character varying, requesttype character varying, resourcedes character varying, resourceproxyurl character varying)

简化下,格式如下:

SELECT t.id,t.name
FROM dblink('dbname=cp hostaddr=192.9.30.27 port=5432 user=postgres password=postgres'::text, 'select id,name from user'::text) t(id character varying, name character varying)

这篇关于数据库-视图(从其他服务器获取视图或者表内容,copy到自己数据库作为新的视图)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!