Slide 12.13: Constructor methods

Constructor Methods

 SQL> CREATE TYPE  emp_typ  AS OBJECT (
   2    emp_number  NUMBER(5),
   3    emp_name    VARCHAR2(32) );
   4  /
 Type created.

 SQL> CREATE TABLE  emp  OF  emp_typ;
 Table created.

The constructor method

is implicitly created when the object type is created,
initializes the attributes of an object, and
has the same name as the object type and takes argument for each attribute. It must specify all argument values or NULLs.

 SQL> INSERT INTO  emp  VALUES ( emp_typ( 101, 'Kids Next Door' ) );
 1 row created.

 SQL> SET SERVEROUTPUT ON;

 SQL> DECLARE
   2    new_emp  emp_typ := emp_typ( 202, 'Powerpuff Girls' );
   3    alt_emp  emp_typ;
   4  BEGIN
   5    DBMS_OUTPUT.PUT_LINE( new_emp.emp_name );
   6    alt_emp := emp_typ( 331, 'Digimon' );
   7    DBMS_OUTPUT.PUT_LINE( alt_emp.emp_number );
   8    SELECT  VALUE( e )  INTO  alt_emp
   9      FROM  emp e  WHERE  e.emp_number = 101;
  10    DBMS_OUTPUT.PUT_LINE( alt_emp.emp_name );
  11  END;
  12  /

 Powerpuff Girls
 331
 Kids Next Door

 PL/SQL procedure successfully completed.

Question: Display the full emp_typ object for the employee 101 of “Kids Next Door.”

 SQL> SELECT  VALUE( e )
   2    FROM  emp e  WHERE  e = emp_typ( 101, 'Kids Next Door' );

 FROM  emp e  WHERE  e = emp_typ( 101, 'Kids Next Door' )
                     *
 ERROR at line 2:
 ORA-00904: "E": invalid identifier

 SQL> SELECT  VALUE( e )
   2    FROM  emp e  WHERE  VALUE( e ) = emp_typ( 101, 'Kids Next Door' );

 VALUE(E)(EMP_NUMBER, EMP_NAME)
 ------------------------------------------------------
 EMP_TYP(101, 'Kids Next Door')