PL/SQL datatypes are not just limited to writing SQL queries but they are used in the PL/SQL block as well, just like any other programming language.
Provising a datatype specifies how any data will be stored and processed by Oracle when any PL/SQL code block is executed.
Datatype defines the type of data being used, whether it is a number or a word(string) or a single character etc. Following datatypes can be used in PL/SQL depending upon the type of data required:
So we have 4 broader categories of datatypes and they are:
We won't be covering all these different datatypes below, but we will be covering the ones which are most widely used.
Range: p= 1 to 38 s= -84 to 127
This datatype is used to store numeric data. Here,
p is precision
s is scale.
Age NUMBER(2);where , Age is a variable that can store 2 digits
percentage NUMBER(4,2);where, percentage is a variable that can store 4 (p) digits before decimal and 2 (s) digits after decimal.
Range: 1 to 2000 bytes
rank CHAR(10);where, rank is a variable that can store upto 10 characters. If the length of data(charcaters) stored in rank is 5 then it will still occupy all the 10 spaces. 5 space in the memory will get used and the rest blank memory spaces will be wasted.
Range: 1 to 2000 bytes
address VARCHAR(10);where, address is a variable that can occupy maximum 10 bytes of memory space and can store alphanumeric value in it. Unused spaces are wasted.
Range: 1 to 4000 bytes
name VARCHAR2(10);where, name is a variable that can occupy maximum 10 bytes of memory to store an alphanumeric value. The unused memory space is released.
Range: 01-Jan-4712 BC to 31-DEC-9999
DOB DATE;where, DOB is a variable that stores date of birth in defined format (i.e,’13-FEB-1991’)
Student sno %TYPE;, where Student is the name of the table created in database and sno is variable whose datatype is unknown and
%TYPEis used to store its value.
isAdmin BOOLEAN;where, isAdmin is a variable whose value can be TRUE or FALSE depending upon the condition being checked.
The datatypes covered above are the ones which are most commonly used. But there are many more different datatypes for which you can check the official Oracle reference at the following links:
In this tutorial we covered the datatypes that are used in PL/SQL and while doing so we talked a lot about variables too. If you are confused, move on to the next tutorial where we have covered all about PL/SQL variables.