学习PLS_INTEGER,BINARY_INTEGER,INTEGER,NUMBER的概念及区别以及在性能方面的差异

转载 2013年12月05日 00:08:54

学习PLS_INTEGER,BINARY_INTEGER,INTEGER,NUMBER的概念及区别以及在性能方面的差异
1,各个概念
              类型                是否可用于表列定义         概念                                      存储情况
              ------------------------------------------------------
              PLS_INTEGER         不可,仅用于PLSQL块        1,它存储比NUMBER及子类型少                1,存储有符号整形
                                                             2,它在计算采用硬件算法,比NUMBER快,      2,取值范围:-2147483648 到 2147483647
                                                                NUMBER采用库算法                        3,代表32BIT
                                                             3,如果精度在PLS_INTEGER之内,从速度上
                                                                最好用PLS_INTEGER,如果精度在其外
                                                                用INTEGER
                                                             4,如果多个PLS_INTEGER运算溢出,即使把运
                                                               算结果类型是NUMBER,也会报错
                             
              NUMBER              可以,也可用于PLSQL块      1,存储定点或浮点数                         1,取值在1E-130(不含)到1.0E126
                                                             2,如果运算结果在取值范围,用它
                                                                如果运算超界,编译报错
                                                                如果NUMBER计算超界,结果未知
                                                                 可能产生不可信的结果和错误
                                                             3,定义:NUMBER(精度,小数位数)  
                                                             4,定义定点型NUMBER
                                                                  NUMBER(精度,小数位数)
                                                                定义浮点型NUMBER
                                                                  NUMBER,所谓浮点即小数位点不定
                                                                定义整型
                                                                  NUMBER(精度) 
                                                             5,精度取值范围:38数字位数,如果未指定精度
                                                                默认取39或40或系统支持最大值,二者取小者
                                                             6,小位位数,取值是-84到127
                                                                小位位数与四舍五入有关,即3.542为3.54
                                                                负小数位数2比如:3452为3400
                                                                小数位数是0,比如3.456为3
                                                             7,如未指定小数位数,默认是0
         
             INTEGER           可用于表列,也可用于PLSQL块   1,INTEGER是NUMBER子类型
                                                                      --说明INTEGER的子类型
                                                                 SQL> create table t_integer(a integer);
                                                                 表已创建。
                                                                
                                                                 SQL> desc t_integer;
                                                                  名称                                      是否为空? 类型
                                                                  ----------------------------------------- -------- -----------
                                                                
                                                                  A                                                  NUMBER(38)   

              BINARY_INTEGER   不可用于表列,但可用于PLSQL块      1,它和PLS_INTEGER是一样的,它的子类型可视
                                                                     PLS_INTEGER的子类型
                                                                  3,BINARY_INTEGER子类型
                                                                       NATURAL
                                                                       NATURAIN 
                                                                       POSITIVE
                                                                       POSITIVEN
                                                                       SIGNTYPE
                                                                      
              BINARY_FLOAT或
              BIANRY_DOUBLE    不可用于表列,但可用于PLSQL块      1,用于高速的科学计算
                                                                  2,单精度或双精度的IEEE754单精度的浮点数
                                                                  3,此类型以F结尾,如:2.04F或3.004D
                                                                  4,与此类型相关的运算产生结果要进行检查,而会
                                                                     不会引发异常
                                                                  5,为了处理OVERFLOW,UNDERFLOW及其它情况,可用
                                                                     几个预定义的常量:
                                                                       BINARY_FLOAT_NAN,
                                                                       BINARY_FLOAT_INFINITY
                                                                       BINARY_FLOAT_MAX_NORMAL
                                                                       BINARY_FLOAT_MIN_NORMAL
                                                                       BINARY_FLOAT_MAX_SUBNORMAL
                                                                       BIANRY_FLOAT_MIN_SUBNORMAL                                                                 
        
         2,用PLSQL进行测试,小数据量,大数据量,大数据量测试,比对各个类型的区别
               1,定义上述类型的表
                   CREATE TABLE T_NUMBER(A NUMBER);
                   CREATE TABLE T_INTEGER(A INTEGER);
                   CREATE TABLE T_PLS_INTEGER(A NUMBER);
                   CREATE TABLE T_BINARY_INTEGER(A NUMBER);
                   CREATE TABLE T_BINARY_FLOAT(A NUMBER);                
               2,以小大大数据量进行对比测试   
                   1,1000条以下 小数据量
                   2,100000条  大数据量
                   3,100000000条  大数据量 
                  
                   4,分别以上述数据量对1定义的6个表进行INSERT
                     
                      --1,以小数据量测试NUMBER类型的INSERT
                      DECLARE
                        V_UPPER NUMBER;
                      BEGIN
                       V_UPPER:=1000;
                       FOR I IN 1..V_UPPER LOOP
                         INSERT INTO T_NUMBER VALUES(I);
                       END LOOP;
                      END;
                      已用时间:  00: 00: 00.09
                    
                     --2,以小数据量测试INTEGER类型的INSERT
                      DECLARE
                        V_UPPER INTEGER;
                      BEGIN
                       V_UPPER:=1000;
                       FOR I IN 1..V_UPPER LOOP
                         INSERT INTO T_INTEGER VALULES(I);
                       END LOOP;
                      END;  
                      已用时间:  00: 00: 00.05
                
                      --3,以小数据量测试PLS_INTEGER类型的INSERT
                      DECLARE
                        V_UPPER PLS_INTEGER;
                      BEGIN
                       V_UPPER:=1000;
                       FOR I IN 1..V_UPPER LOOP
                         INSERT INTO T_PLS_INTEGER VALUES(I);
                       END LOOP;
                      END;  
                      已用时间:  00: 00: 00.06

                      --4,以小数据量测试BINARY_INTEGER类型的INSERT
                      DECLARE
                        V_UPPER BINARY_INTEGER;
                      BEGIN
                       V_UPPER:=1000;
                       FOR I IN 1..V_UPPER LOOP
                         INSERT INTO T_BINARY_INTEGER VALUES(I);
                       END LOOP;
                      END;
                      已用时间:  00: 00: 00.10

                      --5,以小数据量测试BINARY_FLOAT类型的INSERT
                      DECLARE
                        V_UPPER BINARY_FLOAT;
                      BEGIN
                       V_UPPER:=1000;
                       FOR I IN 1..V_UPPER LOOP
                         INSERT INTO T_BINARY_FLOAT VALUES(I);
                       END LOOP;
                      END;
                      已用时间:  00: 00: 00.13
                     
                      --6,小结:用小数据量INSERT,INTEGER最快,而BINARY_FLOAT最慢,BINARY_INTEGER仅比最慢的
                                BINARY_FLOAT快一点,倒数第二
                                PLS_INTEGER比INTEGER稍慢一点儿
                               
           ---------------------------------------------------
 --1,以中数据量测试NUMBER类型的INSERT
                      --先清空上述5个表
                      TRUNCATE TABLE T_NUMBER;
                      TRUNCATE TABLE T_INTEGER;
                      TRUNCATE TABLE T_PLS_INTEGER;
                      TRUNCATE TABLE T_BINARY_INTEGER;
                      TRUNCATE TABLE T_BINARY_FLOAT;
                     
                      --1,以中数据量测试NUMBER类型的INSERT
                      DECLARE
                        V_UPPER NUMBER;
                      BEGIN
                       V_UPPER:=100000;
                       FOR I IN 1..V_UPPER LOOP
                         INSERT INTO T_NUMBER VALUES(I);
                       END LOOP;
                      END;
                      已用时间:  00: 00: 03.57
                    
                     --2,以中数据量测试INTEGER类型的INSERT
                      DECLARE
                        V_UPPER INTEGER;
                      BEGIN
                       V_UPPER:=100000;
                       FOR I IN 1..V_UPPER LOOP
                         insert into  t_integer values(I);
                       END LOOP;
                      END;  
                      已用时间:  00: 00: 05.
    
                      --3,以中数据量测试PLS_INTEGER类型的INSERT
                      DECLARE
                        V_UPPER PLS_INTEGER;
                      BEGIN
                       V_UPPER:=100000;
                       FOR I IN 1..V_UPPER LOOP
                         INSERT INTO T_PLS_INTEGER VALUES(I);
                       END LOOP;
                      END;  
                      已用时间:  00: 00: 03.00
         
                      --4,以中数据量测试BINARY_INTEGER类型的INSERT
                      DECLARE
                        V_UPPER BINARY_INTEGER;
                      BEGIN
                       V_UPPER:=100000;
                       FOR I IN 1..V_UPPER LOOP
                         INSERT INTO T_BINARY_INTEGER VALUES(I);
                       END LOOP;
                      END;
                      已用时间:  00: 00: 03.08

                      --5,以中数据量测试BINARY_FLOAT类型的INSERT
                      DECLARE
                        V_UPPER BINARY_FLOAT;
                      BEGIN
                       V_UPPER:=100000;
                       FOR I IN 1..V_UPPER LOOP
                         INSERT INTO T_BINARY_FLOAT VALUES(I);
                       END LOOP;
                      END;
                      已用时间:  00: 00: 03.27
                     
                      --小结:PLS_INTEGER最快,INTEGER最慢
                              BINARY_INTEGER稍决于PLS_INTEGER
