PLSQL过程逻辑 [英] PLSQL Procedural Logic

问题描述

如何编写以下PL/SQL函数:

How to write a PL/SQL function that:

1. 仅使用数字数据类型和函数-不使用VARCHAR2，CHAR，CLOB，XML等. 换句话说，您的解决方案中将不使用字符/字符串功能，例如REVERSE，SUBSTR等.
2. 接受PLS_INTEGER参数
3. 如果提供的值小于或等于零，则会引发应用程序错误-20001并提供正确的错误消息
4. 以相反的顺序返回PLS_INTEGER值，该值具有输入参数中的数字.如果输入值以一个或多个零结尾，那么这些零将不会出现在返回的数值中，因为它们将是前导零.

1. Uses only numeric datatypes and functions-no VARCHAR2, CHAR, CLOB, XML, etc. " In other words, character/string functions such as REVERSE, SUBSTR, etc are not to be used in your solution.
2. Accepts a PLS_INTEGER parameter
3. If the provided value is less than or equal to zero throws application error -20001 and provides a good error message
4. Returns a PLS_INTEGER value that has the digits from the input parameter in reverse order. If the input value ends in one or more zeroes those zeroes will not appear in the returned numeric value, since they would be leading zeroes.

这是我到目前为止所写的内容:

Here is what I have written so far:

create or replace function test_reverse
    (p_input in pls_integer) 
    return pls_integer 
is
    p_num pls_integer := 0; 
    p_in_num pls_integer := 0; 
    p_out_num pls_integer := 0; 
begin 
    p_num := p_input; 
    loop 
        exit when p_num p_in_num := mod(p_num,10); 
        p_out_num := (p_out_num * 10) + p_in_num;
        p_num := trunc(p_num / 10); 
    end loop; 
    return p_out_num; 
end; 

推荐答案

问题是将1234变成4321.string-y解决方案(可以使用未公开的内置文件)本身就是简单性:to_number(reverse(to_char(1234))).坚持数字数据类型比较麻烦.我的解决方案非常程序化:毫无疑问，存在更优雅的解决方案.

The problem is to turn 1234 into 4321. The string-y solution (admittedly using an undocumented built-in) is simplicity itself: to_number(reverse(to_char(1234))). Sticking to numeric datatypes is more cumbersome. My solution is very procedural: undoubtedly more elegant solutions exist.

无论如何，要将1234转换为4321，我们需要生成1 + 20 + 300 +4000.我的解决方案依次隔离每个值并乘以10的适当幂.为了隔离这些值，我们使用带负值的trunc().这会将数字四舍五入到小数点左边.因此，trunc(1234,-3)产生1000.要将其转换为所需值，我们将乘以10乘以负三的幂.因此1000 * 10(-3) = 1.

Anyway, to turn 1234 into 4321 we need to generate 1 + 20 + 300 + 4000. My solution isolates each value in turn and multiplies by the appropriate power of ten. To isolate the values we use trunc() with a negative value. This rounds down the number to the left of the decimal point. Thus, trunc(1234,-3) produces 1000. To convert this into the required value we multiply by ten to the power of minus three. Thus 1000 * 10(-3) = 1.

该函数遍历数字.将1000转换为1后，我们计算出余数1234 - 1000 = 234.因此，现在我们需要隔离200并将其转换为20.即trunc(234, -2)和power(200, -1).因此，我们可以将偏移量按trunc()递减1，将指数按power()递增2.

The function iterates through the numbers. Having converted 1000 to 1 we calculate the remainder, 1234 - 1000 = 234. So now we need to isolate the 200 and convert it to 20; that is trunc(234, -2) and power(200, -1). So we can the offset fed into trunc() decrements by 1 and the exponent fed into power() increments by 2.

这是一个工作功能(非常松散地基于您发布的功能):

Here is a working function (very loosely based on the one you posted):

create or replace function test_reverse
    (p_input in pls_integer) 
    return pls_integer 
is
    p_out_num pls_integer := 0;
    offset    pls_integer;
    tgt       pls_integer;
    rmdr      pls_integer;
    exp       pls_integer;
begin 
    rmdr := p_input;
    offset := length(p_input)-1;
    exp := -offset;
    loop 
        tgt := trunc(rmdr, -offset);
        p_out_num := p_out_num + (tgt * power(10, exp));
        exp := exp + 2;
        rmdr := rmdr - tgt;
        exit when offset = 0;
        offset := offset-1;
    end loop; 
    return p_out_num; 
end test_reverse; 
/

这是一个LiveSQL演示(需要免费的Oracle Technet帐户，可惜).这没有参数验证的功能，但是很简单:

Here is a LiveSQL demo (free Oracle Technet account required, alas). That doesn't feature the parameter validation but it is straightforward enough:

create or replace function test_reverse
    (p_input in pls_integer) 
    return pls_integer 
is
    p_out_num pls_integer := 0;
    offset    pls_integer;
    tgt       pls_integer;
    rmdr      pls_integer;
    exp       pls_integer;
begin 
    if p_input <= 0 then
         raise_application_error(-20001
              'invalid input: ' || || ' must be greater than zero.'
         );
    end if; 
    rmdr := p_input;
    ....

这篇关于PLSQL过程逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！