在Pl/SQL里想从一个数据库里连接到另一个数据库,要靠Database link.例子如下:
DROP DATABASE LINK elinkDB_copy;
CREATE DATABASE LINK elinkDB_copy
CONNECT TO nims IDENTIFIED BY nims
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev.elinksoft.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = elinkdb)
)
)';
INSERT INTO T_DEPARTMENT_DEFINE
(DEPARTMENT_CODE, INTERNAL_DEPARTMENT_CODE,
DEPARTMENT_CHINESE_NAME, DEPARTMENT_ABBR_NAME, DEPARTMENT_LEVEL,
FOUND_DATE, CHINESE_ADDRESS, POSTCODE, TELEPHONE,
UPPER_DEPARTMENT_CODE, LINK_MAN_CODE, TELE_AREA_CODE,
LEVEL_DEP_CODE, FCD, FCU, LCD, LCU)
(SELECT DEPARTMENT_CODE, INTERNAL_DEPARTMENT_CODE, DEPARTMENT_CHINESE_NAME,
DEPARTMENT_ABBR_NAME, DEPARTMENT_LEVEL, FOUND_DATE,
CHINESE_ADDRESS, POSTCODE, TELEPHONE, UPPER_DEPARTMENT_CODE,
LINK_MAN_CODE, TELE_AREA_CODE, LEVEL_DEP_CODE, FCD, FCU, LCD, LCU
FROM T_DEPARTMENT_DEFINE@elinkDB_copy);
commit;
ALTER SESSION CLOSE DATABASE LINK elinkDB_copy;
另外,如果是同一个数据下的不同用户,可以用视图.