Oracle中存储过程的默认值 [英] Default Values to Stored Procedure in Oracle

问题描述

我有一个stored procedure如下.

 CREATE OR REPLACE PROCEDURE TEST(
       X IN VARCHAR2 DEFAULT 'P',
       Y IN NUMBER DEFAULT 1) AS
 BEGIN
 DBMS_OUTPUT.PUT_LINE('X'|| X||'--'||'Y'||Y);
 END;

当我执行上述过程时

 EXEC TEST(NULL,NULL);

它将打印X--Y.当输入参数为null时，输入参数未默认为过程签名中的指定值. 那么default值的用途是什么?如果我们传递一个null值作为输入并且想要用default值替换一个null值怎么办?

It will print X--Y. The input parameters are not defaulting to the specified values in the procedure signature when input parameters are null. What is the use of default values then? What if we pass a null value as input and we want to replace a null value with the default value?

推荐答案

仅在未指定参数的情况下使用默认值.在您的情况下，您 did 指定参数-都提供了参数，其值为NULL. (是的，在这种情况下，NULL被认为是实际值:-).试试:

Default values are only used if the arguments are not specified. In your case you did specify the arguments - both were supplied, with a value of NULL. (Yes, in this case NULL is considered a real value :-). Try:

EXEC TEST()

分享并享受.

附录:过程参数的默认值肯定埋在某个系统表中(请参见SYS.ALL_ARGUMENTS视图)，但是要从该视图中获取默认值则需要从LONG中提取文本.领域，并且可能会证明比其价值更痛苦. easy 方法是在过程中添加一些代码:

Addendum: The default values for procedure parameters are certainly buried in a system table somewhere (see the SYS.ALL_ARGUMENTS view), but getting the default value out of the view involves extracting text from a LONG field, and is probably going to prove to be more painful than it's worth. The easy way is to add some code to the procedure:

CREATE OR REPLACE PROCEDURE TEST(X IN VARCHAR2 DEFAULT 'P',
                                 Y IN NUMBER DEFAULT 1)
AS
  varX VARCHAR2(32767) := NVL(X, 'P');
  varY NUMBER          := NVL(Y, 1);
BEGIN
  DBMS_OUTPUT.PUT_LINE('X=' || varX || ' -- ' || 'Y=' || varY);
END TEST;

这篇关于Oracle中存储过程的默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！