SQL, PL/SQL 之NUMBER数据类型

    NUMBER数据类型在Oracle中使用的较为广泛,可以存储零值,正负数,以及定长数,对于这个数据类型有个几个概念要搞清,否则容易搞混,下面给出具体描述。

 

1、可表示范围及存储空间
    从1.0 x 10-130 到 1.0 x 10126(不包括),如果表达式或值大于1.0 x 10126,Oracle会返回错误信息
    所需的存储空间为1到22个字节
 
2、Number类型表示法
    NUMBER(p,s)   P 和S 可选

    其中precision表示数字的总长度,scale代表可以有几位小数。
    precision也叫精度,是指数中的总数字个数,默认情况下,精度为38 位,取值范围是1~38 之间。
    scale是小数位数,即数中小数点右边的数字个数。其范围从-84到127,能够决定舍入规则。如果我们不指定scale的值,默认就为0。
    不可以用常量或变量指定NUMBER的长度和精度。NUMBER类型最大的长度是38位。
    如果不指定NUMBER类型的最大长度,就会采用默认长度或是使用系统所支持的最大长度。
    精度和小数位数不会影响数据在磁盘上如何存储,而只会影响允许有哪些值以及数值如何舍入(round)。

    例如,数 123.45 的精度是 5,小数位数是 2。
  
    下面对p和s进行分析
    p>0,对s分2种情况分析:
  
  a. s>0
    精确到小数点右边s位,并四舍五入。然后检验有效数位是否<=p;如果s>p,小数点右边至少有s-p个0填充。
  
  b. s<0
    精确到小数点左边s位,并四舍五入。然后检验有效数位是否<=p+|s|
  
    (有效数位:从左边第一个不为0的数算起)
  
    对于浮点数则不考虑精度问题
  
  c、表示整数
    当s的值被省略时,即等同于s等于0,表示整数
  
    NUMBER(p) 等同于NUMBER(p,0)
  
  c、浮点型
    当p和s都被省略,则当前可表示的数据为浮点型,可以存储正负数、零值、浮点数等
    示例:
    Value       Datatype       Stored Value
    123.2564    NUMBER         123.2564
    1234.9876   NUMBER(6,2)    1234.99              
    12345.12345 NUMBER(6,2)    Error                
    1234.9876   NUMBER(6)      1235                 
    12345.345   NUMBER(5,-2)   12300                
    1234567     NUMBER(5,-2)   1234600              
    12345678    NUMBER(5,-2)   Error                
    123456789   NUMBER(5,-4)   123460000            
    1234567890  NUMBER(5,-4)   Error                
    12345.58    NUMBER(*, 1)   12345.6              
    0.1         NUMBER(4,5)    Error                
    0.01234567  NUMBER(4,5)    0.01235              
    0.09999     NUMBER(4,5)    0.09999              
    0.099996    NUMBER(4,5)    Error

3、示例

a、使用精度(precision)保证数据的完整性
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;    -->给出错误信息,超出精度范围
insert into t select 123456 from dual                   -->精度为5,而实际的数据位有6位
                     *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

b、使用小数位(scale)
scott@CNMMBO> truncate table t;

scott@CNMMBO> alter table 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,'123.45' from dual; 

scott@CNMMBO> insert into t select 123.456,'123.456' from dual;  -->此时的number进行了四舍五入

scott@CNMMBO> select * from t;

       NUM NUM_MSG
---------- ------------
    123.45 123.45
    123.46 123.456

scott@CNMMBO> insert into t select 1234,'1234' from dual; -->同样给出超出精度的错误提示
insert into t select 1234,'1234' from dual                -->此处的1234并不是1234,Oracle根据该列的定义会转换为1234.00
                     *                                    -->因为指定了2位小数,因此小数点左边最后只能有3位,右边为2位
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

c、负小数位的情形
scott@CNMMBO> truncate table t;  -->清空之前的数据

scott@CNMMBO> alter table t modify(num number(5,-2)); -->修改列的scale为负数

scott@CNMMBO> desc t
 Name                Null?    Type
 ------------------- -------- ------------------
 NUM_MSG                      VARCHAR2(12)
 NUM                          NUMBER(5,-2)            

scott@CNMMBO> insert into t select '123.45',12345 from dual;   

scott@CNMMBO> insert into t select '123.45',123.45 from dual;

scott@CNMMBO> insert into t select '123.456',123.456 from dual;

scott@CNMMBO> select * from t;

NUM_MSG             NUM
------------ ----------
123.45            12300     -->输入的12345为整数,即12345.00,小数位之前45被舍掉
123.45              100     -->输入的123.45,同样由于scale为-2,23被舍掉,结果为100
123.456             100     -->同上

scott@CNMMBO> insert into t select '987.65',987.65 from dual;

scott@CNMMBO> select * from t;

NUM_MSG             NUM
------------ ----------
123.45            12300
123.45              100
123.456             100
987.65             1000  -->未超出进度的情况下,产生了进位

scott@CNMMBO> insert into t select '98765432',98765432 from dual;  -->超出精度
insert into t select '98765432',98765432 from dual
                                *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

d、最大值与最小值
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 overflow

scott@CNMMBO> insert into t select 'max_value',power(10,125) from dual; 10的125次方可以成功插入

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+125
min_value    1.000E-130

-->从上面的测试可知,使用number来用作sequence,根部无需担心sequence不够用的情形

d、计算number列的长度

scott@CNMMBO> drop table t purge;

Table dropped.

scott@CNMMBO> create table t(l number,m number);

Table created.