-------------------------------------
-------------------------------------
继续昨天的基于不同数据类型PLS_INTEGER,NUMBER,INTEGER,BINARY_INTEGER,BINARY_FLOAT的性能测试
       1,修正如下PLSQL块,因为报错ORA-04030,内存分配不足,原代码是1亿条提交,现改为1000000提交试下;
          分成10次提交,用绑定变量方式运行,此PLSQL块运行10次,看是否还会报ORA-04030错误
            --1,以大数据量测试NUMBER类型的INSERT
                         --因为一条条插入太慢,采用1万条一提交
                         --1万条一提交仍是INSERT太慢,采用FORALL试下速度如下
                      DECLARE
                        V_LOWER NUMBER;--下界
                        V_UPPER NUMBER;
                        TYPE TYP_TAB_UPPER IS TABLE OF NUMBER index by BINARY_INTEGER;
                        TYP_TAB_UPPER_1 TYP_TAB_UPPER;
                       
                      BEGIN
                       V_LOWER:=1;
                       V_UPPER:=V_LOWER+&V*10000000-1;--上界,上界依赖于下界,二者相差1000000条记录
                       for i in V_LOWER..V_UPPER loop
                        TYP_TAB_UPPER_1(I):=I;
                       end loop;
                      
                       FORALL I IN V_LOWER..V_UPPER
                         INSERT INTO T_NUMBER VALUES(TYP_TAB_UPPER_1(I));
                         COMMIT;
                       --END LOOP;
                      END;
                      已用时间:  00: 00: 17.34
                     
                      --如果提交1亿条记录就报下述错误,我昨天已经提高了SGA和PGA的大小,环境是11G R2,仍报错
                       第 1 行出现错误:
                       ORA-06500: PL/SQL: 存储错误
                       ORA-04030: 在尝试分配 16356 字节 (koh-kghu call ,pmucalm coll) 时进程内存不足
                       ORA-06512: 在 line 8
                     
                      --上述代码绑定变量总出错,整体不好排错,故只单独把出错的绑定变量拿出来
                      --经分析,是因为;写成;了,所以一定要小心
                      SET SERVEROUTPUT ON
                      DECLARE
                      V_LOWER NUMBER;
                      V_UPPER NUMBER;
                      BEGIN
                       V_LOWER:=1;
                       V_UPPER:=V_LOWER+&I*100;
                       DBMS_OUTPUT.PUT_LINE(V_LOWER||'__'||V_UPPER);
                      END;
                     
                      --用普通的FOR LOOP提交1千万看下性能
                       09:39:42 SQL> DECLARE
                       09:39:44   2                        BEGIN
                       09:39:44   3                         FOR I IN 1..10000000 LOOP
                       09:39:44   4                           INSERT INTO T_NUMBER VALUES(I);
                       09:39:44   5                         END LOOP;
                       09:39:44   6                        END;
                       09:39:45   7  /
                      
                       PL/SQL 过程已成功完成。
                      
                       已用时间:  00: 05: 39.37
                      
                       --小结
                          1,FORALL运行INSERT 1千万用时17秒
                             FOR LOOP运行INSERT 1千万用时5分钟
                          2,二者差距相当大,20倍之差,所以大数据量操作一定要用FORALL
                          3,FORALL的语句如何让它更快,要反思的地方
                         
            --2,以大数据量测试INTEGER类型的INSERT
                      DECLARE
                        V_LOWER INTEGER;--下界
                        V_UPPER INTEGER;
                        TYPE TYP_TAB_UPPER IS TABLE OF INTEGER index by BINARY_INTEGER;
                        TYP_TAB_UPPER_1 TYP_TAB_UPPER;
                       
                      BEGIN
                       V_LOWER:=1;
                       V_UPPER:=V_LOWER+&V*10000000-1;--上界,上界依赖于下界,二者相差1000000条记录
                       for i in V_LOWER..V_UPPER loop
                        TYP_TAB_UPPER_1(I):=I;
                       end loop;
                      
                       FORALL I IN V_LOWER..V_UPPER
                         INSERT INTO T_NUMBER VALUES(TYP_TAB_UPPER_1(I));
                         COMMIT;
                       --END LOOP;
                      END;
                      已用时间:  00: 00: 22.3

                      --3,以大数据量测试PLS_INTEGER类型的INSERT
                      DECLARE
                        V_LOWER PLS_INTEGER;--下界
                        V_UPPER PLS_INTEGER;
                        TYPE TYP_TAB_UPPER IS TABLE OF PLS_INTEGER index by BINARY_INTEGER;
                        TYP_TAB_UPPER_1 TYP_TAB_UPPER;
                       
                      BEGIN
                       V_LOWER:=1;
                       V_UPPER:=V_LOWER+&V*10000000-1;--上界,上界依赖于下界,二者相差1000000条记录
                       for i in V_LOWER..V_UPPER loop
                        TYP_TAB_UPPER_1(I):=I;
                       end loop;
                      
                       FORALL I IN V_LOWER..V_UPPER
                         INSERT INTO T_NUMBER VALUES(TYP_TAB_UPPER_1(I));
                         COMMIT;
                       --END LOOP;
                      END;  
                      已用时间:  00: 00: 16.93

                      --4,以大数据量测试BINARY_INTEGER类型的INSERT
                      DECLARE
                        V_LOWER BINARY_INTEGER;--下界
                        V_UPPER BINARY_INTEGER;
                        TYPE TYP_TAB_UPPER IS TABLE OF BINARY_INTEGER index by BINARY_INTEGER;
                        TYP_TAB_UPPER_1 TYP_TAB_UPPER;
                       
                      BEGIN
                       V_LOWER:=1;
                       V_UPPER:=V_LOWER+&V*10000000-1;--上界,上界依赖于下界,二者相差1000000条记录
                       for i in V_LOWER..V_UPPER loop
                        TYP_TAB_UPPER_1(I):=I;
                       end loop;
                      
                       FORALL I IN V_LOWER..V_UPPER
                         INSERT INTO T_NUMBER VALUES(TYP_TAB_UPPER_1(I));
                         COMMIT;
                       --END LOOP;
                      END;   
                      已用时间:  00: 00: 21.6

                      --5,以大数据量测试BINARY_FLOAT类型的INSERT
                      DECLARE
                        V_LOWER BINARY_FLOAT;--下界
                        V_UPPER BINARY_FLOAT;
                        TYPE TYP_TAB_UPPER IS TABLE OF BINARY_FLOAT index by BINARY_INTEGER;
                        TYP_TAB_UPPER_1 TYP_TAB_UPPER;
                       
                      BEGIN
                       V_LOWER:=1;
                       V_UPPER:=V_LOWER+&V*10000000-1;--上界,上界依赖于下界,二者相差1000000条记录
                       for i in V_LOWER..V_UPPER loop
                        TYP_TAB_UPPER_1(I):=I;
                       end loop;
                      
                       FORALL I IN V_LOWER..V_UPPER
                         INSERT INTO T_NUMBER VALUES(TYP_TAB_UPPER_1(I));
                         COMMIT;
                       --END LOOP;
                      END;   
                      已用时间:  00: 00: 21.35
                     
                      小结:最快是PLS_INTEGER;最慢的是INTEGER;

