Home » SQL & PL/SQL » SQL & PL/SQL » Binary Integer Division (Oracle 11g R2)
|
|
|
|
|
|
Re: Binary Integer Division [message #675050 is a reply to message #675047] |
Wed, 06 March 2019 10:48   |
 |
jagman
Messages: 8 Registered: March 2019
|
Junior Member |
|
|
I was fooled by reading this excerpt from
https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/datatypes.htm#LNPLS319
Its clearly put there to deceive C programmers like me...
Quote:
PLS_INTEGER and BINARY_INTEGER Data Types
The PL/SQL data types PLS_INTEGER and BINARY_INTEGER are identical. For simplicity, this document uses PLS_INTEGER to mean both PLS_INTEGER and BINARY_INTEGER.
The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits.
The PLS_INTEGER data type has these advantages over the NUMBER data type and NUMBER subtypes:
PLS_INTEGER values require less storage.
PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic.
For efficiency, use PLS_INTEGER values for all calculations in its range.
|
|
|
|
|
|
|
|
Re: Binary Integer Division [message #675060 is a reply to message #675058] |
Thu, 07 March 2019 00:56   |
 |
Michel Cadot
Messages: 68758 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote:that PL/SQL [...] is not performing integer
This is correct they chose to perform NUMBER arithmetic to have no difference between PLS_INTEGER/BINARY_INTEGER and INTEGER types.
What would you then say if a language has 2 (or 3) integer types (for history reason) with different results? 
I once wrote, in the previous millennium, a package for bit manipulation on Oracle INTEGER type (not hardware one so slower but can use bits strings/integers larger than 32/64 bits):
SQL> exec dbms_output.put_line(bit_functions.bit_rsh(111));
55
PL/SQL procedure successfully completed.
Here it is:
create or replace package bit_functions as
function int_to_bits (in_val in integer) return varchar2;
function bits_to_int (in_val in varchar2) return integer;
function bit_and (in_1 in integer, in_2 in integer) return integer;
function bit_or (in_1 in integer, in_2 in integer) return integer;
function bit_xor (in_1 in integer, in_2 in integer) return integer;
function bit_lsh (in_num in integer,
in_shft in integer default 1) return integer;
function bit_rsh (in_num in integer,
in_shft in integer default 1) return integer;
end;
/
show errors;
create or replace package body bit_functions as
function int_to_bits (in_val in integer) return varchar2
is
work_1 number;
work_2 number;
work_3 number;
digit varchar2(1);
accum varchar2(64);
begin
if in_val < 0 or trunc(in_val) != in_val then
raise_application_error(-20000, 'Invalid input value');
end if;
accum := '';
work_1 := in_val;
work_2 := in_val;
loop
work_2 := work_1/2;
work_3 := trunc(work_2);
if work_2 = work_3 then digit := '0';
else digit := '1'; end if;
accum := digit || accum;
if work_3 = 0 then exit; end if;
work_1 := work_3;
end loop;
return accum;
end;
function bits_to_int (in_val in varchar2) return integer
is
i number;
digit number;
accum number;
begin
accum := 0;
for i in 1..length(in_val) loop
digit := to_number(substr(in_val,i,1));
accum := 2*accum + digit;
end loop;
return accum;
end;
function bit_and (in_1 in integer, in_2 in integer) return integer
is
work_1 varchar2(64);
work_2 varchar2(64);
digit number;
i number;
accum number;
begin
if in_1 < 0 or trunc(in_1) != in_1 or in_2 < 0 or trunc(in_2) != in_2 then
raise_application_error(-20000, 'Invalid input value');
end if;
work_1 := int_to_bits (in_1);
work_2 := int_to_bits (in_2);
accum := 0;
if length(work_1) > length(work_2) then
work_2 := lpad(work_2,length(work_1),'0');
end if;
if length(work_2) > length(work_1) then
work_1 := lpad(work_1,length(work_2),'0');
end if;
for i in 1..length(work_1) loop
if substr(work_1,i,1) = '1' and substr(work_2,i,1) = '1' then
digit := 1;
else
digit := 0;
end if;
accum := 2*accum + digit;
end loop;
return accum;
end;
function bit_or (in_1 in integer, in_2 in integer) return integer
is
work_1 varchar2(64);
work_2 varchar2(64);
digit number;
i number;
accum number;
begin
if in_1 < 0 or trunc(in_1) != in_1 or in_2 < 0 or trunc(in_2) != in_2 then
raise_application_error(-20000, 'Invalid input value');
end if;
work_1 := int_to_bits (in_1);
work_2 := int_to_bits (in_2);
accum := 0;
if length(work_1) > length(work_2) then
work_2 := lpad(work_2,length(work_1),'0');
end if;
if length(work_2) > length(work_1) then
work_1 := lpad(work_1,length(work_2),'0');
end if;
for i in 1..length(work_1) loop
if substr(work_1,i,1) = '1' or substr(work_2,i,1) = '1' then
digit := 1;
else
digit := 0;
end if;
accum := 2*accum + digit;
end loop;
return accum;
end;
function bit_xor (in_1 in integer, in_2 in integer) return integer
is
work_1 varchar2(64);
work_2 varchar2(64);
digit number;
i number;
accum number;
begin
if in_1 < 0 or trunc(in_1) != in_1 or in_2 < 0 or trunc(in_2) != in_2 then
raise_application_error(-20000, 'Invalid input value');
end if;
work_1 := int_to_bits(in_1);
work_2 := int_to_bits(in_2);
accum := 0;
if length(work_1) > length(work_2) then
work_2 := lpad(work_2,length(work_1),'0');
end if;
if length(work_2) > length(work_1) then
work_1 := lpad(work_1,length(work_2),'0');
end if;
for i in 1..length(work_1) loop
if substr(work_1,i,1) = substr(work_2,i,1) then
digit := 0;
else
digit := 1;
end if;
accum := 2*accum + digit;
end loop;
return accum;
end;
function bit_lsh (in_num in integer, in_shft in integer default 1)
return integer
is
begin
if in_num < 0 or trunc(in_num) != in_num or in_shft < 0 or trunc(in_shft) != in_shft then
raise_application_error(-20000, 'Invalid input value');
end if;
return power(2,in_shft)*in_num;
end;
function bit_rsh (in_num in integer, in_shft in integer default 1)
return integer
is
begin
if in_num < 0 or trunc(in_num) != in_num or in_shft < 0 or trunc(in_shft) != in_shft then
raise_application_error(-20000, 'Invalid input value');
end if;
return trunc(in_num/power(2,in_shft));
end;
end;
/
show errors
[Updated on: Thu, 07 March 2019 00:59] Report message to a moderator
|
|
|
|
|
Re: Binary Integer Division [message #675071 is a reply to message #675058] |
Thu, 07 March 2019 15:30  |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
i disagree with the other systems and agree with oracle.
111/2 = 55.5
Following the industry standard .5 rounding rule it goes to 56.
however if you want to duplicate your results then simply throw away the remainder.
trunc(111/2)
floor(111/2)
In effect the other systems are simply truncating the remainders anyway. In Oracle you have a choice wither or not you want to use the remainder.
|
|
|
Goto Forum:
Current Time: Wed Jun 11 18:53:58 CDT 2025
|