Oracle PL/SQL Tutorial/Object Oriented/Alter Type

Материал из SQL эксперт
Перейти к: навигация, поиск

Alter table to add varray type column

   <source lang="sql">

SQL> SQL> create or replace type Address_Type

 2  as object
 3  (  street_addr1   varchar2(25),
 4     street_addr2   varchar2(25),
 5     city           varchar2(30),
 6     state          varchar2(2),
 7     zip_code       number
 8  )
 9  /

SQL> SQL> alter type Address_Type

 2  REPLACE
 3  as object
 4  (  street_addr1   varchar2(25),
 5     street_addr2   varchar2(25),
 6     city           varchar2(30),
 7     state          varchar2(2),
 8     zip_code       number,
 9     member function toString return varchar2,
10     map member function mapping_function return varchar2
11  )
12  /

Type altered. SQL> SQL> create or replace type body Address_Type

 2  as
 3      member function toString return varchar2
 4      is
 5      begin
 6          if ( street_addr2 is not NULL )
 7          then
 8              return street_addr1 || " " ||
 9                     street_addr2 || " " ||
10                     city || ", " || state || " " || zip_code;
11          else
12              return street_addr1 || " " ||
13                     city || ", " || state || " " || zip_code;
14          end if;
15      end;
16
17      map member function mapping_function return varchar2
18      is
19      begin
20          return to_char( nvl(zip_code,0), "fm00000" ) ||
21                 lpad( nvl(city," "), 30 ) ||
22                 lpad( nvl(street_addr1," "), 25 ) ||
23                 lpad( nvl(street_addr2," "), 25 );
24      end;
25  end;
26  /

Type body created. SQL> create table people

 2  ( name           varchar2(10),
 3    home_address   address_type,
 4    work_address   address_type
 5  )
 6  /

SQL> create or replace type Address_Array_Type as varray(50) of Address_Type

 2  /

SQL> SQL> alter table people add previous_addresses Address_Array_Type

 2  /</source>
   
  

Alter type to add member function

   <source lang="sql">

SQL> SQL> create or replace type Address_Type

 2  as object
 3  (  street_addr1   varchar2(25),
 4     street_addr2   varchar2(25),
 5     city           varchar2(30),
 6     state          varchar2(2),
 7     zip_code       number
 8  )
 9  /

SQL> SQL> alter type Address_Type

 2  REPLACE
 3  as object
 4  (  street_addr1   varchar2(25),
 5     street_addr2   varchar2(25),
 6     city           varchar2(30),
 7     state          varchar2(2),
 8     zip_code       number,
 9     member function toString return varchar2,
10     map member function mapping_function return varchar2
11  )
12  /

Type altered. SQL> SQL> create or replace type body Address_Type

 2  as
 3      member function toString return varchar2
 4      is
 5      begin
 6          if ( street_addr2 is not NULL )
 7          then
 8              return street_addr1 || " " ||
 9                     street_addr2 || " " ||
10                     city || ", " || state || " " || zip_code;
11          else
12              return street_addr1 || " " ||
13                     city || ", " || state || " " || zip_code;
14          end if;
15      end;
16
17      map member function mapping_function return varchar2
18      is
19      begin
20          return to_char( nvl(zip_code,0), "fm00000" ) ||
21                 lpad( nvl(city," "), 30 ) ||
22                 lpad( nvl(street_addr1," "), 25 ) ||
23                 lpad( nvl(street_addr2," "), 25 );
24      end;
25  end;
26  /

Type body created. SQL> create table people

 2  ( name           varchar2(10),
 3    home_address   address_type,
 4    work_address   address_type
 5  )
 6  /

SQL> create or replace type Address_Array_Type as varray(50) of Address_Type

 2  /

SQL> alter table people add previous_addresses Address_Array_Type

 2  /

SQL> SQL> select object_type, object_name, decode(status,"INVALID","*","") status, tablespace_name

 2  from user_objects a, user_segments b
 3  where a.object_name = b.segment_name (+)
 4  and   rownum < 50
 5  order by object_type, object_name
 6  /

OBJECT_TYPE


OBJECT_NAME


S TABLESPACE_NAME - ------------------------------ FUNCTION CHITIME

FUNCTION IS_OVERDUE

FUNCTION RECEIVE_MESSAGE

FUNCTION RULERSTR

FUNCTION TABCOUNT

FUNCTION TO_MMSDDSYYYY_OR_NULL drop table people; OBJECT_TYPE


OBJECT_NAME


S TABLESPACE_NAME - ------------------------------ FUNCTION TO_NUMBER_OR_NULL

FUNCTION TRACEIT

FUNCTION VALUE_IN

PACKAGE CLASSPACKAGE

PACKAGE DATES

PACKAGE GENDER_TS OBJECT_TYPE


OBJECT_NAME


S TABLESPACE_NAME - ------------------------------

PACKAGE PARAMETERS

PACKAGE SCOPES

PACKAGE WORKER_TS

PACKAGE WORKER_TYPE_TS

PACKAGE WORKPLACE_TYPE_TS

PACKAGE BODY OBJECT_TYPE


OBJECT_NAME


S TABLESPACE_NAME - ------------------------------ CLASSPACKAGE

PACKAGE BODY DATES

PACKAGE BODY GENDER_TS

PACKAGE BODY PARAMETERS

PACKAGE BODY SCOPES

PACKAGE BODY WORKER_TS

OBJECT_TYPE


OBJECT_NAME


S TABLESPACE_NAME - ------------------------------ PACKAGE BODY WORKER_TYPE_TS

PACKAGE BODY WORKPLACE_TYPE_TS

PROCEDURE BULK_TEST

PROCEDURE CLOSECUR

PROCEDURE CREINDX

PROCEDURE DELETE_CUST

OBJECT_TYPE


OBJECT_NAME


S TABLESPACE_NAME - ------------------------------ PROCEDURE DROPIT

PROCEDURE DROP_IF_EXISTS

PROCEDURE FCREATE

PROCEDURE FLUSH

PROCEDURE GET_LINE

PROCEDURE P1 OBJECT_TYPE


OBJECT_NAME


S TABLESPACE_NAME - ------------------------------

PROCEDURE PRINTLN

PROCEDURE PUT_BOOLEAN

PROCEDURE P_RUN_INSERT

PROCEDURE ROW_AT_A_TIME_TEST

PROCEDURE RUNDDL

PROCEDURE OBJECT_TYPE


OBJECT_NAME


S TABLESPACE_NAME - ------------------------------ SAVESTRINGTOFILE

PROCEDURE SEND_MESSAGE

PROCEDURE WAIT

TABLE HAZARD_LEVEL_T

 SYSTEM

TYPE DEBUG_O

TYPE BODY DEBUG_O

OBJECT_TYPE


OBJECT_NAME


S TABLESPACE_NAME - ------------------------------ VIEW DEPT20_V

VIEW SALES_BY_ATLAS_V

VIEW SALES_PER_PERSON_V

  • </source>