In Oracle PL/SQL, the ALTER TABLE statement is used to modify the structure of an existing table. This statement allows you to add, modify, or drop columns, as well as make various other changes to the table’s structure. The ALTER TABLE statement is a powerful tool for database administrators and developers to adapt database schemas to evolving requirements.
Here are some common uses of the ALTER TABLE statement in Oracle PL/SQL:
1. Adding a New Column:
You can use the ADD keyword to add a new column to an existing table. The basic syntax is as follows:
ALTER TABLE table_name ADD (column_name datatype);
Example:
ALTER TABLE employees ADD (phone_number VARCHAR2(20));
This statement adds a new column called phone_number to the employees table with a data type of VARCHAR2.
2. Modifying Column Data Type:
To modify the data type of an existing column, you can use the MODIFY keyword. Here’s an example:
ALTER TABLE table_name MODIFY column_name new_datatype;
Example:
ALTER TABLE employees MODIFY phone_number NUMBER;
This statement changes the data type of the phone_number column in the employees table to NUMBER.
3. Dropping a Column:
If you want to remove a column from a table, you can use the DROP keyword:
ALTER TABLE table_name DROP COLUMN column_name;
Example:
ALTER TABLE employees DROP COLUMN phone_number;
This statement removes the phone_number column from the employees table.
4. Adding a Primary Key:
To add a primary key constraint to an existing table, you can use the ADD CONSTRAINT clause:
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);
Example:
ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id);
This statement adds a primary key constraint named emp_pk to the employees table on the employee_id column.
5. Renaming a Table:
You can use the RENAME TO clause to rename an existing table:
ALTER TABLE old_table_name RENAME TO new_table_name;
Example:
ALTER TABLE employees RENAME TO staff;
This statement renames the employees table to staff.
These are just a few examples of what you can accomplish with the ALTER TABLE statement in Oracle PL/SQL. It provides a flexible and powerful means to modify the structure of database tables as needed.