Oracle操作

最新推荐文章于 2023-05-27 15:49:29 发布

secaiyingzi

最新推荐文章于 2023-05-27 15:49:29 发布

阅读量90

点赞数

文章标签： Oracle

本文链接：https://blog.csdn.net/secaiyingzi/article/details/103076141

版权

复制数据库表数据

先清除ht2上的所有cstp数据=====

--删除所有的 TABLE_NAME like 'FICSTP_%' 的表数据
begin
      --先禁用外键约束
  for tableName in (select 'alter table '||table_name||' disable constraint '||constraint_name as disable_fk from user_constraints where constraint_type='R' and TABLE_NAME like 'FICSTP_%' and STATUS = 'ENABLED') loop
    execute immediate tableName.disable_fk  ; 
  end loop ;
  --再删除表数据
   for tableName_2 in (select table_name from user_tables where table_name like 'FICSTP_%') loop
    execute immediate 'delete from ' || tableName_2.table_name ; 
  end loop ;
  --最后启用外键约束
    for tableName_3 in (select 'alter table '||table_name||' enable constraint '||constraint_name as enable_fk from user_constraints where constraint_type='R' and TABLE_NAME like 'FICSTP_%' and STATUS = 'DISABLED') loop
      execute immediate tableName_3.enable_fk  ; 
  end loop ;
end ;

--删除所有的 TABLE_NAME like 'FXCSTP_%' 的表数据
begin
      --先禁用外键约束
  for tableName in (select 'alter table '||table_name||' disable constraint '||constraint_name as disable_fk from user_constraints where constraint_type='R' and TABLE_NAME like 'FXCSTP_%' and STATUS = 'ENABLED') loop
    execute immediate tableName.disable_fk  ; 
  end loop ;
  --再删除表数据
   for tableName_2 in (select table_name from user_tables where table_name like 'FXCSTP_%') loop
    execute immediate 'delete from ' || tableName_2.table_name ; 
  end loop ;
  --最后启用外键约束
    for tableName_3 in (select 'alter table '||table_name||' enable constraint '||constraint_name as enable_fk from user_constraints where constraint_type='R' and TABLE_NAME like 'FXCSTP_%' and STATUS = 'DISABLED') loop
      execute immediate tableName_3.enable_fk  ; 
  end loop ;
end ;

delete from CSTPSENTOUT ；

====复制ht的数据到ht2上需要在ht用户打开的sql界面上操作 ht能访问ht2 =====

insert into  ht2.FICSTP_CRSELFDEALT  (select * from  ht.FICSTP_CRSELFDEALT where tradedatetime = ‘那一天的日期’);
insert into  ht2.FICSTP_下一张表  (select * from  ht.FICSTP_CRSELFDEALT where tradedatetime = ‘那一天的日期’);