Number data type of SQL, PL/SQL

The number data type is widely used in Oracle. It can store zero-value, positive-negative, and fixed-length data. There are several concepts for this data type to be clarified, otherwise it is easy to confuse, the following is a detailed description.

 

1. The range and storage space can be expressed.
From 1.0x10-130 to 1.0x10126 (not included). If the expression or value is greater than 1.0x10126, Oracle returns an error message.
The required storage space is 1 to 22 bytes.
 
2. Number Type Representation
Number (P, S) P and S (optional)

Precision indicates the total length of the number, and scale indicates the number of decimal places.
Precision is also called precision, which is the total number of digits in the index. By default, the precision is 38 BITs and the value range is 1 ~ Between 38.
Scale is the number of decimal places. The value ranges from-84 to 127, which determines the Rounding Rule. If the scale value is not specified, the default value is 0.
You cannot use constants or variables to specify the length and accuracy of a number. The maximum length of the number type is 38 characters.
If the maximum length of the number type is not specified, the default length or the maximum length supported by the system is used.
Precision and decimal places do not affect how data is stored on the disk, but only what values are allowed and how the values are rounded (round ).

For example, the precision of 123.45 is 5, and the number of decimal places is 2.

Analysis of P and S
P> 0. Analysis of S in two cases:

A. S> 0
Accurate to the second place to the right of the decimal point, and rounded. Then, check whether the valid digit is <= P; if S> P, at least S-p is filled with 0 at the right of the decimal point.

B. S <0
It is accurate to the second place on the left of the decimal point, and rounded up. Then, check whether the valid digit is <= P + | S |

(Valid digit: starts from the first digit not 0 on the left)

Precision is not considered for floating point numbers.

C. Integer
If the value of S is omitted, it is equivalent to s equal to 0, indicating an integer.

Number (P) is equivalent to number (p, 0)

C. Float Type
If both P and S are omitted, the data that can be expressed currently is a floating point type, which can store positive and negative numbers, zero values, floating point numbers, and so on.
Example:
Value datatype stored value
123.2564 number 123.2564
1234.9876 number (1234.99)
12345.12345 number () Error
1234.9876 number (6) 1235
12345.345 number (5,-2) 12300
1234567 number (5,-2) 1234600
Error 12345678 number (5,-2)
123456789 number (5,-4) 123460000
1234567890 number (5,-4) Error
12345.58 number (*, 1) 12345.6
0.1 number () Error
0.01234567 number (0.01235)
0.09999 number (0.09999)
0.099996 number () Error

3. Example

A. Precision is used to ensure data integrity. Scott @ cnmmbo> Create Table T (Num number (5); Scott @ cnmmbo> insert into T select 12345 from dual; scott @ cnmmbo> insert into T select 123456 from dual; --> error message, beyond the precision range insert into T select 123456 from dual --> precision is 5, the actual data bit has 6 * error at line 1: ORA-01438: value larger than specified precision allowed for this columnb, use scale Scott @ cnmmbo> truncate table t; scott @ cnmmbo> alter t Able t modify (Num number (5, 2); Scott @ cnmmbo> alter table t add num_msg varchar2 (12); Scott @ cnmmbo> DESC t; Name null? Type ------------------------- -------- ------------- num number (5, 2) num_msg varchar2 (12) Scott @ cnmmbo> insert into T select 123.45, '2017. 45' from dual; Scott @ cnmmbo> insert into T select 123.456, '2017. 456 'from dual; --> the number is rounded to Scott @ cnmmbo> select * from t; num num_msg ---------- ------------ 123.45 123.45 123.46 123.456scott @ cnmmbo> insert into T select 1234, '000000' from dual; --> the same error is returned. The error message "insert into T select 1234, '000000' from dual --> here, 1234 is not 1234, oracle converts to 1234.00 * According to the definition of this column --> because two decimal places are specified, only three digits can last to the left of the decimal point, and two digits error at line 1: ORA-01438 on the right: value larger than specified precision allowed for this columnc, Scott @ cnmmbo> truncate table t; --> clear the previous data Scott @ cnmmbo> alter table t modify (Num number (5,-2 )); --> modify the scale of a column to negative Scott @ cnmmbo> DESC t name null? Type ------------------- -------- ---------------- num_msg varchar2 (12) num number (5,-2) Scott @ cnmmbo> insert into T select '100. 45', 12345 from dual; Scott @ cnmmbo> insert into T select '2017. 45', 123.45 from dual; Scott @ cnmmbo> insert into T select '2017. 456 ', 123.456 from dual; Scott @ cnmmbo> select * from t; num_msg num ------------ 123.45 12300 --> the input 12345 is an integer, that is, 12345.00, 45 before decimal point is evicted 123.45 100- -> The input value is 123.45, and the result is 100123.456 100 because the scale value is-987. --> same as Scott @ cnmmbo> insert into T select '100. 65', 987.65 from dual; Scott @ cnmmbo> select * from t; num_msg num ------------ 123.45 12300123.45 100123.456 100987.65 --> If the progress is not exceeded, generate carry Scott @ cnmmbo> insert into T select '000000', 98765432 from dual; --> beyond precision insert into T select '000000', 98765432 from dual * error at line 1: ORA-01438: Value Larger than specified precision allowed for this columnd, maximum and minimum Scott @ cnmmbo> truncate table t; Scott @ cnmmbo> alter table t modify (Num number ); scott @ cnmmbo> insert into T select 'max _ value', power (10,126)-1 from dual; insert into T select 'max _ value', power (10,126) -1 from dual * error at line 1: ORA-01426: Numeric overflowscott @ cnmmbo> insert into T select 'max _ value', power (10,125) from dual; 10 to the power of 125 can Scott @ cnmmbo> insert into T select 'min _ value', power (10,-130) from dual; Scott @ cnmmbo> select * from T; num_msg num ------------ ---------- max_value 1.000e + 125min_value 1.000e-130 --> as shown in the test above, number is used for sequence, the root does not need to worry about insufficient sequence. D. Calculate the length of the number column. Scott @ cnmmbo> drop table t purge; Table dropped. scott @ cnmmbo> Create Table T (L number, M number); table created. --> disk space occupied by a number using vsize Scott @ cnmmbo> insert Into T (l) Select to_number (rpad ('9', rownum * 2, '9') from dba_objects 2 where rownum <= 12; 12 rows created. scott @ cnmmbo> Update t set M = L + 1; 12 rows updated. scott @ cnmmbo> set numformat 99999999999999999999999999999scott @ cnmmbo> column V1 format 99scott @ cnmmbo> column V2 format 99scott @ cnmmbo> select L, M, vsize (l) V1, vsize (m) v2 from t order by L; l m V1 V2 --------------------------------------------- --------------- --- 99 100 2 2 9999 10000 3 2 999999 1000000 4 2 99999999 100000000 5 2 9999999999 6 2 10000000000 999999999999 7 2 1000000000000 99999999999999 8 2 100000000000000 9999999999999999 9 2 10000000000000000 999999999999999999 10 2 1000000000000000000 100000000000000000000 11 2 9999999999999999999999 10000000000000000000000 12 2 999999999999999999999999 1000000 000000000000000000 13 2 --> as the value of column L increases, the storage space consumed by column L increases linearly. --> For column M, the storage space used by the column M remains unchanged --> as shown in the preceding figure, the larger the value is, the more storage space is consumed. Oracle only stores valid numbers, indexes at the specified decimal point, and Symbol Information of numeric values.

4. More references

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % found)

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan