sql - 如何将带有十进制值的 NUMBER 作为参数传递给 FUNCTION 或 PROCEDURE

Question

我正在创建一些过程和函数来简化插入、删除或更新行的任务。
我的问题可能听起来很愚蠢，因为肯定有我遗漏的东西。

每当我尝试将十进制值作为参数传递给过程或函数时，我都会收到错误消息。

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

下一个代码不起作用，而是一种显示问题的方法。

CREATE OR REPLACE FUNCTION test1(num1 NUMBER)
    RETURN NUMBER IS
        BEGIN
        RETURN num1;
        END;
/
SET SERVEROUTPUT ON;
DECLARE
    numVar NUMBER;
BEGIN
numVar:= 2.1;
DBMS_OUTPUT.PUT_LINE('Raw Number: '||numVar);

                      /***********Calling test1*************/
            DBMS_OUTPUT.PUT_LINE('Function Number: '|| test1(2.1));

END;
/

一些输出是西类牙语，但大部分是英语。

Function TEST1 compilado

Raw Number: 2,1


Error que empieza en la línea: 8 del comando :
DECLARE
    numVar NUMBER;
BEGIN
numVar:= 2.1;
DBMS_OUTPUT.PUT_LINE('Raw Number: '||numVar);
    DBMS_OUTPUT.PUT_LINE('Function Number: '|| test1(2.1));

END;
Informe de error -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 6
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

当我调用函数时，没有十进制参数，我不会遇到任何麻烦 .

          /***********Calling test1*************/
DBMS_OUTPUT.PUT_LINE('Function Number: '|| test1(2));

输出

Function TEST1 compilado

Raw Number: 2,1
Function Number: 2

如果我传递一个变量而不是原始十进制数，我也不会遇到任何问题 我实际上可以使用十进制数，但创建一个插入数据的过程对我来说意义不大，该过程需要定义一个变量以将其作为参数传递

         /***********Calling test1*************/
DBMS_OUTPUT.PUT_LINE('Function Number: '|| test1(numVar));

输出

Function TEST1 compilado
Raw Number: 2,1
Function Number: 2,1

我想了解为什么我不能将原始十进制数作为参数传递，也许我不太了解 Oracle 在将数据作为参数引入之前转换数据的方式。

Answer 1

这个问题似乎是 Oracle SQL Developer 特有的。

我有 Oracle 11.2.0.3.0 64 位 ，当我在 中测试它时蟾蜍 12.0.0.61 一切正常，但不是在 Oracle SQL Developer 17.2.0.188 .

我是如何测试的:

首先，创建过程:

CREATE OR REPLACE FUNCTION test_function(x$n in NUMBER)
RETURN NUMBER IS
BEGIN  
    RETURN x$n;
END;

在 TOAD 中测试

测试 #1，使用 . :

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';

DECLARE
    numVar$n NUMBER;
BEGIN
    numVar$n:= 2.1;
    DBMS_OUTPUT.PUT_LINE('Raw Number: '||numVar$n);
    DBMS_OUTPUT.PUT_LINE('Function Number: '||test_function(2.1));
END;

输出:

Raw Number: 2.1
Function Number: 2.1

测试 #2，使用 , :

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

DECLARE
    numVar$n NUMBER;
BEGIN
    numVar$n:= 2.1;
    DBMS_OUTPUT.PUT_LINE('Raw Number: '||numVar$n);
    DBMS_OUTPUT.PUT_LINE('Function Number: '||test_function(2.1));
END;

输出:

Raw Number: 2,1
Function Number: 2,1

在 Oracle SQL Developer 中测试

在 Oracle SQL 开发人员 测试 #1 工作正常，但测试 #2 引发 ORA-06502 - 这是你的情况。

解决方案 #1 :使用 to_number('2,1')而不是 2.1 :

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

DECLARE
    numVar$n NUMBER;
BEGIN
    numVar$n:= 2.1;
    DBMS_OUTPUT.PUT_LINE('Raw Number: '||numVar$n);
    DBMS_OUTPUT.PUT_LINE('Function Number: '||test_function(to_number('2,1')));
END;

输出:

Raw Number: 2,1
Function Number: 2,1

解决方案#2 :更改 NLS_NUMERIC_CHARACTERS执行期间:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

DECLARE
    numVar$n NUMBER;
    nnc$c VARCHAR2(255);
BEGIN
    numVar$n:= 2.1;

    SELECT value
    INTO   nnc$c
    FROM   nls_session_parameters
    WHERE  parameter = 'NLS_NUMERIC_CHARACTERS';

    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS=''.,''';

    DBMS_OUTPUT.PUT_LINE('Raw Number: '||numVar$n);
    DBMS_OUTPUT.PUT_LINE('Function Number: '||test_function(2.1));

    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='''||nnc$c||'''';
END;

输出:

Raw Number: 2.1
Function Number: 2.1