How to convert field type in oracle

In Oracle, you can use the "ALTER TABLE MODIFY" statement to convert field types. The syntax is "ALTER TABLE table_name MODIFY (field name new data type)".

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

In Oracle, you can use the "ALTER TABLE MODIFY" statement to change the definition of existing columns, such as modifying the field type.

Syntax format:

ALTER TABLE table_name MODIFY(字段名称 新数据类型)

For example: there is a table named tb, a field segment named name, and a data type of nchar(20).

1. Assuming that the field data is empty, no matter what field type is changed, it can be executed directly:

alter table tb modify (name nvarchar2(20));

2. Assuming that the field has data, it can be executed directly by changing to nvarchar2(20):

alter table tb modify (name nvarchar2(20));

3. Assuming that the field has data, when it is changed to varchar2(40), it will pop up: "ORA-01439: To change the data type, the column to be modified must be empty". In this case, use The following method is used to solve this problem:

/*修改原字段名name为name_tmp*/
alter table tb rename column name to name_tmp;

/*增加一个和原字段名同名的字段name*/
alter table tb add name varchar2(40);

/*将原字段name_tmp数据更新到增加的字段name*/
update tb set name=trim(name_tmp);

/*更新完，删除原字段name_tmp*/
alter table tb drop column name_tmp;

Summary:

1. When the field has no data or the new type to be modified is compatible with the original type, you can modify it directly.

2. When a field has data and the new type to be modified is incompatible with the original type, create a new field indirectly for transfer.

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to convert field type in oracle. For more information, please follow other related articles on the PHP Chinese website!