Data Types

Data types in DBMS – All the languages in computer world support datatypes. Datatypes defines the domain of the columns in the table or variables in the code. They define whether the column or variable contains numbers, alphabets, boolean values etc. This is very much important because, it controls the misuse of column – if anyone is trying to insert alphabets into numeric column, it does not allow. If the datatypes of column is not defined, we can store any data in any column, hence making the jumble. Any table needs to be well defined and well structured. Datatypes is to help to create a clean database.

There are six types of inbuilt data types

1. Scalar data types : – Used to store scalar values like numbers, decimals etc.
2. Composite data types : – It is combination of other datatypes usually scalar datatypes.
3. Reference data types : – Used to store the information about another datatype. It can be referred as pointers in C.
4. LOB data types : – Used to store large objects like images, videos etc
5. Unknown Column types : – when column types are not known, this datatype is used.
6. User Define data types : – these datatypes are defined by the developer while coding using the above base datatypes. They define new datatypes to ease their coding. This is can be considered as composite datatype too.

Scalar data types

These datatypes are used to store the numeric column values or numeric variables. Suppose we have a column AGE in a table. This column can have only numbers in it. We cannot insert any date or alphabets into it. Hence we declare this column as NUMBER. This restricts the user while they try to insert any other values other than number. This is one of the mechanisms to maintain the domain constraint of a column.

There are four types of Scalar datatypes – Character, Numeric, Date/Time and Boolean.

Character Data type: –This type of datatype is used to store alpha-numeric values like alphabets as well as numbers and special characters. There are different subtypes in this character datatype depending on the storage space and length of the data to be stored.

While creating a table:

CREATE TABLE STUDENT (STD_NAME CHAR (15), ADDRESS VARCHAR2 (50));

In PL/SQL:

DECLARE
		v_name VARCHAR2 (30);
BEGIN ….

Numeric Datatypes: – Stores different types of numeric data – numbers, decimals etc. Depending on the type we have different sub types of numeric datatypes.

While creating a table:

CREATE TABLE STUDENT (
STD_ID NUMBER (8), ADDRESS VARCHAR2 (50));

In PL/SQL:

DECLARE
		v_total PLS_INTEGER;
BEGIN …

Date / Time Datatypes: – These datatypes are used to store date and timestamps in the columns and variables.

Boolean Datatypes: – It is used to store boolean values – TRUE or FALSE. It can also store NULL and is considered as unsigned boolean variable. We cannot compare boolean columns of two tables.

SELECT * FROM EMP
 WHERE IS_EMPLOYED = (SELECT IS_EMPLOYEED 
				FROM EMP WHERE EMP_NAME = ‘John’); -- this query is not valid

Composite data types or User Define data types

Depending upon the need of the program, the developer combines one or more datatype variables into form a one variable. This type of variables will have multiple same or different base datatypes defined in it. These types of variables are used while coding.  This type of datatypes is known as composite or user defined datatypes.

Record is one of the composite datatypes. It can have any number of datatypes within it. One can imagine this as an array with different types of data or as a table itself. Most of the time scalar datatypes are used to create records.

In order to declare a variable as a composite datatype, we have to define the composite datatype according our necessity. Then that datatype can be assigned to other variables to have such records. First, let us see the general syntax for it followed by an example.

TYPE record_type_name IS RECORD 
(Column1 DATATYPE, Column2 DATATYPE… ColumnN DATATYPE);

TYPE rc_emp IS RECORD 
(emp_id NUMBER, emp_name VARCHAR2 (15), date_of_birth DATE); -- An employee record rc_emp with datatypes number, varchar2 and Date is created.

Here rc_emp is the name of the composite datatype.  Above is the syntax for creating the composite datatype. Once it is created, it can be assigned to variable in two methods.

• vr_emp_record rc_emp; — Declares a variable ‘vr_emp_record’ with three columns of different datatypes. This variable now can hold an array or table of data. Each column inside this variable can be referred as vr_emp_record.emp_id, vr_emp_record.emp_name and vr_emp_record.date_of_birth.
• Another method is to create individual variables for each column in the record type.

