Table of Contents
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
- Scalar data types : – Used to store scalar values like numbers, decimals etc.
- Composite data types : – It is combination of other datatypes usually scalar datatypes.
- Reference data types : – Used to store the information about another datatype. It can be referred as pointers in C.
- LOB data types : – Used to store large objects like images, videos etc
- Unknown Column types : – when column types are not known, this datatype is used.
- 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));
DECLARE v_name VARCHAR2 (30); BEGIN ….
|Character Data Type||CHAR||Used to store fixed length of string or value.||32767 bytes|
|CHARACTER||This is same as CHAR, and both are used alternatively. CHAR is most commonly used.||32767 bytes|
|VARCHAR2||This is also similar to CHAR, where strings are stored. This datatype will define fixed length but the actual length of the string will be the length of the value i.e.; if VARCHAR2(10) is defined on column NAME and if NAME has one value ‘James’, then the length of that column value is 5, instead of 10. In the case of CHAR, it will be always 10; the space after the names will be filled with NULLS.||32767 bytes|
|VARCHAR2 Subtypes: Following sub type defines same length value.|
|Sub Data types||Description|
|STRING||they are similar to VARCHAR2, only the name difference|
|NCHAR||Stores National Character data (Unicode) within the specified length.||32767 bytes|
|NVARCHAR2||This is similar to VARCHAR2, but used to store unicode values.||32767 bytes|
|RAW||This datatype is used for such data like music, video, graphics etc where conversion of datatype between different systems is not required. They are representing in bits and bytes.||32767 bytes|
|LONG||Used to store variable length of strings with backward compatibility. We can store very large data in it. But it is usually recommended to use LOB when there is a need for LONG. Because LOB has less restriction on storage and conversions, and have more features added when there is a new release. LONG columns are preferred in SELCT or UPDATE statements than tables.||32760 bytes|
|LONG RAW||This is combination of LONG and RAW datatypes.||32760 bytes|
|ROWID||The ROWID data type represents the actual storage address of a row. And table index identities as a logical ROWID. This data type used to store backward compatibility.|
|UROWID[(size)]||The UROWID data type identifies as universal ROWID, same as ROWID data type. Use UROWID data type for developing newer applications.||4000 bytes|
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));
DECLARE v_total PLS_INTEGER; BEGIN …
|NUMERIC DATA Types||NUMBER (p, s)||NUMBER data type used to store numeric data. Storage Range : Precision range(p) : 1 to 38 and Scale range(s) : -84 to 127|
|NUMBER Subtypes: This sub type defines different types storage range.|
|Sub Data types||Description||Max Precision|
|INTEGER||This data types are used to store fixed decimal points. You can use based on your requirements.||38 digits|
|REAL||63 binary digits|
|DOUBLE PRECISION||126 binary digits|
|FLOAT||126 binary digits|
|BINARY_INTEGER||BINARY_INTEGER data type store positive and negative values. They require less storage space compare of NUMBER data type values. Storage Range: from -2147483647 to 2147483647.|
|Sub Data types||Description|
|NATURAL||Only Positive values are stored.|
|NATURALN||NULL values are not stored in this datatype. Only non-null positive values are allowed.|
|SIGNTYPE||SIGNTYPE allow only -1, 0, and 1 value.|
|PLS_INTEGER||PLS_INTEGER data type used to store signed integers data. They require less storage space compare of NUMBER data type value. Storage Range: from -2147483647 to 2147483647. PLS_INTEGER data type gives better performance on the data. PLS_INTEGER perform arithmetic operation faster than NUMBER / BINARY_INTEGER data type.|
Date / Time Datatypes: – These datatypes are used to store date and timestamps in the columns and variables.
Date / Time Datatype
|DATE||DATE data type stores valid date-time format with fixed length. Starting date from Jan 1, 4712 BC to Dec 31, 9999 AD.||Jan 1, 4712 BC to Dec 31, 9999 AD|
|TIMESTAMP||Stores valid date with year, month, day and time with hour, minute, second|
|Syntax: TIMESTAMP [(fractional_seconds_precision)]|
fractional_seconds_precision optionally specifies the number of digits in the fractional part of the second precision. Range from 0 to 9. The default is 6.
|Example: TIMESTAMP ‘2014-04-13 18:10:52.124’|
|Syntax: TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE|
|Example: TIMESTAMP ‘2014-04-13 18:10:52.124 +05:30’|
|WITH TIME ZONE specify the UTC time zone. Following two values represent the same instant in UTC.|
|TIMESTAMP ‘1999-04-15 8:00:00 -8:00’ (8.00 AM Pacific Standard Time) or|
|TIMESTAMP ‘1999-04-15 11:00:00 -5:00’ (11:00 AM Eastern Standard Time) both are same.|
|Syntax: TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE|
|Example: COL_NAME TIMESTAMP(3) WITH LOCAL TIME ZONE;|
|WITH LOCAL TIME ZONE specifies when you insert values into the database column, value is stored with the time zone of the database.|
|The time-zone displacement is not stored in the column. When you retrieve value from Oracle database, returns it according to your UTC local time zone.|
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.
Advantage of ROWTYPE
Disadvantage of ROWTYPE
|Need not explicitly define the column datatypes. They will be automatically retrieved from the tables.||When a record is created using ROWTYPE, whole of the columns are assigned to the record variable. Memory will be used to create the datatypes of all the columns. The developer might need only few of the columns to code, but other columns are also unnecessarily created in the record.|
|When table is altered for some column datatypes or column length, it will be automatically reflected in the code. No need to modify the code when there is table change.|
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:
|record_name.col_name := value;||To directly assign a value to a specific column of a record or to directly assign value to a specific column of record which is declared using %ROWTYPE.|
|SELECT col1, col2 INTO record_name.col_name1, record_name.col_name2 FROM table_name [WHERE clause];||To assign the values of each columns to the record columns|
|SELECT * INTO record_name FROM table_name [WHERE clause];||To assign the values of each columns or the whole table to a record|
|Variable_name := record_name.col_name;||To get a value from a record column and assigning it to a variable.|
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;
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.
|Collection Type||Number of Records||Key Type||Dense or Sparse||Where Created||Can Be Object Type Attribute|
|Associative array (or index-by table)||Unbounded||String or integer||Either||Only in PL/SQL block||No|
|Nested table||Unbounded||Integer||Starts dense, can become sparse||Either in PL/SQL block or at schema level||Yes|
|Variable-size array (Varray)||Bounded||Integer||Always dense||Either in PL/SQL block or at schema level||Yes|
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:
LOB Data type
|BFILE||Used to store large binary objects into Operating System file. BFILE stores full file locator’s path which are points to a stored binary object with in server. BFILE data type is read only, you can’t modify them.||Size: up to 4GB (232 – 1 bytes)|
|Directory name: 30 character|
|File name: 255 characters|
|BLOB||It is same as BFILE, used to store unstructured binary object into Operating System file. BLOB type fully supported transactions are recoverable and replicated.||Size: 8 TB to 128 TB|
|(4GB – 1) * DB_BLOCK_SIZE|
|CLOB||Large blocks of character data are stored into Database using this datatype. Store single byte and multi byte character data. CLOB types are fully supported transactions, and are recoverable and replicated.||Size: 8 TB to 128 TB|
|(4GB – 1) * DB_BLOCK_SIZE|
|NCLOB||NCLOB data type to store large blocks of NCHAR data into Database. Store single byte and multi byte character data. NCLOB type fully supported transactions are recoverable and replicated.||Size: 8 TB to 128 TB|
|(4GB – 1) * DB_BLOCK_SIZE|
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.
Unknown Column Datatypes
|%TYPE||Used to store single column with unknown datatype. Column is identified by %TYPE data type.|
|%ROWTYPE||Stores the datatype of all the columns in a table. All columns are identified by %ROWTYPE datatype.|
|E.g. EMP%ROWTYPE–> a table will be assigned with the EMP table’s entire column datatypes|
|%ROWID||ROWID is data type. ROWID is two type extended or restricted. Extended ROWID return 0 and restricted ROWID return 1 otherwise return the row number.|
|Some functions are defined on ROWID and are defined in DBMS_ROWID package.|
|ROWID_VERIFY||Verifies that if the ROWID can be extended.|
|ROWID_TYPE||0 = ROWID, 1 = extended.|
|ROWID_BLOCK_NUMBER||Block number that contain the record return 1.|
|ROWID_OBJECT||Number of the object that are in the record.|
|ROWID_RELATIVE_FNUMBER||Returns the Relative file number that contains record.|
|ROWID_ROW_NUMBER||Row number of the Record.|
|ROWID_TO_ABSOLUTE_FNUMBER||Return the absolute file number.|
|ROWID_TO_EXTENDED||Converts the ROWID to extended format.|
|ROWID_TO_RESTRICTED||Converts the ROWID to restricted format.|
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
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;
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.
DATATYPES IN SQL
SUB DATATYPES IN SQL
NUMBER (p, s)
Date / Time
Composite Datatypes / User Define data types
Associative Array / Index by Table
Variable Array (Varray)
Unknown Column Types