/*示例中的数据表为远程数据库10.0.117.126 所有,目标是将其复制到本地数据库中*/
--创建存储过程,用于判断该表是否存在,存在则删除(oracle没有 if exists 函数)
create or replace procedure DROP_IF_EXISTS
(tab_name in varchar2) is
v_cnt number;
begin
select count(*)
into v_cnt
from user_tables
where table_name = upper(tab_name);
if v_cnt>0 then
execute immediate 'drop table ' || tab_name ||' purge';
end if;
end DROP_IF_EXISTS;
--先创建软连接,连接到远程数据库
create public database link Link_XHSD connect to xhsd_jcd identified by jcd using ' (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.117.126)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = JCD)
)
)';
-- 复制整个表(表结构、表数据)
call DROP_IF_EXISTS(JCDMS_WLMXZB) ;
create table JCDMS_WLMXZB as SELECT * FROM JCDMS_WLMXZB@LINK_XHSD ;
SELECT * FROM JCDMS_WLMXZB ;
--复制表头
call DROP_IF_EXISTS(CG_JCFLB) ;
create table CG_JCFLB as SELECT * FROM CG_JCFLB@LINK_XHSD WHERE 1 <> 1 ;
SELECT * FROM CG_JCFLB ;
--复制表数据
call DROP_IF_EXISTS(CG_JCFLB) ;
SELECT * FROM CG_JCFLB ;
--前十行
insert into CG_JCFLB SELECT * FROM CG_JCFLB@LINK_XHSD WHERE rownum < 10 ;
--全部
insert into CG_JCFLB SELECT * FROM CG_JCFLB@LINK_XHSD ;
SELECT * FROM CG_JCFLB ;