Datatypes in PL/SQL

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:

1. Scalar Types: These are basic datatypes which generally holds a single value like a number or a string of characters. Scalar types have 4 different categories which are listed in the diagram above, namely Number Types, Character and String, Boolean Types and Date and Time etc.
2. LOB Types: This datatype deals with large objects and is used to specify location of these large objects like text files, images etc which are generally not stored outside the database.
3. Reference Types: This datatype is used to hold pointer values which generally stores address of other program items.
4. Composite Types: Last but not the least, as the name suggests this type of data is a composition of individual data which can be manipulated/processed separatel as well.

We won't be covering all these different datatypes below, but we will be covering the ones which are most widely used.

NUMBER(p,s)

Range: p= 1 to 38 s= -84 to 127

This datatype is used to store numeric data. Here, p is precision s is scale.

Example:

1. Age NUMBER(2); where , Age is a variable that can store 2 digits
2. percentage NUMBER(4,2); where, percentage is a variable that can store 4 (p) digits before decimal and 2 (s) digits after decimal.

CHAR(size)

Range: 1 to 2000 bytes

• This datatype is used to store alphabetical string of fixed length.
• Its value is quoted in single quotes.
• Occupies the whole declared size of memory even if the space is not utilized by the data.

Example:

1. 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.

VARCHAR(size)

Range: 1 to 2000 bytes

• This datatype is used to store alphanumeric string of variable length.
• Its value is quoted in single quotes.
• Occupies the whole declared size of memory even if the space is not utilized by the data.

Example:

1. 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.

VARCHAR2(size)

Range: 1 to 4000 bytes

• This datatype is used to store alphanumeric string of variable length.
• Its value is quoted in single quotes.
• It releases the unused space in memory, hence saving the unused space.

Example:

1. 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.

DATE

Range: 01-Jan-4712 BC to 31-DEC-9999

• It stores the data in date format DD-MON-YYYY
• The value for this datatype is written in single quotes.

Example:

1. DOB DATE; where, DOB is a variable that stores date of birth in defined format (i.e,’13-FEB-1991’)

%TYPE

• It stores value of that variable whose datatype is unknown and when we want the variable to inherit the datatype of the table column.
• Also, its value is generally being retrieved from an existing table in the database, hence it takes the datatype of the column for which it is used.

Example:

1. Student sno %TYPE;, where Student is the name of the table created in database and sno is variable whose datatype is unknown and %TYPE is used to store its value.

BOOLEAN

• This datatype is used in conditional statements.
• It stores logical values.
• It can be either TRUE or FALSE

Example:

1. isAdmin BOOLEAN; where, isAdmin is a variable whose value can be TRUE or FALSE depending upon the condition being checked.

Conclusion

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:

• PL/SQL LOB Types
• PL/SQL Number Types
• PL/SQL Character and String Types
• PL/SQL Boolean Types
• PL/SQL Date Time

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.