储存过程

提取另一个库表数据到本地库表（表结构一样）

(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 132.122.132.10)(PORT = 52632)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ANIAM)))

 create or replace procedure pro_t_alarm_obj_arch_yf is

sql2 varchar2(2000);
begin

  sql2:='
insert into   t_alarm_obj_arch_yf
select * from iam.t_alarm_obj_arch_yf@yunfuerqi where to_number(to_char(sysdate-8,''yyyymmdd''))<= to_number(to_char(NE_ALARM_TIME,''yyyymmdd''))  
 and to_number(to_char(NE_ALARM_TIME,''yyyymmdd'')) <=to_number(to_char(sysdate-2,''yyyymmdd''))';

execute immediate sql2;
end pro_t_alarm_obj_arch_yf;
后面那个是存储过程

 

 

++++++++++++++++++++++++++++++++++++

 

自己的：

select * from demo.APPR_AUTH_FUNC t;--0
select * from gd_base.APPR_AUTH_FUNC t;--476
select * from gd_base.APPR_AUTH_FUNC@testlink;--479

database:

-- Drop existing database link
drop database link TESTLINK;
-- Create database link
create database link TESTLINK
connect to GD_BASE
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.153)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = oanet)))';

+++++++++++++++++++++++++++

声明本地表结构：

-- Create table
create table APPR_AUTH_FUNC
(
seq NUMBER(10) not null,
user_code VARCHAR2(255) not null,
node_seq NUMBER(10) not null,
node_type NUMBER(1) not null,
user_type NUMBER(1) not null,
is_disabled NUMBER(1),
is_selected NUMBER(1)
)
tablespace WORKFLOW01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column APPR_AUTH_FUNC.node_type
is '1菜单, 2 功能';
comment on column APPR_AUTH_FUNC.user_type
is '1用户,2工作组,3单位';
comment on column APPR_AUTH_FUNC.is_disabled
is '禁用的权限高于使用权限';
comment on column APPR_AUTH_FUNC.is_selected
is '1选中，0半选 (主要用于树展示时使用)';
-- Create/Recreate primary, unique and foreign key constraints
alter table APPR_AUTH_FUNC
add constraint PK_APPR_AUTH_FUNC primary key (SEQ)
using index
tablespace WORKFLOW01
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

+++++++++++++++++++++++++++++

定义存储过程：

create or replace procedure pro_get153_APPR_AUTH_FUNC is
sql2 varchar2(2000);
begin
sql2:='
insert into demo.APPR_AUTH_FUNC
select * from gd_base.APPR_AUTH_FUNC@testlink';

execute immediate sql2;
end pro_get153_APPR_AUTH_FUNC;

在pl命令行：

exec pro_get153_APPR_AUTH_FUNC;回车  就是执行和一次存储过程