oracle修改表字段的数据类型的测试
思路
将要更改类型的字段名改名新字段以备份；
根据需求，添加一个与要更改类型的字段名同名的字段(原字段已经改名)，
然后更新数据，
最后删除改名备份的字段
脚本如下：
/*修改原字段名*/
ALTER TABLE 表名 RENAME COLUMN 字段名 TO 字段名1;
/*添加一个和原字段同名的字段*/
ALTER TABLE 表名 ADD 字段名 VARCHAR2(30);
/*将原来的数据更新到新字段中一定要显示进行数据类型转换*/
UPDATE 表名 SET 字段名 = CAST(字段名1 AS VARCHAR2(30));
/*删除原来的备份字段*/
ALTER TABLE 表名 DROP COLUMN 字段名1;
测试如下：
1.备份表
SQL> create table tt_bak_01 as select * from tt;
Table created.
2.想偷懒，现实直接修改字段如下： SQL> alter table tt modify (DATA_OBJECT_ID number);
alter table tt modify (DATA_OBJECT_ID number)
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
不行，只得转变方法 3.检查表结构 SQL> desc tt Name Null? Type -------------------------------------------------------------------- -------- ---------------------------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) 4.备份需要修改的字段 SQL> ALTER TABLE tt RENAME COLUMN DATA_OBJECT_ID to DATA_OBJECT_ID01; Table altered. SQL> desc tt Name Null? Type -------------------------------------------------------------------- -------- ---------------------------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) DATA_OBJECT_ID VARCHAR2(100) 5.根据业务需求，添加合适的字段以及相应数据类型 SQL> ALTER TABLE tt ADD DATA_OBJECT_ID VARCHAR2(100); Table altered. SQL> desc tt Name Null? Type -------------------------------------------------------------------- -------- ---------------------------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID01 NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) DATA_OBJECT_ID VARCHAR2(100) 6.更新字段数据到新修改的字段中 SQL> UPDATE tt SET DATA_OBJECT_ID = CAST(DATA_OBJECT_ID01 AS VARCHAR2(100)); 390 rows updated. SQL> 检查： SQL> select DATA_OBJECT_ID01 from tt minus 2 3 select to_number(DATA_OBJECT_ID) from tt; no rows selected 7.删除备份的字段结构 SQL> ALTER TABLE tt DROP COLUMN DATA_OBJECT_ID01; Table altered. SQL> desc tt; Name Null? Type -------------------------------------------------------------------- -------- ---------------------------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) DATA_OBJECT_ID VARCHAR2(100) SQL> select count(*) from tt; COUNT(*) ---------- 390 By 老白菜 ========================================================================= -- The End --
如果觉得《oracle 测试数据类型 oracle修改表字段的数据类型测试》对你有帮助，请点赞、收藏，并留下你的观点哦！
