Oracle数据库字典表的运用

--字典表/参数表

CREATE TABLE T_DICT(

D_ID   NUMBER(38,0) PRIMARY KEY,

D_CODE VARCHAR2(10),     --与原表的相同数据列

D_NAME VARCHAR2(100)    --对应的值

DTYPE  VARCHAR2(100)      --对应的类型

);

 

DROP SEQUENCE SEQ_T_DICT;

CREATE  SEQUENCE SEQ_T_DICT;

INSERT INTO T_DICT VALUES(SEQ_T_DICT.NEXTVAL,'1','男','SEX');

INSERT INTO T_DICT VALUES(SEQ_T_DICT.NEXTVAL,'0','女','SEX');

INSERT INTO T_DICT VALUES(SEQ_T_DICT.NEXTVAL,'1','启用','STATE');

INSERT INTO T_DICT VALUES(SEQ_T_DICT.NEXTVAL,'0','禁用','STATE');

多次关联同一张表  T_USER表内 U_SEX、U_STATE是‘1 ’或者‘0’

示例：

SELECT  U.U_ID,U.U_ACCOUNT,U.U_SEX,U.U_BIRTHDAY,D.D_NAME AS C_U_SEX,D1.D_NAME AS C_U_STATE

FROM T_USER U

INNER JOIN T_DICT D ON U.U_SEX=D.D_CODE AND D.D_TYPE ='SEX'

INNER JOIN T_DICT D1 ON U.U_STATE =D1.D_CODE AND D1.D_TYPE='STATE'

WHERE U_NICKNAME LIKE '%L%' ORDER BY U_ACCOUNT DESC ,U_BIRTHDAY DESC;