rc_emp_id vr_emp_record.emp_id%TYPE;
rc_emp_name vr_emp_record.emp_name%TYPE;
rc_date_of_birth vr_emp_record. date_of_birth %TYPE; -- vr_emp_record is a record type

This method of declaration is useful while we have to assign the datatypes of existing table columns. In such case no need to create record types, we can directly use tables to assign the datatypes like below. This will help the developer to automatically assign the datatype of the table columns, and he need not check the datatype of each columns. Also, if there are any changes to datatype or length of the columns, then it will be automatically reflected in the code. No need to change the code in such cases.

rc_emp_id EMPLOYEE.emp_id%TYPE;
rc_emp_name EMPLOYEE.emp_name%TYPE;
rc_date_of_birth EMPLOYEE. date_of_birth %TYPE; -- Where EMPLOYEE is a table.

Let us consider an example to understand it better.

DECLARE
      TYPE rc_emp IS RECORD 
	(emp_id NUMBER, 
	emp_name VARCHAR2 (15), 
	date_of_birth DATE); -- Declaring a record type with user defined columns
	
	vr_emp_record rc_emp; -- Declaring a variable of datatype rc_emp
BEGIN
	DBMS_OUTPUT.PUT_LINE (‘EMPLOYEE RECORD’);
	DBMS_OUTPUT.PUT_LINE (‘--------------------------‘);
	-- Accessing the columns of datatype rc_emp
	DBMS_OUTPUT.PUT_LINE (‘Employee ID:’ || vr_emp_record.emp_id);
	DBMS_OUTPUT.PUT_LINE (‘Employee NAME:’ || vr_emp_record.emp_name);
	DBMS_OUTPUT.PUT_LINE (‘Employee Date Of Birth:’ || vr_emp_record.date_of_birth);
END;

DECLARE
	rc_emp EMPLOYEE%ROWTYPE; -- Declaring a table record type 
	rv_emp_name EMPLOYEE.emp_name%TYPE; --declaring a variable with table column type
BEGIN
	DBMS_OUTPUT.PUT_LINE (‘EMPLOYEE RECORD’);
	DBMS_OUTPUT.PUT_LINE (‘--------------------------‘);
	-- Accessing the columns of datatype rc_emp
	DBMS_OUTPUT.PUT_LINE (‘Employee ID:’ || rc_emp.emp_id); 
	DBMS_OUTPUT.PUT_LINE (‘Employee NAME:’ || rc_emp.emp_name);
	DBMS_OUTPUT.PUT_LINE (‘Employee Date Of Birth:’ || rc_emp.date_of_birth);
END;

Here both the block of codes is same and gives the same output (imagine EMPLOYEE table has only three columns as we defined first). The first block of code uses user defined column datatypes in the record variable whereas second block of code uses table and its column to define the variable datatypes.

This is how we create a datatype. But it will not have any values of the columns of the table. We have to explicitly assign the values to them. In above blocks of code, we have not assigned any values and it will display nothing. That example was just to demonstrate how to declare and access record variables. We can assign and retrieve values of a record type as follows:

DECLARE
	rc_emp EMPLOYEE%ROWTYPE; -- Declaring a table record type 
	rv_emp_name EMPLOYEE.emp_name%TYPE; --declaring a variable with table column type
BEGIN
	rc_emp.emp_id: = 100; -- Assigning the values
	rc_emp.emp_name: = ‘John’;

	DBMS_OUTPUT.PUT_LINE (‘EMPLOYEE RECORD’);
	DBMS_OUTPUT.PUT_LINE (‘--------------------------‘);
	-- Accessing the columns of datatype rc_emp
	DBMS_OUTPUT.PUT_LINE (‘Employee ID:’ || rc_emp.emp_id);
	DBMS_OUTPUT.PUT_LINE (‘Employee NAME:’ || rc_emp.emp_name);
END;

Similar to Records, we have one more composite datatype called collections. It is similar to arrays and it will have same datatypes. There are three types of collections.

Associative Array or Index by Table: – These are two dimensional array with (key, value) pair. Key is a position identifier of each value. The key can be integer or character/string here. We can have any number of (key, value) pair in it. There is no limit for the number of data in this array, hence unbounded. This is the most frequently used type of collections.

