PL/SQL Data Types


Every constant, variable, and parameter has a data type (also called a type) that determines its storage format, constraints, valid range of values, and operations that can be performed on it.

PL/SQL provides many predefined data types and subtypes, and lets you define your own PL/SQL subtypes.

Predefined PL/SQL datatypes:

  • Scalar: Single values with no internal components.
  • Composite: Data items that have internal components that can be accessed individually.
  • Reference: Pointers to other data items.
  • Large Object (LOB): Pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms.

Predefined PL/SQL Scalar Data Types

  • Numeric: Numeric values, on which you can perform arithmetic operations.
  • Character: Alphanumeric values that represent single characters or strings of characters, which you can manipulate.
  • BOOLEAN: Logical values, on which you can perform logical operations.
  • Datetime: Dates and times, which you can manipulate.
  • Interval: Time intervals, which you can manipulate.

Predefined PL/SQL Numeric Data Types

  • PLS_INTEGER or BINARY_INTEGER: Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits.
  • BINARY_FLOAT: Single-precision IEEE 754-format floating-point number.
  • BINARY_DOUBLE: Double-precision IEEE 754-format floating-point number.
  • NUMBER: Fixed-point or floating-point number with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable can also represent 0.

Advantages of PLS_INTEGER over the NUMBER data type:

  • PLS_INTEGER values require less storage.
  • PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic.

Predefined Subtypes of PLS_INTEGER Data Type:

  • NATURAL: Nonnegative PLS_INTEGER value.
  • NATURALN: Nonnegative PLS_INTEGER value with NOT NULL constraint.
  • POSITIVE: Positive PLS_INTEGER value.
  • POSITIVEN: Positive PLS_INTEGER value with NOT NULL constraint.
  • SIGNTYPE: PLS_INTEGER value -1, 0, or 1 (useful for programming tri-state logic).
  • SIMPLE_INTEGER: PLS_INTEGER value with NOT NULL constraint.

Note: SIMPLE_INTEGER is a predefined subtype of the PLS_INTEGER data type that has the same range as PLS_INTEGER (-2,147,483,648 through 2,147,483,647) and has a NOT NULL constraint. It differs significantly from PLS_INTEGER in its overflow semantics.

Predefined Subtypes of NUMBER Data Type:

  • DEC, DECIMAL, or NUMERIC: Fixed-point NUMBER with maximum precision of 38 decimal digits.
  • DOUBLE PRECISION or FLOAT: Floating-point NUMBER with maximum precision of 126 binary digits (approximately 38 decimal digits).
  • INT, INTEGER, or SMALLINT: Integer with maximum precision of 38 decimal digits.
  • REAL: Floating-point NUMBER with maximum precision of 63 binary digits (approximately 18 decimal digits).

Predefined PL/SQL Character Data Types:

  • CHAR: Fixed-length character string with maximum size of 32,767 bytes.
  • VARCHAR2: Variable-length character string with maximum size of 32,767 bytes.
  • RAW: Variable-length binary or byte string with maximum size of 32,767 bytes, not interpreted by PL/SQL.
  • NCHAR: Fixed-length national character string, such as sequences of graphics characters or digitized pictures, with maximum size of 32,767 bytes.
  • NVARCHAR2: Variable-length national character string with maximum size of 32,767 bytes.
  • LONG: Variable-length character string with maximum size of 32,760 bytes.
  • LONG RAW: Variable-length binary or byte string with maximum size of 32,760 bytes, not interpreted by PL/SQL.
  • ROWID: Physical row identifier, the address of a row in an ordinary table.
  • UROWID: Universal row identifier (physical, logical, or foreign row identifier).

Difference between ROWID and UROWID Data Types:

Internally, every database table has a ROWID pseudocolumn, which stores binary values called rowids. Each rowid represents the storage address of a row.

A physical rowid identifies a row in an ordinary table. A logical rowid identifies a row in an index-organized table.

The ROWID data type can store only physical rowids, while the UROWID (universal rowid) data type can store physical, logical, or foreign (not database) rowids.

Predefined PL/SQL Large Object (LOB) Data Types:

  • BFILE: Used to store large binary objects in operating system files outside the database. System-dependent. Cannot exceed 4 gigabytes (GB).
  • BLOB: Used to store large binary objects in the database.8 to 128 terabytes (TB).
  • CLOB: Used to store large blocks of character data in the database.8 to 128 TB.
  • NCLOB: Used to store large blocks of NCHAR data in the database.8 to 128 TB.

User-Defined PL/SQL Subtypes:

A subtype is a subset of another data type, which is called its base type. A subtype has the same valid operations as its base type, but only a subset of its valid values.

Subtypes can increase reliability, provide compatibility with ANSI/ISO types, and improve readability by indicating the intended use of constants and variables.


How to defining Subtypes?

You can define your own subtypes in the declarative part of any PL/SQL block, subprogram, or package using the following syntax:

SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL];

Where,
  • subtype_name is a type specifier used in subsequent declarations,
  • base_type is any scalar or user-defined PL/SQL data type, and
  • constraint applies only to base types that can specify precision and scale or a maximum size.
  • A default value is not permitted

Example of defining Subtypes:

DECLARE
 SUBTYPE BirthDate IS DATE NOT NULL;         -- Based on DATE type
 SUBTYPE COUNTER IS NATURAL;                 -- Based on NATURAL subtype

 rows Counter;                               -- Usage of Subtype 'Counter'
  
 TYPE NameList IS TABLE OF VARCHAR2(10);     --
 SUBTYPE DutyRoster IS NameList;             -- Based on TABLE type
 
 TYPE TIMEREC IS RECORD (MINUTES INTEGER, HOURS INTEGER);
 SUBTYPE FinishTime IS TimeRec;                -- Based on RECORD type
 
 SUBTYPE ID_Num IS employees.employee_id%TYPE; -- Based on column type
 
BEGIN
 NULL;
END;
 /

Using Ranges with Subtypes:

SUBTYPE pinteger IS PLS_INTEGER RANGE -9..9;
y_axis pinteger;

Constraints and Default Values with Subtypes:

SET SERVEROUTPUT ON;

DECLARE
SUBTYPE NEW_VAR IS VARCHAR2(10) NOT NULL;
  fname  NEW_VAR  := 'David';
  lname  new_var  := 'Jhon';
BEGIN
   DBMS_OUTPUT.PUT_LINE (UPPER(fname));
   DBMS_OUTPUT.PUT_LINE (UPPER(lname));
END;
/

PL/SQL Data Type Conversion:

Sometimes it is necessary to convert a value from one data type to another. For example, to use a DATE value in a report, you must convert it to a character string.

For best reliability and maintainability, use explicit conversion. Implicit conversion is context-sensitive and not always predictable, and its rules might change in later software releases. Implicit conversion can also be slower than explicit conversion.

PL/SQL supports two types of data type conversion:

  • Explicit Conversion: To explicitly convert values from one data type to another, you use built-in functions. For example, to convert a CHAR value to a DATE or NUMBER value, you use the function TO_DATE or TO_NUMBER, respectively. Conversely, to convert a DATE or NUMBER value to a CHAR value, you use the function TO_CHAR.
  • Implicit Conversion: Sometimes PL/SQL can convert a value from one data type to another automatically. This is called implicit conversion, and the data types are called compatible. For example, PL/SQL converts the CHAR values to NUMBER values automatically.

Next Article: PL-SQL Control Statement


For any questions, suggestions or feedback, Please write to us. Thanks for Reading :)

No comments:

Post a Comment

Subscribe to: Posts (Atom)