oracle 如何在视图中创建非空列

How to create a not null column in a view

提问by Danny Varod

Given a table like:

给定一个表，如：

CREATE TABLE "MyTable" 
(
  "MyColumn" NUMBER NOT NULL
);

I want to create a view like:

我想创建一个视图，如：

CREATE VIEW "MyView" AS
SELECT
    CAST("MyColumn" AS BINARY_DOUBLE) AS "MyColumn"
FROM "MyTable";

Only where the column "MyColumn" is "NOT NULL".

仅当列“MyColumn”为“NOT NULL”时。

In SQL Server this is pretty straight forward:

在 SQL Server 中，这非常简单：

CREATE VIEW [MyView] AS
SELECT
    ISNULL(CAST([MyColumn] AS Float), 0.0) AS [MyColumn]
FROM [MyTable];

However the Oracle equivalent results in a "NULL" column:

但是，Oracle 等效结果会生成“NULL”列：

CREATE VIEW "MyView" AS
SELECT
    NVL(CAST("MyColumn" AS BINARY_DOUBLE), 0.0) AS "MyColumn"
FROM "MyTable";

Is there anyway to force Oracle to mark the view's column as "NOT NULL" in the metadata?

无论如何要强制 Oracle 在元数据中将视图的列标记为“NOT NULL”？

采纳答案by Alex Poole

You can't add a not null or check constraint to a view; see thisand on the same page 'Restrictions on NOT NULL Constraints' and 'Restrictions on Check Constraints'. You can add a with check option(against a redundant where clause) to the view but that won't be marked as not nullin the data dictionary.

您不能向视图添加非空或检查约束；看到这个和同一页上的“非空约束的限制”和“检查约束的限制”。您可以with check option向视图添加（针对冗余 where 子句），但不会not null在数据字典中标记为。

The only way I can think to get this effect is, if you're on 11g, to add the cast value as a virtual column on the table, and (if it's still needed) create the view against that:

我认为要获得这种效果的唯一方法是，如果您使用的是 11g，则将强制转换值添加为表上的虚拟列，并且（如果仍然需要）针对该列创建视图：

ALTER TABLE "MyTable" ADD "MyBDColumn" AS
    (CAST("MyColumn" AS BINARY_DOUBLE)) NOT NULL;

CREATE OR REPLACE VIEW "MyView" AS
SELECT
    "MyBDColumn" AS "MyColumn"
FROM "MyTable";

desc "MyView"

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MyColumn                                  NOT NULL BINARY_DOUBLE

---

---

Since you said in a comment on dba.se that this is for mocking something up, you could use a normal column and a trigger to simulate the virtual column:

由于您在 dba.se 的评论中说这是为了模拟某些东西，您可以使用普通列和触发器来模拟虚拟列：

CREATE TABLE "MyTable" 
(
  "MyColumn" NUMBER NOT NULL,
  "MyBDColumn" BINARY_DOUBLE NOT NULL
);

CREATE TRIGGER "MyTrigger" before update or insert on "MyTable"
FOR EACH ROW
BEGIN
    :new."MyBDColumn" := :new."MyColumn";
END;
/

CREATE VIEW "MyView" AS
SELECT
    "MyBDColumn" AS "MyColumn"
FROM "MyTable";

INSERT INTO "MyTable" ("MyColumn") values (2);

SELECT * FROM "MyView";

  MyColumn
----------
  2.0E+000

And desc "MyView"still gives:

并且desc "MyView"仍然给出：

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MyColumn                                  NOT NULL BINARY_DOUBLE

As Leigh mentioned (also on dba.se), if you did want to insert/update the view you could use an instead oftrigger, with the VC or fake version.

正如 Leigh 提到的（也在 dba.se 上），如果您确实想插入/更新视图，您可以使用instead of触发器，以及 VC 或假版本。

回答by Bob Jarvis - Reinstate Monica

If you couldhave a NOT NULL constraint on a view column I believe that a SELECT from the view would then fail if the column in question was NULL. If this is the intent then the following might give you what you're looking for:

如果您可以对视图列设置 NOT NULL 约束，我相信如果相关列为 NULL，则视图中的 SELECT 将失败。如果这是意图，那么以下内容可能会为您提供所需的内容：

CREATE OR REPLACE VIEW some_view AS
  SELECT some_field,
         some_other_field,
         CASE
           WHEN field_of_interest IS NOT NULL
             THEN CAST(field_of_interest AS BINARY_DOUBLE)
             ELSE 1 / 0
         END AS field_of_interest_not_null
     FROM some_table;

Not very attractive, and you get an ugly "ORA-01476: division is equal to zero" message if the ELSE branch of the CASE is taken, but perhaps it's a step on the road to "better".

不是很吸引人，如果采用 CASE 的 ELSE 分支，您会收到一条丑陋的“ORA-01476：除法等于零”消息，但也许这是通往“更好”之路的一步。

Share and enjoy.

分享和享受。

---

---

EDIT: If the objective is to only pick up rows where your target column is not null perhaps you could add a WHERE clause to your view, as in:

编辑：如果目标是只选取目标列不为空的行，也许您可​​以在视图中添加 WHERE 子句，如下所示：

CREATE OR REPLACE VIEW some_view AS
  SELECT some_field,
         some_other_field,
         CAST(field_of_interest AS BINARY_DOUBLE) AS field_of_interest
    FROM some_table
    WHERE field_of_interest IS NOT NULL;

YMMV.

天啊。

---

---

EDIT2: Looking at the SQL Server example, it appears that the ISNULL function is being used to ensure that the column is never NULL. If this is acceptable you could do the following:

EDIT2：查看 SQL Server 示例，似乎使用 ISNULL 函数来确保该列永远不会为 NULL。如果这是可以接受的，您可以执行以下操作：

CREATE OR REPLACE VIEW some_view AS
  SELECT some_field,
         some_other_field,
         CAST(NVL(field_of_interest, 0.0) AS BINARY_DOUBLE) AS field_of_interest
    FROM some_table
    WHERE field_of_interest IS NOT NULL;

To quote Bullwinkle, "This time fer sure!!!" :-)

引用 Bullwinkle 的话，“这一次肯定！！！” :-)