pls_integer类型

今天在看一个触发器代码的时候碰到了一个pls_integer类型,以前没碰到过,记录一下: PLS_INTEGER可以存储一个有符号的整形值,其精度范围和BINARY_INTEGER一样,是:-2^...

PLS_INTEGER

http://www.itpub.net/thread-1106923-1-1.html   pls_integer类型也是数字类型,但和number类型不同,number可以存储实数,而pls_...
  • orion61
  • orion61
  • 2012年04月15日 10:33
  • 423

PLS_INTEGER、BINARY_INTEGER和SIMPLE_INTEGER区别

PLS_INTEGER PLS_INTEGER数据类型保存范围-2147483648到2147483647的有符整数。这种类型的值是通过底层硬件平台的原生整数格式来表示的。 ...
  • ashic
  • ashic
  • 2016年08月07日 00:49
  • 722

PLSQL中的类型与PLS_INTEGER

转的别人的: Binary_Integer 与 Pls_Integer 都是整型类型. Binary_Integer类型变量值计算是由Oracle来执行,不会出现溢出,但是执行速度较慢,因为它是由O...

BINARY_INTEGER和INTEGER的区别

从使用范围上讲:INTEGER 是一个SQL数据类型,可以用在定义表时,也可以用在PL/SQL中。BINARY_INTEGER 是一个PL/SQL数据类型,只能用在PL/SQL中。从表示内容上讲:IN...

Pls_Integer、Binary_Integer、Number

如果你要做循环的记数器,可以使用pls_integer. pls_interger,binary_integer,number中,pls_integer速度最快 Binary_Integer 与 P...
  • lqh4188
  • lqh4188
  • 2012年12月10日 10:59
  • 421

存储过程中变量类型:number,pls_integer,small integer

今天在查看批量提交脚本时发现,很多存储过程变量定义整形时都是使用PLS_INTEGER,不禁疑惑它跟number有什么不同。 查看一些资料: PLS_INTEGER Datatyp...

JAVA-int和Integer的区别1.zip

  • 2012年09月01日 16:09
  • 9KB
  • 下载

关于数据库number与integer类型的区别

引自:http://www.itpub.net/viewthread.php?tid=1261515 留意oracle中integer和number的区别; 建表的时候,如果是浮点数,一般...
  • c_kamen
  • c_kamen
  • 2013年01月05日 20:21
  • 386
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:学习PLS_INTEGER,BINARY_INTEGER,INTEGER,NUMBER的概念及区别以及在性能方面的差异
举报原因:
原因补充:

(最多只允许输入30个字)