1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

oracle, integer

Discussion in 'SQL PL/SQL' started by monkey, Jun 3, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    When we write a code, do we add sth to integer like:
    integer(...) and what?
    Many thanks- just checking!!!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Normally you would use the NUMBER datatype:


    create table yapoo(
    snorm number,
    snerm varchar2(20));


    By default NUMBER specifies a NUMBER(38) with no explicit number of decimals. You can also state how many decimal places you want:


    create table plonto(
    gramle number,
    fistubo varchar2(10),
    erpla number(7,2));


    The erpla column would accept numbers up to 7 bytes long with up to 2 decimal places; more decimal places in the number will not throw an error if the total length of the number does not exceed the maximum length (7 in this case) but the decimal portion will be truncated to the declared number of places:


    SQL> create table plonto(
    2 gramle number,
    3 fistubo varchar2(10),
    4 erpla number(7,2));


    Table created.


    SQL> insert into plonto values (1,'Greeper',2.3456);


    1 row created.


    SQL> select * from plonto;


    GRAMLE FISTUBO ERPLA
    ---------- ---------- ----------
    1 Greeper 2.35


    SQL>


    If the supplied value is too large Oracle will throw an error:


    SQL> insert into plonto values (3,'Grouper',77345678.5678)
    2 /
    insert into plonto values (3,'Grouper',77345678.5678)
    *
    ERROR at line 1:
    ORA-01438: value larger than specified precision allowed for this column

    SQL> insert into plonto values (3,'Grouper',773456.5678)
    2 /
    insert into plonto values (3,'Grouper',773456.5678)
    *
    ERROR at line 1:
    ORA-01438: value larger than specified precision allowed for this column

    SQL>
     
  3. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    What does it mean in practise?
    I used integer, not number, this spring , and my tast was ok.
    When do we choose to use integer instead of number?
    MANY THANKS!!!
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    It depends on your requirement: if you want to have whole numbers or not.
    INTEGER is equivalent to NUMBER(38) or to be more accurate NUMBER(38,0).

    The decimal place will be rounded. For example:
    INTEGER(1.2) => 1
    INTEGER(1.5) => 2
    INTEGER(1.9) => 2

    If we need to ensure that the input is whole numbers, then INTEGER is best option to go.
    Otherwise, we can stick with NUMBER data type.

    If I take an example of another of your post:

    Code (SQL):
    SQL> INSERT INTO dog(dogID, dname)  VALUES (1, 'Labrador');

    1 ROW created.

    SQL> INSERT INTO dog(dogID, dname)  VALUES (1.8, 'Labrador 2');

    1 ROW created.

    SQL> SELECT * FROM dog;

         DOGID DNAME      DSCHOOL
    ---------- ---------- ----------
         1 Labrador   ss
         2 Labrador 2 ss