Table of contents
1.
Introduction
2.
Manipulating Objects
2.1.
1. Accessing Object Attributes With Dot Notation
2.2.
2. Calling Object Constructors and Methods
2.3.
3. Updating and Deleting Objects
2.4.
4. Inheritance in Object Type
3.
FAQs
4.
Key Takeaways
Last Updated: Mar 27, 2024

PL/SQL Object Oriented

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

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

FAQs

  1. What is object type in PL SQL?
    In Oracle, PL/SQL allows you to define an object type, which aids in creating object-oriented databases. We can make composite types using an object type. Objects enable us to create real-world objects with a defined data structure and ways of operating them.
  2. What is the syntax to create an object in PL/SQL?
    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>,

);

/

3. How can we access Object Attributes?
    The instance name and the access operator (.) can access the object's attributes and methods.

Key Takeaways

We can declare an object type in PL/SQL, which helps you create object-oriented databases. 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. We learned how to create an object in PL/SQL and have learned various ways to manipulate the objects in this blog.

Also Read - TCL Commands In SQL

Also, try Coding Ninjas Studio to practice programming problems for your complete interview preparation. Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Live masterclass