Wednesday, July 14, 2010

Step By Step Example For Object Type In Oracle

Step By Step Example For Object Type In Oracle

Create Object Type

-----------------------------------------------------------------------------------
SQL> CREATE TYPE ObjectPersonType AS OBJECT (
  2    ID       NUMBER,
  3    FNAME    VARCHAR2(20),
  4    LNAME    VARCHAR2(25),
  5    PHONE    VARCHAR2(20),
  6    MAP MEMBER FUNCTION get_idno RETURN NUMBER,
  7    MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY ObjectPersonType ));
  8  /

Type created.
-----------------------------------------------------------------------------------

here,  object variables are,
ID       NUMBER,
FNAME    VARCHAR2(20),
LNAME    VARCHAR2(25),
PHONE    VARCHAR2(20)

and
Object Memeber Functions,Procedures are
MAP MEMBER FUNCTION get_idno RETURN NUMBER
-- Return Self ID Number,
MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY ObjectPersonType )
-- In & Out Is Own Type Parameter

-----------------------------------------------------------------------------------

Create/ Replace Type Body

SQL> CREATE OR REPLACE TYPE BODY ObjectPersonType AS
  2    MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  3    BEGIN
  4      RETURN ID;
  5    END;
  6    MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY ObjectPersonType ) IS
  7            BEGIN
  8      -- use the PUT_LINE procedure of the DBMS_OUTPUT package to display details
  9              DBMS_OUTPUT.PUT_LINE(TO_CHAR(ID) || ' ' || FNAME || ' ' || LNAME);
  10      DBMS_OUTPUT.PUT_LINE(PHONE);
 11    END;
 12  END;
 13/

Type body created.
-----------------------------------------------------------------------------------

Create New Table Using Object Type (ObjectPersonType)

Description:
Table Name Is PERSONLIST,
Fields are, RECORDDATE as Date & DETAILS As ObjectPersonType(Using Defined Object Type)

CREATE TABLE PERSONLIST (
  RECORDDATE   DATE,
  DETAILS              ObjectPersonType);

Table created.
-----------------------------------------------------------------------------------

Insert Values For PERSONLIST

INSERT INTO PERSONLIST VALUES (
                SYSDATE,  ObjectPersonType (25, 'Raaj', 'Malik','9876700001'));

1 row created.

INSERT INTO PERSONLIST VALUES (
                SYSDATE,  ObjectPersonType (21, 'Boss', 'Sivaji','9876700002'));

1 row created.
-----------------------------------------------------------------------------------

Select Statements For Object Type


SQL> SELECT * FROM PERSONLIST;

RECORDDAT
---------
DETAILS(ID, FNAME, LNAME, PHONE)
--------------------------------------------------------------------------------
13-JUL-10
OBJECTPERSONTYPE(25, 'Raaj', 'Malik', '9876700001')

13-JUL-10
OBJECTPERSONTYPE(21, 'Boss', 'Sivaji', '9876700002')


SQL> SELECT DETAILS FROM PERSONLIST;

DETAILS(ID, FNAME, LNAME, PHONE)
--------------------------------------------------------------------------------
OBJECTPERSONTYPE(25, 'Raaj', 'Malik', '9876700001')
OBJECTPERSONTYPE(21, 'Boss', 'Sivaji', '9876700002')


SQL> SELECT DETAILS FROM PERSONLIST WHERE RECORDDATE=( SELECT MAX(RECORDDATE) FROM PERSONLIST);

DETAILS(ID, FNAME, LNAME, PHONE)
--------------------------------------------------------------------------------
OBJECTPERSONTYPE(21, 'Boss', 'Sivaji', '9876700002')

SQL> SELECT c.DETAILS.get_idno() FROM PERSONLIST c;

C.DETAILS.GET_IDNO()
--------------------
                  25
                  21

SQL> SELECT Obj.DETAILS.FNAME FROM PERSONLIST Obj;

DETAILS.FNAME
--------------------
Raaj
Boss


SQL> SELECT DETAILS FROM PERSONLIST Obj WHERE Obj.DETAILS.FNAME='Raaj';

DETAILS(ID, FNAME, LNAME, PHONE)
--------------------------------------------------------------------------------
OBJECTPERSONTYPE(25, 'Raaj', 'Malik', '9876700001')


SQL> DECLARE
  Obj OBJECTPERSONTYPE;
BEGIN
  SELECT DETAILS INTO Obj FROM PERSONLIST WHERE RECORDDATE=( SELECT MAX(RECORDDATE) FROM PERSONLIST);
  Obj.display_details();
END;
/
21 Boss Sivaji
9876700002

PL/SQL procedure successfully completed.
--------------------------------------------------------------------------------




Post a Comment