alter table

JointLantic

3 years ago

The oracle alter table statement by pl sql is used to add column, modify, or drop/delete columns in a table. The oracle alter table statement is also used to rename a table.

alter table to add column in table oracle

Syntax

how to use pl sql alter table to add column in table, the oracle alter table syatnx is:

ALTER TABLE table_name ADD column_name column_definition;
ALTER TABLE table_name ADD column_name data_type constraint;

1 2	ALTER TABLE table_name ADD column_name column_definition; ALTER TABLE table_name ADD column_name data_type constraint;

Example 1:

alter table PS_JS_EX_PART2 add COMPANY_DESCR VARCHAR2(30 CHAR);
/
alter table PS_JS_EX_PART2 add KTB_TAX_ID VARCHAR2(15 CHAR);
/

Example 2:

ALTER TABLE ps_customers ADD city varchar2(50) DEFAULT 'Thailand';

1	ALTER TABLE ps_customers ADD city varchar2(50) DEFAULT 'Thailand';

add multiple columns in table

Syntax

In this syntax, you separate two columns by a comma

ALTER TABLE table_name
  ADD (column_1 column_definition,
       column_2 column_definition,
       ...
       column_n column_definition);

ALTER TABLE table_name

ADD (column_1 column_definition,

column_2 column_definition,

...

column_n column_definition);

Example 3:

ALTER TABLE products
  ADD (product_name varchar2(50),
       color varchar2(10) DEFAULT 'Pink',
       price number(3));

ALTER TABLE products

ADD (product_name varchar2(50),

color varchar2(10) DEFAULT 'Pink',

price number(3));

Modify column in table

Syntax

ALTER TABLE table_name
  MODIFY column_name column_type;

1 2	ALTER TABLE table_name MODIFY column_name column_type;

Example 4:

ALTER TABLE products
  MODIFY product_name varchar2(50) NOT NULL;

1 2	ALTER TABLE products MODIFY product_name varchar2(50) NOT NULL;

ALTER TABLE products
  MODIFY color varchar2(10) DEFAULT 'white' NOT NULL;

1 2	ALTER TABLE products MODIFY color varchar2(10) DEFAULT 'white' NOT NULL;

Modify Multiple columns in table

Syntax

ALTER TABLE table_name
  MODIFY (column_1 column_type,
          column_2 column_type,
          ...
          column_n column_type);

ALTER TABLE table_name

MODIFY (column_1 column_type,

column_2 column_type,

...

column_n column_type);

Example 5:

ALTER TABLE products
  MODIFY (product_name varchar2(100) NOT NULL,
          city varchar2(75) DEFAULT 'Seattle' NOT NULL);

ALTER TABLE products

MODIFY (product_name varchar2(100) NOT NULL,

city varchar2(75) DEFAULT 'Seattle' NOT NULL);

Drop column in table

Syntax

ALTER TABLE table_name
  DROP COLUMN column_name;

1 2	ALTER TABLE table_name DROP COLUMN column_name;

Example 6:

ALTER TABLE products DROP COLUMN product_name;

1	ALTER TABLE products DROP COLUMN product_name;

Rename column in table (NEW in Oracle 9i Release 2)

Syntax

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

1	ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Example 7:

ALTER TABLE customers RENAME COLUMN product_name TO sku_name;

1	ALTER TABLE customers RENAME COLUMN product_name TO sku_name;

Rename table

Syntax

ALTER TABLE table_name RENAME TO new_table_name;

1	ALTER TABLE table_name RENAME TO new_table_name;

Example 8:

ALTER TABLE products RENAME TO sku_table;

1	ALTER TABLE products RENAME TO sku_table;