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|位小數點左邊的位數數字也將被舍棄
???
?
?
講完！
?
?