Version 8i introduced simple objects in Oracle and so made the Oracle database a true object-oriented environment. This article presents short scripts that demonstrate how to build, use and maintain Oracle simple objects.
Phase 1: The object type creation
Every object you create starts with an Oracle type creation, like this:
Create Or Replace Type Occupation as Object ( start_date date, end_date date, descr varchar2(30), avg_sal number(7,0), Member function job_duration return Number, Member function inc_sal return number ); /
Phase 2: Object type body for methods implementation
Use this only where the function exists in the object's type definition.
Create Or Replace Type Body occupation as member function job_duration return number is begin return (self.end_date - self.start_date) ; end; member function inc_sal return number is f number; begin select Decode (Lower(tri(self.descr)), 'analyst',.05, 'clerk',.03,.1) into f from DUAL; return (self.avg_sal * ( 1 + f)); end; end ; /
Phase 3: Usage in tables
Use the object type simply as a "column" in the table's structure.
Create Table Jobs (JobId number(2), current_occupation occupation );/
Phase 4: Use in PL/SQL
Here is how to insert into a table containing objects:
declare i number; begin i := 0; for ce in (select Job, Min(HireDate) as sDate, Max(HireDate) as fDate, avg(sal) as avgsal from emp group By Job ) loop i := i + 1; Insert into Jobs Values (i, occupation (ce.sDate, ce.fDate, ce.Job, ce.avgSal) ); end loop; commit; end; /
Phase 5: Use in SQL
This shows the usage in a simple SQL select statement. Note that a table alias is necessary; it will not work otherwise.
column sDate format a10 column fDate format a10 column duration format a10 column Incsal format a8 column Job format a12 column avgsal format a8 select j.Current_Occupation.Start_date as sDate, j.Current_Occupation.end_date as fDate, to_char(j.Current_Occupation.Job_duration()) as duration, to_char(j.Current_Occupation.Inc_Sal()) as IncSal, j.Current_Occupation.descr as Job, to_char(j.Current_Occupation.avg_sal) as avgSal from Jobs j; /
About the author
Eli Leiba works at the Israel Electric Company as a senior application DBA in Oracle and Microsoft SQL Server systems. He has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in computer science and has 11 years of experience working in the database field. Additionally, Mr. Leiba teaches SQL Server DBA and development courses at the Microsoft CTEC and serves as a senior database consultant for several Israeli start-up companies.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.