This discussion is archived
5 Replies Latest reply: Dec 6, 2012 3:42 AM by BillyVerreynne RSS

member function and member procedure inside an object type in Oracle.

BS2012 Newbie
Currently Being Moderated
Hi All,
Please do have a look at these codes and help me understand. I have no idea about this member function and member procedure. How do they work? Please explain me about this.

Regards,
BS2012
create type foo_type as object (
  
  foo number,

  member procedure proc(p in number),
  member function  func(p in number) return number
);
/
create type body foo_type as 

  member procedure proc(p in number) is begin
    foo := p*2;
  end proc;

  member function func(p in number) return number is begin
    return foo/p;
  end func;
end;
/
  • 1. Re: member function and member procedure inside an object type in Oracle.
    padders Pro
    Currently Being Moderated
    Suggest you start by reading...

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjbas.htm#autoId10
  • 2. Re: member function and member procedure inside an object type in Oracle.
    BluShadow Guru Moderator
    Currently Being Moderated
    Methods are just like functions or procedures in a package, except they're not in a package, their part of an object type.
    The object has attributes (which are the variables declared in it).

    To use such an object you would do things like this...
    SQL> set serverout on
    SQL>
    SQL> declare
      2    v_foo foo_type;
      3    v_val number;
      4  begin
      5    v_foo := foo_type(20); -- instantiate the object and initialize the object attributes
      6    v_foo.proc(20); -- call the object method (proc)
      7    v_val := v_foo.func(4); -- call the object method (func)
      8    dbms_output.put_line(v_val);
      9  end;
     10  /
    10
    
    PL/SQL procedure successfully completed.
    The Type definition you've declared creates the object class, but not actually an object itself.
    To actually have an object you need to declare a variable of that object class type, and then instantiate it. When you instantiate the object you need to initialize all the attributes (generally you can pass null if required for each of them to initialize them).
    Once you have your object instantiated, you can call the methods within that object as demonstrated above, a bit like calling functions and procedures in a package, except they are methods within the object type itself, and therefore called directly by referencing them from the variable.

    The documentation goes into a lot more detail of objects if you look it up.
  • 3. Re: member function and member procedure inside an object type in Oracle.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    BS2012 wrote:

    Please do have a look at these codes and help me understand. I have no idea about this member function and member procedure. How do they work?
    This is standard object-orientated (o-o) programming principles. The object type created, is called a class in o-o programming. A class has a number of features - including properties (attribute values) and methods (procedures and functions).

    I suggest that you first look at understanding o-o basics and principles - and once you have you head wrapped around those, look at how the Oracle RDBMS implements o-o.
  • 4. Re: member function and member procedure inside an object type in Oracle.
    BS2012 Newbie
    Currently Being Moderated
    Hi All,
    Ok I got this. But can someone give an example where/why we are implementing this? I guess this can be done inside a package as well. I mean please explain me a scenario regarding this Oracle object oriented technique. What's benefit of doing such activities in form of an object type while this can be done inside a package as well? Is it performance related or what?

    Regards,
    BS2012
  • 5. Re: member function and member procedure inside an object type in Oracle.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Yes, this can be implemented using "traditional" procedural-based design, and implemented using a "more modern" o-o design. (though calling the old and new approaches is not correct and misses the strengths of each approach)

    It is unlikely to be performance related - and more likely to be related to design and flexibility.

    As for an example - I have very seldom had to resort to pure o-o to craft solutions in PL/SQL. But you may have a very specialised data model that uses very specialised data types (far more complex than dates, strings and numbers). Perhaps the need for a complex number data type. Or data types that rerpresent quantum state. Whatever. Objects in Oracle allows you to define such a data type and use it for SQL columns and in PL/SQL code.

    As a simplistic example, you can look at how one would use this approach to define a Boolean data type in {message:id=2116991} - not that this in itself has any practical use IMO.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points