17,377
社区成员
发帖
与我相关
我的任务
分享
declare
tname varchar2(60);
cname varchar(100);
dlength number;
maxlength number;
begin
maxlength := 0;
for idx in (select u.TABLE_NAME,
u.COLUMN_NAME,
u.DATA_LENGTH,
decode(mod(u.DATA_LENGTH, 2), 0, 'EVEN', 'ODD') ODD_EVEN
from user_tab_columns u
where u.DATA_TYPE = 'VARCHAR2'
and u.TABLE_NAME in
(select t.table_name
from all_tables t
where t.num_rows > 0
and t.owner = 'BXDB')) loop
tname := idx.TABLE_NAME;
cname := idx.COLUMN_NAME;
dlength := idx.DATA_LENGTH;
execute immediate 'select max(lengthb(' || cname || ')) from ' || tname
into maxlength;
if (1.5 * maxlength) > dlength then
if idx.odd_even = 'ODD' then
dbms_output.put_line('alter table ' || tname || ' modify ' ||
cname || 'varchar2(' || 2 * maxlength || ');');
else
dbms_output.put_line('alter table ' || tname || ' modify ' ||
cname || 'varchar2(' || 1.5 * maxlength || ');');
end if;
end if;
end loop;
end;
--测试了一下没问题,你想直接spool的话,可以将拼出的直接按照下面的方法执行
declare
tname varchar2(60);
cname varchar(100);
dlength number;
maxlength number;
begin
maxlength := 0;
for idx in (select u.TABLE_NAME,
u.COLUMN_NAME,
u.DATA_LENGTH,
decode(mod(u.DATA_LENGTH, 2), 0, 'EVEN', 'ODD') ODD_EVEN
from user_tab_columns u
where u.DATA_TYPE = 'VARCHAR2'
and u.TABLE_NAME in
(select t.table_name
from all_tables t
where t.num_rows > 0
and t.owner = 'BXDB')) loop
tname := idx.TABLE_NAME;
cname := idx.COLUMN_NAME;
dlength := idx.DATA_LENGTH;
execute immediate 'select max(lengthb(' || cname || ')) from ' || tname
into maxlength;
if (1.5 * maxlength) > dlength then
if idx.odd_even = 'ODD' then
dbms_output.put_line('alter table ' || tname || ' modify ' ||
cname || 'varchar2(' || 2 * maxlength || ');');
else
-- dbms_output.put_line('alter table ' || tname || ' modify ' ||
-- cname || 'varchar2(' || 1.5 * maxlength || ');');
execute immediate 'alter table ' || tname || ' modify ' ||
cname || 'varchar2(' || 1.5 * maxlength ); --这里直接执行就完了呗
end if;
end if;
end loop;
end;
'select max(lengthb(' || cname || ')) from ' || tname