This discussion is archived
3 Replies Latest reply: Sep 2, 2011 7:40 AM by Peter RSS

Question about constructor methods

847115 Newbie
Currently Being Moderated
I'm taking a pl/sql class created an object with a constructor function. I realize that Oracle creates a default one, but I decided to go ahead and write my own. My instructor said that creating my own constructor was unacceptable and that it would not be supportable by standard methodologies. I haven't had a chance to speak with him about it yet, but I'd like some input as to what I may have done that makes it unsupportable. This works just fine and I've had no errors.

CREATE OR REPLACE TYPE cw_department AS OBJECT
(
deptno NUMBER,
dname VARCHAR2(30),
loc VARCHAR2(30),
CONSTRUCTOR FUNCTION cw_department(
deptno NUMBER,
dname VARCHAR2,
loc VARCHAR2)
RETURN SELF AS RESULT,
MEMBER PROCEDURE insert_dept(p_deptno in number, p_dname in varchar2, p_loc in varchar2),
MEMBER PROCEDURE delete_dept(p_deptno in number));
/


CREATE OR REPLACE TYPE BODY cw_department AS
     CONSTRUCTOR FUNCTION cw_department(
deptno NUMBER,
dname VARCHAR2,
loc VARCHAR2)
     RETURN SELF AS RESULT
IS
BEGIN
     SELF.deptno := deptno;
     SELF.dname := dname;
     SELF.loc := loc;
     RETURN;
END cw_department;
     MEMBER PROCEDURE insert_dept(p_deptno in number, p_dname in varchar2, p_loc in varchar2) IS
          v_deptno               NUMBER           := p_deptno;
          v_dname                    VARCHAR2(30)      := p_dname;
          v_loc                    VARCHAR2(30)      := p_loc;
          department_exists      exception;
          BEGIN
          
               BEGIN
                    SELECT     DNAME,
                              LOC
                    INTO     v_dname,
                              v_loc
                    FROM     cw_dept_obj_table
                    WHERE     DNAME      = v_dname
                    AND          LOC          = v_loc;

                    IF v_dname IS NOT NULL THEN
                         RAISE department_exists;
                    END IF;

               EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                         NULL;
               END;

               INSERT INTO cw_dept_obj_table
               VALUES(v_deptno,v_dname,v_loc);

          EXCEPTION
               WHEN department_exists THEN
                    DBMS_OUTPUT.PUT_LINE('Department: '||v_dname||' Location" '||v_loc||' already exists');
               WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
          END insert_dept;

          MEMBER PROCEDURE delete_dept(p_deptno in number) IS
               v_deptno          number;
          BEGIN
          SELECT     DEPTNO
          INTO     v_deptno
          FROM     cw_dept_obj_table
          WHERE     DEPTNO = p_deptno;

          DELETE FROM cw_dept_obj_table
          WHERE     DEPTNO = p_deptno;
          DBMS_OUTPUT.PUT_LINE('Department Number '||p_deptno||' has been deleted');
     EXCEPTION
          WHEN NO_DATA_FOUND THEN
               DBMS_OUTPUT.PUT_LINE('Department Number '||p_deptno||' does not exist');
     END delete_dept;
END;
/
  • 1. Re: Question about constructor methods
    gaverill Journeyer
    Currently Being Moderated
    is there a question here?

    your custom constructor does not do anything different than the default constructor does i.e. assign values to each attribute, and is presumable slower than the default constructor would be, so there's really no reason to implement it. perhaps that's what's meant by unsupportable...

    Gerard
  • 2. Re: Question about constructor methods
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    I don't do objects ... but Example 8-10 would be useful

    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11822/adobjadv.htm#CHDECFFH

    As noted earlier, you'd define your own constructor if you want it to do something more than a default behaviour.


    Hemant K Chitale
  • 3. Re: Question about constructor methods
    Peter Explorer
    Currently Being Moderated
    I have worked a lot with objects, and I have to say that i don't get the problem using your own constructors. A system constructor can be overloaded by a user defined constructor, as long as there is a need for it. Your example does not make sense, as already stated before, but I can explain why you should use a user defined constructor. If you have an object for instance with 10 or more attributes, it would not be workable using a constructor with all the attributes as parameter. You can create a constructor without any of the attributes. That way you can instantiate an object without the obligation to fill all the attributes with values (or null). Let me give you an example here:

    create table persons(  id number(9)
    , firstname varchar2(50)
    , lastname  varchar2(50)
    , birthdate date
    );
    
    create or replace type tPerson as object
    (
      id number(9)
    , firstname varchar2(50)
    , lastname  varchar2(50)
    , birthdate date
    , member procedure sel( pID in number )
    , member procedure post
    , constructor function tPerson( pID in number default null ) return self as result
    )
    /
    
    create or replace type body tPerson as 
      member procedure sel( pID in number )
      is
      begin
         select tPerson(id,firstname, lastname, birthdate)
           into self
           from persons
          where id = pID;
      exception 
         when no_data_found
         then
           dbms_output.put_line( 'person with ID '||pID||' not found' );         
      end sel; 
      --
      member procedure post
      is
        lID        persons.id%type;
        lFirstname persons.firstname%type;
        lLastname  persons.lastname%type;
        lBirthdate persons.birthdate%type;
      begin
         lID        := self.id;
         lFirstname := self.firstname;
         lLastname  := self.lastname;
         lBirthdate := self.birthdate;
         --
         merge into persons trg
         using (select lID     id
                  , lFirstname firstname
                  , lLastname  lastname
                  , lBirthdate birthdate
              from dual) src
         on ( src.id = trg.id)
         when matched then
           update set trg.firstname = src.firstname
                    , trg.lastname = src.lastname
                    , trg.birthdate = src.birthdate
         when not matched then
           insert ( trg.id, trg.firstname, trg.lastname, trg.birthdate )
           values ( src.id, src.firstname, src.lastname, src.birthdate );
      end;
      --
      constructor function tPerson( pID in number default null ) return self as result
      is
      begin
         if pID is not null
         then
           sel( pID => pID );
         end if;
         return;
      end tPerson;
      --
    end;
    
    -- insert a row to get some data, although you could do this using the object
    insert into persons values(1, 'Steve', 'Johnson', to_date('01-01-1970','mm-dd-yyyy'));
    
    -- An example of using the object, and using the methods. Here you see how you can fill the object instance
    -- with data retrieved from a table, based on the pID parameter. the post method updates the data in the table
    declare
       l_person tPerson;
    begin
       l_person := tPerson( pID => 1 );
       dbms_output.put_line( l_person.firstname ||' '|| l_person.lastname ); -- output: Steve Johnson
       l_person.firstname := 'Sarah';
       l_person.post;
       --
       l_person.sel( pID => 1 );
       dbms_output.put_line( l_person.firstname ||' '|| l_person.lastname );  -- output: Sarah Johnson
    end;
    As you can see it is also good practice to use methods in object as was it a real object. You do not have to parametrize the procedures, since you can access the attributes of the objects and a select or an update will use that attributes. I consider it (in certain circumstances) nice programming with clean code. This was a very simple example of what you can do with objects. For me i think it is a pitty you cannot create private methods and private attributes etc... but hey, then it would be like really OO... :-)

    Edited by: pboekelaar on Sep 2, 2011 4:35 PM

    Edited by: pboekelaar on Sep 2, 2011 4:36 PM

    Edited by: pboekelaar on Sep 2, 2011 4:39 PM

Legend

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