-->使用vsize过的number的磁盘占用空间
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 99999999999999999999999999999
scott@CNMMBO> column v1 format 99
scott@CNMMBO> column v2 format 99
scott@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                    10000000000   6   2
                  999999999999                  1000000000000   7   2
                99999999999999                100000000000000   8   2
              9999999999999999              10000000000000000   9   2
            999999999999999999            1000000000000000000  10   2
          99999999999999999999          100000000000000000000  11   2
        9999999999999999999999        10000000000000000000000  12   2
      999999999999999999999999      1000000000000000000000000  13   2

-->对于列L,随着值的不断变大,其所耗用的存储空间也不但增加,呈线性增长。
-->对于列M,其所用的存储空间保持不变
-->从上可知,并非数值越大,耗用的存储空间越多。Oracle仅仅存储有效数字,以及指定小数点位置的指数,数值的符号信息等。

4、更多参考

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

时间: 2012-11-06

SQL, PL/SQL 之NUMBER数据类型的相关文章

好书推荐—《精通Oracle Database 12c SQL&amp;PL/SQL编程》

这是学习SQL和PL/SQL的一本好书,适合没有一点SQL基础的入门者.看过很多学习SQL与PL/SQL的书,要么是需要一定的基础,要么是大量的基础知识堆砌而没有形成系统.这本书不一样.首先,它是官方出品的.其次,作者是Oracle公司的产品经理,既会开发,又会管理.最后,本人购买并仔细阅读了这本书,并且是在当当满200减100的时候,所以折算下来,只需要35左右,600页,值了.本书SQL基础打头,PL/SQL接踵而至,章节分的很细腻,阅读轻松,收货却不少. 最后的最后,这不是打广告,是好书推

SQL,PL/SQL 数据类型一览表

The following is a list of datatypes available in Oracle. Character Datatypes The following are the Character Datatypes in Oracle: Data Type Syntax Oracle 9i Oracle 10g Oracle 11g Explanation(if applicable) char(size) Maximum size of 2000 bytes. Maxi

[转贴]Oracle PL/SQL语言基础

oracle|sql语言 [转贴]Oracle PL/SQL语言基础 Oracle PL/SQL语言基础   PL/SQL是ORACLE对标准数据库语言的扩展,ORACLE公司已经将PL/SQL整合到ORACLE 服务器和其他工具中了,近几年中更多的开发人员和DBA开始使用PL/SQL,本文将讲述PL/SQL基础语法,结构和组件.以及如何设计并执行一个PL/SQL程序.    PL/SQL的优点    从版本6开始PL/SQL就被可靠的整合到ORACLE中了,一旦掌握PL/SQL的优点以及其独有

Oracle PL/SQL语言基础

oracle|sql语言 Oracle PL/SQL语言基础  2002-8-23   Oracle PL/SQL语言基础 PL/SQL是ORACLE对标准数据库语言的扩展,ORACLE公司已经将PL/SQL整合到ORACLE 服务器和其他工具中了,近几年中更多的开发人员和DBA开始使用PL/SQL,本文将讲述PL/SQL基础语法,结构和组件.以及如何设计并执行一个PL/SQL程序. PL/SQL的优点 从版本6开始PL/SQL就被可靠的整合到ORACLE中了,一旦掌握PL/SQL的优点以及其独

PL/SQL --&amp;gt; 函数

--================== -- PL/SQL --> 函数 --==================       函数通常用于返回特定的数据.其实质是一个有名字的PL/SQL块,作为一个schema对象存储于数据库,可以被反复执行.函数通常被作为 一个表达式来调用或存储过程的一个参数,具有返回值.   一.建立函数的语法     CREATE [ OR REPLACE ] FUNCTION function_name         (argument1 [mode1] data

PL/SQL 游标变量

    游标变量与游标相似,有其共性,也有其不同点.就其共性来说两者都是指向多行查询的结果集中的当前行.都要经历声明,打开,检索与关闭的过程.所不同的是游标与游标变量类似于常量与变量.游标是静态的,而游标变量是动态的,因为游标变量并不与某个特定的查询相绑定.所以,游标变量可以打开任何类型兼容的查询.其次可以将游标变量作为参数传递给本地和存储子程序.本文主要描述游标变量的使用.   一.什么是游标变量    显示游标用于命名一个工作区域,其中保存多行查询的信息,而且该游标始终指向工作区域的内容.而

PL/SQL --&amp;gt; 游标

--================== -- PL/SQL --> 游标 --==================   一.游标的相关概念及特性     1.定义         映射在结果集中某一行数据的具体位置,类似于C语言中的指针.即通过游标方式定位到结果集中某个特定的行,然后根据业务需求         对该行进行相应特定的操作.             2.游标的分类         显示游标:即用户自定义游标,专门用于处理select语句返回的多行数据         隐式游标:系

PL/SQL --&amp;gt; 动态SQL的常见错误

--============================ -- PL/SQL --> 动态SQL的常见错误 --============================       动态SQL在使用时,有很多需要注意的地方,如动态SQL语句结尾处不能使用分号(;),而动态PL/SQL结尾处需要使用分号(;),但不能使用正 斜杠结尾(/),以及shcema对象不能直接作为变量绑定.本文介绍了动态SQL的常见问题.   一.演示动态SQL的使用     下面的示例中,首先使用动态SQL基于sco

PL/SQL --&amp;gt; PL/SQL记录

--======================= -- PL/SQL --> PL/SQL记录 --=======================       PL/SQL记录有着类似于表的数据结构,是一个或多个字段且拥有数据类型的集合体.定义了PL/SQL记录类型之后,可以定义PL/SQL记录变 量.声明一个PL/SQL记录变量相当于定义了多个标量变量,简化了变量的声明,从而大大节省了内存资源.多用于简化单行多列的数据处理.     一.定义PL/SQL记录     1.直接定义PL/SQL