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

最新推荐文章于 2023-01-11 21:59:28 发布
最新推荐文章于 2023-01-11 21:59:28 发布
阅读量535 收藏 1
点赞数 1
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wolongsuxing/article/details/88525026
版权
  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)))';

 

 

 

 

 

wolongsuxing
关注
oracle存储过程和触发器结合项目详细讲解
michaeltang123的专栏
11-19 1827
项目名：“学生公寓型床位出租管理系统” 业务逻辑： 情形1：床位出租，就是按床位出租而不是按一个房间来算的，一个房间有固定的床位，有4个6个8个不等的床位，一般这种床位房都是一月一交，也就可以算成30天就得交一次房租。当有租客来入住，我首先要查询有空床位的房间，有空床位就办理入住，就填入租客入住信息。另外租客入住进去后，这个房间的床位就要减少一个，所以需要建立两个表房间表(room)和租客信息
Oracle数据库中触发器包含dblink
萌萌的小窝
11-14 4172
Oracle数据库中触发器包含dblink 1、 基本概念 a) 触发器trigger：触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。 b) Dblink： 2、 测试准备 a) 准备数据库test1，test2 b) 两边都创建scott用户，都创建表tes
oracle 存储过程 databaselink 收集
06-30
oracle调用存储过程方法，包括带返回参数和列表参数； oracle创建databaselink的方法和语法，以及对应举例说明。
oracle dblink+trigger触发器实现两个库数据同步
最新发布
Yanshuaijie的博客
01-11 1131
oracle触发器trigger与dblink实现数据迁移
ORCAL数据库DB_LINK和触发器简单使用
花语者
04-27 463
1、DBLINK的创建：   createdatabase link sgccmtDBLink  connect to sgccmt IDENTIFIED BY  sgccmt123  using '(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.141.7.16)(PORT = 11521)))       ...
oracle存储过程报错 跳过,oracle调试存储过程的过程详解
weixin_36165049的博客
04-02 2111
oracle调试存储过程的过程详解oracle如果存储过程比较复杂，我们要定位到错误就比较困难，那么可以存储过程的调试功能先按简单的存储过程做个例子，就是上次做的存储过程(proc_test)1、先在数据库的procedures文件找到我们之前创建存储过程2、选中存储过程，右键弹出菜单，选择 测试3、出现测试脚本页面，输入动态参数值，点击左上角的标志，进入调式模式4、出现调试操作按钮界面(依次：运...
存储过程，触发器，远程数据库
09-25
根据提供的文件信息，我们可以...通过以上介绍可以看出，存储过程、触发器和远程数据库连接都是Oracle数据库中非常重要的特性，它们可以帮助开发者更高效地管理数据、实现复杂的数据处理逻辑以及跨数据库访问等功能。
oracle的触发器结合dblink做跨库操作
renjingkai的博客
10-31 2264
1.首先建立dblink： -- 创建dblink（testlink为dblink的名称，xt是用户名，第二个xt是密码，using后是ip+端口+实例） create public database link testlink connect to xt identified by "xt" USING '127.0.0.1:1521/orcl”' 2.建立触发器： -- 创建user...
Oracle中建立多方案代替多实例的探讨.pdf
10-10
文档中提到的某些特定的Oracle对象，例如触发器、存储过程以及数据库链接（Database Link），都是实现多方案数据库设计中不可或缺的组成部分。触发器和存储过程可以用来自动化业务逻辑，保证数据的一致性和完整性，...
利用Oracle Database Link解决跨平台实时数据同步
Oracle Database Link是Oracle数据库中的一个重要功能，用于在两个不同的数据库实例之间建立连接，使得这两个数据库能够相互访问和交换数据。通过Database Link，可以在当前数据库中访问远程数据库中的表，就好像...
Oracle DBLink+Trigger
RichardGeek的博客
12-20 302
1、建立DBLink A库想查B库的东西。可以在A库上建一个DBLINK，单向，不需要在B上建，除非B也想查A的东西. CREATE [SHARED] [PUBLIC] database link link_name 　　[CONNECT TO [user] [current_user] IDENTIFIED BY password]　 　　[AUTHENTICATED BY user IDE...
oracle触发器 判断插入的记录是否满足条件，如果满足，则在b表中插入相应记录
j277699931的博客
12-17 2798
需求：表A，有字段level等,当a中记录有update操作时，如果其字段level>3，则把这条记录插入到B表中，  create trigger triggerTest   on A   for update   as   begin     insert b     select xxx,xxx from inserted a   //查询在a中插入的记录的level字
Oracle使用dblink同步数据
kangaroo的博客
06-01 3116
两台Oracle之间进行数据同步，可以借助Oracle的dblink完成，如果是Oracle与其他数据库之间，则可以使用Oracle提供的Database Gateways+dblink实现，当然，也可以使用开源ETL工具如kettle完成。这里提供两种在Oracle之间同步数据的简单解决方案。...
根据业务写触发器（oracle触发器片）
cj990133的博客
05-23 110
最近公司需要根据业务写一个触发器，要求当某个用户添加删除更新t_prouduct_order_col 表的字段时，知道是那个数据库用户，哪个ip，什么客户端，以及更新字段涉及到的客户用户、字段说明等内容。研究的两天终于写...
dblink+trigger实现两个Oracle数据库中两个表的数据同步
Python小二
12-07 1949
dblink+trigger实现两个Oracle数据库中两个表的数据同步
Oracle创建Database link方法
月下独影灬的博客
09-21 3292
Oracle创建Database link方法
Oracle建立database link（dbLink）示例
xiaokewei5的博客
04-16 1165
Oracle建立database link（dbLink）示例查询DataBase Link使用PL/SQL建立dbLink使用navicat建立dbLink再次执行查询dbLink查询新建的dbLink删除dbLink可能遇到的问题 查询DataBase Link select owner,object_name from dba_objects where object_type='DATABASE LINK'; 使用PL/SQL建立dbLink 登录时使用sysdba登录 使用navicat建立d
oracle使用存储过程进行跨数据库访问和操作并设置定时器
luohuaer
01-06 2030
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录前言使用步骤1.建立database link连接2.编写存储过程（定时任务体）3.创建定时器总结关于定时器详解借鉴 前言 工作中需要使用oracle进行跨数据库的访问和表插入，因此记录一下具体的操作步骤 使用步骤 1.建立database link连接 create database link 连接名（步骤二用） connect to 要访问的数据库用户名 identified by 用户密码 usi
Oracle存储过程与触发器详解
"Oracle存储过程和触发器" Oracle存储过程和触发器是数据库管理系统中用于实现复杂业务逻辑的重要工具。存储过程是一组预先编译的SQL语句，它们被封装在一起，以执行特定的任务。在Oracle中，存储过程可以包含输入...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值