oracle存储过程和触发器结合database link的实例

  1. 业务背景

先上图

完成车辆档案信息的统一管理

  1. dblink的创建

在基础平台数据库上创建链接2.0系统的dblink

select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='SYSTEM';


如果上面的查不到数据,sys用户登陆赋权限
 

grant create public database   link to system;
 Create database link 


创建dblink

create public database link CAR_LOAN_LINK
  connect to "ztcbos" IDENTIFIED BY "ztcbos"
  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 172.16.9.201)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = innetdb)))';

    验证dblink是否创建正确

select * from T_B_CAR_INFO@CARINFO

 

3 ,存储过程

create or replace procedure SYN_HIS_CARINFO is

sqlstr varchar(5000);

  --1,清理IOV_CAR_INFO表的数据
 -- EXECUTE   IMMEDIATE   'truncate table IOV_CAR_INFO';
  --2,插入2.0(车队)系统的车辆档案数据
 begin
 sqlstr := 'insert into IOV_CAR_INFO
  (C_CAR_NO,
   C_VIN,
   C_VIN17,
   C_DATA_ID,
   C_SOURCE,
   C_SOURCE_TYP,
   C_PLATE_NO,
   C_REL_PLATE_NO,
   N_CAR_TYP,
   C_CAR_MODEL,
   C_USE_TYP,
   N_CAR_MODEL_SER,
   N_BAXLE_TYP,
   N_BAXLE_RATE,
   N_CAR_COLOR,
   N_OIL_TYP,
   N_OILFEEL_TYP,
   C_OIL_BULK,
   N_ENGI_FIRM,
   N_ENGI_TYP,
   C_ENGI_ORDER_NO,
   C_ENGI_NO,
   C_ENGI_EOL,
   N_ENGI_ECU,
   C_GEAR_FIRM,
   C_GEAR_TYP,
   N_MAX_HEIGHT,
   N_WEIGHT,
   N_MUST_QUALITY,
   N_RATELOAD,
   N_LOCK_TYP,
   N_IS_LOCK,
   N_K_VAL,
   C_TYRE_FIRM,
   N_TYRE_TYP,
   C_TYRE_RADIUS,
   C_DRIVE_TYP,
   C_STATUS,
   C_CRT_CDE,
   T_CRT_TM,
   C_UPD_CDE,
   T_UPD_TM)
  SELECT iov_car_info_seq.nextval,
         t1.car_vin,
         nvl(t1.vin_code17,t1.car_vin),
         t1.terminal_id,
         ''1'',
         ''1'',
         t1.car_no,
         t1.car_realno,
         NVL(t1.fuel_type,0),
         nvl(t1.car_model,'' ''),
         nvl(t1.car_type,'' ''),
         NVL(t1.series_id,0),
         NVL(t1.da_id,0),
         NVL(t1.sr_id,0),
         NVL(t1.car_color,0),
         NVL(t1.tank_type,0),
         NVL(t1.oil_type,0),
         null, --油箱体积
         NVL(t1.engine_manufacturer,0), --发动机厂商
         NVL(t1.engine_id,0), --发动机型号
         null, --发动机订货号
         t1.engine_no, --发动机编号
         null, --ECU码
         nvl(t1.ecu_type,0), --ECU类型
         null, --变速箱厂商
         t1.tr_id, --变速箱型号
         nvl(t1.car_height,0),
         nvl(t1.car_weight,0), --车辆自重KG
         nvl(t1.must_tran_quality,0), --准牵引质量KG
         NVL(t1.rate_load,0), --额定载重KGc_car_typ c_car_typ
         NVL(t1.lock_state_ex,0), --锁车类型
          nvl(t1.lock_car_state,1), --是否具备锁车
         NVL(t1.k_value,0),
         null, --轮胎厂商
         NVL(t1.tir_id,0), --轮胎型号
         0, --轮胎半径M
         t1.driver_id, --驱动形式
         ''1'',
         decode(t1.add_userid, null, ''admin'', t1.add_userid),
         t1.add_time,
         ''admin'',
         sysdate
    FROM T_B_CAR_INFO_back@FLEET_LINK t1 where t1.car_vin is not null';

    EXECUTE IMMEDIATE sqlstr;

    --同步2.0系统车辆档案信息
    execute immediate 
    'insert into iov_ter_info
