ADT

From Oracle FAQ
Jump to: navigation, search

ADT (Abstract DataType) is a user defined data type (also referred to as UDT's).

Examples[edit]

To define a new datatype to store a person's address:

CREATE OR REPLACE TYPE persons_address AS OBJECT (
  streetNumber NUMBER,
  streetName   VARCHAR2(30),
  citySuburb   VARCHAR2(30),
  state        VARCHAR2(4),
  postCode     NUMBER
);

Define a employee_type; define a "raise_sal" member function; and create a table based on our new type:

CREATE TYPE employee_t AS OBJECT (
       name    VARCHAR2(30),
       ssn     VARCHAR2(11),
       salary  NUMBER,
       MEMBER FUNCTION raise_sal RETURN NUMBER)
/

CREATE TYPE BODY employee_t AS
       MEMBER FUNCTION raise_sal RETURN NUMBER IS
       BEGIN
          RETURN salary * 1.1;
       END;
END;
/

-- Test the member function
SELECT employee_t('Frank', '12345', 1000).raise_sal() from dual;

-- Create table based on employee_t
CREATE TABLE emp2 OF employee_t;
INSERT INTO emp2 VALUES ( employee_t('Frank', '12345', 1000) ); 
SELECT x.raise_sal() FROM emp2 x;

Also see[edit]

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #