NUMBER data type : Number « SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL Data Types
3. Number

SQL>
SQL> CREATE TABLE precision (
  2     value NUMBER(38,5),
  3     scale NUMBER(10));

Table created.

SQL>
SQL> INSERT INTO precision (value, scale)
  2     VALUES (12345, 0);

1 row created.

SQL> INSERT INTO precision (value, scale)
  2     VALUES (123456, 0);

1 row created.

SQL> INSERT INTO precision (value, scale)
  2     VALUES (123.45, 0);

1 row created.

SQL> INSERT INTO precision (value, scale)
  2     VALUES (12345, 2);

1 row created.

SQL> INSERT INTO precision (value, scale)
  2     VALUES (123.45, 2);

1 row created.

SQL> INSERT INTO precision (value, scale)
  2     VALUES (12.345, 2);

1 row created.

SQL> INSERT INTO precision (value, scale)
  2     VALUES (1234.5, 2);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     v_integer NUMBER(5);
  3     v_scale_2 NUMBER(5,2);
  4     v_real NUMBER;
  5
  6     CURSOR scale_0_cur
  7     IS
  8        SELECT value
  9        FROM precision
 10        WHERE scale = 0;
 11
 12     CURSOR scale_2_cur
 13     IS
 14        SELECT value
 15        FROM precision
 16        WHERE scale = 2;
 17  BEGIN
 18
 19     DBMS_OUTPUT.PUT_LINE('PRECISION 5 SCALE 0');
 20
 21     OPEN scale_0_cur;
 22
 23     -- Loop thorugh all records that have a scale of zero
 24     LOOP
 25     FETCH scale_0_cur INTO v_real;
 26     EXIT WHEN scale_0_cur%NOTFOUND;
 27
 28     -- Assign different values to the v_integer variable
 29     --  to see how it handles it
 30     BEGIN
 31        DBMS_OUTPUT.PUT_LINE('     ');
 32        DBMS_OUTPUT.PUT_LINE('Assigned: '||v_real);
 33
 34        v_integer := v_real;
 35
 36        DBMS_OUTPUT.PUT_LINE('Stored: '||v_integer);
 37     EXCEPTION
 38        WHEN OTHERS
 39        THEN
 40           DBMS_OUTPUT.PUT_LINE('Exception: '||sqlerrm);
 41     END;
 42     END LOOP;
 43
 44     CLOSE scale_0_cur;
 45
 46
 47  END;
 48  /
PRECISION 5 SCALE 0
Assigned: 12345
Stored: 12345
Assigned: 123456
Exception: ORA-06502: PL/SQL: numeric or value error: number precision too large
Assigned: 123.45
Stored: 123

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table precision;

Table dropped.