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, useALTER 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 namedaccountsfor 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 theaccountstable 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 followingSELECTstatement:

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 theALTER TABLE MODIFYcolumn statement.

For example, the following statement makes thefull_namecolumn 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 theaccountstable except thefull_namecolumn:

SELECT * FROM accounts;

This is becausefull_nameColumn 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 theemailcolumn 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 fromnulltonot null, you must ensure that the existing data conforms to the new constraints (that is, ifNULLis not allowed in the original data ).

To solve this problem, first update the value of theemailcolumn:

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

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

Then change the constraint on theemailcolumn:

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 thephonecolumn, such as : Prefix with86. Before modifying the value of the column, we must expand the size of thephonecolumn 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 with86.

#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 thephonecolumn to12characters:

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 replace86with the empty string.

Then shorten the size of thephonecolumn:

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 columnfull_nameas 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!