oracle 数据库割接,oracle的一次数据割接（1）

很久之前做过的一个典型数据割接，和大家分享下：

需求1：对多个表新增同一字段，新增字段初始值必须依赖于各表的内容大字段，而各表表结构不一样(有关联的就只是表里都有一个类似评论的字段，而新增的字段只是标示这个字段是不是有中文)。

疑问1：对于同时割接6张表，面对不同表结构的它们是否能用同一方法处理？还是一个一个表处理。

疑问2：中文字段如何判断。

分析：6张表大概可以分为分区和不分区两大类。

联想到有可能是逐条数据处理的情况一般都是从它们的表主键或者索引作为突破口(和表分区的思路一样，分区有范围、list、hash分区都可以借鉴)。

继续细分不分区的表：可分为主键为varchar2和number类型的表(其实这几个不分区的表主键都是number类型的，当初建表没同一处理可当做是涉及失误导致后面的麻烦)

对于中文判断：Oracle有个函数：convert转换函数估计大家有些人已经接触过了，对于非汉字有着将其转ascii码和其不变的特性，而中文的话就不同了。

--创建控制表

create table comment_ctrl

(

tablename varchar2(50),

actionsql varchar2(1024),

isnumber  varchar2(1),

maxandminsql    varchar2(1024)

)

tablespace TBS_MREAD_DAT;

comment on column comment_ctrl.tablename

is '表名';

comment on column comment_ctrl.actionsql

is '执行的sql';

comment on column comment_ctrl.isnumber

is 'id字段是否是number类型 0否 1是';

comment on column comment_ctrl.maxandminsql

is '计算最大\小值sql';

insert into comment_ctrl

(

tablename,

actionsql,

isnumber,

maxandminsql

)

values

(

'us_publiccomment',

'update us_publiccomment t

set t.isnotcnchar = decode(t.commentary,convert(t.commentary, ''US7ASCII'', ''ZHS16GBK''),

''0'',

''1'')

where t.bookreviewid >= :a and t.bookreviewid < :b',

'1',

'select max(t.bookreviewid),min(t.bookreviewid)  from us_publiccomment t'

);

insert into comment_ctrl

(

tablename,

actionsql

)

values

(

'us_bookcomment_audit',

'update us_bookcomment_audit  partition(:a)

set isnotcnchar = decode(commentary,convert(commentary, ''US7ASCII'', ''ZHS16GBK''),

''0'',

''1'')'

);

insert into comment_ctrl

(

tablename,

actionsql

)

values

(

'us_bookcomment_publish',

'update us_bookcomment_publish  partition(:a)

set isnotcnchar = decode(commentary,convert(commentary, ''US7ASCII'', ''ZHS16GBK''),

''0'',

''1'')'

);

insert into comment_ctrl

(

tablename,

actionsql,

isnumber,

maxandminsql

)

values

(

'us_usercomment',

'update us_usercomment t

set t.isnotcnchar = decode(t.content,convert(t.content, ''US7ASCII'', ''ZHS16GBK''),

''0'',

''1'')

where t.msisdn >= :a and t.msisdn < :b',

'0',

'select max(t.msisdn),min(t.msisdn)  from us_usercomment t'

);

insert into comment_ctrl

(

tablename,

actionsql,

isnumber,

maxandminsql

)

values

(

'us_topic',

'update us_topic t

set t.isnotcnchar = decode(t.content,convert(t.content, ''US7ASCII'', ''ZHS16GBK''),

''0'',

''1'')

where t.topicid >= :a and t.topicid < :b',

'1',

'select max(t.topicid),min(t.topicid)  from us_topic t'

);

insert into comment_ctrl

(

tablename,

actionsql,

isnumber,

maxandminsql

)

values

(

'us_leaveword',

'update us_leaveword t

set t.isnotcnchar = decode(t.leaveword,convert(t.leaveword, ''US7ASCII'', ''ZHS16GBK''),

''0'',

''1'')

where t.leavewordid >= :a and t.leavewordid < :b',

'1',

'select max(t.leavewordid),min(t.leavewordid)  from us_leaveword t'

);

commit;

--割接us_publiccomment表

alter table us_publiccomment add isnotcnchar varchar2(1) default '0' not null;

comment on column us_publiccomment.isnotcnchar

is '非汉字内容 0 非汉字 1 汉字内容';

--对us_bookcomment_audit数据割接

alter table us_bookcomment_audit add isnotcnchar varchar2(1) default '0' not null;

comment on column us_bookcomment_audit.isnotcnchar

is '非汉字内容 0 非汉字 1 汉字内容';

