ORACLE’s User Defined Types


Oracle allows users can define their own types. By using type declaration,
users can create their own types similar to creating a table column type like VARHCHAR, NUMBERS …etc.

TYPEs are oracle objects and can store the data inside its structure
Here is the syntax for it;
–this is a book_type and I will use it inside tables’ columns
–it has  two properties one of them is isbn no and the other one is published year

CREATE OR REPLACE TYPE  book_type  AS OBJECT (
ISBN_no NUMBER(12),
published_year NUMBER(4)

) NOT FINAL; –I mean this type can be inherited by another type defined by oracle user

–default value is FINAL meaning that we can not inherit another type from this type
–inheritance allows us code reusing

–now I am creating a table for these books

CREATE TABLE shelves
(
 id NUMBER (8),
 book book_type –we are using our own data type
);

INSERT INTO shelves VALUES (1,book_type(111111,2008)); –we can insert with this syntax
SELECT * FROM shelves;

ID                     BOOK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
————-      ———————————
1                    HR.BOOK_TYPE(111111,2008)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      

1 rows selected

–here I can make update

UPDATE shelves
SET book = book_type(222222222,2009) –book type can be assigned to a book
WHERE id = 1;

SELECT * FROM shelves;

ID                 BOOK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
—–              —————
1                  HR.BOOK_TYPE(222222222,2009)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

1 rows selected

–another usage
UPDATE shelves
SET book = book_type(3333,2009)
WHERE book = book_type(222222222,2009);

SELECT * FROM shelves;
ID                     BOOK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
—–                 —————
1                      HR.BOOK_TYPE(3333,2009)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

1 rows selected

Here I can use SELECT operation with the help of dot access to elements which is similar to Java at that case or other OO programming languages
SELECT * FROM shelves s
WHERE s.book.ISBN_no = 3333;

ID                     BOOK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
—–                 —————
1                      HR.BOOK_TYPE(3333,2009)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

1 rows selected

–now I am creating a sub type by using book_type (super type)

CREATE OR REPLACE TYPE  science_book_type UNDER  book_type (
–under means sub type
description VARCHAR2(30),
MEMBER PROCEDURE do_someOperation(param1 IN NUMBER),
 –this procedure belongs to science_book_type
MEMBER FUNCTION get_todayDate RETURN DATE
–this function belongs to science_book_type
)NOT FINAL;

–HERE I am defining the body for the function and procedure
 
CREATE OR REPLACE TYPE BODY science_book_type AS
  MEMBER PROCEDURE  do_someOperation (param1 IN NUMBER) IS
  BEGIN
    dbms_output.put_line(‘input parameter is :’ || param1);
  END;
  MEMBER FUNCTION get_todayDate RETURN DATE IS
    dtmToday DATE;
  BEGIN
    SELECT SYSDATE
    INTO dtmtoday
    FROM dual;
    RETURN dtmToday;
  END;
END;

–I can create a test table for the new subtype like we have used before

CREATE TABLE science_shelves
(
  id NUMBER (8),
  sbook science_book_type –data type having one fuction and one procedure inside of its structure
);

INSERT INTO science_shelves
VALUES (1,science_book_type(1,1,’test for description’));

–here I am inserting a science book having one more column for the description
–the other properties coming from the book type
–here I can apply SELECT operations

SELECT s.sbook.description
FROM science_shelves s; — I must use table alias here otherwise errors would arise

SBOOK.DESCRIPTION           
——————————
test for description        

1 rows selected

— to call a MEMBER FUNCTION

SELECT s.sbook.get_todayDate() –>member function
FROM science_shelves s;

S.SBOOK.GET_TODAYDATE()  
————————-
28-JUN-09                

1 rows selected

–to call a MEMBER PROCEDURE

DECLARE
science_book science_shelves.sbook%TYPE;
BEGIN
SELECT s.sbook
INTO science_book
FROM science_shelves s
WHERE s.sbook.isbn_no= 1;
science_book.do_someOperation(123); –>member procedure
END;

input parameter is :123

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s