Menu Close

Articles

PL/SQL Data Types

PL/SQL can be used to manipulate data. It is a strongly typed language with explicit types. The SQL types and PL/SQL types are a little bit different. Let’s explore these types.

String Type

There are 3 types of strings in PL/SQL:

  1. CHAR: Right padded, fixed-length strings; 
  2. VARCHAR2: Variable length strings,  maximum 32767 code points;
  3. CLOB: Character large objects, up to 128 terabytes.

Numeric Type

SQL has only one numeric data type: NUMBER. This data type can be stored in the database. PL/SQL  has several other numeric data types designed to improve computation performance. 

  • NUMBER: A true decimal datatype that is ideal for working with monetary amounts. NUMBER is the only one of PL/SQL’s numeric types to be implemented in a platform-independent fashion.

  • PLS_INTEGER: Integer datatype conforming to your hardware’s underlying integer representation. You cannot store values of this type in tables;

  • SIMPLE_INTEGER: Introduced as of Oracle Database 11g Release 1. This datatype results in significantly shorter execution times for native compiled code. 

  • BINARY_FLOAT : Single-precision, IEEE-754, binary floating-point datatypes. These datatypes are highly specialized and are useful when you need to improve the performance of computation-intensive operations. 
  • BINARY_DOUBLE: Single- and double-precision, IEEE-754, binary floating-point datatypes. These BINARY datatypes are highly specialized and are useful when you need to improve the performance of computation-intensive operations. 

Date Type

There are three datatypes you can use to work with dates and times:

  • DATE: This datatype stores a date and a time, resolved to the second. It does not include the time zone. DATE is the oldest and most commonly used datatype for working with dates in Oracle applications.

  • TIMESTAMP: Time stamps are similar to dates, but with these two key distinctions:
    1. you can store and manipulate times resolved to the nearest billionth of a second (9 decimal places of precision),
    2. you can associate a time zone with a time stamp, and Oracle Database will take that time zone into account when manipulating the time stamp.

  • INTERVAL: Whereas DATE and TIMESTAMP record a specific point in time, INTERVAL records and computes a time duration. You can specify an interval in terms of years and months, or days and seconds.

Boolean Type

You use the BOOLEAN datatype to store the logical values TRUE, FALSE, and NULL. Only logic operations are allowed on BOOLEAN variables. Only the values TRUE, FALSE, and NULL can be assigned to a BOOLEAN variable.

Note: You cannot insert the values TRUE and FALSE into a database column. Also, you cannot select or fetch column values into a Boolean variable. Database SQL do not support Boolean type.

Data Elements

For working with data types you need two things: First you must declare variable, constant or parameter using a data type. Second you can give a value to these elements using an assignment.
Constants and input parameters are immutable. Variables and output parameters can be modified using modification operators. Next I will post examples with comments:

Declaring variables:

In the example above we declare several variables and use only one. That is, we can define variables that can be used later in the code but when we compile this code we may receive some warnings for variables that are not used.

Data Operators

First operator we already use is “:=”. This is assignment operator. It can be used to setup the initial value for a variable or reset an existing variable to take  a new value. The old value is lost and is replaced by the new value using an expression or a literal. 

Expressions are based on variables, data literals, operators and function calls. Operators are specific to a particular data type. For example operator “||” can be used to concatenate two strings while operator “+” can be used for addition between two numbers:

Modification operators

OperatorDescriptionExample
:=Assign/reset operatorstr := ‘test’
||Concatenation of two stringsstr := ‘test ‘||’concatenation’
+Addition of two numberssum := 4 + 8;
Subtraction of two numbersdiff := 8 – 4;
*Multiplication of two numbersprod := 4 * 8;
/Division of two numbersdiv := 8 / 4;
**Exponent operator bool := (2 ** 3 = 8); — true 

Relation operators

Next operators have a Boolean result. The result can be used in conditional statements. 
given: a = 1, b = 2

OperatorDescriptionExample
=Is equala = b; — False
<>Not equal: alternatives: (!=, ~=)a != b; — True
<Less thana < b; — True
>Greater thana > b; — False
>=Greater than or equal to2 >= b; — True
<=Less than or equal toa <= b; — True

Logical operators

Next operators are keyword based operators for Boolean expressions.
given: a = True, b = False

OperatorDescriptionExample
andLogical ANDa and b; — False
orLogical ORa or b; — True
notLogical NOTnot b; — True
not a; — False

Expressions

In PL/SQL expressions have data type. Expressions of same data type can be combined in larger expressions. Logical expressions can be used in conditional statements: {IF, WHILE, CASE}.

Different data types can be converted using functions to make two data types compatible. For this we will explain more in next chapter about data processing.

example:

Read Next: Data Processing