Shulou(Shulou.com)06/02 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.