12c不可见列

在12c中，Oracle允许隐藏某些列。这个操作可以由用户自己来控制，通过CREATE TABLE或者ALTER TABLE等方式。被隐藏的列之后也可以通过ALTER TABLE还原。一般访问表时都是无法显示出隐藏字段的，比如select * from table或者DESC TABLE。

隐藏列的基本特性：

• 一般对表的访问都不会显示隐藏列（SELECT * FROM TABLE, DESC TABLE）
• 数据库通常会按表创建的顺序来存放字段，如果你将这张表新增了一个字段，这个新字段默认会排在表的最后。当表含有隐藏字段时，则表字段的顺序不会包含这些隐藏字段。
• SELECT语句中只有显式的查询不可见列才能返回结果

示例

创建表

DROP TABLE t2 purge;

CREATE TABLE t2 (a int,b int invisible, c int NOT NULL);

xb@PDB12C> @desc t2;
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        NUMBER(38)
    2      C                               NOT NULL NUMBER(38)

SELECT COLUMN_NAME,
       COLUMN_ID,
       HIDDEN_COLUMN
FROM USER_TAB_COLS
WHERE TABLE_NAME ='T2';

COLUMN_NAME           COLUMN_ID HID
-------------------- ---------- ---
A                             1 NO
B                               YES
C                             2 NO

可以看到通过desc查询表结构是无法看到字段b的，不可见列B的COLUMN_ID变为空，而第三个字段C的COLUMN_ID为2。

不过这里可以通过设置选项COLINVISIBLE来让DESC命令看到不可见列

SET COLINVISIBLE ON
xb@PDB12C> @desc t2
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        NUMBER(38)
    2      C                               NOT NULL NUMBER(38)
    3      B (INVISIBLE)                            NUMBER(38)

插入部分数据

xb@PDB12C> INSERT INTO T2 values (1,2,3);
INSERT INTO T2 values (1,2,3)
            *
ERROR at line 1:
ORA-00913: too many values

xb@PDB12C> INSERT INTO T2 values (1,2);

1 row created.

xb@PDB12C> INSERT INTO T2(a,b,c) values(3,4,5);

1 row created.

xb@PDB12C> SELECT * FROM t2;

         A          C
---------- ----------
         1          2
         3          5

xb@PDB12C> SELECT A,B,C FROM t2;

         A          B          C
---------- ---------- ----------
         1                     2
         3          4          5

恢复不可见列

ALTER TABLE t2 modify(b visible);

xb@PDB12C> @desc t2;
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        NUMBER(38)
    2      C                               NOT NULL NUMBER(38)
    3      B                                        NUMBER(38)

SELECT COLUMN_NAME,
       COLUMN_ID,
       HIDDEN_COLUMN
FROM USER_TAB_COLS
WHERE TABLE_NAME ='T2';

COLUMN_NAME           COLUMN_ID HID
-------------------- ---------- ---
A                             1 NO
B                             3 NO
C                             2 NO

可以看到将字段B重新可见后，它的COLUMN_ID发生了改变，所以要注意的是如果这时你再通过INSERT INTO T2插入数据时，第二个值不是插到B，而是C，所以在写sql的时候一定注意规范，将插入的字段名称都写在语句里。

INSERT INTO t2
VALUES(6,
       7,
       8);


SELECT *
FROM t2;

         A          C          B
---------- ---------- ----------
         1          2
         3          5          4
         6          7          8

将NOT NULL字段不可见

ALTER TABLE t2 modify(c invisible);

INSERT INTO t2
VALUES(5,
       6);
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("XB"."T2"."C")

所以即使字段不可见，但是因为有非空约束，所以还是要插入非空值到列

INSERT INTO t2 (a,b,c)
VALUES(5,
       6,
       7);

1 row created.

当非空字段有默认值的时候，那插入就不会有问题了。

不可见列上的约束键

不可见列上也时可以创建主键和外键约束的，与其他字段没什么区别

CREATE TABLE test1(a number,b number invisible PRIMARY KEY);

CREATE TABLE test2(c number, d number invisible REFERENCES test1(b));

限制

• 有些类型的表不能包含不可见列

  • 外部表
  • 聚簇表
  • 临时表

• 用户自定义类型的属性不能是不可见