PL/SQL and Object-Oriented Programming

A little known fact about PL/SQL is that Oracle has been introducing ever-increasing support for Object-Oriented Programming (OOP) ever since version 8. I have some examples below that will help illustrate some of the OOP concepts. (Note: The following examples are run using Oracle 10g, you'll have to try for yourself to see if they work with a different version.)

To get started, lets review some terminology and I'm going to assume you are familiar with Java or C++. Most terms are the same, for instance in Java class methods are still methods in PL/SQL. However, class variables or fields are called attributes in pl/sql. Enough with the talking, time for code.

1. CREATE OR REPLACE TYPE user_object AS OBJECT  
2. (  
3. id number,  
4. first_name varchar2(250),  -- attribute  
5. last_name varchar2(250),  
6. email_address varchar2(1000),  
7. -- constructor specification  
8. CONSTRUCTOR FUNCTION user_object  
9. (  
10. id number,  
11. first_name varchar2,  
12. last_name varchar2,  
13. email_address varchar2)  
14. RETURN SELF AS RESULT,  
15. -- member procedure  
16. MEMBER PROCEDURE set_first_name  
17. (first_name varchar2),  
18. -- member function  
19. MEMBER FUNCTION get_first_name  
20. RETURN varchar2)  
21. INSTANTIABLE NOT FINAL;  
22.   
23.   
24. CREATE OR REPLACE TYPE BODY user_object AS  
25. CONSTRUCTOR FUNCTION user_object  
26. (  
27. id number,  
28. first_name varchar2,  
29. last_name varchar2,  
30. email_address varchar2)  
31. RETURN SELF AS RESULT IS  
32. BEGIN  
33.   self.id := id;  
34.   self.first_name := first_name;  
35.   self.last_name := last_name;  
36.   self.email_address := email_address;  
37.   RETURN;  
38. END user_object;  
39.   
40. MEMBER PROCEDURE set_first_name (first_name varchar2)  
41. is  
42. BEGIN  
43.   self.first_name := first_name;  
44. END set_first_name;  
45.   
46. MEMBER FUNCTION get_first_name   
47. RETURN varchar2  
48. IS  
49. BEGIN  
50.   RETURN self.first_name;  
51. END get_first_name;  
52.   
53. END;  

Okay, so what are we looking at here? We're looking at an OOP representation of a data structure. Thank you, Captain Obvious. The top statement is the object specification, notice I've specified 4 attributes, a constructor and two member methods. The constructor takes 4 parameters, one for each attribute. The member methods "set" and "get" the first name attribute.

So your next question, wow, this is great and all, but how can this be useful to me. The answer is simple, lets say I have a function that I need to return several pieces of information. You can then declare your function to return an object type.

This is a huge subject, however if there are some specifics that you are wondering about, just leave a comment and I'll try to answer them.