Introduction
PL/SQL allows you to define an object type, which aids in creating object-oriented databases. You can make composite types using an object type. Objects enable you to create real-world objects with a defined data structure and ways of operating them. Attributes and methods are features of objects. Attributes are object properties used to store an object's state, whereas methods describe the object's behavior.
Recommended topics, Coalesce in SQL and Tcl Commands in SQL
Manipulating Objects
1. Accessing Object Attributes With Dot Notation
An object type can represent any real-world entity. Object types include students, bank accounts, computer screens, rational numbers, and data structures like queues, stacks, and lists. Object types cannot currently be defined in a PL/SQL block, subprogram, or package. The SQL command CREATE TYPE can be used to define them interactively.
The Construct [OR REPLACE] TYPE command is used to create objects.
Syntax:
CREATE OR REPLACE TYPE<object_type_name> AS OBJECT ( <attribute_1><datatype>, …. <attribute_n><datatype>, ); / |
Example: Working With Object Types
CREATE TYPE student_data AS OBJECT ( full_name VARCHAR2(30), address VARCHAR2(20), gender CHAR(5), roll_number VARCHAR2(6) ); END; / |
When the given code is executed at the SQL prompt, the following is the result:
Type created. |
Defining an object type gives the thing a blueprint. It would be best to create instances of this object to use it. The instance name and the access operator (.) can access the object's attributes and methods, as shown below.
DECLARE amisha student_data; BEGIN amisha := student_data('Amisha', 'Ahmedabad', 'Female','100001'); dbms_output.put_line('Full Name: '|| amisha.full_name); dbms_output.put_line('Address: '||amisha.address); dbms_output.put_line('Gender: '||amisha.gender); dbms_output.put_line('Roll Number: '||amisha.roll_number); END; / |
When the given code is executed at the SQL prompt, the following is the result:
Full Name: Amisha Address: Ahmedabad Gender: Female Roll Number: 100001 PL/SQL procedure completed. |
2. Calling Object Constructors and Methods
Constructors are an object's implicit methods with the same name as the object type. The constructor will be called implicitly whenever the object is referenced for the first time.
Using these constructors, we can also initialize the objects. The constructor can be defined directly by declaring a member with the same name as the object type in the object type body.
Example: Inserting Rows in an Object Table
DECLARE stud student_data; BEGIN INSERT INTO student_data VALUES (student_data('Manish', 'Ahmedabad', 'Male','100002')); INSERT INTO student_data VALUES (student_data('Dinesh', 'Pune', 'Male','100003')); INSERT INTO student_data VALUES (student_data('Anushka', 'Delhi', 'Female','100004')); END; / |
You can also define user-defined constructors in the type body, just like any other method. The phrase CONSTRUCTOR FUNCTION is used to begin the declaration and definition, and the clause RETURN SELF AS RESULT is used to end it. A type's constructor must have the same name as the type. Refer to this link to learn more about user-defined constructors and methods.
3. Updating and Deleting Objects
You can alter and delete rows in an object table within a PL/SQL block.
Example: Deleting and Updating Rows in an Object Table
DECLARE stud student_data; BEGIN INSERT INTO student_data VALUES (student_data('Dun', 'Ahmedabad', 'Female','100005')); UPDATE student_data s SET s.address = 'Delhi' WHERE s.roll_number = 100005; DELETE FROM student_data s WHERE s.roll_number = 100005; END; / |
4. Inheritance in Object Type
The inheritance property allows the sub-object type to access all super object types' or parent object types' attributes and members.
Inherited object type is the sub-object type, and parent object type is the super object type. The syntax below demonstrated how to make a parent and inherited object type.
CREATE TYPE <object_type_name_parent> AS OBJECT ( <attribute_l><datatype>, . . )NOT FINAL; / |
The SUPER type is created using the syntax shown above.
CREATE TYPE<object_type_name_sub>UNDER<object_type_name_parent> ( <attribute_l><datatype>, . ); / |
The SUB type is created using the syntax shown above. It inherits all of the parent object type's members and attributes.
Example: In the below example, we will use the inheritance property to insert the record with teacher id as '102' for the following record ('Amisha', 'Ahmedabad' ,'Female', '100001').
Creating SUPER type or Parent type. The 'stud object' object type contains three members and four attributes. It contains a constructor with only three parameters. It is parent type because it is marked as 'NOT FINAL.'
CREATE TYPE stud_object AS OBJECT( stud_no NUMBER, stud_name VARCHAR2(50), class NUMBER, teacher NUMBER, CONSTRUCTOR FUNCTION stud_object(p_stud_no NUMBER,p_stud_name VARCHAR2(50), stud_class NUMBER)RETURN SELF AS RESULT), MEMBER PROCEDURE insert_records, MEMBER PROCEDURE display_records)NOT FINAL; / |
Creating SUB type under SUPER type. Here, the sub_emp_object is inherited with an attribute 'default_manager' and member procedure declaration.
CREATE OR REPLACE TYPE sub_stud_object UNDER stud_object (default_teacher NUMBER,MEMBER PROCEDURE insert_default_teacher); / CREATE OR REPLACE TYPE BODY sub_stud_object AS MEMBER PROCEDURE insert_default_teacher IS BEGIN INSERT INTO stud VALUES(stud_no,stud_name,stud_class,stud_teacher); END; / |
Creating an anonymous block to call the SUB type. Here, Declaring ‘student_teacher_details’ as ‘sub_stud_object’ type.
DECLARE student_teacher_details sub_stud_object; BEGIN student_teacher_details:= sub_stud_object('Amisha', 'Ahmedabad', 'Female','100001','102'); student_teacher_details.insert_default_teacher; COMMIT; END; / |
Also see, Four Pillars of OOPS