Re: How to write a function with variable parameters?

Dear all,

Replies are embedded.

On Sat, 02 Jun 2001 16:56:12 GMT, "Paul Q" <paulq_at_home.com> wrote:

>create or replace function sf_case (case_1 element_type, case_2 element
>type)
>return var_name element_type;
>is
>begin
>....
>
>return(var_name);
>end;
>

What do you mean by this? Is this a user function with variable number of parameters?

>"Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message
>news:dpehht06m4vpvhj6nhihqdrna629aur9r2_at_4ax.com...
>> Dear all,
>>
>> To similate the Oracle 8i CASE WHEN ... THEN ... ELSE ... END clause
>> in Oracle 8, I need to write a stored function like this:
>> FUNCTION sf_case(case1, case2, case3, ..., case-n, ...) RETURN NUMBER
>> where case-n's are all of boolean type.
>> This function will check case1 first, then case2, case3, etc., and
>> return the n that first evaluates to true.
>>
>> An extended edition is like this:
>> FUNCTION sf_case_ex(case1, value1, case2, value2, case3, value3, ...)
>> RETURN value_type
>> where value-n's are the corresponding return values.
>>
>> These two functions will compansate the limited DECODE() built-in
>> function in Oracle 8. I cannot figure out how at the moment, I hope
>> someone can help me with this. Thanks in advance.
>>
>> Dino
>>
>

I read posts in an earlier thread of this news group, and realized that variable number of parameters can be implementd in this way: FUNCTION sf_case(i_case1 IN BOOLEAN DEFAULT FALSE, i_case2 IN BOOLEAN DEFAULT FALSE) RETURN NUMBER;
with the rules:
1.the missing parameters should be trailing. 2.a max number of parameters, say 10 parameters, must be known in advance. The max number of parameters cannot be unknown as do in other 3G languages such as C and PASCAL.

Another problem of the sf_case user function is when the BOOLEAN actual parameters are logical expressions, SQL*PLUS doesn't seem to evaluate the expressions before pass them to PL/SQL engine. Therefore, the sf_case and sf_case_ex user functions cannot be implemented. In this case, a more specific one should be used instead: FUNCTION sf_group(i_subject IN NUMBER, i_test1 IN NUMBER DEFAULT 0, i_test2 IN NUMBER DEFAULT 2) RETURN NUMBER IS

    v_result NUMBER;
BEGIN
    IF i_subject<=i_test1 THEN

        v_result := 1;
    ELSIF i_subject<i_test2 THEN

        v_result := 2;
    ELSE
        v_result := 0;
    ENDIF;
    RETURN v_result;
END; Anyone more comments? Thanks again.

Dino Received on Sat Jun 02 2001 - 19:57:46 CDT