oracle 内容转列名,Oracle通过表名获取所有列名类型（转）

(一)

1.判断数据库对象是否存在

select count(1)

into v_count

from dba_objects t where t.object_name =‘TBALE_NAME’;

2.通过表名获取列名称、类型、字宽、精度等(loop处理)

select owner,

table_name,

column_name,

data_type,

data_length,

data_precision

from dba_tab_cols t

where TABLE_NAME = ‘TBALE_NAME’

order by column_id;

注释：当字段类型为number类型时，字宽取精度值：data_precision

3.获取字段注释

select comments

into v_COL_COMMENT

from dba_col_comments t

where TABLE_NAME =  ‘TBALE_NAME’

and column_name = ‘v_COL_NAME’

and owner = ‘owner’;

注：未注释的字段会抛异常，注意捕获处理。

4.处理查询出来的数据结果

保存表名查询出的列信息

(二)相关说明

Oracle 的数据字典分两类，一个数据字典表，另一个是数据字典视图。

数据字典表里的数据是Oracle系统存放的系统数据，而普通表存放的是用户的数据。为了方便的区别这些表，这些表的名字都是用"$"结尾，这些表属于SYS用户。

数据字典表由$ORACLE_HOME/rdbms/admin/sql.bsq 脚本创建。

这些数据字典表名称不好记，所以Oracle 又根据这些表创建了一些视图。 即方便使用，又影藏了那些数据字典表。

我们查询表列的信息时，不是直接查询SYS.COL$，而是查询USER_TAB_COLUMNS 视图

SQL> select owner,object_name,object_type from all_objects where object_name='COL$';

OWNER      OBJECT_NAME        OBJECT_TYPE

----------------- ------------------------------ ------------------

SYS           COL$                TABLE

SQL> set long 9999

SQL> select text from dba_views where view_name ='USER_TAB_COLUMNS';

TEXT

--------------------------------------------------------------------------------

select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,

DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,

DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,

DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,

CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,

GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,

V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM

from USER_TAB_COLS

where HIDDEN_COLUMN = 'NO'

这里查看的是USER_TAB_COLS视图，我们在挖一层：

SQL>select * from dba_views where view_name ='USER_TAB_COLS'

...此处省略

我们看到了本质：sys.col$

Oracle数据库没有提供直接修改表中列名称的功能，但在实际使用时常需要修改表的列名和列顺序。

我们可以通过间接的方法来实现，就是重新创建一个新的具有正确列名和顺序的数据库表，再将旧表的数据转储进来，最后删除旧表并将新表重命名为旧表的方法来完成此功能。

这种方法的最大问题是要求有双倍的存储空间、较大的回滚段和较长的时间，如果表中数据量较大，这项工作开销会很大。

而SYS.COL$保存的就是表列的信息，所以我们可以直接修改这个表列的信息，从而改变表中列的顺序。 当然Oracle 是不建议这么操作的。 这里也只做个知识点了解一下。

二.  SYS.COL$ 示例

2.1 创建测试表

SQL> conn dave/dave;

已连接。

SQL> create table myuser as select username,user_id from all_users;

表已创建。

SQL> select * from myuser where rownum=1;

USERNAME                          USER_ID

------------------------------ ----------

DAVE                                   90

下面的操作就是将2个列换一下顺序，并将列名改为ID 和 NAME.

2. 2 从ALL_OBJECTS中查找对象DAVE.MYUSER表的ID

在第一节里我们将了，对象的表列信息是存放在SYS.COL$表里的，要修改对象的列，就需要知道对象的ID.

SQL> SELECT OBJECT_NAME,OBJECT_ID FROM ALL_OBJECTS WHERE OWNER ='DAVE' AND OBJECT_NAME='MYUSER';

OBJECT_NAME       OBJECT_ID

------------------------------ ----------

MYUSER              74344

2.3根据MYUSER的ID，从SYS.COL$检索出表中列的定义信息

SQL> conn / as sysdba;

已连接。

SQL> SELECT OBJ#,COL#,NAME FROM SYS.COL$ WHERE OBJ# =74344;

OBJ#       COL# NAME

---------- ---------- ------------------------------

74344          1 USERNAME

74344          2 USER_ID

注意：SYS.COL$ 只能sys 用户才有权限查询。

2.4 使用Update语句来进行修改

SQL> UPDATE SYS.COL$ SET COL# = 1,NAME='ID' WHERE OBJ# = 74344 AND NAME='USER_ID';

已更新 1 行。

SQL> UPDATE SYS.COL$ SET COL# = 2,NAME='NAME' WHERE OBJ# = 74344 AND NAME ='USERNAME';

已更新 1 行。

SQL> COMMIT;

提交完成。

2.5 重启数据库服务

直接修改数据字典表是个危险的操作。 所以以上测试仅做了解。

dba_tab_cols 等同与user_tab_cols

select table_name,column_name,data_type,data_length,data_precision

from dba_tab_cols t

where TABLE_NAME = 'TABLE_NAME'

order by column_id;

SELECT OBJECT_NAME,OBJECT_ID FROM ALL_OBJECTS WHERE OWNER ='TBCS' AND OBJECT_NAME='CS_REC_SMS_INFO_HIS';

select * from sys."_CURRENT_EDITION_OBJ" t where t.name = 'TABLE_NAME';

SYS.COL$表保存的就是表列的定义信息:

select t.*,t.obj# from SYS.COL$ t where t.obj# = '1042996';

select * from sys.coltype$ ac where ac.obj# = '1042996';