3 Replies Latest reply: Sep 2, 2011 9:40 AM by Peter RSS

    Question about constructor methods

    847115
      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
          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
            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
              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