Difference Between Data types, Object types, and Cursor in PL SQL

Let’s understand what object type, data type & cursor are.

In PL/SQL, a data type refers to the type of data that a variable or constant can store, such as VARCHAR2, NUMBER, DATE, etc. On the other hand, object types allow you to create your structured types using the OBJECT keyword and the cursor is a mechanism for fetching and processing query results.

Part 1: Data Type

A data type specifies the type of data that a variable can store. PL/SQL supports various data types, including basic types like VARCHAR2, NUMBER, DATE, TIMESTAMP, etc. Each column in a table must be declared with a specific data type.

Here are some data type examples:

Let’s create one table using data types.

CREATE TABLE PERSON_DETAILS (

PER_ID NUMBER,

PER_FIRSTNAME VARCHAR2(250 BYTE),

PER_LASTNAME VARCHAR2(250 BYTE),

DESIGNATION VARCHAR2(250 BYTE),

DOJ                    DATE

);

The table gets created after executing the create table query. Shown below.

Part 2: Understanding Object Types with example

An object type is a user-defined composite data type that encapsulates a data structure and the functions and procedures needed to manipulate the data. Object types must be created through SQL and stored in an Oracle database, where they can be shared by many programs. We can define an object type using the “CREATE TYPE” statement.

Syntax of Object type:

CREATE OR REPLACE TYPE <object_type_name> AS OBJECT

(

<attribute_1><datatype>,

<attribute_2><datatype>,….

);

Let’s create one example:

Step 1: Here we create one object type called “ADDRESS_TYPE” and execute the query.

This can be used as a built-in/scalar data type. So, we will create one table and on that, we will use “ADDRESS_TYPE” as the object type.

Step 2: Create one table called “EMP_DETAILS”.

On the above query, we created a column called “LOCATION” where we used the user-defined data type i.e. “ADDRESS_TYPE”.

Step 3:  When we try to insert a record into the table and the table has a column that has a user-defined data type then we need to refer to the data type name to insert records to that table, as shown below.

Step 4: If we want to check the inserted data on the table then need to refer to a table alias. Column name & field name.

Part 3: What is Cursor?

Cursor is used to process multiple records in the PL/SQL program. It is a temporary memory area where Oracle executes SQL statements. Oracle associates every SELECT statement with a cursor. There are two types of cursors: implicit and explicit. Implicit cursors are automatically created by Oracle for SQL statements, while explicit cursors are defined by the programmer using the ‘DECLARE’, ‘OPEN’, ‘FETCH’ and ‘CLOSE’ statements.

Syntax for Cursor:

DECLARE

Variable_name table_name.column_name%type;

Cursor cursor_name is select query;

Begin

Open cursor_name;

Fetch cursor_name into variable_name;

——process the records;

Close cursore_name;

End;

Example: Execute this query and it will provide all the names that are present in the table.

Recommended for you: A Definitive Guide to Agent Installation and MYSQL Connection