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.
Each variable must have a name. There are rules you must follow:
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;
DECLARE Variable1 DataType1; Variable2 DataType2; Variable_n DataType_n;
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().
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.
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.
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.
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 /
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 /
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.
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 /
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 /
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:
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:
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.
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; /