How to add columns to oracle table

In Oracle, you can use the "ALTER TABLE ADD" statement to add one or more columns to the table, the syntax is "ALTER TABLE table_name ADD column_name data_type constraint;".

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

In Oracle, you can use the "ALTER TABLE ADD" statement to add one or more columns to the table.

To add a new column to the table, use the ALTER TABLE statement as follows:

ALTER TABLE table_name 
ADD column_name data_type constraint;

In the above statement,

• First, specify the name of the table to which you want to add the new column after the ALTER TABLE clause.
• Secondly, specify the column name, data type and its constraints.

Please note that you cannot add columns that already exist in the table; doing so will result in an error. Additionally, the ALTER TABLE ADD column statement adds a new column at the end of the table. Oracle does not provide a direct way to allow you to specify the location of new columns like other database systems (such as MySQL).

If you want to add multiple columns, use the following syntax:

ALTER TABLE table_name 
ADD (
    column_name_1 data_type constraint,
    column_name_2 data_type constraint,
    ...
);

In this syntax, separate the two columns with a comma.

Oracle ALTER TABLE ADD column example

Let’s create a table named members. Refer to the following SQL statement -

-- 12c语法
CREATE TABLE members(
    member_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    PRIMARY KEY(member_id)
);

The following statement adds a new column named birth_date to the members table:

ALTER TABLE members 
ADD birth_date DATE NOT NULL;

In this example , the birth_date column is a DATE column, which does not accept null.

Suppose you want to record the creation and update time of a row. Then you can add two more columns created_at and updated_at, as follows:

ALTER TABLE
    members ADD(
        created_at TIMESTAMP WITH TIME ZONE NOT NULL,
        updated_at TIMESTAMP WITH TIME ZONE NOT NULL
    );

created_at and updated_at columns The data type is TIMESTAMP WITH TIME ZONE. These columns also don't accept null.

To check whether a column exists in the table, you can query the data from the user_tab_cols view. For example, the following statement will check whether the members table has a first_name column.

SELECT
    COUNT(*)
FROM
    user_tab_cols
WHERE
    column_name = 'FIRST_NAME'
    AND table_name = 'MEMBERS';

This query comes in handy when you want to check if a column exists in a column before adding it to the table.
For example, the following PL/SQL block checks whether the members table has an effective_date column before adding it.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  v_col_exists NUMBER 
BEGIN
  SELECT count(*) INTO v_col_exists
    FROM user_tab_cols
    WHERE column_name = 'EFFECTIVE_DATE'
      AND table_name = 'MEMBERS';

   IF (v_col_exists = 0) THEN
      EXECUTE IMMEDIATE 'ALTER TABLE members ADD effective_date DATE';
   ELSE
    DBMS_OUTPUT.PUT_LINE('The column effective_date already exists');
  END IF;
END;
/

If this block is executed for the first time, the effective_date column will be added to the end of the members table. However, once you execute it from the second time, you will see the following message:

The column effective_date already exists

This is as expected from the program written above.

Recommended tutorial: "Oracle Tutorial"

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