Oracle 如何在纯PL/SQL中验证数字的精度和规模

你好，
我遇到了一个问题，以验证用户提供的数字是否适合表中的列。在PL/SQL中进行的检查是验证精度和规模是否足以满足所提供的数字。
你有什么建议来实现这一点？
下面我的努力。


我在文档中找不到它，但是我发现PL/SQL既不允许VALIDATE_CONVERSION也不允许强制转换函数中的数据类型限制，因此以下操作失败:

DECLARE 
  tn NUMBER;
BEGIN
  tn := CAST( '123.456' AS NUMBER( 6, 2 ));
  DBMS_OUTPUT.PUT_Line( tn);
END;

SQL确实允许数据类型限制在那里，所以解决方案可能是这样的:

execute IMMEDIATE 'SELECT CAST(:p_test_num AS NUMBER(:p_precision,:p_scale)) from dual' INTO ...

但是，检查要批量完成 (加载文本文件)，因此上下文切换将成为问题。
我可以使用PL/SQL像:

set serveroutput ON SIZE UNLIMITED

CREATE OR REPLACE PROCEDURE P(p_test_num IN VARCHAR2, p_precision IN PLS_INTEGER, p_scale IN PLS_INTEGER, p_format IN VARCHAR2 DEFAULT NULL)
IS
   l_result PLS_INTEGER;  
   -- -1 - the number will fit but with rounding
   --  0 - the number will not fit
   --  1 - the number will fit perfectly 
BEGIN 
execute IMMEDIATE 'declare
  l_test_num NUMBER('||p_precision||', '||p_scale||');
begin
  if :test_format is null then
     l_test_num := to_number(:test_num);
  else
     l_test_num := to_number(:test_num, :test_format);
  end if;
  if l_test_num = :test_num 
  then :result := 1;
  else :result := -1;
  end if;
exception
   when value_error then :result := 0;
   when others then raise; 
end;' USING IN p_format, IN p_test_num, OUT l_result;
   
   DBMS_OUTPUT.PUT_Line( l_result);
END;


exec p('21474.83647',38,1)
exec p('2147483647' ,38,1)
exec p('2147483647' ,38,127)
exec p('21474.83647',38,1,'99999D99')
exec p('21474.83647' ,38,1,'99999D99999')
exec p('21474.83647' ,38,1,'99999999D99999999')
exec p('21474.83647' ,38,12,'99999999D99999999')
exec p('2147483647' ,38,127,'99999999999')

...但是我觉得这可以做得更好...

您可以声明所需比例和精度的子类型，并强制转换为这些子类型。

如果超出精度，这将引发异常，但不超出比例，因为Oracle数据库会对这些进行循环。所以你必须检查输入等于结果。

declare 
  tn varchar2(10) := '9.91';
  subtype n is number(2,1);
begin
  case
    when tn = cast ( tn as n ) then 
      null;
    else
      raise_application_error ( -20001, 'rounding' );
  end case;
end;
/

ORA-20001: rounding

您还可以将子类型作为列 % 类型取消，将它们绑定到基础列的数据类型:

create table t (
  c1 number ( 2, 1 )
);

declare 
  tn varchar2(10) := '9.91';
  subtype n is t.c1%type;
begin
  case
    when tn = cast ( tn as n ) then 
      null;
    else
      raise_application_error ( -20001, 'rounding' );
  end case;
end;
/

ORA-20001: rounding

你不能动态传递子类型虽然。所以要做到这一点在一个，你需要传递你正在检查的列，并强制转换到适当的子类型:

drop table t 
  cascade constraints purge;
create table t (
  c1 number ( 2, 1 ), c2 number ( 3, 2 )
);

create or replace procedure check_num_limits ( 
  val varchar2, col varchar2
) as
  subtype t1 is t.c1%type;
  subtype t2 is t.c2%type;
begin
  case
    when col = 'C1' and val = cast ( val as t1 ) then 
      null;
    when col = 'C2' and val = cast ( val as t2 ) then 
      null;
    else
      raise_application_error ( -20001, 'rounding' );
  end case;
end check_num_limits;
/

exec check_num_limits ( '9.91', 'C1' );

ORA-20001: rounding

exec check_num_limits ( '9.91', 'C2' );
exec check_num_limits ( '9.991', 'C2' );

ORA-20001: rounding