How to modify table field type in oracle

In Oracle, you can use the "alter table" statement to modify the table field type. The function of this statement is to change the structure of the original table. The syntax is "alter table table name modify (field name new type name); "statement.

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

Modify table field data type

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, you can directly execute:

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

2. Assuming that the field has data, then change It can be executed directly for nvarchar2(20):

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

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

/*Modify the original field name to name_tmp*/

alter table tb rename column name to name_tmp;

/ *Add a field name with the same name as the original field name*/

alter table tb add name varchar2(40);

/*Update the original field name_tmp data to the added field name*/

update tb set name=trim(name_tmp);

/*After updating, delete the original field name_tmp*/

alter table tb drop column name_tmp;

3. Add fields and delete fields

    --添加新的字段

    alter table lyz_emp add(e_age number(3));

    --添加多个新的字段

    alter table lyz_emp add(

    e_age number(3),

    e_age_tmp,varchar2(20));

    --获取表字段：

    select *

    from user_tab_columns

    where Table_Name='lyz_emp'

    order by column_name

    --删除表中的字段

    alter table lyz_emp drop column e_age;

Recommended tutorial: "Oracle Tutorial"

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