--添加某列
alter table am_bdc_datum add zl varchar2(255);
alter table am_bdc_page add zl varchar2(255);
alter table am_bdc_entry add qybs varchar2(255);
alter table am_bdc_datum add qybs varchar2(255);
alter table am_bdc_page add qybs varchar2(255);
--清空數(shù)據(jù);
delete from am_bdc_datum;
delete from am_bdc_entry;
delete from am_bdc_page;
--兩張表對比
select a.*,a.rowid from 南嶺村 a where a.ywh not in(select bh from 南嶺村ABC) and a.f8 is null;
select a.*,a.rowid from 南嶺村ABC a where a.bh not in(select ywh from 南嶺村);
--查詢某列重復(fù)數(shù)據(jù)
select ywh,count(ywh) from am_bdc_entry having count(ywh)>1 group by ywh;
--備注遷移標(biāo)識
update am_bdc_entry set qybs=2;
update am_bdc_datum set qybs=2;
update am_bdc_page set qybs=2;
--處理am_bdc_entry;
select * from am_bdc_entry;
update am_bdc_entry a set a.ywh=regexp_substr(a.description,'[^\]+',1,5);
update am_bdc_entry a set a.ybdcqzh=
(select '潭政林證字('||substr(t.bdcqzh,1,4)||')第'||substr(t.bdcqzh,5,15)||'號'
from 南嶺村 t where t.bdcqzh is not null and a.ywh=t.ywh);
update am_bdc_entry a set a.zl='南平市建陽區(qū)水吉鎮(zhèn)南嶺村';
update am_bdc_entry a set a.qlrmc=(select t.ldsyqr from 南嶺村 t where a.ywh=t.ywh);
update am_bdc_entry a set a.xmmc=qlrmc;
update am_bdc_entry a set a.RETENTION_PERIOD='永久';
update am_bdc_entry a set a.SECURITY_LEVEL='MM';
--處理am_bdc_page;
update am_bdc_page set CREATED_BY='sa';
update am_bdc_page set bz_name=IMAGE_DESC;
update am_bdc_page set IMAGE_DESC=replace(replace(regexp_substr(IMAGE_DESC,'[^|]+',1,2),'.jpg',''),'.JPG','');
update am_bdc_page set zl='南嶺村';
--處理AM_BDC_DATUM;
update AM_BDC_DATUM set bz_name=name;
update AM_BDC_DATUM set name=name||')' where name like '%(%' and name not like '%)%';
update am_bdc_datum a set a.datum_order=
(select s.bz from
(select datum_id,entry_id,name,row_number () over
(partition by entry_id order by name)as bz from am_bdc_datum)s where a.datum_id=s.datum_id) where exists
(select 1 from (select datum_id,entry_id,name,row_number () over(partition by entry_id order by name)as bz from am_bdc_datum)s where a.datum_id=s.datum_id);
update am_bdc_datum set zl='南嶺村';
--檢查
select a.*,a.rowid from am_bdc_datum a;
select a.*,a.rowid from am_bdc_entry a where a.xmmc is null;
select a.*,a.rowid from am_bdc_page a where a.datum_id is null;
--復(fù)制表格式到新表;
create table am_bdc_datum as select * from AM_BDC_DATUM_安口村 where 1=2;
create table am_bdc_entry as select * from am_bdc_entry_安口村 where 1=2;
create table am_bdc_page as select * from am_bdc_page_安口村 where 1=2;