Oracle - Lesson 04: Variables and Data Types

	Variables and Data Types

Variables Fundamentals

Introduction

A variable is a technique of reserving an area in the computer memory to temporarily store a value in it. To make this possible, there are a few rules you must observe.

Practical Learning: Introducing Variables

To launch Oracle, click Start -> (All) Programs -> Oracle - OraDb11g_home1 -> Application Development -> SQL Plus
Enter the username and press Enter
Enter the password and press Enter

Declaring a Variable

Reserving an area of memory to store a value is referring to as declaring a variable. To declare a variable, use the DECLARE keyword using the following formula:

DECLARE Variable1 Options;

To declare more than one variable, you can use the following formula:

DECLARE
      Variable1 Options;
      Variable2 Options;
      Variable_n Options;

The DECLARE keyword lets the interpreter know that you are making a declaration.

Objects Names

Each variable must have a name. There are rules you must follow:

A name must start with a letter
After the first letter, it can be followed by a combination of underscores, letters, dollar signs, and digits
The following symbols are not allowed: -, /, &, and an empty space*

Introduction to Data Types

When declaring a variable, after setting the name of a variable, you must specify the amount of memory that the variable will need to store its value. Since there are various kinds of information a database can deal with, PL/SQL provides a set of data types. Therefore, a variable declaration uses the following formulas:

DECLARE Variable1 DataType;

or:

DECLARE
      Variable1 DataType1;
      Variable2 DataType2;
      Variable_n DataType_n;

Initializing a Variable

When declaring a variable, the interpreter reserves a space in the computer memory for it but the space doesn't necessarily hold a recognizable value. This means that, at this time, the variable is null. One way you can change this is to give a value to the variable. This is referred to as initializing the variable.

There are two ways you can initialize a variable: when declaring it or after declaring it.

To initialize a variable when declaring it, follow its name by the := operator and the declared value:

DECLARE Variable1 DataType := Value;

If you are declaring more than one variable and you want to initialize one or more, follow the desired one with := and the necessary value:

DECLARE
      Variable1 DataType1 := Value1;
      Variable2 DataType2 := Value1;
      Variable_n DataType_n := Value_n;

After declaring an initializing a variable, you can use it. For example you can enter it in the parentheses of DBMS_OUTPUT.PUT_LINE().

Characters and String Variables

Characters

A field of characters can consist of any kinds of alphabetical symbols in any combination, readable or not. If you want a variable to hold a fixed number of characters, such as the book shelf numbers of a library, declare it with the CHAR data type. Here is an example:

DECLARE Gender CHAR;

By default, the CHAR data type can be applied to a variable that would hold one character at a time. After declaring the variable, when initializing it, include its value in single-quotes. When initializing the variable, you must use only one character. Here is an example:

SQL> DECLARE Gender CHAR := 'F';
  2  BEGIN
  3     DBMS_OUTPUT.PUT_LINE('Gender: ' || Gender);
  4  END;
  5  /
Gender: F

PL/SQL procedure successfully completed.

Strings

A string is a character or a combination of characters. If a variable will hold strings of different lengths, you can declare it using either the VARCHAR2 or the NVARCHAR2 data type. The maximum length of text that the variable can hold is 32767 bytes.

In some circumstances, you will need to change or specify the number of characters used in a string variable. To specify the maximum number of characters that can be stored in a string variable, on the right side of CHAR, VARCHAR2, or NVARCHAR2, type an opening and a closing parentheses. Inside of the parentheses, type the desired number.

To initialize the variable, include its value between single-quotes. Here is an example:

SQL> DECLARE FirstName NVARCHAR2(40) := 'Patricia';
  2  BEGIN
  3     DBMS_OUTPUT.PUT_LINE('First Name: ' || FirstName);
  4  END;
  5  /
First Name: Patricia

PL/SQL procedure successfully completed.

The NCHAR and NVARCHAR2 types follow the same rules as the CHAR and VARCHAR2 respectively except that they can be applied to variables that would hold international characters; that is, characters of languages other than US English. This is done following the rules of Unicode formats.

Practical Learning: Using String Variables

At the prompt, type the following:

DECLARE FirstName NVARCHAR2(40);
BEGIN
    FirstName := 'Patricia';
    DBMS_OUTPUT.PUT_LINE('First Name: ' || FirstName);
END;
/

Integer Variables

Introduction

An integer, also called a natural number, or a whole number, is a number that can start with a + or a - sign and is made of digits. Between the digits, no character other than a digit is allowed.

When the number starts with +, such as +44 or +8025, such a number is referred to as positive and you should omit the starting + sign. This means that the number should be written as 44 or 8025. Any number that starts with + or simply a digit is considered as greater than 0 or positive. A positive integer is also referred to as unsigned. On the other hand, a number that starts with a - symbol is referred to as negative.

Integers

To declare an integer variable, use the INT or the INTEGER Data type. You can initialize the variable with a number between -2,147,483,648 and 2,147,483,647. Here is an example:

SQL> DECLARE Distance INTEGER := 628635;
  2  BEGIN
  3     DBMS_OUTPUT.PUT_LINE('Distance: ' || Distance);
  4  END;
  5  /

Small Integers

