oracle 在 Oracle11g 中使用函数作为列的默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6795654/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use function as default value for column in Oracle11g
提问by thinkdevcode
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:
我们正在我的工作中测试 Oracle，我负责在 Oracle 上构建所有数据库对象（表、过程、触发器等），我们目前使用 Microsoft SQL Server 2008 R2。我们对几乎所有的 ID 列都使用 uniqueidentifier。我使用这个函数来创建 GUID：
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")
);
And the error:
和错误：
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.
任何帮助将非常感激。谢谢你。
采纳答案by Kerri Shotts
Suggestion:
建议：
- Use triggers instead.
- 改用触发器。
For example:
例如：
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).
这应该可以解决问题（假设我没有在某处弄乱语法）。
回答by Justin Cave
I completely agree with the other answers that if you really want to call a function, you'll need to use a trigger or you'll need to embed the function call in the INSERTstatement itself. Embedding the function call in the INSERTstatement is more efficient than forcing Oracle to execute a trigger for every row that you insert.
我完全同意其他答案，如果你真的想调用一个函数，你需要使用触发器，或者你需要将函数调用嵌入到INSERT语句本身中。在INSERT语句中嵌入函数调用比强制 Oracle 为您插入的每一行执行触发器更有效。
I should point out, though, that you can use SYS_GUID()alone as the default value for a column without worrying about a trigger
不过，我应该指出，您可以SYS_GUID()单独使用作为列的默认值，而不必担心触发器
SQL> create table foo (
2 col1 varchar2(32) default sys_guid(),
3 col2 number
4 );
Table created.
SQL> insert into foo( col2 ) values( 1 );
1 row created.
SQL> select * from foo;
COL1 COL2
-------------------------------- ----------
7B64E8AE7404421C80A590F65873CD79 1
Do you really need the extra dashes in your GUID values? Could you potentially add the dashes only when you display the data? Or, since you're on 11g, add a function-based virtual column that converts the dash-free GUID to a GUID in the format that you prefer?
您真的需要 GUID 值中的额外破折号吗？您是否可以仅在显示数据时才添加破折号？或者，由于您使用的是 11g，添加一个基于函数的虚拟列，以将无破折号的 GUID 转换为您喜欢的格式的 GUID？
And since you're coming from SQL Server, I should point out that it would be conventional in Oracle to use a sequence to populate synthetic primary keys rather than using a GUID. Using a sequence to populate the key would generally be more efficient than calling SYS_GUID.
由于您来自 SQL Server，我应该指出，在 Oracle 中使用序列来填充合成主键而不是使用 GUID 是惯例。使用序列填充键通常比调用 SYS_GUID 更有效。
回答by Allan
As @Kerri implied, you cannot use a PLSQL function as a default value in a table definition. The relevant statement in the Oracle documentationis "A DEFAULT expression cannot contain references to PL/SQL functions...".
正如@Kerri 暗示的那样，您不能将 PLSQL 函数用作表定义中的默认值。Oracle 文档中的相关语句是“DEFAULT 表达式不能包含对 PL/SQL 函数的引用...”。
In addition to being the only way to insert a formatted GUID, as you're attempting to do, a trigger provides a second advantage: unlike with a default value, the value set by the trigger cannot be overridden by a careless developer.
除了作为插入格式化 GUID 的唯一方法之外，触发器还提供了第二个优点：与默认值不同，触发器设置的值不能被粗心的开发人员覆盖。
As an aside, you should really revise your newidfunction to use direct assignment rather than select ... from dual:
顺便说一句，您应该真正修改您的newid函数以使用直接赋值而不是select ... from dual：
CREATE OR REPLACE FUNCTION NEWID RETURN CHAR IS
guid VARCHAR(36);
BEGIN
guid := SYS_GUID();
guid :=
SUBSTR(guid, 1, 8) ||
'-' || SUBSTR(guid, 9, 4) ||
'-' || SUBSTR(guid, 13, 4) ||
'-' || SUBSTR(guid, 17, 4) ||
'-' || SUBSTR(guid, 21);
RETURN guid;
END NEWID;
/
Finally, I would advise you to use varchar2instead of char. Contrary to popularopinion, there is no storage or efficiency advantage to char, so you might as well use varchar2for everything, just for simplicity's sake.
最后，我建议你使用varchar2而不是char. 与流行的观点相反， 没有存储或效率优势char，因此您不妨varchar2为所有事情使用，只是为了简单起见。
回答by DCookie
You can't use a function for this. You don't see it because your syntax is wrong (DEFAULT before CONSTRAINTS). You should have:
您不能为此使用函数。您看不到它，因为您的语法错误（CONSTRAINTS 之前的 DEFAULT）。你应该有：
"ID" CHAR(36) DEFAULT NEWID() NOT NULL ,
at which point you would receive the following message (11g):
此时您将收到以下消息 (11g)：
SQL> create table tt (id varchar2(36) default newid() not null);
create table tt (id varchar2(36) default newid() not null)
*
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here
SQL>
As @Kerri states, it'll take a trigger to do this automatically.
正如@Kerri 所说，自动执行此操作需要触发。
回答by scottrudy
How about removing the function and trigger dependencies by trying something like this:
如何通过尝试以下操作来删除函数并触发依赖项：
CREATE TABLE "NonWorkingExample"
(
"ID" CHAR(36) NOT NULL DEFAULT regexp_replace((rawtohex(sys_guid()), '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})', '----'),
"UnitNumber" NUMBER(38) NOT NULL,
"StartDateTime" TIMESTAMP NOT NULL,
"EndDateTime" TIMESTAMP NULL,
CONSTRAINT PK_RentalAgreements PRIMARY KEY ("ID")
)
回答by James Daily
If Oracle SYS_GUID()'s somewhat similar/sequential GUIDs bother you, you can wrap java.util.UUID.randomUUID() in a pl/sql function to get less predictable values:
如果 Oracle SYS_GUID() 有点类似/顺序的 GUID 打扰您，您可以将 java.util.UUID.randomUUID() 包装在 pl/sql 函数中以获得更难预测的值：
CREATE OR REPLACE FUNCTION Java_Util_Random_UUID RETURN VARCHAR2 AS
language java
name 'java.util.UUID.randomUUID() return String';
