Oracle PL/SQL Tutorial/PL SQL Data Types/BINARY INTEGER DOUBLE FLOAT — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 13:07, 26 мая 2010
Содержание
BINARY_INTEGER
The BINARY_INTEGER datatype is used for declaring signed integer variables.
BINARY_INTEGER variables are stored in binary format, which takes less space.
Calculations on binary integers can also run slightly faster because the values are already in a binary format.
The Syntax for the BINARY_INTEGER Datatype
<source lang="sql">
variable_name BINARY_INTEGER;</source>
BINARY_INTEGER in action
Both of these datatypes exist only in PL/SQL, and you cannot create a column of these types.
<source lang="sql">
SQL> SQL> declare
2 variable1_nr BINARY_INTEGER; 3 variable2_nr PLS_INTEGER; 4 begin 5 NULL; 6 7 end; 8 /
PL/SQL procedure successfully completed. SQL> SQL> -- SQL></source>
BINARY_INTEGER Subtypes
Subtype Usage POSITIVE Allows only positive integers to be stored, up to the maximum of 2,147,483,647. Zero is not considered a positive number, and so is not an allowed value. NATURAL Allows only natural numbers to be stored, which includes zero. Allowed values are 0, 1, 2, 3, and so on up to the maximum of 2,147,483,647. POSITIVEn Like POSITIVE but cannot be null. NATURALn Like NATURAL but cannot be null. SIGNTYPE Restricts a variable to only the values -1, 0, and 1.
NUMBER and BINARY_INTEGER Data types
<source lang="sql">
SQL> SQL> set timing on SQL> declare
2 v_nr number; 3 begin 4 for i in 1..1000000 loop 5 v_nr:=v_nr+i-i+i*2-i*2; 6 end loop; 7 DBMS_OUTPUT.put_line(v_nr); 8 end; 9 /
PL/SQL procedure successfully completed. Elapsed: 00:00:00.93 SQL> SQL> declare
2 v_nr binary_integer; 3 begin 4 for i in 1..1000000 loop 5 v_nr:=v_nr+i-i+i*2-i*2; 6 end loop; 7 DBMS_OUTPUT.put_line(v_nr); 8 end; 9 /
PL/SQL procedure successfully completed. Elapsed: 00:00:00.25 SQL> SQL> set timing off</source>
Using BINARY_FLOAT and BINARY_DOUBLE for complex calculations
The BINARY_FLOAT and BINARY_DOUBLE datatypes offer better performance over other datatypes.
<source lang="sql">
SQL> SQL> -- SQL> SQL> set timing on SQL> SQL> create or replace function pi return number
2 as 3 last_pi number := 0; 4 delta number := 0.000001; 5 pi number := 1; 6 denom number := 3; 7 oper number := -1; 8 negone number := -1; 9 two number := 2; 10 begin 11 loop 12 last_pi := pi; 13 pi := pi + oper * 1/denom; 14 exit when (abs(last_pi-pi) <= delta ); 15 denom := denom + two; 16 oper := oper * negone; 17 end loop; 18 return pi * 4; 19 end; 20 /
Function created. Elapsed: 00:00:00.20 SQL> SQL> select pi from dual;
PI
3.14159465 Elapsed: 00:00:01.03 SQL> SQL> create or replace function pi return number
2 as 3 last_pi BINARY_DOUBLE := 0; 4 delta BINARY_DOUBLE := 0.000001; 5 pi BINARY_DOUBLE := 1; 6 denom BINARY_DOUBLE := 3; 7 oper BINARY_DOUBLE := -1; 8 negone BINARY_DOUBLE := -1; 9 two BINARY_DOUBLE := 2; 10 begin 11 loop 12 last_pi := pi; 13 pi := pi + oper * 1/denom; 14 exit when (abs(last_pi-pi) <= delta ); 15 denom := denom + two; 16 oper := oper * negone; 17 end loop; 18 return pi * 4; 19 end; 20 /
Function created. Elapsed: 00:00:00.14 SQL> SQL> select pi from dual;
PI
3.14159465 Elapsed: 00:00:00.31 SQL> SQL> create or replace function pi return number
2 as 3 last_pi BINARY_FLOAT := 0; 4 delta BINARY_FLOAT := 0.000001; 5 pi BINARY_FLOAT := 1; 6 denom BINARY_FLOAT := 3; 7 oper BINARY_FLOAT := -1; 8 negone BINARY_FLOAT := -1; 9 two BINARY_FLOAT := 2; 10 begin 11 loop 12 last_pi := pi; 13 pi := pi + oper * 1/denom; 14 exit when (abs(last_pi-pi) <= delta ); 15 denom := denom + two; 16 oper := oper * negone; 17 end loop; 18 return pi * 4; 19 end; 20 /
Function created. Elapsed: 00:00:00.11 SQL> SQL> select pi from dual;
PI
3.14159775 Elapsed: 00:00:00.28 SQL> SQL> SQL> set timing off SQL> SQL> --This procedure is revised from one of the discussions on http://asktom.oracle.ru.</source>