储存过程
提取另一个库表数据到本地库表(表结构一样)
(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 132.122.132.10)(PORT = 52632)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ANIAM)))
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;回车 就是执行和一次存储过程