Day 3

Writing Declarations
and Blocks

by Jonathan Gennick

The block is the fundamental unit of PL/SQL programming. Blocks contain both program code and variable declarations. Understanding the various datatypes available to you when declaring variables is crucial when programming in any language, and PL/SQL is no exception. It's also important to understand PL/SQL's block structure, its use, and its impact on the scope of variable declarations. Today you are going to learn more about:

• PL/SQL datatypes
• PL/SQL blocks
• Scoping rules

Datatypes

PL/SQL provides a number of datatypes for your use, as shown in Table 3.1.

Table 3.1. PL/SQL datatypes.

These datatypes can be used for creating simple scalar variables, or they can be combined into structures such as records or PL/SQL tables. You will learn more about records and tables during Day 9, "Using SQL: INSERT, SELECT, Advanced Declarations, and Tables."

NEW TERM
A scalar variable is a variable that is not made up of some combination of other variables. Scalar variables don't have internal components that you can manipulate individually. They are often used to build up more complex datatypes such as records
and arrays.

You might notice that some of the datatype names match those used by Oracle for defining database columns. In most cases the definitions are the same for both the database and PL/SQL, but there are a few differences. These differences are noted when discussing each particular datatype.

NEW TERM
PL/SQL also provides subtypes of some datatypes. A subtype represents a special case of a datatype, usually representing a narrower range of values than the parent type. For example, POSITIVE is a subtype of BINARY_INTEGER that holds only positive values. In some cases the subtypes exist only to provide alternative names for compatibility with the SQL standard or other popular database brands on the market.

In the next few subsections, you'll learn about each of the PL/SQL datatypes. You'll learn the type of data that each one holds, what the range of possible values is, and any subtypes that are defined for it.

VARCHAR2

The VARCHAR2 datatype is used to hold variable-length character string data. It typically uses 1 byte per character and has a maximum length of 32767 bytes.

The Syntax for the VARCHAR2 Datatype

variable_name VARCHAR2(size);

In this syntax, variable_name is whatever name you want to give to the variable, and size is the maximum length, in bytes, of the string.

Here are some examples:

employee_name VARCHAR2(32);
employee_comments VARCHAR2(10000);

Referring to the example declaration of employee_name, here are some sample assignment statements showing values that could be assigned to this variable:

employee_name := `Jenny Gennick';
employee_name := `Jonathan Gennick';

VARCHAR2 Subtypes
Oracle has two subtypes defined for VARCHAR, which are

• VARCHAR
• STRING

These subtypes exist for compatibility with other database brands and also with the SQL standard. Both have the exact same meaning as VARCHAR2. However, Oracle currently recommends against using the VARCHAR datatype because its definition is expected to change as the SQL standards evolve.

CHAR

The CHAR datatype is used to hold fixed-length character string data. Unlike VARCHAR2 strings, a CHAR string always contains the maximum number of characters. Strings shorter than the maximum length are padded with spaces. Like VARCHAR2, the CHAR datatype typically uses 1 byte per character and has a maximum length of 32767 bytes.

The Syntax for the CHAR Datatype

variable_name CHAR(size);

In this syntax, variable_name is whatever you want to call the variable, and size is the size, in bytes, of the string.

Here are some examples:

employee_name CHAR(32);
employee_comments CHAR(10000);

Referring to the example declaration of employee_name, here are some sample assignment statements showing values that could be assigned to this variable:

employee_name := `Jenny Gennick';
employee_name := `Jeff Gennick';