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.
--------------------------------------------------------------------------------
