Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: Usage of TYPE structure

    Hi,

    I ve a below structured object TYPE in Oracle to calculate the Simple and Compound interest for an amount..

    Code:
    CREATE TYPE pnr_typ AS OBJECT
       (
            principle NUMBER,
            interest NUMBER,
            year NUMBER,
            MEMBER FUNCTION si RETURN FLOAT,
            MEMBER FUNCTION ci RETURN FLOAT,
            MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER
       );
    /
    show err
    
    CREATE TYPE BODY pnr_typ IS
    
          MEMBER FUNCTION si RETURN FLOAT IS
           BEGIN
               RETURN (principle*interest*SELF.year)/100;
           END;      MEMBER FUNCTION ci RETURN FLOAT IS
           BEGIN
               RETURN POWER(SELF.principle*(1 + SELF.interest/100), year);
           END;
    
    
          MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS
           BEGIN
               RETURN (year + invent);
           END;
          END;
    /
    Show err
    With in the TYPE i define some attributes like principle, interest, year and more member functions like si, ci, prod to access and modify the TYPE attributes.

    This TYPE can be used in

    * the column definition while creating a column in a table,
    * the procedures or Oracle DB objects as an instance

    I would like to know if the TYPE (structured) in DB2 can do the same as the above Oracle TYPE.

    Thanks,
    Sn
    Last edited by Shefu; 06-23-06 at 06:19.

  2. #2
    Join Date
    Apr 2005
    Posts
    127
    Hi ,

    I ve successfully created the equivalent for the above Oracle TYPE object in DB2 with the below DB2 code snippet ..


    Code:
    CREATE TYPE s1.pnr_typ AS
           (
           principle INT,
           interest DECIMAL(5, 2),
           year INT
           )
         NOT FINAL
         MODE DB2SQL
           METHOD SI()
           RETURNS FLOAT
           LANGUAGE SQL
           DETERMINISTIC
           CONTAINS SQL
           NO EXTERNAL ACTION,
    
           METHOD CI()
           RETURNS FLOAT
           LANGUAGE SQL
           DETERMINISTIC
           CONTAINS SQL
           NO EXTERNAL ACTION,
    
           METHOD PROD(NUM INT)
           RETURNS INTEGER
           LANGUAGE SQL
           DETERMINISTIC
           CONTAINS SQL
           NO EXTERNAL ACTION
    @
    
       CREATE METHOD SI()
         FOR s1.pnr_typ
         RETURN (SELF..principle*SELF..interest*SELF..year)/100
    @
    
       CREATE METHOD CI()
         FOR s1.pnr_typ
         RETURN SELF..principle * POWER((1 + SELF..interest/100), SELF..year)
    @
    
       CREATE METHOD PROD(NUM INT)
         FOR s1.pnr_typ
         RETURN  NUM+SELF..year
    @

    I do not have an idea how to use this TYPE in tables or SQL queries or Procedures, could any body help me with some simple eg. using this TYPE.

    Thanks,
    Sn

  3. #3
    Join Date
    Apr 2005
    Posts
    127
    Guyz,

    Any helpful documentations on this will do for me...

    Thanks,
    Sn

  4. #4
    Join Date
    Apr 2005
    Posts
    127
    Now I am able to create tables with the structured TYPE as a column , insertions and selections of the TYPE columns into and from the table and the method calls in a Select query is working fine now,

    Could any one throw the light on the usage of structured TYPEs within a procedure or function...

    Any help on this would be highly appreciated..

    Thanks
    Sn

  5. #5
    Join Date
    Apr 2005
    Posts
    127

    Red face

    With due respects to the experts here I wonder is this (TYPE)too complex ... i dint find any solution for my problem...

    Thanks
    Sn

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •