1.5.1 Scalar Datatypes

Scalar datatypes divide into four families: number, character, date-time, and Boolean.

1.5.2 NLS Character Datatypes

The standard ASCII character set does not support some languages, such as Chinese, Japanese, or Korean. To support these multibyte character sets, PL/SQL8 supports two character sets, the database character set and the national character set (NLS). There are two datatypes, NCHAR and NVARCHAR2, that can be used to store data in the national character set.

NCHAR values are fixed-length NLS character data; the maximum length is 32767 bytes. For variable-length character sets (like JA16SJIS), the length specification is in bytes; for fixed-length character sets, it is in characters.

NVARCHAR2 values are variable-length NLS character data. The maximum length is 32767 bytes, and the length specification follows the same fixed/variable-length rule as NCHAR values.

1.5.3 LOB Datatypes

PL/SQL8 supports a number of Large OBject (LOB) datatypes, which can store objects of up to four gigabytes of data. Unlike the scalar datatypes, variables declared for LOBs use locators, or pointers to the actual data. LOBs are manipulated in PL/SQL using the built-in package DBMS_LOB.

BFILE

File locators pointing to read-only large binary objects in operating system files. With BFILEs, the large objects are outside the database.

BLOB

LOB locators that point to large binary objects inside the database.

CLOB

LOB locators that point to large "character" (alphanumeric) objects inside the database.

NCLOB

LOB locators that point to large national character set objects inside the database.

1.5.4 NULLs in PL/SQL

PL/SQL represents unknown values as NULL values. Since a NULL is unknown, a NULL is never equal or not equal to anything (including another NULL value). Additionally, most functions return a NULL when passed a NULL argument -- the notable exceptions are NVL, CONCAT, and REPLACE. You cannot check for equality or inequality to NULL; therefore, you must use the IS NULL or IS NOT NULL syntax to check for NULL values.

Here is an example of the IS NULL syntax to check the value of a variable:

BEGIN
   IF myvar IS NULL
   THEN
      ...

1.5.5 Declaring Variables

Before you can use a variable, you must first declare it in the declaration section of your PL/SQL block or in a package as a global. When you declare a variable, PL/SQL allocates memory for the variable's value and names the storage location so that the value can be retrieved and changed. The syntax for a variable declaration is:

variable_name datatype [CONSTANT] [NOT NULL] 
   [:= | DEFAULT initial_value]

1.5.6 Anchored Declarations

Use the %TYPE attribute to anchor the datatype of a scalar variable to either another variable or to a column in a database table or view. Use %ROWTYPE to anchor a record's declaration to a cursor or table (see the Section 1.11, "Records in PL/SQL " section for more detail on the %ROWTYPE attribute).

The following block shows several variations of anchored declarations:

DECLARE
tot_sales NUMBER(20,2);
   -- Anchor to a PL/SQL variable.
   monthly_sales tot_sales%TYPE;

   -- Anchor to a database column.
   v_ename employee.last_name%TYPE;

   CURSOR mycur IS
      SELECT * FROM employee;

   -- Anchor to a cursor.
   myrec mycur%ROWTYPE;

The NOT NULL clause on a variable declaration (but not on a database column definition) follows the %TYPE anchoring and requires anchored declarations to have a default in their declaration. The default value for an anchored declaration can be different from that for the base declaration:

tot_sales      NUMBER(20,2) NOT NULL DEFAULT 0;
monthly_sales  tot_sales%TYPE DEFAULT 10;

1.5.7 Programmer-Defined Subtypes

PL/SQL allows you to define unconstrained scalar subtypes. An unconstrained subtype provides an alias to the original underlying datatype, for example:

CREATE OR REPLACE PACKAGE std_types
IS
   -- Declare standard types as globals.
   TYPE dollar_amt_t IS NUMBER;
END std_types;

CREATE OR REPLACE PROCEDURE process_money
IS
   -- Use the global type declared above.
   credit std_types.dollar_amt_t;
   ...

A constrained subtype limits or constrains the new datatype to a subset of the original datatype. For example, POSITIVE is a constrained subtype of BINARY_INTEGER. The declaration for POSITIVE in the STANDARD package is:

SUBTYPE POSITIVE IS BINARY_INTEGER RANGE 1..2147483647;

You cannot define constrained subtypes in your own programs; this capability is reserved for Oracle itself. You can, however, achieve the same effect as a constrained subtype by using %TYPE. Here is a rewriting of the previous subtype that enforces a constraint on the size of dollar amount variables:

PACKAGE std_types
IS
   v_dollar NUMBER (10, 2);

   TYPE dollar_amt_t IS v_dollar%TYPE;
END;