向Oracle添加许多(UDF)验证功能-哪种方法运行最快 [英] Adding Many (UDFs) Validation Functions to Oracle - Which Method Run Fastest

问题描述

我必须将大约50多个验证功能转移到Oracle中.我正在寻找运行速度最快的方法，但也希望尽可能解决boolean问题.它们的返回对象都必须相同，以便应用程序可以一致的方式对结果做出反应，并警告用户或显示我们可能需要的任何弹出窗口，消息.我为此创建了一个valObj，但是不确定这是否是最好的方法.可以更改返回格式，因为尚未对其做出反应的前端尚未开发.最后，它将包含许多不同的验证功能，包括整数，数字，电话，电子邮件，IPv4，IPv6等.这是我到目前为止所拥有的...

I have to move around 50+ validation functions into Oracle. I'm looking for the approach that runs fastest, but also would like to get around a boolean issue if possible. The return object for them all needs to be the same so that the application can react off the result in a consistent fashion and alert the user or display whatever popups, messages we may need. I created a valObj for this, but not sure yet if that is the best approach. The return format can be changed because the front-end that reacts off of it is not developed yet. In the end it will contain many different validation functions, from integer, number, phone, email, IPv4, IPv6, etc... This is what I have so far...

/***
This is the validation object.
It stores 1 for valid, 0 for not valid and some helper text that can be relayed back to the user.
***/
create or replace type valObj as object (
    result number(1),
    resultText varchar(32000)
);

/***
Coming from ColdFusion this seems clean to me but the function
will end up being a couple thousand lines long.
***/
create or replace function isValid(v in varchar2, format in varchar2)
return valObj
is
  test number;
begin
if format = 'number' then
    begin
        test := to_number(v);
        return valObj(1,null);
        exception when VALUE_ERROR then return valObj(0,'Invalid number. Valid formats are: 12345, 12345.67, -12345, etc...');
    end;
elsif format = 'integer' then
    null; --TO DO
elsif format = 'email' then
    null; --TO DO
elsif format = 'IPv4' then
    null; --TO DO
elsif format = 'IPv6' then
    null; --TO DO
end if;
--dozens of others to follow....
end;
/

/* Example Usage in SQL */
select isValid('blah','number') from dual; -- returns: (0, Invalid number. Valid formats are: 12345, 12345.67, -12345, etc...)
select isValid('blah','number').result from dual; -- returns: 0
select isValid('blah','number').resulttext from dual; -- returns: Valid formats are: 12345, 12345.67, -12345, etc...
select isValid(1234567890.123,'number') from dual; -- returns: 1,{null}
select isValid(1234567890.123,'number').result from dual; -- returns: 1
select isValid(1234567890.123,'number').resulttext from dual; -- returns: {null}

/* Example Usage in PL/SQL */
declare
temp valObj;
begin
    temp := isValid('blah','number');
    if (temp.result = 0) then
        dbms_output.put_line(temp.resulttext);
    else
        dbms_output.put_line('Valid');
    end if;
end;
/

我的问题是:

1. 在PL/SQL中使用它时，我希望能够像这样进行boolean检查:if (temp.result) then，但我不知道一种方法，因为在SQL中无法使用.我应该在valObj上添加第三个布尔属性，还是我不知道的另一种方式?
2. 这些验证功能最终可能在大循环中被调用.知道吗，这是完成这些验证的最有效方法吗?

1. When using it in PL/SQL I would love to be able to do boolean checks instead like this: if (temp.result) then but I can't figure out a way, cause that won't work in SQL. Should I just add a 3rd boolean attribute to the valObj or is there another way I don't know of?
2. These validation functions could end up being called within large loops. Knowing that, is this the most efficient way to accomplish these validations?

我将不胜感激.谢谢！

更新:我忘记了MEMBER FUNCTIONS.感谢@Brian McGinity提醒我.因此，我想使用此方法，因为它可以将type及其functions封装在一起. 此方法与独立功能之间是否会有速度差异?会像独立函数一样编译和存储吗?

UPDATE: I forgot about MEMBER FUNCTIONS. Thanks @Brian McGinity for reminding me. So I'd like to go with this method since it keeps the type and its functions encapsulated together. Would there be any speed difference between this method and a stand-alone function? Would this be compiled and stored the same as a stand-alone function?

create or replace type isValid as object (
    result     number(1),
    resulttext varchar2(32000),
    constructor function isValid(v varchar, format varchar) return self as result );
/

create or replace type body isValid as
    constructor function isValid(v varchar, format varchar) return self as result as
        test number;
    begin
        if format = 'number' then
            begin
                test := to_number(v);
                self.result := 1;
                self.resulttext := null;
                return;
                exception when VALUE_ERROR then
                    self.result := 0;
                    self.resulttext := 'Invalid number. Valid formats are: 12345, 12345.67, -12345, etc...';
                    return;
            end;
        elsif format = 'phone' then
            null; --TO DO
        end if;
        --and many others...
    end;
end;
/

/* Example Usage in SQL */
select isValid('a','number') from dual;

/* Example Usage in PL/SQL */
declare
begin
    if (isValid('a','number').result = 1) then
        null;
    end if;
end;
/

测试结果:

/* Test isValid (the object member function), this took 7 seconds to run */
declare
begin
    for i in 1 .. 2000000 loop
        if (isValid('blah','number').result = 1) then
            null;
        end if;
    end loop;
end;

/* Test isValid2 (the stand-alone function), this took 16 seconds to run */
declare
begin
    for i in 1 .. 2000000 loop
        if (isValid2('blah','number').result = 1) then
            null;
        end if;
    end loop;
end;

isValid和isValid2都执行完全相同的代码，它们只运行此行test := to_number(v);，然后在失败时执行异常并返回结果.这似乎是有效的测试吗?实际上，Object成员函数方法比独立函数要快??

Both isValid and isValid2 do the same exact code, they just run this line test := to_number(v); then do the exception if it fails and return the result. Does this appear to be a valid test? The Object member function method is actually faster than a stand-alone function???

推荐答案

如果将独立功能设置为DETERMINISTIC，并且数据具有高度重复性，则独立功能会更快.在我的机器上，此设置将运行时间从9秒减少到0.1秒.由于某些原因，我不明白设置不能提高对象功能的性能.

The stand-alone function can be much faster if you set it to DETERMINISTIC and if the data is highly repetitive. On my machine this setting decreased run time from 9 seconds to 0.1 seconds. For reasons I don't understand that setting does not improve performance of the object function.

create or replace function isValid2(v in varchar2, format in varchar2)
return valObj
deterministic --<< Hit the turbo button!
is
  test number;
begin
if format = 'number' then
    begin
        test := to_number(v);
        return valObj(1,null);
        exception when VALUE_ERROR then return valObj(0,'Invalid number. Valid formats are: 12345, 12345.67, -12345, etc...');
    end;
end if;
end;
/

这篇关于向Oracle添加许多(UDF)验证功能-哪种方法运行最快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！