Oracle PL/SQL/Data Type/Binary Data Type

Материал из SQL эксперт
Перейти к: навигация, поиск

BINARY_FLOAT and BINARY_DOUBLE example

   <source lang="sql">

SQL> CREATE TABLE binary_test (

 2    bin_float BINARY_FLOAT,
 3    bin_double BINARY_DOUBLE
 4  );

Table created. SQL> SQL> INSERT INTO binary_test (

 2    bin_float, bin_double
 3  ) VALUES (
 4    39.5f, 15.7d
 5  );

1 row created. SQL> SQL> INSERT INTO binary_test (

 2    bin_float, bin_double
 3  ) VALUES (
 4    BINARY_FLOAT_INFINITY, BINARY_DOUBLE_INFINITY
 5  );

1 row created. SQL> SQL> drop table binary_test; Table dropped.

 </source>
   
  


BINARY_FLOAT_INFINITY, BINARY_DOUBLE_INFINITY

   <source lang="sql">

SQL> CREATE TABLE binary_test (

 2    bin_float BINARY_FLOAT,
 3    bin_double BINARY_DOUBLE
 4  );

Table created. SQL> SQL> INSERT INTO binary_test (

 2    bin_float, bin_double
 3  ) VALUES (
 4    39.5f, 15.7d
 5  );

1 row created. SQL> SQL> INSERT INTO binary_test (

 2    bin_float, bin_double
 3  ) VALUES (
 4    BINARY_FLOAT_INFINITY, BINARY_DOUBLE_INFINITY
 5  );

1 row created. SQL> SQL> drop table binary_test; Table dropped.

 </source>
   
  


BINARY_INTEGER as loop counter

   <source lang="sql">
 

SQL> SQL> SQL> CREATE TABLE MyTable (

 2    num_col    NUMBER,
 3    char_col   VARCHAR2(60)
 4    );

Table created. SQL> SQL> SQL> DECLARE

 2    v_LoopCounter BINARY_INTEGER := 1;
 3  BEGIN
 4    LOOP
 5      INSERT INTO MyTable (num_col)
 6        VALUES (v_LoopCounter);
 7      v_LoopCounter := v_LoopCounter + 1;
 8      EXIT WHEN v_LoopCounter > 50;
 9    END LOOP;
10  END;
11  /

PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;

NUM_COL CHAR_COL

------------------------------------------------------------
   1.00
   2.00
   3.00
   4.00
   5.00
   6.00
   7.00
   8.00
   9.00
  10.00
  11.00
NUM_COL CHAR_COL

------------------------------------------------------------
  12.00
  13.00
  14.00
  15.00
  16.00
  17.00
  18.00
  19.00
  20.00
  21.00
  22.00
NUM_COL CHAR_COL

------------------------------------------------------------
  23.00
  24.00
  25.00
  26.00
  27.00
  28.00
  29.00
  30.00
  31.00
  32.00
  33.00
NUM_COL CHAR_COL

------------------------------------------------------------
  34.00
  35.00
  36.00
  37.00
  38.00
  39.00
  40.00
  41.00
  42.00
  43.00
  44.00
NUM_COL CHAR_COL

------------------------------------------------------------
  45.00
  46.00
  47.00
  48.00
  49.00
  50.00

50 rows selected. SQL> SQL> drop table MyTable; Table dropped. SQL> SQL>

 </source>
   
  


BINARY_INTEGER assignment

   <source lang="sql">
 

SQL> SQL> DECLARE

 2    v_BinInt BINARY_INTEGER;
 3  BEGIN
 4    v_BinInt := 2147483647;
 5    v_BinInt := v_BinInt + 1 - 1;
 6  END;
 7  /

DECLARE

ERROR at line 1: ORA-01426: numeric overflow ORA-06512: at line 5

SQL>

 </source>
   
  


Use binary data type

   <source lang="sql">

SQL> --TO_BINARY_DOUBLE(x): New for Oracle Database 10g. Converts x to a BINARY_DOUBLE. SQL> CREATE TABLE float_point_demo

 2    (dec_num    NUMBER(10,2),
 3     bin_double BINARY_DOUBLE,
 4     bin_float  BINARY_FLOAT);

Table created. SQL> SQL> INSERT INTO float_point_demo VALUES (1234.56,1234.56,1234.56); 1 row created. SQL> SQL> SELECT * FROM float_point_demo;

  DEC_NUM BIN_DOUBLE  BIN_FLOAT

---------- ----------
  1234.56 1.235E+003 1.235E+003

SQL> SQL> SELECT DUMP(dec_num) "Decimal",

 2     DUMP(bin_double) "Double"
 3     FROM float_point_demo;

Decimal


Double


Typ=2 Len=4: 194,13,35,57 Typ=101 Len=8: 192,147,74,61,112,163,215,10

SQL> SQL> SQL> SQL> drop table float_point_demo; Table dropped. SQL> SQL> SQL>


 </source>