facebook google plus twitter
Webucator's Free Oracle PL/SQL Tutorial

Lesson: Declaring Variables

Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.

A PL/SQL block can contain an optional section where types and variables are defined. These items are accessed and manipulated in the executable section of the block. This chapter describes the types of variables available and how to create, access, and modify the values that variables contain.

Lesson Goals

  • Learn about simple program variables and complex data types
  • Learn how to use the declare clause
  • Learn how to declare simple types
  • Learn how to assign values to variables

Variable Usage

Variables can be created and modified in a variety of ways. To create a variable, you declare it in the DECLARE section of the PL/SQL block. Declaring a variable allocates storage space for the value it contains, specifies its data type, and sets up a reference to the value. Once a variable has been declared it is eligible to be assigned a value. Variable assignment at the time of declaration is known as initialization.

Variable Data Types

PL/SQL Variables can be classified in a number of broad categories. These categories are not part of the language itself, but are helpful for organizing the numerous data types into meaningful groupings. Scalar data types include numeric, character and date/time types. Composite data types include collections and records. These complex data types contain other values that can be individually referenced. Large Objects or LOBs reference large objects such as binary files or documents. Reference data types (e.g. cursors) are essentially pointers.

Many variable data types includes several variations. Numeric variable types can be created in a variety of sizes and can have a defined scale and precision. Character data types can be of a set size (CHAR) or vary in size (VARCHAR2). Time data types can store date and time information at various levels of precision, and data types exist not only to define specific moments of time but also to describe intervals of time. Some data types (most notably VARCHAR) have been deprecated and should not be used.

One way that PL/SQL is closely integrated with SQL is by the fact that all SQL variable types are available in PL/SQL. There is also a syntax that allows the declaration of PL/SQL variables to correspond to the data types that exist in Oracle tables. The %TYPE keyword is used to declare a variable whose type matches a specific database table's column. The %ROWTYPE keyword is used to declare a complex variable (a record) which has individual fields that correspond to every column in a given database table. However, not all PL/SQL data types are available in SQL. An important consideration is that the Boolean data type available in PL/SQL is not a valid datatype in SQL.

Variable Naming

PL/SQL variable names must start with a letter and can be followed by up to 29 additional characters (for a total maximum length of 30 characters). Valid characters include letters, numerals, dollar signs, underscores, and number signs. Note that dashes in names are not permitted (this is a common problem when using XML and PL/SQL together). Also note that variable names are not case sensitive. This is similar to SQL but unlike other case sensitive languages such as Java.

There is no standard naming convention for variables beyond the enforcement on size and characters used. Some programmers use v_ to distinguish a variable from a parameter (identified using p_). Others indicate the data type in a similar manner (i_ for integer, d_ for date, v_ for VARCHAR2). It is advisable to have your organization select a naming standard to encourage some degree of uniformity in coding.

Variable Assignment

Although it is not required, it is generally a good idea to initialize a variable when creating it. When variables are created, they can be assigned a value using the assignment operator, :=, or the DEFAULT keyword. Oracle suggests that you use DEFAULT for variables that have a typical value and the assignment operator for variables that do not. A declaration can also specify a NOT NULL constraint, to prevent the assignment of a NULL value to the variable. Variables that are not initialized have the value NULL. As with SQL, literal character data is always enclosed in single quotes. Similarly, quoted strings may not contain line breaks.

Code Sample:

Declare-Clause/Demos/declare_v.sql
DECLARE
  my_variable VARCHAR2(11);
BEGIN
  NULL;
END;

Code Explanation

This example consists of the simple declaration of a VARCHAR2 variable. Verify that code can compile (run will do nothing).

Code Sample:

Declare-Clause/Demos/declare_v_and_init.sql
DECLARE
  my_variable VARCHAR2(11) := 'Hello World';
BEGIN
  NULL;
END;

Code Explanation

This example involves both the declaration and initialization of a VARCHAR2 variable. This code can also compile but there will be no visible results. Variable assignment can be performed using the := operator. (Keep this in mind if you have a background in a language where = is used for assignment).

Code Sample:

Declare-Clause/Demos/declare_v_using_default_keyword.sql
--using default keyword
DECLARE
  my_variable VARCHAR2(11) NOT NULL  default 'Hello World';
BEGIN
 dbms_output.put_line(my_variable);
END;

Code Explanation

This example declares, initializes, and sets the default value of a VARCHAR2 variable.

Variables can also be assigned values after declaration, in the executable section. This is typically done in one of two ways, using the assignment operator (:=) or a SELECT INTO statement.

Code Sample:

Declare-Clause/Demos/assign_operator_date.sql
DECLARE
  my_date DATE;
BEGIN  
  my_date := current_date;
  DBMS_OUTPUT.PUT_LINE('My date is:  ' ||
                       my_date);
END;

Code Explanation

The DATE data type is assigned in this example using the := operator.

Code Sample:

Declare-Clause/Demos/assign_into_date.sql
DECLARE
  my_date DATE;
  
BEGIN  
  select sysdate
  into my_date
  from dual;
  
  DBMS_OUTPUT.PUT_LINE('My date is (from select into): '
                       || my_date);
END;

Code Explanation

The SELECT INTO statement is used above to assign a value (rather than using the := operator as in the previous example).

Variable Display

Once you have declared a variable and assigned it a value, you can display its assigned value in the context of PL/SQL using the DBMS_OUTPUT.PUT_LINE stored procedure.

Code Sample:

Declare-Clause/Demos/declare_v_init_and_display.sql
DECLARE
  my_variable VARCHAR2(11) := 'Hello World';
BEGIN
  DBMS_OUTPUT.PUT_LINE(my_variable);
END;

Code Explanation

This demonstration illustrates the declaration, initialization and display of a VARCHAR2 variable. As discussed previously, ensure that serveroutput is on to display the results.

As is the case with most languages, special care must be taken when dealing with NULL values. For example, a variable can be assigned the value of NULL + 1. The resulting assignment sets the variable to NULL, which might not be the intent.

Code Sample:

Declare-Clause/Demos/declare_n_null_increment.sql
DECLARE
  my_number NUMBER;  
BEGIN
  my_number := my_number + 1;
  DBMS_OUTPUT.PUT_LINE('My number is: ' 
                       || my_number);
END;

Code Explanation

This demonstration involves the declaration of a NUMBER which is not initialized but subsequently incremented. The absence of a displayed result illustrates that NULL is not the same as zero, and variables are not initialized to zero or another value by default.

Code Sample:

Declare-Clause/Demos/declare_n_increment_display.sql
DECLARE
  my_number NUMBER := 0;
BEGIN
  my_number := my_number + 1;
  DBMS_OUTPUT.PUT_LINE('My number is: ' || my_number);
END;

Code Explanation

The proper declaration, initialization, incrementing and display of a variable is shown in this example.

At times you may want to utilize a special character in a character string such as a tab or a newline. The CHR function can be used to insert these types of special characters.

Code Sample:

Declare-Clause/Demos/chr_function.sql
BEGIN
 dbms_output.put_line('9:' || CHR(9) || '9.' ||
                      '10:' || CHR(10) || 'end 10.' ||
                      '13:' || CHR(13) || 'end 13.' ||
                      '15:' || CHR(15) || 'end 15.');
END;

Code Explanation

In this example characters 9 (tab), 10 (line feed), 13 (carriage return), and 15 (shift in) are used to influence the output.

Complex Variable Types

The two types of complex types are collections and records. All of the internal components of a collection are of the same PL/SQL data type. The internal components of a record (fields) may have different data types. PL/SQL has three collection types: index-by table (or associative array), nested table, and varray (or variable-size array).

Collection types have different attributes related to how they allocate and store data that can make them useful in certain circumstances to enhance performance. However, due to the tight integration of SQL with PL/SQL, many tasks that might be handled by a collection simply can be addressed by using SQL queries and other Oracle features such as temporary tables.

Records are often created to correspond with existing database tables. They provide an efficient syntax to ensure that variables used by a PL/SQL program are of the same type and precision of data stored in a table. This prevents various errors that can result when data exceeds the size of the declared type and prevents concern of data loss when passing data between PL/SQL and the database.

Code Sample:

Declare-Clause/Demos/record_type_variables.sql
DECLARE
TYPE personinfo
  IS
  RECORD
  ( pid       INT :=0,
    firstname VARCHAR2(20),
    lastname  VARCHAR2(30),
    address1  VARCHAR2(50),
    city      VARCHAR2(40),
    state     CHAR(2),
    zipcode   VARCHAR2(10) );

  person personinfo;
BEGIN
  -- Using composite data type
  person.pid       := 1;
  person.firstname := 'Sam';
  person.lastname  := 'Spade';
  person.address1  := '1 Maltese Way';
  person.city      := 'New York';
  person.state     := 'NY';
  person.zipcode   := '10020';
  DBMS_OUTPUT.PUT_LINE('ID =' || person.pid ||
                       ', First Name = ' || person.firstname);
END;
/

Code Explanation

Code Sample:

Declare-Clause/Demos/record_using_rowtype.sql
DECLARE
  Person employees%ROWTYPE;
BEGIN
  SELECT *
  INTO person
  FROM employees
  WHERE rownum <= 1;

  DBMS_OUTPUT.PUT_LINE('1st "random" employee is ' ||
                       person.first_name || '.');
END;
/

Code Explanation

Code Sample:

Declare-Clause/Demos/record_using_type.sql
--The problem with using %rowtype is that it is usually used
-- in conjunction with select *, a very slow performing query.
-- So, use just the columns you need.
DECLARE
  TYPE personinfo IS record
      (firstname  employees.first_name%TYPE,
       lastname   employees.last_name%TYPE,
       empsalary  employees.salary%TYPE);

  person personinfo;

BEGIN
  SELECT first_name, last_name, salary
  INTO person
  FROM employees
  WHERE rownum <= 1;

  DBMS_OUTPUT.PUT_LINE('1st "random" person is ' ||
                       person.firstname || ' ' ||
                       person.lastname ||
                       ', with a salary of ' ||
                       person.empsalary);
END;
/


-- Here is an alternative to using a record type.
declare
  firstname  employees.first_name%type;
  lastname   employees.last_name%type;
  empsalary  employees.salary%type;

begin
  select first_name, last_name, salary
  into firstname, lastname, empsalary
  from employees
  where rownum <= 1;

  dbms_output.put_line('1st "random" person is ' ||
                       firstname || ' ' || lastname ||
                       ', with a salary of ' ||
                       empsalary);

end;

Code Explanation

Code Sample:

Declare-Clause/Demos/associative_array_index_by_varchar2.sql
-- Using a collection (in this case an associative array).
DECLARE
  TYPE population_size
     IS
     TABLE OF NUMBER INDEX BY VARCHAR2(64);

  city_population   population_size;
  state_population  population_size;

  NY_population       NUMBER;
  index_position_id   VARCHAR2(64);
BEGIN
  -- Enter the populations of ...
  city_population('Syracuse') := 300000;
  state_population('NY')      := 3000000;
  city_population('Albany')   := 400000;
  state_population('CA')      := 3500000;

  -- returns value for index position NY
  NY_population    := state_population('NY');

  -- returns name of first index position (Albany)
  index_position_id := city_population.FIRST;
  DBMS_OUTPUT.PUT_LINE( 'The name of first index position is '
                       || index_position_id);
  DBMS_OUTPUT.PUT_LINE ('The value of state(''NY'') is '
                       || NY_population);
END;
/

Code Explanation

Code Sample:

Declare-Clause/Demos/associative_array_index_by_number.sql
-- Creating a datatype of associative array with the
-- index by clause of number.
DECLARE
    TYPE salaries
    IS
      TABLE OF employees.salary%TYPE
      index BY binary_integer;

    my_salaries salaries;
BEGIN

 -- We'd really use a loop, but we cover
 -- loops in the next chapter. In the meantime...

 select salary
 into my_salaries(1)
 from employees
 where rownum <= 1;
 dbms_output.put_line('1st "random" salary is ' || my_salaries(1));

 select salary
 into my_salaries(2)
 from employees
 where rownum <= 1
 and my_salaries(1) <> salary;
 dbms_output.put_line('2nd "random" salary is ' || my_salaries(2));
END;
/

Code Explanation

Code Sample:

Declare-Clause/Demos/associative_array_without_index_by.sql
-- Creating a datatype of associative array
-- WITHOUT the index by clause.
DECLARE
    TYPE nbrs
    IS
      TABLE OF NUMBER;

    my_numbers nbrs;

BEGIN
  my_numbers := nbrs(99,98,97,96,95);

  dbms_output.put_line('my_numbers(1) is ' || my_numbers(1));
  DBMS_OUTPUT.PUT_LINE('my_numbers(3) is ' || my_numbers(3));

  my_numbers(5) := 6;
  DBMS_OUTPUT.PUT_LINE('my_numbers(5) is now ' || my_numbers(5));

  -- We can extend the array past its size of 5:
  my_numbers.extend;
  my_numbers(6) := 42;
  DBMS_OUTPUT.PUT_LINE('my_numbers(6) is ' || my_numbers(6));

END;
/

Code Explanation

Code Sample:

Declare-Clause/Demos/varray.sql
DECLARE
  TYPE state_abbrev IS varray(10) OF CHAR(2);

  states state_abbrev;

BEGIN
    states := state_abbrev('NY','CA','MD','ME','FL');

    dbms_output.put_line( 'My state is: ' || states(1));

    -- Just as with an associative array created WITHOUT an index,
    -- we're "stuck" with just 5 members in the varray even though
    -- it has a size of 10. But we can extend here as well.
    states.extend;
    states(6) := 'GA';
    dbms_output.put_line( 'Your state is: ' || states(6));
    states.extend;
    states(7) := 'IL';
END;
/

More Information

This chapter includes many of the most frequently used aspects of PL/SQL but is not intended to be comprehensive or exhaustive. The focus of the demonstrations is on numbers, character strings and dates as the fundamental (and most commonly encountered) data types. For more information see the Oracle documentation (i.e., http://docs.oracle.com).

Variable Declaration, Initialization and Display

Duration: 10 to 15 minutes.
  1. Create a program that prints out a person's name. Declare, initialize and display a character string that contains the name.
  2. Modify the program so that it prints out the person's salary as well. Declare, initialize and display a numeric variable that contains the salary.
  3. Modify the program so that it prints out the current date in addition to the information above. Declare, initialize and display a date variable that contains the current date.
  4. Option: format the information so that it reads: As of <date in MM/DD/YYYY> <NAME> will be paid <$x,xxx.xx>.

Solution:

Declare-Clause/Solutions/name_salary_date.sql
DECLARE
  v_name   VARCHAR2(25) := 'Larry Ellison';
  v_salary NUMBER := 1.00; 
  v_date   DATE   := CURRENT_DATE;
BEGIN
  dbms_output.put_line(v_date || ' ' || v_name || ' ' || v_salary);  
  dbms_output.put_line('As of ' ||
                       to_char(v_date, 'mm/dd/yyyy') || ' ' ||
                       v_name || ' will be paid ' ||
                       to_char(v_salary, '$9,999.00') || '.');
END;

Code Explanation