--对书评发布表(us_bookcomment_publish)表进行数据割接

alter table us_bookcomment_publish add isnotcnchar varchar2(1) default '0' not null;

comment on column us_bookcomment_publish.isnotcnchar

is '非汉字内容 0 非汉字 1 汉字内容';

--对us_usercomment表进行数据割接

alter table us_usercomment add isnotcnchar varchar2(1) default '0' not null;

comment on column us_usercomment.isnotcnchar

is '非汉字内容 0 非汉字 1 汉字内容';

--对us_topic表进行数据割接

alter table us_topic add isnotcnchar varchar2(1) default '0' not null;

comment on column us_topic.isnotcnchar

is '非汉字内容 0 非汉字 1 汉字内容';

--对us_leaveword表进行数据割接

alter table us_leaveword add isnotcnchar varchar2(1) default '0' not null;

comment on column us_leaveword.isnotcnchar

is '非汉字内容 0 非汉字 1 汉字内容';

--创建业务存储过程

create or replace procedure pro_addclomn_comment

(

tablename  in varchar2,--目标表名

ispartition in varchar2--是否分区表 '0'不是分区 '10'范围(时间)分区  '11' hash分区 可自由扩展

)

/*

* 评论等表增加是否非汉字字段业务处理

* @param tablename 目标表名

* @param ispartition 是否分区表 '0'不是分区 '10'范围(时间)分区  '11' hash分区 可自由扩展

* @author xKF24575

* @version [版本号, May 18, 2011]

* @see [相关类/方法]

* @since [产品/模块版本]

*/

is

--resultchar       varchar2(256);--执行结果

partitionname    varchar2(256);--分区名称

v_cs_sql         varchar2(1024);--动态游标sql

v_sql            varchar2(1024);--执行sql

v_sql_temp       varchar2(1024);--执行sql

v_error          varchar2(2048);--出错日志

v_countsql       varchar2(1024);--计算最大、小值sql

v_isnumber       varchar2(1);   --是否是数字类型

n_max            number(30);

n_min            number(30);

n_cur            number(30);

n_tmp            number(30);

v_max            varchar2(30);

v_min            varchar2(30);

v_cur            varchar2(30);

v_tmp            varchar2(30);

TYPE t_ref_cursor IS REF CURSOR;

c                t_ref_cursor; --动态游标

begin

if ispartition = '10' then --是范围分区表

v_cs_sql := 'select PARTITION_NAME

from USER_TAB_PARTITIONS

where TABLE_NAME = upper(:a) and PARTITION_NAME <> ''P1''';

v_sql_temp :='select actionsql from comment_ctrl where tablename = :c';

open c for v_cs_sql using tablename;

fetch c into partitionname;

while c% found loop

execute immediate v_sql_temp into v_sql using tablename;

v_sql := replace(v_sql,':a',partitionname);

execute immediate v_sql;

commit;--以分区commit一次

fetch c into partitionname;

end loop;

end if;

if ispartition = '0' then --不是分区表

v_sql_temp :='select actionsql,isnumber,maxandminsql from comment_ctrl where tablename = :c';

execute immediate v_sql_temp into v_sql,v_isnumber,v_countsql using tablename;

if v_isnumber = '1' then --是数字id

execute immediate v_countsql into n_max,n_min;

n_cur := n_min;

while (n_cur <= n_max) loop

n_tmp := n_cur + 20000;   --这里就是借鉴list分区的思路了

execute immediate v_sql using n_cur,n_tmp;

commit;

n_cur := n_tmp;

end loop;

end if;

if v_isnumber = '0' then --是字符id

execute immediate v_countsql into v_max,v_min;

v_cur := v_min;

while (v_cur <= v_max) loop

v_tmp := v_cur + '10000000';--这里就是借鉴list分区的思路了

execute immediate v_sql using v_cur,v_tmp;

commit;

v_cur := v_tmp;

end loop;

end if;

end if;

commit;

exception

when others then

v_error := sqlcode || ' - ' || sqlerrm;

prc_iread_sys_writelog (2,4,'pro_addclomn_comment',v_error,'');

end pro_addclomn_comment;

/

begin

pro_addclomn_comment('us_publiccomment','0');

end;

/

begin

pro_addclomn_comment('us_bookcomment_audit','1');

end;

/

begin

pro_addclomn_comment('us_bookcomment_publish','1');

end;

/

begin

pro_addclomn_comment('us_usercomment','0');

end;

/

begin

pro_addclomn_comment('us_topic','0');

end;

/

begin

pro_addclomn_comment('us_leaveword','0');

end;

/