(C_TER_ID,C_TER_NO,C_COMU_NO,C_SIM_ID,C_SIM_NO,C_SIM_SERNO,
c_supplier,c_ter_firm,c_ter_model,c_ter_typ,c_net_typ,c_firm_no,c_chan_typ,c_draw_no,t_out_factory_tm
,t_in_tm,c_banner,c_ter_name,c_posit_source,c_is_autoplay,c_status,C_CRT_CDE,T_CRT_TM,C_UPD_CDE,T_UPD_TM)
select 
iov_ter_info_seq.nextval,t1.TERMINAL_ID,nvl(t1.phone_num,'' ''),nvl(t1.sim_no,'' ''),nvl(t1.SIM_NO,'' ''),nvl(t1.sim_serial_num,'' ''),
t1.suppliers,t1.access_provider,'''',t1.terminal_type,t1.network_type,null,null,null,null,
t1.create_time,null,null,null,null,t1.state,
decode(t1.user_id, null, ''admin'', t1.user_id),t1.create_time,''admin'',sysdate
from t_b_terminal_info@fleet_link t1 ';

   --同步2.0系统车辆档案关系表信息
   execute immediate 
   'insert into iov_car_ter_rel
(pkid,
C_VIN17,
C_DATA_NO,
C_TER_NO,
C_SIM_NO,
C_COMU_NO,
C_STATUS,
C_CRT_CDE,
T_CRT_TM,
C_UPD_CDE,
T_UPD_TM)
select 
iov_car_ter_rel_seq.nextval,t1.CAR_VIN,'' '',nvl(t.TERMINAL_ID,'' ''),nvl(t.SIM_NO,'' ''),nvl(t.SIM_NO,'' ''),'' '',decode(t.user_id, null, ''admin'', t.user_id),t.create_time,''admin'',sysdate
from t_b_terminal_info@fleet_link t left join t_b_car_info_back@fleet_link t1 on t.TERMINAL_ID = t1.TERMINAL_ID where t1.car_vin is not null';
    
--同步车贷系统车辆档案信息
    EXECUTE IMMEDIATE
    'merge into IOV_CAR_INFO a
using (select * from T_B_CAR_INFO_tmp190311@car_loan_LINK) t1
on (a.c_vin = t1.car_vin)
when not matched then
insert
  (C_CAR_NO,
   C_VIN,
   C_VIN17,
   C_DATA_ID,
   C_SOURCE,
   C_SOURCE_TYP,
   C_PLATE_NO,
   N_CAR_TYP,
   C_CAR_MODEL,
   C_USE_TYP,
   N_CAR_MODEL_SER,
   N_BAXLE_TYP,
   N_BAXLE_RATE,
   N_CAR_COLOR,
   N_OIL_TYP,
   N_OILFEEL_TYP,
   C_OIL_BULK,
   N_ENGI_FIRM,
   N_ENGI_TYP,
   C_ENGI_ORDER_NO,
   C_ENGI_NO,
   C_ENGI_EOL,
   N_ENGI_ECU,
   C_GEAR_FIRM,
   C_GEAR_TYP,
   N_MAX_HEIGHT,
   N_WEIGHT,
   N_MUST_QUALITY,
   N_RATELOAD,
   N_LOCK_TYP,
   N_IS_LOCK,
   N_K_VAL,
   C_TYRE_FIRM,
   N_TYRE_TYP,
   C_TYRE_RADIUS,
   C_DRIVE_TYP,
   C_STATUS,
   C_CRT_CDE,
   T_CRT_TM,
   C_UPD_CDE,
   T_UPD_TM)
 values( iov_car_info_seq.nextval,
         t1.car_vin,
         nvl(t1.vin_code17,t1.car_vin),
         t1.terminal_id,
         ''1'',
         ''1'',
         t1.car_no,
         NVL(t1.fuel_type,0),
         nvl(t1.car_model,'' ''),
         nvl(t1.car_type,'' ''),
         NVL(t1.series_id,0),
         NVL(t1.da_id,0),
         NVL(t1.sr_id,0),
         NVL(t1.car_color,0),
         NVL(t1.tank_type,0),
         NVL(t1.oil_type,0),
         null, --油箱体积
         NVL(t1.engine_manufacturer,0), --发动机厂商
         NVL(t1.engine_id,0), --发动机型号
         null, --发动机订货号
         t1.engine_no, --发动机编号
         null, --ECU码
         nvl(t1.ecu_type,0), --ECU类型
         null, --变速箱厂商
         t1.tr_id, --变速箱型号
         nvl(t1.car_height,0),
         nvl(t1.car_weight,0), --车辆自重KG
         nvl(t1.must_tran_quality,0), --准牵引质量KG
         NVL(t1.rate_load,0), --额定载重KGc_car_typ c_car_typ
         NVL(t1.lock_state_ex,0), --锁车类型
          nvl(t1.lock_car_state,1), --是否具备锁车
         NVL(t1.k_value,0),
         null, --轮胎厂商
         NVL(t1.tir_id,0), --轮胎型号
         0, --轮胎半径M
         t1.driver_id, --驱动形式
         ''1'',
         decode(t1.add_userid, null, ''admin'', t1.add_userid),
         t1.add_time,
         ''admin'',
         sysdate) ';
--同步车贷系统车辆档案信息
 execute immediate 
    'merge into iov_ter_info a
 using (select * from t_b_terminal_info@car_loan_link) b 
 on (a.c_ter_id = b.terminal_id)
 when not matched then
 insert 
(C_TER_ID,C_TER_NO,C_COMU_NO,C_SIM_ID,C_SIM_NO,C_SIM_SERNO,
c_supplier,c_ter_firm,c_ter_model,c_ter_typ,c_net_typ,c_firm_no,c_chan_typ,c_draw_no,t_out_factory_tm
,t_in_tm,c_banner,c_ter_name,c_posit_source,c_is_autoplay,c_status,C_CRT_CDE,T_CRT_TM,C_UPD_CDE,T_UPD_TM)
values( 
iov_ter_info_seq.nextval,b.TERMINAL_ID,'' '',nvl(b.sim_no,'' ''),nvl(b.SIM_NO,'' ''),nvl(b.sim_serial_num,'' ''),
b.suppliers,b.access_provider,'''',b.terminal_type,b.network_type,null,null,null,null,
b.create_time,null,null,null,null,b.state,
decode(b.user_id, null, ''admin'', b.user_id),b.create_time,''admin'',sysdate)';
--同步车贷系统车辆档案关系表信息
 execute immediate 
   'merge into iov_car_ter_rel a 
 using (select t1.CAR_VIN,t.TERMINAL_ID ,t.SIM_NO,t.user_id ,t.create_time  
  from  t_b_terminal_info@fleet_link t left join T_B_CAR_INFO_tmp190311@car_loan_link t1 on t.TERMINAL_ID = t1.TERMINAL_ID ) b
 on (a.c_ter_no = b.terminal_id)
 when not matched then
   insert 
