存储过程-跨数据库查询数据-插入表

create or replace procedure insert_test_tab
as
type balance is ref cursor;
balanceDo balance;
MATERIALNAME varchar2(100);
begin
  open balanceDo for select i.code from wms_item@fdj_wms136 i;
  loop
    fetch balanceDo into MATERIALNAME;
    exit when balanceDo%notfound;
    insert into test_tab(ID,JIT_CODE) values (seq_enumerate.nextval,MATERIALNAME);
    end loop;
    exception
      when others
        then rollback;
    close balanceDo;
    commit;
    end;
------执行存储过程
SQL> exec insert_test_tab;
------创建dblink
SQL> create public database link fdj_wms136 connect to fdjwms  identified by fdjwms using '192.168.33.136/jqwlscl';
------测试表
CREATE TABLE test_tab (
  ID NUMBER(19) NOT NULL,
  JIT_CODE varchar2(255 CHAR) DEFAULT NULL,
  SEND_TIME TIMESTAMP(6) DEFAULT systimestamp
)
tablespace JACDATAMONITORING
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table test_tab
  add primary key (ID)
  using index 
  tablespace JACDATAMONITORING
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );