How to determine whether the columns in the table exist and modify the table structure in oracle
It is believed that many inexperienced people are at a loss about how to judge whether the columns in the table exist and modify the table structure in oracle. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.
A method to determine whether a column exists in a table
Method 1:
You can use the user_tab_ colors table to query, and the result of the query indicates that the field exists:
Sql:select * from user_tab_cols where table_name='T_AAA' and column_name='COL_BBB'
Method 2:
You can also use the all_tab_ columns table to query, and the result of the query indicates that the field exists:
Sql:select * from all_tab_columns where owner='SYS_CCC' and table_name='T_AAA' and column_name='COL_BBB'
Note: all query fields must be uppercase, otherwise the query will have errors.
The method of modifying table structure
Add field syntax: alter table tablename add (column datatype [default value] [null/not null], … .)
Description: alter table table name add (whether the default value of field name field type is empty)
Example: alter table sf_users add (HeadPIC blob)
Example: alter table sf_users add (userName varchar2 (30) default 'empty' not null)
Modify the syntax of the field: alter table tablename modify (column datatype [default value] [null/not null], … .)
Description: alter table table name modify (whether the default value of field name field type is empty)
Example: alter table sf_InvoiceApply modify (BILLCODE number (4))
Syntax for deleting fields: alter table tablename drop (column)
Description: alter table table name drop column field name
Example: alter table sf_users drop column HeadPIC
Rename the field:
Description: alter table table name rename column column name to new column name (where: column is the keyword)
Example: alter table sf_InvoiceApply rename column PIC to NEWPIC
Rename the table:
Description: alter table table name rename to new table name
Example: alter table sf_InvoiceApply rename to sf_New_InvoiceApply
Script instance
Declare v_count integer
V_sql varchar2 (5000): =''
Begin
-- query whether there is such a front column
Select count (*) into v_count from user_tab_cols where table_name=upper ('tSkuPlu') and column_name=upper (' pluremark')
If v_count > 0 then
Dbms_output.put_line ('column already exists!')
Else
Alter table tSkuPlu add alter table tSkuPlu add (SQL (50))'
Execute immediate v_sql
End if
End
After reading the above, have you mastered the method of how to determine whether the columns in the table exist and modify the table structure in oracle? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!