(pkid,
C_VIN17,
C_DATA_NO,
C_TER_NO,
C_SIM_NO,
C_COMU_NO,
C_STATUS,
C_CRT_CDE,
T_CRT_TM,
C_UPD_CDE,
T_UPD_TM)
values (
iov_car_ter_rel_seq.nextval,nvl(b.car_vin,'' ''),nvl(b.car_vin,'' ''),nvl(b.TERMINAL_ID,'' ''),nvl(b.SIM_NO,'' ''),nvl(b.SIM_NO,'' ''),'' '',decode(b.user_id, null, ''admin'', b.user_id),b.create_time,''admin'',sysdate
)';
     commit;
end SYN_HIS_CARINFO;

4,触发器

create or replace trigger syn_app_carinfo_trigger
  after update
  on iov_car_info
  for each row
declare
   sqlstr1 varchar(1000);
   sqlstr2 varchar(1000);
  -- local variables here
  --更新车贷和车队的车辆档案信息表
begin
  --车队

  sqlstr1:= 'update T_B_CAR_INFO_back@FLEET_LINK
      set terminal_id ='|| nvl(:new.c_data_id,'''''')
     || ' ,car_no ='|| nvl(:new.C_PLATE_NO,'''')
     || ' ,car_realno ='|| nvl(:new.C_REL_PLATE_NO,'''''')
     || ' ,fuel_type =' ||nvl(:new.N_CAR_TYP,0)
     || ' ,car_model =' ||nvl(:new.C_CAR_MODEL,0)
     || ' ,car_type =' ||nvl(:new.C_USE_TYP,'''''')
     || ' ,series_id =' ||nvl(:new.N_CAR_MODEL_SER,0)
     || ' ,da_id ='||nvl(:new.N_BAXLE_TYP,0)
     || ' ,sr_id ='||nvl(:new.N_BAXLE_RATE,0)
     || ' ,car_color ='|| nvl(:new.N_CAR_COLOR,0)
     || ' ,tank_type ='||nvl(:new.N_OIL_TYP,0)
     || ' ,oil_type ='|| nvl(:new.N_OILFEEL_TYP,0)
     || ' ,fuel_tankage ='||nvl(:new.C_OIL_BULK,'''X''')
     || ' ,engine_manufacturer =' ||nvl(:new.N_ENGI_FIRM,0)
     || ' ,engine_id =' ||nvl(:new.N_ENGI_TYP,0)
     || ' ,engine_no =' ||nvl(:new.C_ENGI_NO,'''''')
     || ' ,ecu_type =' ||nvl(:new.N_ENGI_ECU,0)
     || ' ,tr_id =' ||nvl(:new.C_GEAR_TYP,0)
     || ' ,car_height =' ||nvl(:new.N_MAX_HEIGHT,0)
     || ' ,car_weight =' ||nvl(:new.N_WEIGHT,0)
     || ' ,must_tran_quality ='|| nvl(:new.N_MUST_QUALITY,0)
     || ' ,rate_load =' || nvl(:new.N_RATELOAD,0)
     || ' ,lock_state_ex =' ||nvl(:new.N_LOCK_TYP,0)
     || ' ,lock_car_state =' ||nvl(:new.N_IS_LOCK,0)
     || ' ,k_value =' ||nvl(:new.N_K_VAL,0)
     || ' ,tir_id =' ||nvl(:new.N_TYRE_TYP,0)
     || ' ,driver_id =' ||nvl(:new.C_DRIVE_TYP,0)
     || ' where CAR_VIN = '''||:new.c_vin||'''';
       execute immediate sqlstr1;

     --车贷

  sqlstr2:= 'update T_B_CAR_INFO_tmp190311@car_loan_link
      set terminal_id ='|| nvl(:new.c_data_id,'''''')
     || ' ,car_no ='|| nvl(:new.C_PLATE_NO,'''')
     --|| ' ,car_realno ='|| nvl(:new.C_REL_PLATE_NO,'''''')
     || ' ,fuel_type =' ||nvl(:new.N_CAR_TYP,0)
     || ' ,car_model =' ||nvl(:new.C_CAR_MODEL,0)
     || ' ,car_type =' ||nvl(:new.C_USE_TYP,'''''')
     || ' ,series_id =' ||nvl(:new.N_CAR_MODEL_SER,0)
     || ' ,da_id ='||nvl(:new.N_BAXLE_TYP,0)
     || ' ,sr_id ='||nvl(:new.N_BAXLE_RATE,0)
     || ' ,car_color ='|| nvl(:new.N_CAR_COLOR,0)
     || ' ,tank_type ='||nvl(:new.N_OIL_TYP,0)
     || ' ,oil_type ='|| nvl(:new.N_OILFEEL_TYP,0)
     || ' ,fuel_tankage ='||nvl(:new.C_OIL_BULK,'''X''')
     || ' ,engine_manufacturer =' ||nvl(:new.N_ENGI_FIRM,0)
     || ' ,engine_id =' ||nvl(:new.N_ENGI_TYP,0)
     || ' ,engine_no =' ||nvl(:new.C_ENGI_NO,'''''')
     || ' ,ecu_type =' ||nvl(:new.N_ENGI_ECU,0)
     || ' ,tr_id =' ||nvl(:new.C_GEAR_TYP,0)
     || ' ,car_height =' ||nvl(:new.N_MAX_HEIGHT,0)
     || ' ,car_weight =' ||nvl(:new.N_WEIGHT,0)
     || ' ,must_tran_quality ='|| nvl(:new.N_MUST_QUALITY,0)
     || ' ,rate_load =' || nvl(:new.N_RATELOAD,0)
     || ' ,lock_state_ex =' ||nvl(:new.N_LOCK_TYP,0)
     || ' ,lock_car_state =' ||nvl(:new.N_IS_LOCK,0)
     || ' ,k_value =' ||nvl(:new.N_K_VAL,0)
     || ' ,tir_id =' ||nvl(:new.N_TYRE_TYP,0)
     || ' ,driver_id =' ||nvl(:new.C_DRIVE_TYP,0)
     || ' where CAR_VIN = '''||:new.c_vin||'''';
       execute immediate sqlstr2;

      --日志
       insert into iov_oper_log
       (pkid,c_oper_source,t_oper_tm,c_oper_type,c_oper_content,c_result,c_crt_cde,t_crt_tm,c_upd_cde,t_upd_tm)
      values
       (
      IOV_OPER_LOG_SEQ.Nextval,:new.c_upd_cde,:new.t_upd_tm,'update','更新车辆档案信息','','admin',sysdate,'admin',sysdate
       );

end syn_app_carinfo_trigger;

 

表结构

-- Create table
create table IOV_CAR_INFO
(
  c_car_no        VARCHAR2(32) not null,
  c_vin           VARCHAR2(32) not null,
  c_vin17         VARCHAR2(32) not null,
  c_data_id       VARCHAR2(32) not null,
  c_source        VARCHAR2(32) default 1 not null,
  c_source_typ    VARCHAR2(32) default 1 not null,
  c_plate_no      VARCHAR2(32) not null,
  c_rel_plate_no  VARCHAR2(32),
  n_car_typ       NUMBER default 0 not null,
  c_car_model     VARCHAR2(20) default 0,
  n_car_model_ser NUMBER default 0,
  n_baxle_typ     NUMBER,
  n_baxle_rate    NUMBER,
  c_use_typ       VARCHAR2(32) default 0 not null,
  n_car_color     NUMBER default 0 not null,
  n_oil_typ       NUMBER,
  n_oilfeel_typ   NUMBER,
  c_oil_bulk      VARCHAR2(32),
  n_engi_firm     NUMBER,
  n_engi_typ      NUMBER,
  c_engi_order_no VARCHAR2(32),
  c_engi_no       VARCHAR2(32),
  c_engi_eol      VARCHAR2(32),
  n_engi_ecu      NUMBER,
  c_gear_firm     VARCHAR2(32),
  c_gear_typ      VARCHAR2(32),
  n_max_height    NUMBER(32,10),
  n_weight        NUMBER(32,10),
  n_must_quality  NUMBER(32,10),
  n_rateload      NUMBER(32,10),
  n_lock_typ      NUMBER,
  n_is_lock       NUMBER,
  n_k_val         NUMBER,
  c_tyre_firm     VARCHAR2(32),
  n_tyre_typ      NUMBER,
  c_tyre_radius   VARCHAR2(32),
  c_status        CHAR(1) default 1,
  c_crt_cde       VARCHAR2(32) default 'admin',
  t_crt_tm        DATE default sysdate,
  c_upd_cde       VARCHAR2(32) default 'admin',
  t_upd_tm        DATE default sysdate,
  c_drive_typ     VARCHAR2(100)
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table IOV_CAR_INFO
  is '车辆基本信息表';
-- Add comments to the columns 
comment on column IOV_CAR_INFO.c_car_no
  is '自编号';
comment on column IOV_CAR_INFO.c_vin
  is 'VIN简码';
comment on column IOV_CAR_INFO.c_vin17
  is '17位VIN码';
comment on column IOV_CAR_INFO.c_data_id
  is '数据号';
comment on column IOV_CAR_INFO.c_source
  is '数据来源';
comment on column IOV_CAR_INFO.c_source_typ
  is '数据来源方式';
comment on column IOV_CAR_INFO.c_plate_no
  is '车牌号';
comment on column IOV_CAR_INFO.c_rel_plate_no
  is '真实车牌号';
comment on column IOV_CAR_INFO.n_car_typ
  is '车辆类型';
comment on column IOV_CAR_INFO.c_car_model
  is '车辆型号/车辆选项';
comment on column IOV_CAR_INFO.n_car_model_ser
  is '车型系列';
comment on column IOV_CAR_INFO.n_baxle_typ
  is '后桥类型';
comment on column IOV_CAR_INFO.n_baxle_rate
  is '后桥速比';
comment on column IOV_CAR_INFO.c_use_typ
  is '载货车/牵引车/自卸车/特种车/专用车';
comment on column IOV_CAR_INFO.n_car_color
  is '车辆颜色';
comment on column IOV_CAR_INFO.n_oil_typ
  is '油箱类型';
comment on column IOV_CAR_INFO.n_oilfeel_typ
  is '油感类型';
comment on column IOV_CAR_INFO.c_oil_bulk
  is '油箱体积';
comment on column IOV_CAR_INFO.n_engi_firm
  is '发动机厂商';
comment on column IOV_CAR_INFO.n_engi_typ
  is '发动机型号';
comment on column IOV_CAR_INFO.c_engi_order_no
  is '发动机订货号';
comment on column IOV_CAR_INFO.c_engi_no
  is '发动机编号';
comment on column IOV_CAR_INFO.c_engi_eol
  is '发动机EOL码';
comment on column IOV_CAR_INFO.n_engi_ecu
  is 'ECU类型';
comment on column IOV_CAR_INFO.c_gear_firm
  is '变速箱厂商';
comment on column IOV_CAR_INFO.c_gear_typ
  is '变速箱型号';
comment on column IOV_CAR_INFO.n_max_height
  is '车辆最大高度';
comment on column IOV_CAR_INFO.n_weight
  is '车辆自重KG';
comment on column IOV_CAR_INFO.n_must_quality
  is '准牵引质量KG';
comment on column IOV_CAR_INFO.n_rateload
  is '额定载重KG';
comment on column IOV_CAR_INFO.n_lock_typ
  is '锁车类型';
comment on column IOV_CAR_INFO.n_is_lock
  is '是否具备锁车';
comment on column IOV_CAR_INFO.n_k_val
  is 'K值';
comment on column IOV_CAR_INFO.c_tyre_firm
  is '轮胎厂商';
comment on column IOV_CAR_INFO.n_tyre_typ
  is '轮胎型号';
comment on column IOV_CAR_INFO.c_tyre_radius
  is '轮胎半径M';
comment on column IOV_CAR_INFO.c_status
  is '状态';
comment on column IOV_CAR_INFO.c_crt_cde
  is '创建人';
comment on column IOV_CAR_INFO.t_crt_tm
  is '创建时间';
comment on column IOV_CAR_INFO.c_upd_cde
  is '更新人';
comment on column IOV_CAR_INFO.t_upd_tm
  is '更新时间';
comment on column IOV_CAR_INFO.c_drive_typ
  is '驱动形式';
-- Create/Recreate primary, unique and foreign key constraints 
alter table IOV_CAR_INFO
  add primary key (C_CAR_NO)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table IOV_CAR_INFO
  add constraint INX_DATA_ID unique (C_DATA_ID)
  using index 
  tablespace TBS_GTOS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table IOV_CAR_INFO
  add constraint INX_VIN unique (C_VIN)
  using index 
  tablespace TBS_GTOS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table IOV_CAR_INFO
  add constraint INX_VIN17 unique (C_VIN17)
  using index 
  tablespace TBS_GTOS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create table
create table IOV_CAR_TER_REL
(
  pkid      VARCHAR2(32) not null,
  c_vin17   VARCHAR2(32) not null,
  c_data_no VARCHAR2(32) not null,
  c_ter_no  VARCHAR2(32) not null,
  c_sim_no  VARCHAR2(32) not null,
  c_comu_no VARCHAR2(32) not null,
  c_status  VARCHAR2(32) not null,
  c_crt_cde VARCHAR2(32) not null,
  t_crt_tm  DATE not null,
  c_upd_cde VARCHAR2(32) not null,
  t_upd_tm  DATE not null
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table IOV_CAR_TER_REL
  is '车辆终端关联关系表';
-- Add comments to the columns 
comment on column IOV_CAR_TER_REL.pkid
  is 'PKID';
comment on column IOV_CAR_TER_REL.c_vin17
  is '车主键,车辆唯一标识';
comment on column IOV_CAR_TER_REL.c_data_no
  is '数据号';
comment on column IOV_CAR_TER_REL.c_ter_no
  is '终端号';
comment on column IOV_CAR_TER_REL.c_sim_no
  is 'SIM卡号';
comment on column IOV_CAR_TER_REL.c_comu_no
  is '通讯号';
comment on column IOV_CAR_TER_REL.c_status
  is '状态';
comment on column IOV_CAR_TER_REL.c_crt_cde
  is '创建人';
comment on column IOV_CAR_TER_REL.t_crt_tm
  is '创建时间';
comment on column IOV_CAR_TER_REL.c_upd_cde
  is '更新人';
comment on column IOV_CAR_TER_REL.t_upd_tm
  is '更新时间';
-- Create/Recreate primary, unique and foreign key constraints 
alter table IOV_CAR_TER_REL
  add primary key (PKID)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create table
create table IOV_TER_INFO
(
  c_ter_id         VARCHAR2(32) not null,
  c_ter_no         VARCHAR2(32) not null,
  c_comu_no        VARCHAR2(32) not null,
  c_sim_id         VARCHAR2(32) not null,
  c_sim_no         VARCHAR2(32) not null,
  c_sim_serno      VARCHAR2(32) not null,
  c_supplier       VARCHAR2(32),
  c_ter_firm       VARCHAR2(32),
  c_ter_model      VARCHAR2(32),
  c_ter_typ        VARCHAR2(32),
  c_net_typ        VARCHAR2(32),
  c_firm_no        VARCHAR2(32),
  c_chan_typ       VARCHAR2(32),
  c_draw_no        VARCHAR2(32),
  t_out_factory_tm DATE,
  t_in_tm          DATE not null,
  c_banner         VARCHAR2(32),
  c_ter_name       VARCHAR2(32),
  c_posit_source   VARCHAR2(32),
  c_is_autoplay    VARCHAR2(32),
  c_status         CHAR(1),
  c_crt_cde        VARCHAR2(32) not null,
  t_crt_tm         DATE not null,
  c_upd_cde        VARCHAR2(32) not null,
  t_upd_tm         DATE not null
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table IOV_TER_INFO
  is '终端表';
-- Add comments to the columns 
comment on column IOV_TER_INFO.c_ter_id
  is '终端ID';
comment on column IOV_TER_INFO.c_ter_no
  is '终端号';
comment on column IOV_TER_INFO.c_comu_no
  is '通讯号';
comment on column IOV_TER_INFO.c_sim_id
  is 'SIM卡号';
comment on column IOV_TER_INFO.c_sim_no
  is 'SIM卡编号';
comment on column IOV_TER_INFO.c_sim_serno
  is 'SIM卡串号';
comment on column IOV_TER_INFO.c_supplier
  is '终端供应商';
comment on column IOV_TER_INFO.c_ter_firm
  is '终端生产企业';
comment on column IOV_TER_INFO.c_ter_model
  is '终端型号';
comment on column IOV_TER_INFO.c_ter_typ
  is '终端类型';
comment on column IOV_TER_INFO.c_net_typ
  is '网络类型';
comment on column IOV_TER_INFO.c_firm_no
  is '终端企业编号';
comment on column IOV_TER_INFO.c_chan_typ
  is '监控信道种类';
comment on column IOV_TER_INFO.c_draw_no
  is '图号';
comment on column IOV_TER_INFO.t_out_factory_tm
  is '出厂日期';
comment on column IOV_TER_INFO.t_in_tm
  is '入库日期';
comment on column IOV_TER_INFO.c_banner
  is '条形码';
comment on column IOV_TER_INFO.c_ter_name
  is '终端名称';
comment on column IOV_TER_INFO.c_posit_source
  is '定位来源';
comment on column IOV_TER_INFO.c_is_autoplay
  is '是否自动播放';
comment on column IOV_TER_INFO.c_status
  is '状态';
comment on column IOV_TER_INFO.c_crt_cde
  is '创建人';
comment on column IOV_TER_INFO.t_crt_tm
  is '创建时间';
comment on column IOV_TER_INFO.c_upd_cde
  is '更新人';
comment on column IOV_TER_INFO.t_upd_tm
  is '更新时间';
-- Create/Recreate primary, unique and foreign key constraints 
alter table IOV_TER_INFO
  add primary key (C_TER_ID)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

database link

 

-- Create database link 
create public database link FLEET_LINK
  connect to ZTCBOS
  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 172.16.9.201)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = innetdb)))';
-- Create database link 
create public database link CAR_LOAN_LINK
  connect to TXJBOS
  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 172.16.9.206)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = innetdb)))';

 

 

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值