oracle 表字段 缩短,oracle正确缩短字段（要考虑以前的是否有值）

--表名.email (字段调整为varchar2(100))

--表名.corpname (字段调整为varchar2(40))

--表名.INDUSTRY (字段调整为varchar2(2))

--表名.duty (字段调整为varchar2(32))

declare

iCount INTEGER := 0;

begin

select count(1) into iCount from user_tab_cols u where lower(u.table_name )= '表名' and u.column_name = 'email';

if iCount > 0 THEN

UPDATE 表名 t SET t.email = substrb(t.email, 0, 100) WHERE lengthb(t.email)>100;

COMMIT;

execute immediate 'alter table 表名 modify email varchar2(100)';--原值---120

END IF;

select count(1) into iCount from user_tab_cols u where lower(u.table_name )= '表名' and u.column_name = 'corpname';

if iCount > 0 THEN

UPDATE 表名 t SET t.corpname = substrb(t.corpname, 0, 40) WHERE lengthb(t.corpname)>40;

COMMIT;

execute immediate 'alter table 表名 modify corpname varchar2(40)';--原值---80

END IF;

select count(1) into iCount from user_tab_cols u where lower(u.table_name )= '表名' and u.column_name = 'INDUSTRY';

if iCount > 0 THEN

UPDATE 表名 t SET t.INDUSTRY=trim(t.INDUSTRY) where lengthb(INDUSTRY)>2;

COMMIT;

execute immediate 'alter table 表名 modify INDUSTRY varchar2(2)';--原值是字典值---3

END IF;

select count(1) into iCount from user_tab_cols u where lower(u.table_name )= '表名' and u.column_name = 'duty';

if iCount > 0 THEN

execute immediate 'alter table 表名 modify duty varchar2(32)';--原值----60

END IF;

END;

/