TYPE index_table IS TABLE OF value_datatype [NOT NULL] INDEX BY key_datatype;
v_ index_table index_table;

Above syntax creates a collection with name index_table with values of value_datatype and key with key_datatype. Once the collection is created, collection variable v_index_table is created.

DECLARE
TYPE salary_col IS TABLE OF NUMBER INDEX BY VARCHAR2 (20);
   v_salary_list salary_col;

v_name VARCHAR2 (25);
BEGIN
   -- Assigning the data to tables
   salary_list (‘Sophia’):= 45000;
   salary_list (‘John’):= 75000;
v_name:= salary_list. FIRST; -- Retrieving the first data
DBMS_OUTPUT.PUT_LINE (‘Salary of ‘|| v_name || ‘is ‘|| salary_list (v_name)); --it will display output as ‘Salary of Sophia is 45000. It will not display John’s detail. In order to get his details, we have to reassign v_name:= salary_list. Next and then display
END;

Nested Tables: – These are like one dimensional array, but we can have any number of records in it. The size of this type increases dynamically as and when we get records. Records are added one after the other in a sequence, but when we delete any record from it, the space for deleted record is not deleted. Hence this type of tables will be dense while inserting the data, and gradually becomes sparse. Nested tables can be used as a column in the table as well as can be used in PL/SQL code.

Syntax for this is as below:

TYPE nt_name IS TABLE OF record_datatype [NOT NULL];
table_name nt_name;

where record_datatype can be an any DBMS datatypes or it can be any column type of a table.

E.g.; TYPEnt_salary IS TABLE OF NUMBER;

OR    TYPE nt_salary IS TABLE OF EMPLOYEE.SALARY%TYPE;

Below program shows how to declare, create and access nested tables.

DECLARE
TYPE nt_salary IS TABLE OF NUMBER;
TYPE nt_empname IS TABLE OF VARCHAR2 (20);

   nt_salary_list nt_salary;
 nt_emp_list nt_empname;
BEGIN
   -- Assigning the data to nested tables
 nt_emp_list: = nt_empname (‘Sophia’, ‘James’, ‘Bryan’);
nt_salary_list: = nt_salary (20000, 40000, 60000);

-- Retrieving the records of nested table
FOR i IN 1... nt_emp_list.count LOOP -- nt_emp_list.countwill give total number of records in the nested table
      DBMS_OUTPUT.PUT_LINE (‘Salary For ‘||nt_emp_list (i) || ‘: ‘||nt_salary_list (i)); 
END LOOP;
END;

The Output would be:

Salary for Sophia: 20000

Salary for James: 40000

Salary for Bryan: 60000

Variable array (varray): – This is also similar to nested array and can be considered as one dimensional array, provided it has fixed number of records. Unlike nested tables, we have to declare the number of records in this array in advance and can be used in the program. In addition, we cannot delete the individual records of the array. Because of these features, developers are less likely to use this array. They prefer associative array or nested tables in their programs.

Syntax for Varray is:

TYPE varray_name IS VARRAY (index) OF DATATYPE;

E.g.: – TYPE va_salary IS VARRAY (4) OF NUMBER;

va_salary_list va_salary;

Below program shows how to declare, create and access varray.

DECLARE
		TYPE va_salary IS VARRAY (4) OF NUMBER;
		TYPE va_empname IS VARRAY (4) OF VARCHAR2 (20);
		
   va_salary_list va_salary;
 va_emp_list va_empname;
BEGIN
   -- Assigning the data to varray
 va_emp_list: = va_empname (‘Sophia’, ‘James’, ‘Bryan’);
va_salary_list: = va_salary (20000, 40000, 60000);

-- Retrieving the records of varray
FOR i IN 1... va_emp_list.count LOOP -- va_emp_list.countwill give total number of records in the varray
      DBMS_OUTPUT.PUT_LINE (‘Salary For ‘||va_emp_list (i) || ‘: ‘||va_salary_list (i)); 
END LOOP;
END;

The Output would be:

