oracle 元数据学习

来源：互联网发布：文件读入列表 python 编辑：程序博客网时间：2024/05/16 09:36

创建表，并给表和字段加上描述

CREATE TABLE MANTIS_CODE(
ID VARCHAR2(32) NOT NULL,
NAME VARCHAR2(255) NOT NULL
);

CREATE TABLE MANTIS_CODE_ITEM(
    CODE_ID    VARCHAR2(32)     NOT NULL,
    VALUE      VARCHAR2(32)     NOT NULL,
    NAME       VARCHAR2(255)
)
;

.创建主键

alter TABLE MANTIS_CODE add constraint PK_MANTIS_CODE primary key (ID)

联合主键

alter TABLE MANTIS_CODE add constraint PK_MANTIS_CODE primary key (ID，NAME)

创建表描述

COMMENT ON TABLE mantis_code is '代码表'

创建字段描述

COMMENT ON COLUMN mantis_code.ID is '代码标识'

创建外键

alter table MANTIS_CODE_ITEM add constraint FK_MANTIS_CODE_ITEM foreign key(CODE_ID) references MANTIS_CODE(ID)

取得表的描述

select * from user_tab_comments where comments is not null

取得字段

select t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE,t.DATA_LENGTH,t.DATA_PRECISION,t.DATA_SCALE,

t.NULLABLE,t.DATA_DEFAULT from user_tab_columns t where t.TABLE_NAME='USER_INFO'

取得字段的描述

select * from user_col_comments where comments is not null;

取得表的主键

select * from user_cons_columns cc,user_constraints c
where c.constraint_name = cc.constraint_name
and c.constraint_type = 'P'
and cc.table_name = 'USER_INFO'

取得外键

select r.CONSTRAINT_NAME,
       r.TABLE_NAME,
       r.R_CONSTRAINT_NAME,
       rc.COLUMN_NAME,
       pc.COLUMN_NAME as PK_COLUMN_NAME,
       pc.TABLE_NAME as PK_TABLE_NAME
from USER_CONSTRAINTS r,
       USER_CONS_COLUMNS rc,
       USER_CONSTRAINTS p,
       USER_CONS_COLUMNS pc
where r.CONSTRAINT_TYPE = 'R'
   and r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
   and pc.CONSTRAINT_NAME = p.CONSTRAINT_NAME
   and p.CONSTRAINT_TYPE = 'P'
   and r.R_CONSTRAINT_NAME = p.CONSTRAINT_NAME
   and r.TABLE_NAME = 'USER_ROLE_MAP'

如果取得数据库表则从ALL_CONSTRAINTS ALL_CONS_COLUMNS中取得

你可以通过这个链接引用该篇文章:http://lianghyan.bokee.com/tb.b?diaryId=18763148