Oracle database experiences

Oracle database blog

Working with large integers using PL/SQL – Part I – Addition

Scope

I would like to compute 75! with all the digits what could not be done directly with the Oracle standard data types.

Oracle objects

In order to achieve this, we will use the Oracle objects. Oracle objects are defined with the CREATE TYPE and the CREATE TYPE BODY statements.
Internally, we must define how to store large integers. In our case we will store large integers as 3 integers of 37 digits, so the maximum number of digits will be 111.

Specifications

In the same way than a PL/SQL package, we will define first the specifications of the object type that will be named “large_integer”:

The specifications are:

CREATE OR REPLACE TYPE large_integer AS OBJECT (
   int_part0 NUMBER(37),
   int_part1 NUMBER(37),
   int_part2 NUMBER(37),
   --
   CONSTRUCTOR FUNCTION large_integer
   RETURN SELF AS RESULT,
   --
   CONSTRUCTOR FUNCTION large_integer(int_part0 number,int_part1 number,int_part2 number)
   RETURN SELF AS RESULT,
   --
   MEMBER PROCEDURE display,
   --
   MEMBER PROCEDURE add_li(p_large_integer large_integer)
   --
) INSTANTIABLE NOT FINAL;

Remarks:

  • A constructor method returns SELF which is an instance of the object,
  • The constructor method name is the same than the object type name,
  • The system implicitly defines the second constructor method which is in our case a user-Defined constructor method,
  • “INSTANTIABLE NOT FINAL” means that we could define an object type that inherit from this type and that we can define an instance of this object.

Addition

In order to implement the addition, we will add each “int_part” of the objects and propagate the carry when the addition is greater than an integer of 37 digits as described in the following figure:

Working_large_integers_1h

Remarks:

  • In the above example, we have taken integer blocks of 2 digits,
  • The line Carry1 represents the carry that we must propagate to the next block of integers,
  • The line Carry2 represents the carry that we must add to a block of integers,
  • We have used a number of 38 digits for doing additions in the block of integers in order to have the carry stored in the 38 nth digit.

You will find below the BODY part of the “larger_integer” TYPE:

CREATE OR REPLACE TYPE BODY large_integer
IS
   CONSTRUCTOR FUNCTION large_integer 
   RETURN SELF AS RESULT 
   IS
   BEGIN
      int_part0:=0;
      int_part1:=0;
      int_part2:=0;
      RETURN;
   END large_integer;
   --
   CONSTRUCTOR FUNCTION large_integer(int_part0 number,int_part1 number,int_part2 number) 
   RETURN SELF AS RESULT 
   IS
   BEGIN
      SELF.int_part0:=int_part0;
      SELF.int_part1:=int_part1;
      SELF.int_part2:=int_part2; 
      RETURN;
   END large_integer;
   --
   MEMBER PROCEDURE display 
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('#' 
                        || LPAD(TO_CHAR(SELF.int_part2),37,'0') 
                        || LPAD(TO_CHAR(SELF.int_part1),37,'0') 
                        || LPAD(TO_CHAR(SELF.int_part0),37,'0'));
   END display;
   --
   MEMBER PROCEDURE add_li(p_large_integer large_integer) 
   IS
      v_carry    NUMBER    :=0;
      v_sub_add  NUMBER(38);
   BEGIN
      v_sub_add := SELF.int_part0 + p_large_integer.int_part0; 

      v_carry := TO_NUMBER(SUBSTR(LPAD(TO_CHAR(v_sub_add),38,'0'),1,1));

      SELF.int_part0 := TO_NUMBER(SUBSTR(LPAD(TO_CHAR(v_sub_add),38,'0'),2));

      v_sub_add := SELF.int_part1 + p_large_integer.int_part1 + v_carry;

      v_carry := TO_NUMBER(SUBSTR(LPAD(TO_CHAR(v_sub_add),38,'0'),1,1));

      SELF.int_part1 := TO_NUMBER(SUBSTR(LPAD(TO_CHAR(v_sub_add),38,'0'),2));

      v_sub_add := SELF.int_part2 + p_large_integer.int_part2 + v_carry;

      v_carry := TO_NUMBER(SUBSTR(LPAD(TO_CHAR(v_sub_add),38,'0'),1,1)); 

      SELF.int_part2 := TO_NUMBER(SUBSTR(LPAD(TO_CHAR(v_sub_add),38,'0'),2));

      IF v_carry > 0 THEN 
         RAISE_APPLICATION_ERROR(-20000,'Add_li:Large integer too big!'); 
      END IF;
     
   END add_li;
  
END;

Examples:

SA> DECLARE
  2     v_li1 large_integer;
  3     v_li2 large_integer;
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE('-- Example1: Addition');
  6     v_li1 := large_integer(9999999999999999999999999999999999999,9999999999999999999999999999999999999,0);
  7     v_li2 := large_integer(9999999999999999999999999999999999999,9999999999999999999999999999999999999,0);
  8     v_li1.display();
  9     v_li2.display();
 10     v_li1.add_li(v_li2);
 11     v_li1.display();
 12
 13     DBMS_OUTPUT.PUT_LINE('-- Example2: Addition');
 14     v_li1 := large_integer(9999999999999999999999999999999999999,9999999999999999999999999999999999999,999999999999999999999999999999999999);
 15     v_li2 := large_integer(1,0,0);
 16     v_li1.display();
 17     v_li2.display();
 18     v_li1.add_li(v_li2);
 19     v_li1.display();
 20  END;
 21  /
-- Example1: Addition
#000000000000000000000000000000000000099999999999999999999999999999999999999999999999999999999999999999999999999
#000000000000000000000000000000000000099999999999999999999999999999999999999999999999999999999999999999999999999
#000000000000000000000000000000000000199999999999999999999999999999999999999999999999999999999999999999999999998
-- Example2: Addition
#099999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
#000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
#100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

PL/SQL procedure successfully completed.

Remark:

  • In this series of post we will focus mainly on natural numbers.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: