varchar2, number, date, Boolean, VARRAY on PL/SQL examples

PL/SQL Data Types The use of local variables within a PL/SQL program is important for everyone using the language to understand. It is a basic component of each program, and as such, it is invaluable for understanding what is available and how best to use it. You can now look at how you use and define variables and working storage within your PL/SQL programs.

As with all programming languages, there are characters that you use to write your programs. Each language has its own rules and restrictions when it comes to the valid characters. In the following sections of article, we will show you:

Table of contents[Show]

Valid characters when programming in PL/SQL
Arithmetic operators
Relational operators

Valid PL/SQL Characters

When programming in PL/SQL, you can use only characters as defined here:

Characters can be typed in either upper- or lowercase. PL/SQL is case insensitive.
All whole numbers between 0 and 9.
The following symbols: ( ) + − * / < > = ! ~ ; : . @ % , " ‘ # ^ & _ | { } ? [ ]

Some of these characters are for program commands; others serve as relational or arithmetic operators. Together they form a program.

Arithmetic Operators

Table 1 shows the common arithmetic operators used in PL/SQL. They are listed in the order of precedence in which they are executed (that is, by priority). When the functions appear in the same line, it means that they are executed with the same level of precedence, so the position of the expression determines which goes first in the operational execution.

TABLE 1. Arithmetic Operators

Table 2 shows the common relational operators used in PL/SQL. These are the logical variables that are used to compare data. As with any comparison, they need to conform to logic hierarchies, especially when using multiple operators in your conditional clauses.

TABLE 4-2. Relational Operators

The use of variables in a PL/SQL program is usually required to truly leverage the power of the language. It is here that you define how your data is to be held while you work it through your program. These variables can be the same types you have already learned about in the SQL language. However, in addition to these standard data types, there are some special ones that have been created specifically for the PL/SQL language.

NOTE
All SQL within Oracle is supported directly with PL/SQL.

One of the important features of Oracle Database 11g is the tight integration of the SQL and PL/SQL engines into one system. This means that from Oracle9i forward you can run the same commands in PL/SQL that you use in SQL. This was not true in earlier versions of PL/SQL, so take care if using these versions (it may be time to consider an upgrade). That would also qualify you as more than a beginner.

Now you can move on to the most common data types that you will use when writing PL/SQL programs:

varchar2
number
date
Boolean

You’ll use these variables in PL/SQL the same way that you would use them in SQL. Assigning values to variables is very important when programming in PL/SQL or any other programming language. You can assign values to variables in any section of your program code, and you can also assign values to variables in the DECLARE section. Defining your variable in the DECLARE section is done to initialize values in advance of their use in the program or to define values that will be used as constants in your program. To assign a value to a variable in the declaration section, you would use the following format:

Variable_name variable_type := value;

NOTE
The important item to notice here is that you use the := to assign a value. This is the standard used in PL/SQL.

You can also define variable values in the execution and exception sections of your PL/SQL program. Within the program, you would use the following format to assign a value to a variable:

Variable name := value;

To assign values to variables you use, let’s look at a small program that assigns values in each section of a program:

-- Declaration section of the program 
declare

   number : = 0;	-- initiate value to 0
   date := sysdate; -- assign system date to variable varchar2(50);	-- variable is 
   defined but has no value
   number ian_seq.nextval; -- assigns the next sequence value to the variable

---
--- Execution section of the program 
begin

   l_counter := l_counter +1; -- add one to current value of counter 
   l_name := 'DIZZY THE CAT'; -- set a value to character variable 

-- Error (EXCEPTION) handling section of the program 
exception
   --- Generic error handling to handle any type of error when others then 
   --- print out an error message
   raise_application_error (-20100, 'error#' || sqlcode || ' desc: ' sqlerrm) 

end;

NOTE
Oracle has some special variables that can be used in a PL/SQL program. In the example, we used the SQLCODE and SQLERRM variables. These variables represent the Oracle error number and the Oracle error message, respectively. You can use these variables to capture Oracle errors in your program.

The VARCHAR2 Data Type

VARCHAR2 is a variable-length alphanumeric data type. In PL/SQL, it may have a length up to 32,767 bytes. When you define the VARCHAR2 variable in the DECLARE section, remember to terminate the line with a semicolon (;). The following is the form of VARCHAR2 variable declarations:

Variable_name varchar2(max length);

where MAX_LENGTH is a positive integer, as in

l_name varchar2(30);

You can also set an initial or default value for the variable. This is done on the same line as the variable declaration in the DECLARE section of your program. You can do this by using the following syntax:

l_name varchar2(30) := 'ABRAMSON';

The preceding statement will set that value of the variable l_name to the value of ABRAMSON.

The NUMBER Data Type

The NUMBER data type is used to represent all numeric data. The format of the declaration is

Number_field number(length, decimal_places);

where the length can be from 1 to 38 numerical positions, and decimal_places represents the positions for numerical precision of the decimal place for the variable. Keep this in mind when you define your numerical variable, as in

l_average_amount number(12,2);

This describes a variable that can hold up to ten digits [Length(12) – decimal_places(2)] and up to two decimal places. This means the variable can hold a number up to a value of 9,999,999,999.99. The NUMBER data type has a number of supported subtypes. These include DECIMAL, FLOAT, REAL, and others. These are quite familiar to people who use other languages to program for their business needs.

The DATE Data Type

The DATE data type variable is used to store DATE and DATETIME values. The following is the format of the date declaration:

Date variable date;

By default, Oracle displays values using the format DD-MON-YY. So a value of 14-JAN-08 would be the equivalent of saying January 14, 2008. When programming in PL/SQL, you should always use this data type when performing date manipulation. It is possible when combining this data type with some built-in Oracle functions to extend the flexibility of your date manipulations. For example, let’s say that you create a variable for a start date and you want to place values into this variable. Let’s see how this can be done:

Declare
   L_start_date date;
Begin
   L_start_date := '29-SEP-89';	-- Sets variable
                                -- to September 29, 1989.

   L_start_date to_date('29-SEP-2083 ',	'DD-MON-YYYY'); -- Sets variable 
                                                        -- to September 29, 2083 

   L_start_date := to_date('09-JUN-91:13:01 ', 'DD-MON-YY:HH24:MI'); 
                           -- Sets variable to June 9, 1991, 1:01 p.m.
End;

So here you have set the date variable in three different forms. The first is the simplest, while the second is more complex (because it uses the TO_DATE function), although it does allow for more flexible data declarations because you can use a four-digit year definition. The final example shows you how to put a DATETIME into the variable. Again, you’ll use the TO_DATE function, but you’ll include the time in the value and then define it with the date mask definition.

NOTE
For more information on other Oracle built-in functions, see “Oracle Database SQL Reference,” in the Oracle Database 11g documentation.

You should also familiarize yourself with the variations of the DATE data type. This includes the TIMESTAMP data type, which provides additional date support that may not be available with the simple date data type.

The BOOLEAN Data Type

The final basic data type we will discuss is the BOOLEAN data type. Simply put, this variable will hold a value of either true or false. When you use this data type, you must test its status and then do one thing if it is true and another if it is false. You can use a Boolean expression to compare arithmetic expressions or character expressions. So, if you have the following arithmetic values, you’ll get

L_record_goals := 91;
L_season_goals := 77;
-- Therefore the following expression will be true 
L_record_goals > l_season_goals 
-- However the next is false 
l_record_goals <= l_season_goals

If you wish to compare character strings, the same can be done. Here’s an example:

l_web_developer := 'MOTI FISHMAN; 
l_Oracle_dba := 'PETER FORTIER';

-- The following expression will be true in a true Boolean value 
v_compare_names := l_Cognos developer <> 1 oracle_dba;

The VARRAY Data Type

The VARRAY is another type that is quite commonly used in PL/SQL. PL/SQL provides a data structure called the VARRAY, which can store a fixed-size sequential collection of elements of the same type. A VARRAY is used to store an ordered collection of data, but it is often more useful to think of an array as a collection of variables of the same type. A VARRAY is similar to a nested table except an upper bound must be specified in the declaration. Like nested tables, they can be stored in the database. But unlike nested tables, individual elements cannot be deleted so they remain dense.

The following code shows how a simple example can be rewritten to use a VARRAY:

SET SERVEROUTPUT ON SIZE 1000000 
DECLARE
   type namesarray IS VARRAY(5) OF VARCHAR2(10);
   type grades IS VARRAY(5) OF INTEGER;
   names namesarray;
   marks grades;
   total integer;
BEGIN
   names := namesarray('Ian', 'Michelle', 'Mike', 'Clarke', 'Albert');
   marks:= grades(95, 94, 99, 85, 90);
   total := names.count;
   dbms output.put line('Total '|| total || ' Students');
   FOR i in 1 . . total LOOP
      dbms_output.put_line('Student: ' Marks: ' || marks(i));
   END LOOP;
END;
/
Student: Ian Marks: 95
Student: Michelle Marks: 94
Student: Mike Marks: 99
Student: Clarke Marks: 85
Student: Albert Marks: 90
PL/SQL procedure successfully completed.

There are a variety of methods that can be applied on the collections to help for more advanced calculations and processing:

EXISTS(n) Returns TRUE if the specified element exists
COUNT Returns the number of elements in the collection
LIMIT Returns the maximum number of elements for a VARRAY, or NULL for nested tables
FIRST Returns the index of the first element in the collection
LAST Returns the index of the last element in the collection
PRIOR(n) Returns the index of the element prior to the specified element
NEXT(n) Returns the index of the next element after the specified element
EXTEND Appends a single NULL element to the collection
EXTEND(n) Appends n NULL elements to the collection
EXTEND(n1,n2) Appends n1 copies of the n2th element to the collection
TRIM Removes a single element from the end of the collection
TRIM(n) Removes n elements from the end of the collection
DELETE Removes all elements from the collection
DELETE(n) Removes element n from the collection
DELETE(n1,n2) Removes all elements from n1 to n2 from the collection

The collections can help you process information in sets and provide higher performance for your PL/SQL processing.

Ask the Expert
Q: How do you let Oracle set the definition of a variable within PL/SQL programs based on a table’s column definition?
A: Dynamic variable definitions based on column definitions are very important, and you should always utilize them within PL/SQL. This ties the variable definition to a table within the database. When defining your variable, use the name of the table, the column, and the special string %TYPE. The following is an example of using the product table’s prod_id as a variable data definition:
By using the %TYPE variable type, you have freed your program of the need to ever redefine this field. If the column’s definition changes, so will the variable within your program.