oracle 在oracle包中定义默认值的位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7260634/
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
where to define default value in oracle package
提问by kralco626
when you define a package in oracle there is a like a header and then a body.
当您在 oracle 中定义一个包时，有一个类似的标题，然后是一个正文。
One must define all parameters in both locations. I want to make one of the parameters optional for the calling program (IBM message broker). Do I have to add the default value in both the header and body definition?
必须在两个位置定义所有参数。我想为调用程序（IBM 消息代理）设置可选参数之一。我是否必须在标题和正文定义中添加默认值？
Addionally, can anyone confirm that messagebroker will be able to call the proc with out specifying any value for the parameter with a default?
此外，任何人都可以确认消息代理将能够调用 proc 而无需为具有默认值的参数指定任何值吗？
Thanks!
谢谢！
Update: I notice that I can add the default into the header and not the body, or I can add it into both. I cannot add it into just the body.
更新：我注意到我可以将默认值添加到标题而不是正文中，或者我可以将它添加到两者中。我不能将它添加到正文中。
What is the differance between adding it to both vs just the header?
将它添加到两者与仅添加到标题之间有什么区别？
Update:
更新：
I can do this where I only specify the default in the spec and not the body. Or i can also specify the default in both places. What is the differance?
我可以这样做，我只在规范中指定默认值而不是正文。或者我也可以在两个地方指定默认值。有什么区别？
create or replace
package myPackage is
PROCEDURE myProc (
parm1 IN varchar2,
parm1 IN date,
parm1 IN number default null
);
end myPackage;
create or replace
package body myPackage is
PROCEDURE myProc (
parm1 IN varchar2,
parm1 IN date,
parm1 IN number
) is
...
...
...
end myProc;
end myPackage;
回答by DCookie
If you want to make a parameter optional, then you must specify a default. I would be surprised if the default value works properly if it isn't in the declaration of the body.
如果要使参数可选，则必须指定默认值。如果默认值不在正文的声明中，我会感到惊讶。
I have gotten in the habit of making all my package spec declarations exact copies of the package body declarations to avoid issues.
我已经习惯于将所有包规范声明制作为包主体声明的精确副本以避免出现问题。
EDIT:
编辑：
As OP points out, it can be in the spec only and it works. If it's in the body but not the spec, an error results:
正如 OP 指出的那样，它只能在规范中并且可以工作。如果它在正文中但不在规范中，则会导致错误：
SQL> CREATE OR REPLACE PACKAGE p AS
2 PROCEDURE prc(p1 VARCHAR2, p2 VARCHAR2);
3 END;
4 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p AS
2 PROCEDURE prc(p1 VARCHAR2 DEFAULT 'P1', p2 VARCHAR2)
3 IS
4 BEGIN
5 dbms_output.put_line(p1||','||p2);
6 END;
7 END;
8 /
Warning: Package body created with compilation errors
SQL>
But if in the spec only, all works:
但如果仅在规范中，一切正常：
SQL> CREATE OR REPLACE PACKAGE p AS
2 PROCEDURE prc(p1 VARCHAR2 DEFAULT 'P1Dflt', p2 VARCHAR2);
3 END;
4 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p AS
2 PROCEDURE prc(p1 VARCHAR2, p2 VARCHAR2)
3 IS
4 BEGIN
5 dbms_output.put_line(p1||','||p2);
6 END;
7 END;
8 /
Package body created
SQL> DECLARE
2 BEGIN
3 p.prc(p2=>'Test');
4 END;
5 /
P1Dflt,Test
PL/SQL procedure successfully completed
SQL>
That said, the answer to the question as to what the difference it, it appears that there is no difference between putting the default in the spec only or in both places - the end result is the same. I would reiterate my belief that you should put it in both places for documentary purposes.
也就是说，关于它有什么区别的问题的答案，似乎将默认值仅放在规范中或在两个地方都没有区别 - 最终结果是相同的。我要重申我的信念，您应该将它放在两个地方以用于记录目的。
回答by Ben
In the package you can have default variables / constants in either the spec or the body. Personally I put them in the body as I don't really have to look at the spec to work out what's going on; I know official Oracle disagrees with me. In the body this should come straight under the create or replace
在包中，您可以在规范或正文中使用默认变量/常量。就我个人而言，我把它们放在 body 中，因为我真的不需要查看规范来弄清楚发生了什么；我知道官方 Oracle 不同意我的观点。在身体中，这应该直接在create or replace
I'm a little confused by your use of the word parameter though which implies you're passing this to functions / procedures in your package. If you have a global variable set in a package spec or body there's no need at all to pass it anywhere. If you're altering a global then you're asking for a whole heap of mess or will be gifting one to whoever follows you in a years time.
我对您使用参数这个词有点困惑，尽管这意味着您将它传递给包中的函数/过程。如果您在包规范或正文中设置了全局变量，则根本无需将其传递到任何地方。如果你正在改变一个全局，那么你就是在要求一堆乱七八糟的东西，或者将一个送给一年后跟随你的人。
If you're using it for only one function / procedure then set it in the declaration to that particular element.
如果您仅将它用于一个函数/过程，则在声明中将其设置为该特定元素。
Thisshould help.
这应该有帮助。