If you want a variable that would hold small natural numbers, declare it using the SMALLINT data type. When initializing the variable, assign it a small number between -32,768 and 32,767. Here is an example:

SQL> DECLARE Age SMALLINT := 36;
  2  BEGIN
  3     DBMS_OUTPUT.PUT_LINE('Age: ' || Age);
  4  END;
  5  /

Decimal Variables

Introduction

A decimal number is a number that can have a period (or the character used as the decimal separator as set in the Control Panel) between the digits. An example would be 12.625 or 44.80. Like an integer, a decimal number can start with a + or just a digit, which would make it a positive number. A decimal number can also start with a - symbol, which would make it a negative number. If the number represents a fraction, a period between the digits specifies what portion of 1 was cut.

Real Variables

A floating-point number is a fractional number. To declare a variable for decimal values that do not require too much precision, use the FLOAT or REAL data type. Here is an example:

SQL> DECLARE Measure FLOAT := 36.12;
  2  BEGIN
  3     DBMS_OUTPUT.PUT_LINE('Measure: ' || Measure);
  4  END;
  5  /

Precision-Based Variables

To declare a variable for decimal values, use the NUMBER data type. A precision is the number of digits used to display a numeric value. For example, the number 42005 has a precision of 5, while 226 has a precision value of 3. If the data type is specified as an integer (the INT and its variants) or a floating-point number (FLOAT and REAL), the precision is fixed by the database and you can just accept the value set by the interpreter.

The scale of a number if the number of digits on the right side of the period (or the character set as the separator for decimal numbers for your language, as specified in Control Panel). The scale is used only for numbers that have a decimal part.

To control the level of precision applied on a NUMBER variable, follow the NUMBER data type by parentheses. In the parentheses, use two values separated by a comma. The left value represents the precision. The right value represents the scale. The value must be an integer between 0 and 18. Here is an example:

SQL> DECLARE Measure NUMBER(8, 3) := 284636.48;
  2  BEGIN
  3     DBMS_OUTPUT.PUT_LINE('Measure: ' || Measure);
  4  END;
  5  /

Practical Learning: Using a Numeric Variable

Type the following:

DECLARE
    FirstName NVARCHAR2(28);
    LastName  NVARCHAR2(28);
    FullName  NVARCHAR2(60);
    HourlySalary NUMBER(6, 2);
BEGIN
    FirstName := 'Patricia';
    LastName := 'Katts';
    FullName := LastName || ', ' || FirstName;
    HourlySalary := 32.85;

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || FullName);
    DBMS_OUTPUT.PUT_LINE('Hourly Salary: ' || HourlySalary);
END;
/

Date and Time Variables

A DATE data type is used for a variable whose values would consist of date and/or time values. The entries must be valid date or time values. The date value of a DATE variable can be comprised between January 1st, 4712 BC and December 31, 9999.

To initialize a DATE variable, include its value between single-quote. For a date, use the following format:

DD-MMM-Y

DD-MMM-YY

DD-MMM-YYYY

The first number represents the day. If the number is between 1 and 9, you can omit or include a leading 0.

The second section will contain the 3-letter name of the month in any case of your choice (remember that SQL is not case-sensitive).

The right section contains the value of the year:

If the year is expressed with one digit, the year would be considered in the current decade. For example, if the year is expressed as 4 and we are in 2011, it would be considered as 2014
If the year is expressed with 2 digits, the year would be considered in the current century. For example, if the year is expressed as 84 and we are in 2008, it would be considered as 2084

Probably to be on the safe side, you should always express the year with 4 digits.

Here is an example:

SQL> DECLARE DateOfBirth DATE := '06-Feb-1996';
  2  BEGIN
  3     DBMS_OUTPUT.PUT_LINE('Date of Birth: ' || DateOfBirth);
  4  END;
  5  /
Date of Birth: 06-FEB-96

PL/SQL procedure successfully completed.

Practical Learning: Using a Date-Based Variable

Type the following:

DECLARE
    DateHired DATE;
    FirstName NVARCHAR2(28);
    LastName  NVARCHAR2(28);
    FullName  NVARCHAR2(60);
    HourlySalary NUMBER(6, 2);
BEGIN
    DateHired := '06-Aug-2002';
    FirstName := 'Patricia';
    LastName := 'Katts';
    FullName := LastName || ', ' || FirstName;
    HourlySalary := 32.85;

    DBMS_OUTPUT.PUT_LINE('Date Hire    : ' || DateHired);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || FullName);
    DBMS_OUTPUT.PUT_LINE('Hourly Salary: ' || HourlySalary);
END;
/

To execute, click Run

Other Data Types

Boolean Variables

A Boolean value is a piece of information stated as being true or false. To declare a Boolean variable, use the BOOLEAN type. Here is an example:

DECLARE IsOrganDonor BOOLEAN;

As stated previously, you can initialize the variable when declaring. Here is an example:

DECLARE IsOrganDonor BOOLEAN := TRUE;

To initialize the variable after declaring it, in the BEGIN...END section, access the variable and assign the desired value. Here is an example:

DECLARE IsOrganDonor BOOLEAN;
BEGIN
    IsOrganDonor := TRUE;
END;
/