index<--

PL/SQL Data 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:

declare
    v_message varchar2(30); -- variable length string with maxim capacity 30
    c_code char(10); -- fixed length string
    n_index number(5,2); -- number with two decimals
    d_today date := SYSDATE; -- a date with initial value: today
begin
    v_message := 'hello world'; -- alter a variable
    DBMS_OUTPTUT.PUT_LINE(v_message); -- print a message
end;

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.

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:

-- example of expressions
declare
    m varchar2(10);
    a pls_integer;
    b boolean;
begin
    -- string expression
    m := 'hello' || 'world';
    -- numeric expression
    a := a + 1;
    -- logical expression
    if b = False then
    dbms_output.put_line('initial value for boolean is False');
    end if;
end;

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

Relation operators

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

Logical operators

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

Read next: Packages