PLSQL中的REAL数据类型 [英] REAL datatype in PLSQL

问题描述

SET SERVEROUTPUT ON;
DECLARE
A REAL := 123456789123456789123456789123456789123456789123456789123456789123456789;
BEGIN
DBMS_OUTPUT.PUT_LINE('A Value is : ' || A);
END;

上面的程序运行并给了我如下输出.

The above program ran and gave me the output as below.

anonymous block completed
A Value is : 123456789123456789123456789123456789123500000000000000000000000000000000

有人可以解释为什么它在第40位数字之后显示0而不是实际值.而且第40位数字也从5代替4.

Can someone please explain why it is showing 0 instead of actual values after 40th digit. And also 40th digit as 5 instead of 4.

推荐答案

The SQL language reference says "The REAL data type is a floating-point number with a binary precision of 63, or 18 decimal", and it's shown as FLOAT(63). And FLOAT [(p)] is:

具有精度p的NUMBER数据类型的子类型. FLOAT值在内部以NUMBER表示.精度p的范围可以是1到126个二进制数字. FLOAT值需要1到22个字节.

A subtype of the NUMBER data type having precision p. A FLOAT value is represented internally as NUMBER. The precision p can range from 1 to 126 binary digits. A FLOAT value requires from 1 to 22 bytes.

如果使用REAL列创建表，则其行为类似于FLOAT(63):

If you create a table with a REAL column is behaves like a FLOAT(63):

create table t42 (a real, b float(126), c float(63), d number);
insert into t42 (a, b, c, d)
values (123456789123456789123456789123456789123456789123456,
  123456789123456789123456789123456789123456789123456,
  123456789123456789123456789123456789123456789123456,
  123456789123456789123456789123456789123456789123456);

select a, b, c, d from t42;

                                                               A
----------------------------------------------------------------
                                                               B
----------------------------------------------------------------
                                                               C
----------------------------------------------------------------
                                                               D
----------------------------------------------------------------
             123456789123456789100000000000000000000000000000000 
             123456789123456789123456789123456789120000000000000 
             123456789123456789100000000000000000000000000000000 
             123456789123456789123456789123456789123000000000000

我使用了一个较小的值，因此可以在numformat的SQL * Plus/SQL Developer限制为49位数字的范围内显示.请注意，FLOAT(126)和NUMBER值与该值并不完全相同.

I've used a smaller value so it can be displayed within the SQL*Plus/SQL Developer limit of 49 digits for numformat. Notice that the FLOAT(126) and NUMBER values are not quite the same with that value.

PL/SQL稍有不同.在标准包装中，您可以看到:

PL/SQL is slightly different. In the standard package you can see:

  type NUMBER is NUMBER_BASE;
  subtype FLOAT is NUMBER; -- NUMBER(126)
  subtype REAL is FLOAT; -- FLOAT(63)

在PL/SQL块中，您的REAL变量可以采用不受限制的NUMBER可以具有相同缩放/精度效果的任何值；在这种情况下，仅保留最重要的(38-40) 位，然后将其余部分四舍五入到前40位中的最小位.您的值的整体大小"(以72位数字表示)得以保留，但您失去的精度超出了Oracle内部格式所能存储的范围.如果您具有与表示例相同的变量类型，并将原始值放在以下位置:

In a PL/SQL block your REAL variable can take any value that an unrestricted NUMBER can and has the same scale/precision effects; in this case only preserving the most significant (38-40) digits, and rounding the rest into the least of those first 40 digits. The overall 'size' of your value, as a 72-digit number, is preserved, but you lose the precision beyond what can be stored in Oracle's internal format. If you have the same variable types as the table example and put your original values in:

DECLARE
  A REAL := 123456789123456789123456789123456789123456789123456789123456789123456789;
  B FLOAT(126) := 123456789123456789123456789123456789123456789123456789123456789123456789;
  c FLOAT(63) := 123456789123456789123456789123456789123456789123456789123456789123456789;
  D NUMBER := 123456789123456789123456789123456789123456789123456789123456789123456789;
BEGIN
  DBMS_OUTPUT.PUT_LINE('A Value is : ' || A);
  DBMS_OUTPUT.PUT_LINE('B Value is : ' || B);
  DBMS_OUTPUT.PUT_LINE('C Value is : ' || C);
  DBMS_OUTPUT.PUT_LINE('D Value is : ' || D);
END;
/

A Value is : 123456789123456789123456789123456789123500000000000000000000000000000000
B Value is : 123456789123456789123456789123456789120000000000000000000000000000000000
C Value is : 123456789123456789100000000000000000000000000000000000000000000000000000
D Value is : 123456789123456789123456789123456789123500000000000000000000000000000000

这一次请注意，不受限制的FLOAT和NUMBER显示相同的值，而受限制的FLOAT具有您期望的精度.

Notice this time that the unrestricted FLOAT and NUMBER show the same value, while the restricted FLOATs have the precision you expect.

因此，它在第40位数字之后显示零，并且第40位数字是5而不是4，因为您已经超出了精度，并且该值已四舍五入到最高有效数字. SQL REAL数据类型的精度为63个二进制或18个十进制数字.但除非指定，否则PL/SQL REAL匹配NUMBER.

So it is showing zeros after the 40th digit and that 40th digit is 5 instead of 4 because you've exceeded the precision and the value is being rounded to the most significant digits. The SQL REAL data type has precision of 63 binary or 18 decimal digits; but unless specified a PL/SQL REAL matches NUMBER.

这篇关于PLSQL中的REAL数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！