给出如下表：
CREATE TABLE "MyTable" ( "MyColumn" NUMBER NOT NULL );
我想创建一个视图,如：
CREATE VIEW "MyView" AS
SELECT
CAST("MyColumn" AS BINARY_DOUBLE) AS "MyColumn"
FROM "MyTable";
仅在“MyColumn”列为“NOT NULL”的位置.
在sql Server中,这非常简单：
CREATE VIEW [MyView] AS
SELECT
ISNULL(CAST([MyColumn] AS Float),0.0) AS [MyColumn]
FROM [MyTable];
但是,Oracle等效项会产生“NULL”列：
CREATE VIEW "MyView" AS
SELECT
NVL(CAST("MyColumn" AS BINARY_DOUBLE),0.0) AS "MyColumn"
FROM "MyTable";
反正是否强制Oracle将视图的列标记为元数据中的“NOT NULL”？
您不能向视图添加非null或检查约束;请参阅
this并在同一页面上“限制NOT NULL约束”和“限制检查约束”.您可以向视图添加with check选项(针对冗余where子句),但在数据字典中不会将其标记为非null.
我想到这个效果的唯一方法就是,如果你在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
既然你在对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
并且desc“MyView”仍然给出：
Name Null? Type ----------------------------------------- -------- ---------------------------- MyColumn NOT NULL BINARY_DOUBLE
正如Leigh所提到的(同样在dba.se上),如果你确实想要插入/更新视图,你可以使用VC或假版本而不是触发器.
