使用函数作为Oracle11g中列的默认值 [英] Use function as default value for column in Oracle11g

问题描述

我们正在测试Oracle，并负责在Oracle上构建所有数据库对象(表，proc，触发器等)，并且当前使用Microsoft SQL Server 2008 R2.我们几乎对所有ID列都使用uniqueidentifier.我使用此函数创建了GUID:

We are testing out Oracle at my work and im in charge of building all of the database objects (tables, procs, trigger, etc) on Oracle, and we currently use Microsoft SQL Server 2008 R2. We use uniqueidentifier's for almost all of our ID column's. I used this function to create GUID's:

CREATE OR REPLACE FUNCTION NEWID RETURN CHAR IS guid CHAR(36) ;
BEGIN
    SELECT SYS_GUID() INTO guid FROM DUAL;
    guid :=
               SUBSTR(guid,  1, 8) ||
        '-' || SUBSTR(guid,  9, 4) ||
        '-' || SUBSTR(guid, 13, 4) ||
        '-' || SUBSTR(guid, 17, 4) ||
        '-' || SUBSTR(guid, 21);
    RETURN guid;
END NEWID;
/

但是现在我不知道如何在创建表时将其用作列的默认值.这是一个无效的示例:

But now I cant figure out how to use it as the default value on columns when creating tables. Here is a non-working example:

CREATE TABLE "NonWorkingExample"
(   
  "ID"                              CHAR(36)      NOT NULL DEFAULT   NEWID(),
  "UnitNumber"                      NUMBER(38)    NOT NULL,
  "StartDateTime"                   TIMESTAMP     NOT NULL,
  "EndDateTime"                     TIMESTAMP     NULL,

  CONSTRAINT PK_RentalAgreements  PRIMARY KEY ("ID")
);

错误:

Error starting at line 1 in command:
CREATE TABLE "NonWorkingExample"
(   
  "ID"                          CHAR(36)      NOT NULL DEFAULT NEWID(),
  "UnitNumber"                  NUMBER(38)    NOT NULL,
  "StartDateTime"               TIMESTAMP     NOT NULL,
  "EndDateTime"                 TIMESTAMP     NULL,

  CONSTRAINT PK_RentalAgreements  PRIMARY KEY ("ID")
)
Error at Command Line:3 Column:58
Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:

任何帮助将不胜感激.谢谢.

Any help would be much appreciated. Thank you.

推荐答案

建议:

• 改为使用触发器.

例如:

CREATE TRIGGER SetGUIDforTableXYZ BEFORE INSERT ON TableXYZ
FOR EACH ROW
BEGIN
    :new.ID := NEWID();
END;

这应该可以解决问题(假设我没有弄乱某个地方的语法).

That should do the trick (assuming I haven't messed up the syntax somewhere).

这篇关于使用函数作为Oracle11g中列的默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！