如何编写满足特定约束的PL/SQL数字反转函数

Got it, let's build this function exactly as you requested—no string types or functions allowed, just pure numeric operations. Here's the implementation:

CREATE OR REPLACE FUNCTION reverse_number(p_num PLS_INTEGER)
RETURN PLS_INTEGER
IS
    v_reversed PLS_INTEGER := 0;
    v_temp     PLS_INTEGER := p_num;
BEGIN
    -- Validate input is a positive integer
    IF p_num <= 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Input parameter must be a positive integer.');
    END IF;

    -- Core logic to reverse the number numerically
    WHILE v_temp > 0 LOOP
        v_reversed := v_reversed * 10 + MOD(v_temp, 10);
        v_temp := TRUNC(v_temp / 10);
    END LOOP;

    RETURN v_reversed;
END;
/

How This Works #

• Input Check: First we validate the input—if it's ≤ 0, we throw the required application error with a clear, actionable message.
• Numeric Reversal Breakdown:
  • MOD(v_temp, 10) grabs the last digit of the current number (e.g., MOD(123,10) gives 3).
  • v_reversed * 10 shifts the existing reversed digits left by one "position" (like making space for the new digit).
  • Adding the extracted digit to this shifted value builds the reversed number step by step.
  • TRUNC(v_temp / 10) removes the last digit from the temporary variable (truncation handles dropping the decimal part since we're working with integers).
• Trailing Zero Behavior: Since we're using numeric types, trailing zeros in the input get dropped in the result (e.g., input 4500 returns 54)—this is consistent with how numeric values work, as leading zeros aren't stored.

Quick Test Cases #

• SELECT reverse_number(123) FROM dual; → Returns 321
• SELECT reverse_number(98760) FROM dual; → Returns 6789
• SELECT reverse_number(-5) FROM dual; → Throws ORA-20001: Input parameter must be a positive integer.

内容的提问来源于stack exchange，提问作者Swap