Types in PL/SQL and Variable Declaration

Data type in any programming language lets programmer identify and define the kind of data they will use in their codes. These data types can be numbers, text, dates, images, boolean etc. The Data Types in PL/SQL are the same that we have been using in Oracle SQL. So let’s discuss them.

Commonly used Data Types in PL/SQL

VARCHAR2(size)

This data type is used to store character strings from single character to 6000 characters. When a variable is declared as varchar2 it stores only the value assigned to it. If the declared size of variable is large but actual value is of lesser length, the remaining spaces are not used or blocked .

Example: a variable studentName is declared as varchar2(40). The value assigned is ‘James’. This variable will use only 5 characters of space out of allotted 40.

NUMBER [(precision, scale)]

This data types is used in all situations where you have to store numeric data. It accepts two parts in parenthesis while declaring a variable. The first part is precision for the total number of digits in the number. Second part is Scale for the count of digits after the decimal part.

Example

Salary NUMBER(8,2); will allow a maximum of 8 digits in salary variable out of which 2 digits can be stored after the decimal. 8000.50 is allowed where as 200000.530 or 3000000.50 is not allowed.

DATE

Date data type is used when you have to store some date or do some date related manipulation. Date is always stored in Oracle in ‘dd-mon-yy’ format. ’09-JUN-19’ is the data form for 9th June 2019.

CHAR [(maximum_length)]

Char data type is used when you know how many characters are going to be in a column or variable. It is best to use char data type when the length of data is kwon beforehand. When a variable is declared as char( maxlength), even if the actual data value is less than maxlength, the space allocated to that value will be of maxlength size.

Example: a variable studentName is declared as char(40). The value assigned is ‘James’. This variable is actually having only 5 characters but the space allotted will be 40 characters. It means out of 40 , 35 character spaces will remain unused.

CLOB

CLOB is the data type to store variable-length character data. If you declare a variable as CLOB you are actually using an address pointer storing address of a large block of character data. It can store a huge amount of character data in comparison to varchar2.

BOOLEAN

BOOLEAN data type is used to store the logical values like TRUE, FALSE, and NULL. NULL is neither TRUE nor FALSE. NULL represents a missing, unknown, or irrelevant value. When you use Boolean variable you can use only logical operators for manipulation. You can assign TRUE, FALSE, and NULL to variable with BOOLEAN Data-type.

Here you have learned about the most commonly used data types in PL/SQL. There are many other data types in PL/SQL available for different coding needs.  You can read more about PL/SQL data types here

Be First to Comment

Leave a Reply

Your email address will not be published.