精华内容
下载资源
问答
  • oracle number存储小数

    千次阅读 2013-05-21 15:17:00
    创建字段paraA number(16,2); 16是存储整形的长度,2是小数的长度,存储数据格式为...反之paraA number(16,3) 存储数据格式为:16.333 转载于:https://www.cnblogs.com/fuzhaoyang56/archive/2013/05/21/3090811.html...

    创建字段 paraA number(16,2); 16是存储整形的长度,2是小数的长度,存储数据格式为:16.22

    反之paraA number(16,3) 存储数据格式为:16.333

    转载于:https://www.cnblogs.com/fuzhaoyang56/archive/2013/05/21/3090811.html

    展开全文
  • ORACLE存储过程

    万次阅读 多人点赞 2018-11-02 18:14:48
    oracle存储过程 目录 一.什么是存储过程 二.为什么要写存储过程 三.存储过程基础 1.存储过程结构 2.存储过程语法 3.pl/sql处理存储过程 四.存储过程进阶 1.BUIK COLLECT 2.FORALL 3.pl/sql调试存储过程 ...

                                                  oracle存储过程

    目录

             一.什么是存储过程

    二.为什么要写存储过程

    三.存储过程基础

    1.存储过程结构

    2.存储过程语法

    3.pl/sql处理存储过程

    四.存储过程进阶

    1.BUIK COLLECT

    2.FORALL

    3.pl/sql调试存储过程

    4.案例实战

    附.参考资料


    一.什么是存储过程

    存储过程,百度百科上是这样解释的,存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来调用存储过程。

    简单的说就是专门干一件事一段sql语句。

    可以由数据库自己去调用,也可以由java程序去调用。

    在oracle数据库中存储过程是procedure。

    二.为什么要写存储过程

    1.效率高

      存储过程编译一次后,就会存到数据库,每次调用时都直接执行。而普通的sql语句我们要保存到其他地方(例如:记事本  上),都要先分析编译才会执行。所以想对而言存储过程效率更高。

    2.降低网络流量

    存储过程编译好会放在数据库,我们在远程调用时,不会传输大量的字符串类型的sql语句。

    3.复用性高

    存储过程往往是针对一个特定的功能编写的,当再需要完成这个特定的功能时,可以再次调用该存储过程。

    4.可维护性高

    当功能要求发生小的变化时,修改之前的存储过程比较容易,花费精力少。

    5.安全性高

    完成某个特定功能的存储过程一般只有特定的用户可以使用,具有使用身份限制,更安全。

    三.存储过程基础

    1.存储过程结构

    (1).基本结构

    Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常(可写可不写,要增强脚本的容错性和调试的方便性那就写上异常处理)

    (2).无参存储过程

    CREATE OR REPLACE PROCEDURE demo AS/IS
    	变量2 DATE;
    	变量3 NUMBER;
    BEGIN
    	--要处理的业务逻辑
    	EXCEPTION    --存储过程异常
    END 
    

    这里的as和is一样任选一个,在这里没有区别,其中demo是存储过程名称。

    (3).有参存储过程

    a.带参数的存储过程

    CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE)
    AS/IS
    name student.name%TYPE;
    age number :=20;
    BEGIN
      --业务处理.....
    END

    上面脚本中,

    第1行:param1 是参数,类型和student表id字段的类型一样。

    第3行:声明变量name,类型是student表name字段的类型(同上)。

    第4行:声明变量age,类型数数字,初始化为20

     

    b.带参数的存储过程并且进行赋值

    CREATE OR REPLACE PROCEDURE 存储过程名称(
           s_no in varchar,
           s_name out varchar,
           s_age number) AS
    total NUMBER := 0;
    BEGIN
      SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
      dbms_output.put_line('符合该年龄的学生有'||total||'人');
      EXCEPTION
        WHEN too_many_rows THEN 
        DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
    END

    上面脚本中:

    其中参数IN表示输入参数,是参数的默认模式。
    OUT表示返回值参数,类型可以使用任意Oracle中的合法类型。
    OUT模式定义的参数只能在过程体内部赋值,表示该参数可以将某个值传递回调用他的过程
    IN OUT表示该参数可以向该过程中传递值,也可以将某个值传出去

    第7行:查询语句,把参数s_age作为过滤条件,INTO关键字,把查到的结果赋给total变量。

    第8行:输出查询结果,在数据库中“||”用来连接字符串

    第9—11行:做异常处理

    2.存储过程语法

    (1).运算符

    这里s,m,n是变量,类型是number;

    分类

    运算符

    含义

    示例表达式

     

     

     

    算术运算符

    +

    s := 2 + 2;

    -

    s := 3 – 1;

    *

    s := 2 * 3;

    /

    s := 6 / 2;

    mod(,)

    取模,取余

    m : = mod(3,2)

    **

    乘方

    10**2 =100

     

     

     

    关系运算符

    =

    等于

    s = 2

    <>或!=或~=

    不等于

    s != 2

    <

    小于

    s < 3

    >

    大于

    s > 0

    <=

    小于等于

    s <= 9

    >=

    大于等于

    s >= 1

     

     

    比较运算符

    LIKE

    满足匹配为true

    ‘li’ like ‘%i’返回true

    BETWEEN

    是否处于一个范围中

    2 between 1 and 3 返回true

    IN

    是否处于一个集合中

    ‘x’ in (‘x’,’y’) 返回true

    IS NULL

    判断变量是否为空

    若:n:=3,n is null,返回false

     

    逻辑运算符

    AND

    逻辑与

    s=3 and c is null

    OR

    逻辑或

    s=3 or c is null

    NOT

    逻辑非

    not c is null

     

    其他

    :=

    赋值

    s := 0;

    ..

    范围

    1..9,即1至9范围

    ||

    字符串连接

    ‘hello’||’world’

     

    (2).SELECT INTO STATEMENT语句

    该语句将select到的结果赋值给一个或多个变量,例如:

    CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS
    s_name VARCHAR2;   --学生名称
    s_age NUMBER;      --学生年龄
    s_address VARCHAR2; --学生籍贯
    BEGIN
      --给单个变量赋值
      SELECT student_address INTO s_address
      FROM student where student_grade=100;
       --给多个变量赋值
      SELECT student_name,student_age INTO s_name,s_age
      FROM student where student_grade=100;
      --输出成绩为100分的那个学生信息
      dbms_output.put_line('姓名:'||s_name||',年龄:'||s_age||',籍贯:'||s_address);
    END

    上面脚本中:

    存储过程名称:DEMO_CDD1, student是学生表,要求查出成绩为100分的那个学生的姓名,年龄,籍贯

    (3).选择语句

    a.IF..END IF

    学生表的sex字段:1-男生;0-女生

    IF s_sex=1 THEN
      dbms_output.put_line('这个学生是男生');
    END IF

    b.IF..ELSE..END IF

    IF s_sex=1 THEN
      dbms_output.put_line('这个学生是男生');
    ELSE
      dbms_output.put_line('这个学生是女生');
    END IF

    (4).循环语句

    a.基本循环

    LOOP
      IF 表达式 THEN
        EXIT;
      END IF
    END LOOP;

    b.while循环

    WHILE 表达式 LOOP
      dbms_output.put_line('haha');
    END LOOP;

    c.for循环

    FOR a in 10 .. 20 LOOP
      dbms_output.put_line('value of a: ' || a);
    END LOOP;

    (5).游标

        Oracle会创建一个存储区域,被称为上下文区域,用于处理SQL语句,其中包含需要处理的语句,例如所有的信息,行数处理,等等。

        游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)SQL语句返回。行集合光标保持的被称为活动集合。

    a.下表是常用的游标属性

    属性

    描述

    %FOUND

    如果DML语句执行后影响有数据被更新或DQL查到了结果,返回true。否则,返回false。

    %NOTFOUND

    如果DML语句执行后影响有数据被更新或DQL查到了结果,返回false。否则,返回true。

    %ISOPEN

    游标打开时返回true,反之,返回false。

    %ROWCOUNT

    返回DML执行后影响的行数。

    b.使用游标

    声明游标定义游标的名称和相关的SELECT语句:

    CURSOR cur_cdd IS SELECT s_id, s_name FROM student;

    打开游标游标分配内存,使得它准备取的SQL语句转换成它返回的行:

    OPEN cur_cdd;

    抓取游标中的数据,可用LIMIT关键字来限制条数,如果没有默认每次抓取一条:

    FETCH cur_cdd INTO id, name ;

    关闭游标来释放分配的内存:

    CLOSE cur_cdd;

    3.pl/sql处理存储过程

    (1).新建存储过程:右键procedures,点击new,弹出PROCEDURE框,再点击OK,如下图:

     

    (2).在下面的编辑区,编写存储过程脚本

     

    (3).在这里我们编写一个demo_cdd存储过程,要求输出“hello world”,如下图:

     

    (4).右键刚才新建的存储过程名称,点击“Test”,在点击执行按钮

     

    4.案例实战

    场景:

    有表student(s_no, s_name, s_age, s_grade),其中s_no-学号,也是主键,是从1开始向上排的(例如:第一个学生学号是1,第二个是2,一次类推);s_name-学生姓名;s_age-学生年龄;s_grade-年级;这张表的数据量有几千万甚至上亿。一个学年结束了,我要让这些学生全部升一年级,即,让s_grade字段加1。

    这条sql,写出来如下:

    update student set s_grade=s_grade+1

    分析:

    如果我们直接运行运行这条sql,因数据量太大会把数据库undo表空间撑爆,从而发生异常。那我们来写个存储过程,进行批量更新,我们每10万条提交一次。

    CREATE OR REPLACE PROCEDURE process_student is
    total NUMBER := 0;
    i NUMBER := 0;
    BEGIN
      SELECT COUNT(1) INTO total FROM student;
      WHILE i<=total LOOP
        UPDATE student SET grade=grade+1 WHERE s_no=i;
        i := i + 1;
        IF i >= 100000 THEN
          COMMIT;
        END IF;
      END LOOP;
      dbms_output.put_line('finished!');
    END;
    

    四.存储过程进阶

           在上面的案例中,我们的存储过程处理完所有数据要多长时间呢?事实我没有等到它执行完,在我可接受的时间范围内它没有完成。那么对于处理这种千万级数据量的情况,存储过程是不是束手无策呢?答案是否定的,接下来我们看看其他绝招。

           我们先来分析下执行过程的执行过程:一个存储过程编译后,在一条语句一条语句的执行时,如果遇到pl/sql语句就拿去给pl/sql引擎执行,如果遇到sql语句就送到sql引擎执行,然后把执行结果再返回给pl/sql引擎。遇到一个大数据量的更新,则执行焦点(正在执行的,状态处于ACTIVE)会不断的来回切换。

           Pl/SQL与SQL引擎之间的通信则称之为上下文切换,过多的上下文切换将带来过量的性能负载。最终导致效率降低,处理速度缓慢。

           从Oracle8i开始PL/SQL引入了两个新的数据操纵语句:FORALLBUIK COLLECT,这些语句大大滴减少了上下文切换次数(一次切换多次执行),同时提高DML性能,因此运用了这些语句的存储过程在处理大量数据时速度简直和飞一样。

    1.BUIK COLLECT

        Oracle8i中首次引入了Bulk Collect特性,Bulk Collect会能进行批量检索,会将检索结果结果一次性绑定到一个集合变量中,而不是通过游标cursor一条一条的检索处理。可以在SELECT INTO、FETCH INTO、RETURNING INTO语句中使用BULK COLLECT,接下来我们一起看看这些语句中是如何使用BULK COLLECT的。

    (1).SELECT INTO

    查出来一个结果集合赋值给一个集合变量。

    语法结构是:

    SELECT field BULK COLLECT INTO var_conllect FROM table where colStatement;

    说明:

           field:要查询的字段,可以是一个或多个(要保证和后面的集合变量要向对应)。

           var_collect:集合变量(联合数组等),用来存放查到的结果。

           table:表名,要查询的表。

           colStatement:后面过滤条件语句。比如s_age < 10;

    例子:查出年龄小于10岁的学生姓名赋值给数组arr_name变量

    SELECT s_name BULK COLLECT INTO arr_name FROM s_age < 10;

    (2).FETCH INTO

    从一个集合中抓取一部分数据赋值给一个集合变量。

    语法结构如下:

    FETCH cur1 BULK COLLECT INTO var_collect [LIMIT rows]

    说明:

            cur1:是个数据集合,例如是个游标。

            var_collect:含义同上。

            [LIMIT rows]:可有可无,限制每次抓取的数据量。不写的话,默认每次一条数据。

    例子:给年龄小于10岁的学生的年级降一级。

    --查询年龄小于10岁的学生的学号放在游标cur_no里
    CURSOR cur_no IS 
    		SELECT s_no FROM student WHERE s_age < 10;
    
    --声明了一个联合数组类型,元素类型和游标cur_no每个元素的类型一致
    TYPE ARR_NO IS VARRAY(10) OF cur_no%ROWTYPE;
    
    --声明一个该数组类型的变量no
    no ARR_NO;
    BEGIN
      FETCH cur_no BULK COLLECT INTO no LIMIT 100;
      FORALL i IN 1..no.count SAVE EXCEPTONS
    	UPDATE student SET s_grade=s_grade-1 WHERE no(i);
    END;
    

    说明:先查出年龄小于10岁的学生的学号放在游标里,再每次从游标里拿出100个学号,进行更新,给他们的年级降一级。

    (3).RETURNING

    BULK COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用,可以返回这些DML语句执行后所影响的记录内容(某些字段)。

    再看一眼学生表的字段情况:student(s_no, s_name, s_age, s_grade)

    语法结构如下:

    DMLStatement
           RETURNING field BULK COLLECT INTO var_field;

    说明:

            DMLStatement:是一个DML语句。

            field:是这个表的某个字段,当然也可以写多个逗号隔开(field1,field2, field3)。

            var_field:一个类型为该字段类型的集合,多个的话用逗号隔开,如下:

            (var_field1, var_field2, var_field3)

     

    例子:获取那些因为年龄小于10岁而年级被将一级的学生的姓名集合。

    TYPE NAME_COLLECT IS TABLE OF student.s_name%TYPE;
    names NAME_COLLECT;
    BEGIN
      UPDATE student SET s_grade=s_grade-1 WHERE s_age < 10
      RETURNING s_name BULK COLLECT INTO names;
    END;

    说明:

           NAME_COLLECT:是一个集合类型,类型是student表的name字段的类型。

           names:定义了一个NAME_COLLECT类型的变量。

    (4).注意事项

    a.不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。

    b.只能在服务器端的程序中使用BULK COLLECT,如果在客户端使用,就会产生一个不支持这个特性的错误。

    c.BULK COLLECT INTO 的目标对象必须是集合类型。

    d.复合目标(如对象类型)不能在RETURNING INTO 子句中使用。

    e.如果有多个隐式的数据类型转换的情况存在,多重复合目标就不能在BULK COLLECT INTO 子句中使用。

    f.如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。

    2.FORALL

    (1).语法

    FORALL index IN bounds [SAVE EXCEPTIONS]     
         sqlStatement;

    说明:

            index是指下标;

            bounds是一个边界,形式是start..end

            [SAVE EXCEPTIONS] 可写可不写,这个下面介绍;

            sqlStatement是一个DML语句,这里有且仅有一个sql语句;

    例子:

    --例子1:移除年级是5到10之间的学生
    FORALL i IN 5..10
           DELETE FROM student where s_grade=i;
    --例子:2,arr是一个数组,存着要升高一年级的学生名称
    FORALL s IN 1..arr.count SAVE EXCEPTIONS
           UPDATE student SET s_grade=s_grade+1 WHERE s_name=arr(i);

    (2).SAVE EXCEPTIONS

    通常情况写我们在执行DML语句时,可能会遇到异常,可能致使某个语句或整个事务回滚。如果我们写FORALL语句时没有用SAVE EXCEPTIONS语句,那么DML语句会在执行到一半的时候停下来。

           如果我们的FORALL语句后使用了SAVE EXCEPTIONS语句,当在执行过程中如果遇到异常,数据处理会继续向下进行,发生的异常信息会保存到SQL%BULK_EXCEPTONS的游标属性中,该游标属性是个记录集合,每条记录有两个字段,例如:(1, 02300);

           ERROR_INDEX:该字段会存储发生异常的FORALL语句的迭代编号;

           ERROR_CODE:存储对应异常的,oracle错误代码;

    SQL%BULK_EXCEPTONS这个异常信息总是存储着最近一次执行的FORALL语句可能发生的异常。而这个异常记录集合异常的个数则由它的COUNT属性表示,即:

           SQL%BULK_EXCEPTONS.COUNT,SQL%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。

    (3). INDICES OF

    在Oracle数据库10g之前有一个重要的限制,该数据库从IN范围子句中的第一行到最后一行,依次读取集合的内容,如果在该范围内遇到一个未定义的行,Oracle数据库将引发ORA-22160异常事件:ORA-22160: element at index [N] does not exist。针对这一问题,Oracle后续又提供了两个新语句:INDICES OF 和 VALUES OF。

    接下来我们来看看这个INDICES OF语句,用于处理稀疏数组或包含有间隙的数组(例如:一个集合的某些元素被删除了)。

    该语句语法结构是:

    FORALL i INDICES OF collection [SAVE EXCEPTIONS]
    
           sqlStatement;

    说明:

    i:集合(嵌套表或联合数组)下标。

    collection:是这个集合。

    [SAVE EXCEPTIONS]和sqlStatement上面已经解释过。

    例子:arr_std是一个联合数组,每个元素包含(name,age,grade),现在要向student表插入数据。

    FORALL i IN INDICES OF arr_stu
           INSERT INTO student VALUES(
               arr_stu(i).name,
                  arr_stu(i).age,
                  arr_stu(i).grade
           );

    (4). VALUES OF

    VALUES OF适用情况:绑定数组可以是稀疏数组,也可以不是,但我只想使用该数组中元素的一个子集。VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。但是,VALUES OF在使用时有一些限制:

           如果VALUES OF子句中所使用的集合是联合数组,则必须使用PLS_INTEGER和BINARY_INTEGER进行索引,VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER;

           当VALUES OF 子句所引用的集合为空,则FORALL语句会导致异常;

    该语句的语法结构是:

    FORALL i IN VALUES OF collection [SAVE EXCEPTIONS]
           sqlStatement;

    说明:i和collection含义如上

    联合数组请看文章(或自行百度):https://blog.csdn.net/leshami/article/details/7372061

    3.pl/sql调试存储过程

    首先,当前这个用户得有能调试存储过程的权限,如果没有的话,以数据库管理员身份给你这个用户授权:

    --userName是你要拿到调试存储过程权限的用户名
    GRANT DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION TO username;

    (1).右键一个存储过程名称,点击测试,如下图:

    这里我用的pl/sql是12.0.4版本的,下面截图中与低版本的pl/sql按钮位置都相同,只是图标不一样。

     

    (2).点击两次step into按钮,进入语句调试,如下图:

     

    (3).每点击一次step into按钮,会想下执行一条语句,也可以查看变量和表达式的值,如下图:

     

    查看变量值:在查看变量区域,在Variable列输入变量i,在Value列点击下,该变量的值就显示出来了。

    4.案例实战

    场景和上面的案例实战是同一个,如下:

    有表student(s_no, s_name, s_age, s_grade),其中s_no-学号,也是主键,是从1开始向上排的(例如:第一个学生学号是1,第二个是2,一次类推);s_name-学生姓名;s_age-学生年龄;s_grade-年级;这张表的数据量有几千万甚至上亿。一个学年结束了,我要让这些学生全部升一年级,即,让s_grade字段加1。

    这条sql,写出来如下:

    update student set s_grade=s_grade+1

    编写存储过程:

    (1).存储过程1

    名称为:process_student1,student表的s_no字段类型为varchar2(16)。

    CREATE OR REPLACE PROCEDURE process_student1 AS
        CURSOR CUR_STUDENT IS SELECT s_no FROM student;
        TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
        students REC_STUDENT;
    BEGIN
      OPEN CUR_STUDENT;
      WHILE (TRUE) LOOP
        FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
        FORALL i IN 1..students.count SAVE EXCEPTIONS
          UPDATE student SET s_grade=s_grade+1 WHERE s_no=students(i);
        COMMIT;
        EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
      END LOO;
      dbms_output.put_line('finished');
    END;

    说明:

            把student表中要更新的记录的学号拿出来放在游标CUR_STUDENT,每次从这个游标里抓取10万条数据赋值给数组students,每次更新这10万条记录。循环进行直到游标里的数据全部抓取完。

            FETCH .. BULK COLLECT INTO .. LIMIT rows语句中:这个rows我测试目前最大可以为10万条。

    (2).存储过程2(ROWID)

           如果我们这个student表没有主键,也没有索引呢,该怎么来做呢?

    分析下:

           ROWNUM是伪列,每次获取结果后,然后在结果集里会产生一列,从1开始排,每次都是从1开始排。

            ROWID在每个表中,每条记录的ROWID都是唯一的。在这种情况下,我们可以用ROWID。但要注意的是,ROWID是一个类型,注意它和VARCHAR2之间的转换。有两个方法:ROWIDTOCHAR()是把ROWID类型转换为CHAR类型;CHARTOROWID()是把CAHR类型转换为ROWID类型。

    接下来我们编写存储过程process_student2,脚本如下:

    CREATE OR REPLACE PROCEDURE process_student1 AS
        CURSOR CUR_STUDENT IS SELECT ROWIDTOCHAR(ROWID) FROM student;
        TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
        students REC_STUDENT;
    BEGIN
      OPEN CUR_STUDENT;
      WHILE (TRUE) LOOP
        FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
        FORALL i IN 1..students.count SAVE EXCEPTIONS
          UPDATE student SET s_grade=s_grade+1 WHERE ROWID=CHARTOROWID(students(i));
        COMMIT;
        EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
      END LOO;
      dbms_output.put_line('finished');
    END;

    说明:

           我们首先查到记录的ROWID并把它转换为CHAR类型,存放到游标CUR_STUDENT里,

    再每次抓取10万条数据赋值给数组进行更新,更新语句的WHERE条件时,又把数组元素是CAHR类型的rowid串转换为ROWID类型。

    附.参考资料

    存储过程基础:

           https://www.yiibai.com/plsql/plsql_basic_syntax.html

    存储过程进阶之FORALL:

           https://blog.csdn.net/leshami/article/details/7536926

           https://blog.csdn.net/jie1336950707/article/details/49966753

    存储过程进阶之BUIL COLLECT:

           https://blog.csdn.net/leeboy_wang/article/details/7991021

           https://blog.csdn.net/leshami/article/details/7545597

    联合数组:

           https://blog.csdn.net/leshami/article/details/7372061

    展开全文
  • Oracle数据库存储number类型数据

    万次阅读 2018-12-17 15:50:44
    转载自初一七月Oracle数据类型之number oraclenumber类型是oracle的内置类型之一,是oracle的最基础数值数据类型。在9iR2及其以前的版本中只支持一种适合存储数值数据的固有数据类型,在10g以后,才出现了两种新的...

    转载自初一七月Oracle数据类型之number

    oracle的number类型是oracle的内置类型之一,是oracle的最基础数值数据类型。在9iR2及其以前的版本中只支持一种适合存储数值数据的固有数据类型,在10g以后,才出现了两种新的数值类型,即推出本地浮点数据类型(Native Floating-Point Data Types): BINARY_FLOAT(单精度32位)和BINARY_DOUBLE(双精度64位). 这些新数据类型都是基于IEEE二进制浮点运算标准,ANSI/IEEE Std 754-1985 [IEEE 754],使用这些类型时要加上文字f(BINARY_FLOAT)或者d(BINARY_DOUBLE),比如2.07f、3.000094d。

    number数据类型

    number类型的语法很简单:number(p,s):
    p:精度位,precision,是总有效数据位数,取值范围是38,默认是38,可以用字符*表示38。
    s:小数位,scale,是小数点右边的位数,取值范围是-84~127,默认值取决于p,如果没有指定p,那么s是最大范围,如果指定了p,那么s=0。
    p:is the precision,or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.
    s:is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.

    number类型的p和s,与其底层存储完全没有关系,根本不会影响数据在磁盘上如何存储,它只会影响允许哪些值以及数值如何舍入,你可以认为其是对数据的“编辑”。简单的说,精度位p表示数值最多能有多少个有效数字,而小数位s表示最多能有多少位小数。换句话说,p表示一共有多少位有效数字(即小数点左边最多有p-s位有效数字),s表示小数点右边有s位有效数字。如number(5,2)类型的数据,就表示小数点左边最多有3位有效数字,右边最多有2位有效数字,加起来就是最多有5位有效数字,超过这个范围的数字就不能正确的存储下来,注意这里说的是不能正确存储,但并不是不能存储。

    最高整数位数=p-s
    s正数,小数点右边指定位置开始四舍五入
    s负数,小数点左边指定位置开始四舍五入
    s是0或者未指定,四舍五入到最近整数
    当p小于s时候,表示数字是绝对值小于1的数字,且从小数点右边开始的前s-p位必须是0,保留s位小数。

    p>0,对s分2种情况:

    1. s>0
      精确到小数点右边s位,并四舍五入。然后检验有效数位是否<=p;如果s>p,小数点右边至少有s-p个0填充。
    2. s<0
      精确到小数点左边s位,并四舍五入。然后检验有效数位是否<=p+|s|

    具体数据可参考下表

    参考栗子

    里面发生错误的行有的是因为源数据超过了可以表示的范围,有的是因为进行小数四舍五入后超过了可以表示的范围。

    展开全文
  • OracleNUMBER类型最多由三个部分构成,这三个部分分别是最高位表示位、数据部分、符号位。其中负数包含符号位,正数不会包括符号位。另外,数值0比较特殊,它只包含一个数值最高位表示位80,没有数据部分。  ...
    Oracle的NUMBER类型最多由三个部分构成,这三个部分分别是最高位表示位、数据部分、符号位。其中负数包含符号位,正数不会包括符号位。另外,数值0比较特殊,它只包含一个数值最高位表示位80,没有数据部分。
    

       正数的最高位表示位大于80,负数的最高位表示位小于80。其中一个正数的最高位是个位的话,则最高位表示位为C1,百位、万位依次为C2、C3,百分位、万分为依次为C0、BF。一个负数的最高位为个位的话,最高位表示位为3E,百位、万位依次为3D、3C,百分位、万分位依次为3F、40。

       数据部分每一位都表示2位数。这个两位数可能是从0到99,如果是数据本身是正数,则分别用二进制的1到64表示,如果数据本身是负数,则使用二进制65到2表示。

       符号位用66表示。

       上面的这些是我通过DUMP结果总结出来的,对于上面提到的这些关系常数,Oracle之所以这样选择是有道理的,我们后面根据例子也可以推导出来,而且会进一步说明为什么会采用这种方式表示。这里列出的意思是使大家先对NUMBER类型数据有一个大概的了解。

       下面我们通过一个例子详细说明:

       SQL> CREATE TABLE TEST_NUMBER (NUMBER_COL NUMBER);
            表已创建。
       SQL> INSERT INTO TEST_NUMBER VALUES (0);
       SQL> INSERT INTO TEST_NUMBER VALUES (1);
       SQL> INSERT INTO TEST_NUMBER VALUES (2);
       SQL> INSERT INTO TEST_NUMBER VALUES (25);
       SQL> INSERT INTO TEST_NUMBER VALUES (123);
       SQL> INSERT INTO TEST_NUMBER VALUES (4100);
       SQL> INSERT INTO TEST_NUMBER VALUES (132004078);
       SQL> INSERT INTO TEST_NUMBER VALUES (2.01);
       SQL> INSERT INTO TEST_NUMBER VALUES (0.3);
       SQL> INSERT INTO TEST_NUMBER VALUES (0.00000125);
       SQL> INSERT INTO TEST_NUMBER VALUES (115.200003);
       SQL> INSERT INTO TEST_NUMBER VALUES (-1);
       SQL> INSERT INTO TEST_NUMBER VALUES (-5);
       SQL> INSERT INTO TEST_NUMBER VALUES (-20032);
       SQL> INSERT INTO TEST_NUMBER VALUES (-234.432);
       SQL> COMMIT;
            提交完成。

       SQL> COL D_NUMBER FORMAT A50
       SQL> SELECT NUMBER_COL, DUMP(NUMBER_COL, 16) D_NUMBER FROM TEST_NUMBER;
            NUMBER_COL D_NUMBER
            ---------- --------------------------------------------------

               0 Typ=2 Len=1: 80
             1 Typ=2 Len=2: c1,2
             2 Typ=2 Len=2: c1,3
            25 Typ=2 Len=2: c1,1a
           123 Typ=2 Len=3: c2,2,18
          4100 Typ=2 Len=2: c2,2a
    132004078 Typ=2 Len=6: c5,2,21,1,29,4f
          2.01 Typ=2 Len=3: c1,3,2
            .3 Typ=2 Len=2: c0,1f
    .00000125 Typ=2 Len=3: be,2,1a
    115.200003 Typ=2 Len=6: c2,2,10,15,1,4
            -1 Typ=2 Len=3: 3e,64,66
            -5 Typ=2 Len=3: 3e,60,66
        -20032 Typ=2 Len=5: 3c,63,65,45,66
      -234.432 Typ=2 Len=6: 3d,63,43,3a,51,66

    已选择15行。 


         
       下面根据例子得到的结果,对每行进行说明。首先说明两点基本的。DUMP函数返回的TYPE=2表示DUMP的数据类型是NUMBER,LENGTH=N表示数值在数据库中存储的长度是N。

       1.DUMP(0)的结果是0x80,在前面已经提到,0只有高位表示位,没有数据位。由于0的特殊,既不属于正数,也不属于负数,因此使用高位表示位用80表示就足够了,不会和其它数据冲突,Oracle出于节省空间的考虑将后面数据部分省掉了。但是为什么Oracle选择0x80表示0呢?我们知道正数和负数互为相反数,每个正数都有一个对应的负数。因此如果我们要使用编码表示数值,则表示正数和负数的编码应该各占一半,这样才能保证使Oracle表示数据范围是合理的。而0x80的二进制编码是1000 0000,正好是一个字节编码最大值的一半,因此,Oracle选择0x80来表示0,是十分有道理的。

       2.DUMP(1)的结果是0xc102,0xc1表示了最高位个位,0x2表示数值是1。首先,Oracle为什么用C1表示个位呢?其实,道理和刚才的差不多。采用科学计数法,任何一个实数S都可以描述为A.B×10n,A表示整数部分,B表示小数部分,而N表示10的指数部分。当S大于1时,N大于等于0,S小于1时,N小于0。也就是说,采用指数的方式表示,N大于0和N小于0的情况各占一半左右时,Oracle所表示的范围最广。因此,Oracle选择了C1表示个位是最高位的情况。

       SQL> SELECT TO_CHAR(ROUND(TO_NUMBER('81', 'XXX') + (TO_NUMBER('FF', 'XXX') - TO_NUMBER('81', 'XXX') + 1)/2), 'XX') FROM DUAL; 

             TO_
             ---
             C1

       为什么ORACLE使用0x2表示1,而不直接使用0x1表示1呢?Oracle每个字节表示2位数,因此对于这个2位数,出现的可能是0~99共100种可能,问题出在0这里。Oracle底层是用C语言实现的,我们知道二进制0在C语言中用作字符串终结符,Oracle为了避免这个问题,因此使用了0x1表示0,并依次类推,使用0x64表示99。

       3.DUMP(2)的结果是0xc103。
       4.DUMP(25)的结果是0xc11a。前面提到,数据部分是以2位为最小单位保存的。因此对于25来说,最高位表示位仍然是个位,个位上的值是25,根据上面推出的规则,25在存储为0xc11a。
       SQL> SELECT TO_CHAR(25 + 1, 'xx') FROM DUAL; 
            TO_
            ---
            1a

       5.DUMP(123)的结果是0xc20218。由于123最高为是百位,所以最高位表示位为0xc2,百位上是1,用0x02表示,个位上是23,用0x18表示。

       6.DUMP(4100)的结果是0xc22a。
         注意一点,如果数字最后数位上如果是0,Oracle出于节省空间的考虑不会存储。比如:4100只保存百位上的41,12000000只保存百位位上的12,512000只保存万位上的51和百位上的20。

       7.DUMP(132004078)的结果是0xc5022101294f。最高位是亿位,因此用0xC5表示,亿位上是1用0x02表示,百位位上是32用0x21表示,万位上是0用0x01表示,百位上是40用0x29表示,个位上78用0x4F表示。
        注意:中间数位上的0不能省略。

       8.DUMP(2.01)的结果是0xc10302。最高位是个位用0xC1表示,个位上是2用0x03表示,百分位上是1用0x02表示。
         注意:个位下面一位是百分位不是十分位。
     

       9.DUMP(0.3)的结果是0xc01f。最高位是百分位,使用0xC0表示,百分位上是30用0x1F表示。

       10.DUMP(0.00000125)的结果是0xbe021a。最高位是百万分位,用0xBE表示,最高位上的1用0x02表示,25用0x1a表示。

       11.DUMP(115.200003)的结果是0xc20210150104。

       12.DUMP(-1)的结果是0x3e6466。最高位个位,用0x3E表示,64表示个位上是1,66是符号位,表示这个数是负数。

       负数和正数互为相反数,负数的最高位表示位和它对应的相反数的最高位相加的值是FF。1的最高位表示位是C1,-1的最高位表示位是3E。负数中1用64表示。负数中的数值和它相反数的数据相加是0x66,也就是符号位。正数1用0x02表示,负数1用0x64表示,二者相加是0x66。负数多个一个标识位,用0x66表示。由于正数的表示范围是0x01到0x64,负数的表示范围是0x65到0x02。因此,不会在表示数字时出现的0x66表示。

       13.DUMP(-5)的结果是0x3e6066。0x3e表示最高位是个位,0x60表示个位上是5,0x66是符号标识位。0x3E加0xC1是0xFF。0x60加0x06的结果是0x66。

       14.DUMP(-20032)的结果是0x3c63654566。最高位是万位,正数的万位是0xC3,因此负数的万位是0x3C。万位上是2,正数用0x03表示,负数为0x63,百位上是0,正数用0x01表示,负数使用0x65表示,个位上是32,正数用0x21表示,负数使用0x45表示。0x66是负数表示位。

       15.DUMP(-234.432)的结果是0x3d63433a5166。

       根据Oracle的存储特性,还可以推出Oracle的number类型的取值范围。Oracle的concept上是这样描述的:
       The following numbers can be stored in a NUMBER column:
       Positive numbers in the range 1 x 10^-130 to 9.99...9 x 10^125 with up to 38 significant digits.
       Negative numbers from -1 x 10^-130 to 9.99...99 x 10^125 with up to 38 significant digits.
       Zero.

       下面来推导出取值范围。
       来看符号位,0xC1表示个位。

       SQL> select to_number('ff', 'xxx') - to_number('c1', 'xxx') from dual;
            TO_NUMBER('FF','XXX')-TO_NUMBER('C1','XXX')
            -------------------------------------------
            62

       由于Oracle是两位、两位存储的,因此最高位相当于62×2=124,而且最高位上最大值是99,因此正数的最大值为9.999……×10^125。

       SQL> select to_number('c1', 'xxx') - to_number('80', 'xxx') from dual;

            TO_NUMBER('C1','XXX')-TO_NUMBER('80','XXX')
            -------------------------------------------
            65

       最高位相当于65×2=130,因此正数的最小值为1×10^-130。

       负数和正数在各使用了一半的编码,因此具有相同的极值范围。
    展开全文
  • 本篇文章是对OracleNumber型数值存储与转换的实现方法进行了详细的分析介绍,需要的朋友参考下
  • 【inernal】oraclenumber类型存储解析

    千次阅读 2017-12-10 17:58:37
     number类型不指定精度即相当于设置了number最大精度(38位)。  number类型的实际存储机制与varchar雷同,即存储多少数据占用多少空间。  number类型下存储的23.23和number(9,3)下存储的23.23都是一样的。...
  • oracle中所有的数值类型除了binary_float和binary_double之外,其他的数据类型都转换为number类型存储number类型是一种变长格式,长度范围[0~22],精度可达38numeric(p,s):完全映射至number(p,s),p默认为38decimal...
  • Oracle Number类型

    千次阅读 2013-09-06 10:34:32
    NUMBER,是以十进制格式进行存储的,它便于存储,但是在计算上,系统会自动的将它转换成为二进制进行运算的。...☆Number补充 在Oracle Number类型可以用来存储0,正负定点或者浮点数,可表示的数据范围在 1.0 *
  • (网上说没有指定oraclenumber字段长度会就会科学计数超过8位还是9位)。然后问题来了....... 当取出库中字段去前端展示时候,发现最后四位都变成了0,有的是最后追加了10000。 例子: 原始值 123457689098221781...
  • Oracle存储过程

    万次阅读 多人点赞 2019-07-01 14:52:17
    Oracle存储过程详解 procedure 1.创建Oracle存储过程 prodedure create or replace procedure test(var_name_1 in type,var_name_2 out ty pe) as --声明变量(变量名 变量类型) begin --存储过程的执行体 end ...
  • 将Java对象存储Oracle数据库中

    千次阅读 2010-10-26 14:12:00
    将Java对象存储Oracle数据库中
  • oracle存储过程

    千次阅读 2012-10-14 16:20:40
    一,oracle存储过程语法   1.oracle存储过程结构  CREATE OR REPLACE PROCEDURE oracle存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END oracle存储过程名字...
  • varchar2转number 详解 Oracle

    千次阅读 2019-03-14 11:27:10
    varchar2转number 详解 Oracle 1.使用转换方法: to_number(‘12.50’) 2.方法1存在一个问题,如果转换一个可能为null的varchar2字段值,转换之后结果依然为null,而null与任何值相加结果都为null,这样可能导致查询...
  • oracle 存储过程的基本语法 及注意事项 oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名 ( 参数 1 IN NUMBER, 参数 2 IN NUMBER ) IS 变量 1 INTEGER :=0; 变量 2 DATE; BEGIN END ...
  • 其实有公式可以计算: number(p,s)占用得空间为: length = floor((p+1)/2) + 1 ...例如:NUMBER(14,4)的类型数值,存储空间为 select floor((14+1)/2) + 1 from dual 结果输出为: 8
  • JPA调用oracle存储函数

    千次阅读 2018-08-28 18:17:47
    JPA调用oracle存储函数 Oracle stored procedures 带有返回值 简单的Oracle函数 Oracle函数 CREATE OR REPLACE **PROCEDURE** count_comments ( postId IN NUMBER, commentCount OUT NUMBER ) AS BEGIN ...
  • 在oralce中,如果存储过程需要接收含有数值类型的参数时,如何声明呢。如下: CREATE OR REPLACE PACKAGE GPS.PKG_MONTH_TARGET AS --------------写入一条日志----------------- PROCEDURE LOG (CUR_MONTH_...
  • 关于oracle的int能存储最大最小值

    万次阅读 2013-11-24 01:04:41
    在网上很多资料都说oracle的int类型相当于number(38),也有少量说是10^126-1的,今天特意测试了下发现,两个说法都不对.一下是次数代码: drop table tt3; create table tt3(a int ); insert into tt3 values...
  • Oracle ASM存储转移

    千次阅读 2012-09-06 19:25:15
    ASM存储方式随着ORACLE 10g的推出,作为oracle一直力推的一个存储新特性。尽管在稳定性和和操作性还有有待改进,但许多企业已经把ASM存储方式部署在核心业务系统中 了。 ASM特性为我们做迁移提供了更多的选择方案...
  • oracle存储过程分页处理

    千次阅读 2012-08-26 16:39:42
    知识点链接 1、oracle分页 2、游标使用 创建存储过程 ...create or replace procedure copylimit(startpoint number,getnum number) as /*定义table*/ type emp_tab is table of myemp%rowty
  • Oracle数据类型之number

    千次阅读 2011-12-27 08:48:02
    oraclenumber类型是oracle的内置类型之一,是oracle的最基础数值数据类型。在9iR2及其以前的版本中只支持一种适合存储数值数据的固有数据类型,在10g以后,才出现了两种新的数值类型,即推出本地浮点数据
  • OracleNumber类型

    千次阅读 2007-08-27 17:22:00
    OracleNumber类型这两天因为工作需要学习了一下OracleNumber数据类型,现在将自己写的部分文档贴上来,一、OracleNumber数据类型在OracleNumber类型可以用来存储0,正负定点或者浮点数,可表示的数据范围在...
  • 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字
  • 今天看到一个有趣的问题,SQL得到的查询结果和存储过程不一致。 原始问题参加:http://www.itpub.net/showthread.php?s=&threadid=763283 首先模拟一下这个问题: SQL> CREATE OR REPLACE PROCEDURE P_TEST...
  • oracle存储过程实例

    2012-11-25 15:06:43
    oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字
  • oracle存储过程语法

    千次阅读 2018-10-29 16:39:33
    前两天无意见看见了一个非常适合学习Oracle附上链接:...Oracle存储过程基本语法 存储过程   1 CREATE OR REPLACE PROCEDURE 存储过程名   2 IS   3 BEGIN   4 NULL;   5 END;  行1:   CREAT...
  • [Oracle]Oracle中的decimal与Number区别

    千次阅读 2015-09-21 14:27:14
    Oracle只是在语法上支持decimal类型,但是在底层实际上它就是number类型,支持decimal类型是为了能把数据从Oracle数据库移到其他数据库中(如DB2等)。因为decimal在Oracle底层就是number类型,所以就当number类型使用...
  • Oracle数据库存储过程开发

    千次阅读 2018-04-06 09:26:29
    Oracle数据库存储过程开发 Oracle数据库存储过程开发: 一个完整的存储过程开发包含如下流程:: 1.存储过程包头Package定义 2.存储过程包体PackageBody定义 3.存储过程定时程序Program定义 4.存储过程定时调度...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 99,844
精华内容 39,937
关键字:

number存储最大oracle