Oracle PL/SQL/Data Type/Binary Data Type
Содержание
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>