Salary for Sophia: 20000

Salary for James: 40000

Salary for Bryan: 60000

Comparison of three types of collections is given below.

Reference data types

This datatype refers to the existing data in the program. It acts like pointers to the variable. One of the examples of reference datatype is refcursor. They are the cursor variables used to reference and access the static cursor. We can pass this variable to procedures / functions and get the values from function as a refcursor. In short it acts a variable, but reference to the query defined at the runtime.

We can see the difference between cursor and refcursor in below program. We will learn more about it in Advanced SQL article.

DECLARE
           TYPE rc_cursor is ref cursor; 
	CURSOR c_course IS
		SELECT * FROM COURSE;
           l_cursor rc_cursor;
         n_ID NUMBER;
BEGIN
	IF n_ID = 10 THEN
	-- Dynamically opens the cursor for student ids less than 10
		Open l_cursor FOR ‘SELECT * FROM STUDENT WHERE STD_ID<= 10’;
	ELSE	
	-- Dynamically opens the cursor for student ids greater than 10
		OPEN l_cursor FOR ‘SELECT * FROM STUDENT WHERE STD_ID > 10’;
	 END IF;
	-- Opens static cursor c_course
	 OPEN c_course;
END;

LOB data types

These types of datatypes are used to store very large amount of data in a column / variable. It can store file like music, graphics etc. We have following types of LOB datatypes:

Unknown Column types

We have seen this in the user defined datatypes to declare the variables. This is called unknown columns because this type of datatypes is not base datatypes and is defined by the user or tables. The developer does not predict the datatype of it just by seeing name or declaration. He has to see the definition of record datatype to understand it.

Variables

Variables are used to store the data temporarily in a program. They act as an intermediary place holder to hold the results while manipulating the data in the program. Each variable will have datatype depending on the kind of data that we are going to store. Datatypes will be same as column datatypes that we saw above.

Syntax for declaring a variable is

Variable_name DATATYPE;

We can even assign default value to the variable. When a variable is declared as NOT NULL, default value has to be assigned.

Variable_name DATATYPE: = value; 
Variable_name DATATYPE NOT NULL: = value; 

n_age NUMBER NOT NULL: = 18;
v_name VARCHAR2 (25);

In a program, we can either directly assign value to a variable or we can use it in a query to get the value of columns into it.

Scope of variables

In a PL/SQL program, we can have multiple blocks i.e.; it has multiple BEGIN – END blocks one inside another.  When variables are declared in these blocks, they act with respect to their blocks.

A variable declared inside a block is accessible to that block alone. Such variables are called local variables. They cannot be accessed by any outer blocks. When we have inner blocks, then we can access the variables declared by its outer blocks. Such variables are called global variables.

DECLARE
	n_age NUMBER; -- Global Variable
BEGIN
	SELECT AGE INTO n_age FROM PERSON WHERE SSN = 323234;
	DECLARE
	v_name VARCHAR2 (30); -- Local Variable, accessible only inside this block
	BEGIN
		SELECT PERS_NAME 
			INTO v_name 
		    FROM PERSON WHERE SSN = 323234;
		DBMS_OUTPUT.PUT_LINE (‘Name of the Person is ‘|| v_name);
	END;
DBMS_OUTPUT.PUT_LINE (‘Age of the Person is ‘|| v_name);
END;

Constants

As the name suggests, they are constant in the program. They are variables with literal value and their remains unchanged throughout the program. These are used to reduce the use of actual values again and again in the program. Declaring a constant and assigning a value once at the beginning, and then using constant throughout the program, makes program clean and understandable. Also, if there is any change to the constant value, we have to change only at one place rather than changing all the code till the end.

Syntax for constant is:

constant_name CONSTANT DATATYPE: = value; -- value has to be assigned without fail, since it is a constant
	pi CONSTANT NUMBER (2, 2): = 3.14;
	incr_percentage CONSTANT NUMBER: = 10;
	declared_holiday CONSTANT VARCHAR2 (7): = ‘SUNDAY’;

In a program these constants are used as any regular variable. Only difference from variable is that we cannot change the value of the constant.

Summary

Reference