Construct user-defined type with subquery : Constructor « Object Oriented « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Object Oriented
3. Constructor

SQL>
SQL> create table course_schedule
  2  ( course     VARCHAR2(6)
  3  , begindate  DATE
  4  , trainer    NUMBER(4)
  5  , location   VARCHAR2(20)) ;

Table created.

SQL>
SQL>
SQL> insert into course_schedule values ('SQL',date '1999-04-12',1,'VANCOUVER' );

1 row created.

SQL> insert into course_schedule values ('OAU',date '1999-08-10',2,'CHICAGO');

1 row created.

SQL> insert into course_schedule values ('SQL',date '1999-10-04',3,'SEATTLE');

1 row created.

SQL> insert into course_schedule values ('SQL',date '1999-12-13',4,'DALLAS' );

1 row created.

SQL> insert into course_schedule values ('JAV',date '1999-12-13',5,'SEATTLE');

1 row created.

SQL> insert into course_schedule values ('XML',date '2000-02-03',6,'VANCOUVER' );

1 row created.

SQL> insert into course_schedule values ('JAV',date '2000-02-01',7,'DALLAS' );

1 row created.

SQL> insert into course_schedule values ('PLS',date '2000-09-11',8,'VANCOUVER' );

1 row created.

SQL> insert into course_schedule values ('XML',date '2000-09-18',NULL,'SEATTLE');

1 row created.

SQL> insert into course_schedule values ('OAU',date '2000-09-27',9,'DALLAS' );

1 row created.

SQL> insert into course_schedule values ('ERM',date '2001-01-15',10, NULL    );

1 row created.

SQL> insert into course_schedule values ('PRO',date '2001-02-19',NULL,'VANCOUVER' );

1 row created.

SQL> insert into course_schedule values ('RSD',date '2001-02-24',8,'CHICAGO');

1 row created.

SQL>
SQL> create type addressType as object
  2  ( street varchar2(20)
  3  , nr     varchar2(5)
  4  , pcode  varchar2(6)
  5  , city   varchar2(20)
  6  ) ;
  7  /

Type created.

SQL>
SQL> describe addressType
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STREET                                             VARCHAR2(20)
 NR                                                 VARCHAR2(5)
 PCODE                                              VARCHAR2(6)
 CITY                                               VARCHAR2(20)

SQL>
SQL> select type_name, typecode from user_types;

TYPE_NAME                      TYPECODE
------------------------------ ------------------------------
DEBUG_O                        OBJECT
NUMBERLIST_T                   COLLECTION
ADDRESS2                       OBJECT
ZIP_CODE                       OBJECT
NUM_VARRAY                     COLLECTION
NUM_TABLE                      COLLECTION
ADD_LIST                       COLLECTION
HOME_ADD_LIST                  COLLECTION
SYS_PLSQL_14797_54_1           COLLECTION
SYS_PLSQL_14797_DUMMY_1        COLLECTION
SYS_PLSQL_14797_9_1            OBJECT
VCARRAY                        COLLECTION
STRINGS_NT                     COLLECTION
RIVER                          OBJECT
WATERFALL                      OBJECT
SQLMONTH_TABLETYPE             COLLECTION
ORD_TYPE                       OBJECT
ORD_TABLE                      COLLECTION
DTARRAY                        COLLECTION

TYPE_NAME                      TYPECODE
------------------------------ ------------------------------
NMARRAY                        COLLECTION
EMP_T                          OBJECT
ASCII_TABLE_TYPE               OBJECT
PHONE_NUM_REC_TYPE             OBJECT
NUMBERVARRAY                   COLLECTION
ADDRESSTYPE                    OBJECT

25 rows selected.

SQL>
SQL> create table o as select course, begindate, trainer
  2  from   course_schedule;

Table created.

SQL>
SQL> alter table o add (address addressType);

Table altered.

SQL>
SQL> update o
  2  set    o.address =
  3         addressType('','','',
  4                (select initcap(x.location)
  5                 from   course_schedule     x
  6                 where  x.course    = o.course
  7                 and    x.begindate = o.begindate)
  8  );

13 rows updated.

SQL>
SQL> drop table o;

Table dropped.

SQL> drop type addressType force;

Type dropped.

SQL>
SQL> drop table course_schedule;

Table dropped.

SQL>
SQL>