Varchar2 & Number 的长度
最近碰到了varchar2和number的限制问题，所以专门去查询了Oracle中对varchar2和number最大值的说明，一查还真发现之前了解的很少。所以专门记载下来，以备今后忘记的时候查询之用。
VARCHAR2 Datatype
You use the VARCHAR2 datatype to store variable-length character data. How the data
is represented internally depends on the database character set. The VARCHAR2
datatype takes a required parameter that specifies a maximum size up to 32767 bytes.
The syntax follows:
VARCHAR2(maximum_size [CHAR | BYTE])
You cannot use a symbolic constant or variable to specify the maximum size; you must
use an integer literal in the range 1 .. 32767.
Small VARCHAR2 variables are optimized for performance, and larger ones are
optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2
that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to
hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes,
PL/SQL preallocates the full declared length of the variable. For example, if you
assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a
VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes
up 1999 bytes.
If you specify the maximum size in bytes rather than characters, a VARCHAR2(n)
variable might be too small to hold n multibyte characters. To avoid this possibility,
use the notation VARCHAR2(n CHAR) so that the variable can hold n characters in the
database character set, even if some of those characters contain multiple bytes. When
you specify the length in characters, the upper limit is still 32767 bytes. So for
double-byte and multibyte character sets, you can only specify 1/2 or 1/3 as many
characters as with a single-byte character set.
Although PL/SQL character variables can be relatively long,
you cannot insert
VARCHAR2 values longer than 4000 bytes into a VARCHAR2 database column
.
You can insert any VARCHAR2(n) value into a LONG database column because the
maximum width of a LONG column is 2147483648 bytes or two gigabytes. However,
you cannot retrieve a value longer than 32767 bytes from a LONG column into a
VARCHAR2(n) variable. Note that the LONG datatype is supported only for backward
compatibility; see "LONG and LONG RAW Datatypes" on page 3-5 more information.
When you do not use the CHAR or BYTE qualifiers, the default is determined by the
setting of the NLS_LENGTH_SEMANTICS initialization parameter. When a PL/SQL
procedure is compiled, the setting of this parameter is recorded, so that the same
setting is used when the procedure is recompiled after being invalidated.
----《PLSQL User's Guide and Reference》
由此可知，varchar2类型的数据，在PLSQL中的最大长度为32767个字符，而在SQL表中的最大长度为4000个字符。
在PLSQL中如果varchar2长度在2000字符以上，Oracle内部会使用不同的存储方法。
NUMBER Datatype
The NUMBER datatype reliably stores fixed-point or floating-point numbers with
absolute values in the range 1E-130 up to (but not including) 1.0E126. A NUMBER
variable can also represent 0. See Example 2–1 on page 2-5.
Oracle recommends only using the value of a NUMBER literal or result of a NUMBER
computation that falls within the specified range.
■ If the value of the literal or a NUMBER computation is smaller than the range, the
value is rounded to zero.
■ If the value of the literal exceeds the upper limit, a compilation error is raised.
■ If the value of a NUMBER computation exceeds the upper limit, the result is
undefined and leads to unreliable results and errors.
The syntax of a NUMBER datatype is:
NUMBER[(precision,scale)]
Precision is the total number of digits and scale is the number of digits to the right of
the decimal point. You cannot use constants or variables to specify precision and scale;
you must use integer literals.
To declare fixed-point numbers, for which you must specify scale, use the following
form that includes both precision and scale:
NUMBER(precision,scale)
To declare floating-point numbers, for which you cannot specify precision or scale
because the decimal point can float to any position, use the following form without
precision and scale:
NUMBER
To declare integers, which have no decimal point, use this form with precision only:
NUMBER(precision) -- same as NUMBER(precision,0)
The maximum precision that can be specified for a NUMBER value is 38 decimal digits.
If you do not specify precision, it defaults to 39 or 40, or the maximum supported by
your system, whichever is less.
Scale, which can range from -84 to 127, determines where rounding occurs. For
instance, a scale of 2 rounds to the nearest hundredth (3.4562 becomes 3.46). A
negative scale rounds to the left of the decimal point. For example, a scale of -3 rounds
to the nearest thousand (34562 becomes 34000). A scale of 0 rounds to the nearest
whole number (3.4562 becomes 3). If you do not specify scale, it defaults to 0, as shown
in the following example.
DECLARE
x NUMBER(3);
BEGIN
x := 123.89;
DBMS_OUTPUT.PUT_LINE('The value of x is ' || TO_CHAR(x));
END;
/
The output is: The value of x is 124
For more information on the NUMBER datatype, see Oracle Database SQL Reference.
----《PLSQL User's Guide and Reference》
NUMBER Datatype
The NUMBER datatype stores zero as well as positive and negative fixed numbers with
absolute values from 1.0 x 10^-130 to (but not including) 1.0 x 10^126. If you specify an
arithmetic expression whose value has an absolute value greater than or equal to 1.0 x
10^126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes.
Specify a fixed-point number using the following form:
NUMBER(p,s)
where:
■ p is the precision, or the total number of significant decimal digits, where the most
significant digit is the left-most nonzero digit, and the least significant digit is the
right-most known digit. Oracle guarantees the portability of numbers with
precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits
depending on the position of the decimal point.
■ s is the scale, or the number of digits from the decimal point to the least
significant digit. The scale can range from -84 to 127.
– Positive scale is the number of significant digits to the right of the decimal
point to and including the least significant digit.
– Negative scale is the number of significant digits to the left of the decimal
point, to but not including the least significant digit. For negative scale the
least significant digit is on the left side of the decimal point, because the actual
data is rounded to the specified number of places to the left of the decimal
point. For example, a specification of (10,-2) means to round to hundreds.
Scale can be greater than precision, most commonly when e notation is used. When
scale is greater than precision, the precision specifies the maximum number of
significant digits to the right of the decimal point. For example, a column defined as
See Also: "Datatype Comparison Rules" on page 2-37 for
information on comparison semantics
Datatypes
Basic Elements of Oracle SQL 2-11
NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all
values past the fifth digit after the decimal point.
It is good practice to specify the scale and precision of a fixed-point number column
for extra integrity checking on input. Specifying scale and precision does not force all
values to a fixed length. If a value exceeds the precision, then Oracle returns an error. If
a value exceeds the scale, then Oracle rounds it.
Specify an integer using the following form:
NUMBER(p)
This represents a fixed-point number with precision p and scale 0 and is equivalent to
NUMBER(p,0).
Specify a floating-point number using the following form:
NUMBER
The absence of precision and scale designators specifies the maximum range and
precision for an Oracle number.
----《SQL Reference》
number型的范围是绝对值在10^-130 和 10^126 之间，在精度设置中，即NUMBER(p,s)中的p最大为38，如果不写
则取操作系统的最大精度，一般是39或者40
具体在NUMBER操作时要注意下面几点：
1、p、s都为正时：
当p>s>0时：
小数点后面是几位都不要紧，会自动把s位之后的位数舍去
但是要注意小数点前的位数，不能超过p-s的值！因为s的精度无论有没有具体数值，都会被计算
当s>p时：
小数点前必然是0
小数点后必须至少要有s-p位的0，这样才能保证小数点后位数比有效位数多
2、当s为负时：
表示|s|位小数点左边的位数数字也将被舍弃
讲完！