Oracle函数中的默认值 [英] Default Values in Oracle Functions

问题描述

假定以下函数声明:

FUNCTION ARTTEXTJN
(p_art_id     in number
,p_arttextart in varchar2 default 'basis'
,p_sprache    in varchar2 default null
,p_aufart     in number   default null
,p_fallback_arttextart in varchar2  default 'J' 
)
RETURN VARCHAR2

期望第一个参数的所有参数都具有默认值.

Expect the first parameter all parameter have a default value.

jOOQ生成这样的打包方法:

jOOQ generate a package method like this:

public static Field<String> arttextjn(Field<? extends Number> pArtId, Field<String> pArttextart, 
              Field<String> pSprache, Field<? extends Number> pAufart, Field<String> pFallbackArttextart) {
    Arttextjn f = new Arttextjn();
    f.setPArtId(pArtId);
    f.setPArttextart(pArttextart);
    f.setPSprache(pSprache);
    f.setPAufart(pAufart);
    f.setPFallbackArttextart(pFallbackArttextart);
    return f.asField();
}

如果要在查询中使用它，则必须将null传递给该函数:

If I want to use it in a query I have to pass null to the function:

dsl.select(KAMPARTIKEL.ARTNR, KAMPARTIKEL.ARTNRKAMP,
           PaBez.arttextjn(KAMPARTIKEL.ART_ID, null, null, null, null))

但是Oracle不使用默认值.

But then Oracle does not use the default values.

是否可以告诉jOOQ使用所有可能的组合生成重载方法?否则，我将无法在select子句中使用该功能.

Is there a way to tell jOOQ to generate overloaded methods with all possible combinations? Otherwise I'm not able to use that function in a select clause.

推荐答案

是否可以告诉jOOQ使用所有可能的组合生成重载方法?

Is there a way to tell jOOQ to generate overloaded methods with all possible combinations?

不，会有太多的组合.当然，您可以自己扩展代码生成器，但我建议不要这样做.

No, there would be way too many combinations. Of course, you could extend the code generator yourself, but I would advise against it.

否则，我将无法在select子句中使用该功能.

Otherwise I'm not able to use that function in a select clause.

是的，您可以使用它！但是不使用该辅助方法PaBez.arttextjn.可以将其作为独立的函数调用来调用:

Yes, you can use it! But not using that auxiliary method PaBez.arttextjn. It's possible to call it as a standalone function call:

Arttextjn f = new Arttextjn();
f.setPArtId(1);
f.execute();
String result = f.getReturnValue();

也应该可以在SQL语句中使用

It should be possible to use in a SQL statement as well:

Arttextjn f = new Arttextjn();
f.setPArtId(KAMPARTIKEL.ART_ID);

var result =
dsl.select(KAMPARTIKEL.ARTNR, KAMPARTIKEL.ARTNRKAMP, f.asField())
   .from(KAMPARTIKEL)
   .fetch();

对于您来说，这应该是开箱即用的.

In your case, this should work out of the box.

请注意，从jOOQ 3.11开始，在Oracle中，在这种情况下，jOOQ通过索引传递函数参数，而不是通过名称传递函数参数(与PostgreSQL一样).生成的SQL是:

Note that as of jOOQ 3.11, and in Oracle, jOOQ passes function arguments by index in this case, not by name (as it does for PostgreSQL). The generated SQL is:

select KAMPARTIKEL.ARTNR, KAMPARTIKEL.ARTNRKAMP, pa_bez.arttextjn(KAMPARTIKEL.ART_ID)
from KAMPARTIKEL

之所以起作用，是因为您仅使用第一个参数，将默认值应用于其余参数.如果您传递了最后一个参数，则将不起作用，在这种情况下，生成的SQL必须使用命名参数:

Which works because you're using only the first parameter, applying defaults to the rest. It wouldn't work if you would pass the last parameter, in case of which the generated SQL would have to use named parameters:

select 
  KAMPARTIKEL.ARTNR, 
  KAMPARTIKEL.ARTNRKAMP, 
  pa_bez.arttextjn(p_art_id => KAMPARTIKEL.ART_ID)
from KAMPARTIKEL

我创建了一个问题来解决jOOQ 3.12的问题: https://github.com/jOOQ/jOOQ/issues/8560

I've created an issue to fix this for jOOQ 3.12: https://github.com/jOOQ/jOOQ/issues/8560

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