How to modify fields in oracle database

In Oracle, you can use the "ALTER TABLE MODIFY" statement to modify fields. The syntax is "ALTER TABLE table name MODIFY field name operations that need to be performed;"; common operations include: modifying column visibility, changing Default values ​​for columns, expressions that modify virtual columns, etc.

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

How to modify fields in oracle database

In Oracle, you can use the "ALTER TABLE MODIFY" statement to modify fields and change the value of existing fields. definition.

To change the definition of a column in a table, use ALTER TABLE MODIFYcolumn syntax as follows:

ALTER TABLE 表名 
MODIFY 字段名 需要执行的操作;

The statement is straightforward. To modify a table's columns, you need to specify the column name, table name, and operation to be performed.

Oracle allows you to perform a variety of operations, but the following are the main commonly used operations:

• Modify the visibility of a column

• Allow or disallow NULL values

• Shorten or expand the size of a column

• Change the default value of a column

• Expressions to modify virtual columns

To modify multiple columns, use the following syntax:

ALTER TABLE 表名
MODIFY (
    字段名1 action,
    字段名2 action,
    ...
);

Oracle ALTER TABLE MODIFY column example

First, create a new table named accounts for the demo:

-- 12c语法
CREATE TABLE accounts (
    account_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(25) NOT NULL,
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(100),
    phone VARCHAR2(12) ,
    full_name VARCHAR2(51) GENERATED ALWAYS AS( 
            first_name || ' ' || last_name
    ),
    PRIMARY KEY(account_id)
);

Second, create a new table to the accounts table Insert some rows into:

INSERT INTO accounts(first_name,last_name,phone)
VALUES('Trinity',
       'Knox',
       '410-555-0197');


INSERT INTO accounts(first_name,last_name,phone)
VALUES('Mellissa',
       'Porter',
       '410-555-0198');


INSERT INTO accounts(first_name,last_name,phone)
VALUES('Leeanna',
       'Bowman',
       '410-555-0199');

Third , verify the insertion operation by using the following SELECT statement:

SELECT
    *
FROM
    accounts;

Execute the above query statement and get The following results-

1. Modify the visibility of the column

In Oracle 12c, you can Table columns are defined as invisible or visible. Invisible columns cannot be used for queries, such as:

SELECT
    *
FROM
    table_name;

or

DESCRIBE table_name;

. Invisible columns cannot be found.

However, it is possible to query invisible columns by explicitly specifying them in the query:

SELECT
    invisible_column_1,
    invisible_column_2
FROM
    table_name;

By default, table columns are visible. Invisible columns can be defined when creating the table or using the ALTER TABLE MODIFY column statement.

For example, the following statement makes the full_name column invisible:

ALTER TABLE accounts 
MODIFY full_name INVISIBLE;

Execute query data in the table again and get the following results-

The following statement returns data in all columns of the accounts table except the full_name column:

SELECT
    *
FROM
    accounts;

This is because full_name Column is not visible. To change a column from invisible to visible, use the following statement:

ALTER TABLE accounts 
MODIFY full_name VISIBLE;

2. Allow or disallow null Example

The following statement Change the email column to accept non-empty (not null) values:

ALTER TABLE accounts 
MODIFY email VARCHAR2( 100 ) NOT NULL;

However, Oracle issues the following error:

SQL Error: ORA-02296: cannot enable (OT.) - null values found

because when When changing a column from null to not null, you must ensure that the existing data conforms to the new constraints (that is, if NULL is not allowed in the original data ).

To solve this problem, first update the value of the email column:

UPDATE 
    accounts
SET 
    email = LOWER(first_name || '.' || last_name || '@oraok.com') ;

Please note that the LOWER() function converts the string to lowercase letter.

Then change the constraint on the email column:

ALTER TABLE accounts 
MODIFY email VARCHAR2( 100 ) NOT NULL;

Now, it should work as expected.

3. Expand or shorten the size of the column example

Suppose you want to add international codes to the phone column, such as : Prefix with 86. Before modifying the value of the column, we must expand the size of the phone column using the following statement:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 24 );

Now, we can update the phone number data:

UPDATE
    accounts
SET
    phone = '+86 ' || phone;

The following statement Verification update:

SELECT
    *
FROM
    accounts;

In the results of executing the above query statement, you should be able to see that the original phone number has the international area code prefixed with 86.

#To shorten the size of a column, make sure all data in the column fits the new size.

For example, trying to reduce the size of the phone column to 12 characters:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 12 );

Oracle Database issues the following error:

SQL Error: ORA-01441: cannot decrease column length because some  value is too big

To solve this problem, first, the international code should be removed from the phone number (ie: 86):

UPDATE
    accounts
SET
    phone = REPLACE(
        phone,
        '+86 ',
        ''
    );

The REPLACE() function replaces a substring with a new one String. In this case it will replace 86 with the empty string.

Then shorten the size of the phone column:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 12 );

4. Modify the virtual column

Assumption Fill in the full name in the following two-column format:

last_name, first_name

To do this, you can change the expression of the virtual column full_name as follows:

ALTER TABLE accounts 
MODIFY full_name VARCHAR2(52) 
GENERATED ALWAYS AS (last_name || ', ' || first_name);

以下语句验证修改：

SELECT
    *
FROM
    accounts;

执行上面查询语句，可以看到以下结果

5. 修改列的默认值

添加一个名为status的新列，默认值为1到accounts表中。参考以下语句 -

ALTER TABLE accounts
ADD status NUMBER( 1, 0 ) DEFAULT 1 NOT NULL ;

当执行了该语句，就会将accounts表中的所有现有行的status列中的值设置为1。

要将status列的默认值更改为0，请使用以下语句：

ALTER TABLE accounts 
MODIFY status DEFAULT 0;

可以在accounts表中添加一个新行来检查status列的默认值是0还是1：

INSERT INTO accounts ( first_name, last_name, email, phone )
VALUES ( 'Julia',
         'Madden',
         'julia.madden@oraok.com',
         '410-555-0200' );

现在，查询accounts表中的数据：

SELECT
  *
FROM
  accounts;

执行上面查询语句，应该看类似下面的结果 

正如所看到的那样，ID为4的账户的status列的值是0。

推荐教程：《Oracle教程》

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