Summary of common Oracle operations and summary of oracle

Summary of common Oracle operations and summary of oracle
1. oracle modifies the table name, column name, field type, adds a table column, and deletes a table column:

Alert table scott. test rename to test1 -- modify the table name
Alter table scott. test rename column name to name1 -- modify the table column name
Alter table scott. test modify name1 number (20) -- modify the field type
Alter table scott. test add address varchar2 (40) -- add a table column
Alter table scott. test drop name cascadeconstraints -- delete a table column

 

2. copy the data of A table B (data of several fields) to the new Table A (several different fields:
Insert into tableA (A, B) select C, D from tableB t;

 

3. Take out a table set, loop (Update, delete,) another table attribute (data, comment, structure), mostly used for local data synchronization in the data table:
Begin
For I in (select gy. sblx_mc, gy. sblx_dm from nd_gy_sblx gy) loop
Execute immediate 'Update nd_dm_sblx dm set dm. sblx_mc = '| I. sblx_mc | 'set dm. sblx_jc =' | I. sblx_dm;
End loop;
End;

Add chr (39) before the variable of string type );

 

4. Update the field data in upper case (lower case ):
Update nd_dm_sblx set sblx_jc = upper (sblx_jc );
Update nd_dm_sblx set sblx_jc = lower (sblx_jc );

5. Update the field sequence of a data table (two methods are available ):

Method 1:

A. Create an SQL statement for the original table, view the table structure, and paste it;

B. Update the entire table preparing to update the table field sequence to the temporary table;

C. drop and you are about to update the data table;

D. modify the original table creation statement that you just pasted, and modify the field order in the statement;

E. Update the data in the temporary table to the newly created table and drop the temporary table;

 

Method 2:

A. view the physical ID of the data table;

Select object_id from all_objects where owner = 'table ow' and object_name = 'specific table ';

B. view the physical sequence of the table fields by the physical ID;

Select obj #, col #, name from sys. col $ where obj # = 131347 order by col #;

C. Update the physical sequence of the corresponding table fields (this step requires the database super administrator permission, please use it with caution ):

Update sys. col $ set col # = 5 where obj # = 131347 and name = 'jkse ';

D. Restart the database after the update is completed;

The two methods are compared. If your project is collaborative development, a database is used by multiple people, and method 2 is not suitable for you, Because restarting the database will delay others' development time, if you are proficient in writing SQL statements, the first method will only take up about five minutes for you to implement your ideas;

 

6. the row ID rowid and row number rownum of oracle:

ROWID is the unique physical address of the row record queried, and rownum is the serial number of the query result.