PL/SQL - SPLessons

PL/SQL Data Types

Chapter 6

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Data Types

PL/SQL Data Types

shape Description

Every parameter, variable and constant has a data type that describes dimensional values, constraints, storage format and auxiliary operations that can be performed on it. Subtypes and predefined data types will be provided by PL/SQL, and users can define any number of sub types in PL/SQL.

A subtype contains the operation similar to its base type, but only a subset of its value. A subtype is the subset of another datatype, which is called as base type. Sub types can provide compatibility with ISO/ANSI types, and upgrade the reliability by announcing the intended use of variables and constants. PL/SQL constants, parameters and variables should contain valid data types to determine a particular range of values, constraints and storage format. The different types of PLSQL Data Types are scalar, large object(LOB), composite and reference.

Scalar

Scaler functions contain exceptional values that do not contain any interior elements such as date/boolean  and number.

Large object (LOB)

Large objects focus on expansive objects, and these extensive items are put independently from other informational items like realistic images, sound wave, text and video clips.

Composite

The interior parts of informational items can be achieved independently such as records and collections.

Reference

Gives reference to different informational items. 

PL/SQL scalar datatypes

shape Description

PL/SQL scaler datatypes do not contain any interior components and the data type sorts goes under the accompanying classes such as:

Numeric datatypes

shape Description

In PLSQL Data Types numeric datatypes, arithmetic operations are performed on numeric values. PL/SQL provides some pre-defined sub types and numeric data types.

Data types Description
BINARY_INTEGER Written integer ranges between -2,147,483,643 to 2,147,483,647 and is represented in 32 bits.
BINARY_FLOAT Written precision IEEE 754-schema floating mark figure
PLS_INTEGER Written integer ranges between -2,147,483,648 to 2,147,483,647 and is represented in 32 bits.
BINARY_DOUBLE Double precision IEEE 754 schema floating mark figure
Number(prec,scale) Floating mark number or fixed points with the value between 1E-130 to 1.OE126
DECIMAL(prec,scale) IBM specific fixed point type with superlative attention of 38 decimal figure.
DEC(prec,scale) ANSI specific fixed point with superlative attention of 38 decimal figure.
DOUBLE PRECISION ANSI specific floating mark sort with superlative attention of 126 binary figure
NUMERIC(pre,secale) Floating sort with superlative attention of 38 decimal figure.
REAL Floating mark sort with superlative attention of 62 binary figure
INT IBM and ANSI specific integer sort with superlative attention of 38 decimal figure
INTEGER IBM and ANSI specific integer sort with superlative attention of 39 decimal figure
SMALL INT IBM and ANSI specific integer sort with superlative attention of 38 decimal figure

Character datatypes

shape Description

Character PLSQL Data Types are specific alphanumeric values that determine either a string or single character. These characters are predefined PL/SQL character datatypes along with their subtypes.

Data types Description
RAW Variable length byte or binary string contains maximum range of 32,767 bytes
CHAR Fixed length character string with maximum range of 32,767 bytes
NCHAR Fixed length national character string with maximum range of 32,767 bytes
VARCHAR2 Variable length character string with maximum range of 32,767 bytes
NVARCHAR2 Variable length national character string with maximum range of 32,767 bytes
LONG Variable length character string with maximum range of 32,760 bytes
LONG RAW Variable length binary string with maximum range of 32,760 bytes
UROWID Universal row identifier
ROWID Physical row identifier that contains ordinary table

Boolean datatypes

shape Description

Boolean PLSQL Data Types are logical values on which logical operations are performed. These logical values describe boolean null values and TRUE & FALSE values. And, in SQL there is no other data type similar to boolean and therefore these values should not be performed in:

  • PL/SQL actions appealed from SQL statements
  • Built-in SQL functions
  • SQL statements

Date and time data types

shape Description

The data type DATE will store the fixed length date time values that includes day time in seconds from late night. A particular date ranges between 1st Jan 4721 BC to 31st Dec, 9999 AD.

Oracle initialization parameter will set the default data design as NLS_DATE_FORMAT. For instance, the default format is ‘DD-MM-YY’, which includes two digit number from the day of the month. Each DATE includes the month, year, day, hour, century and minute. The legitimate qualities for every field are:

Field Name Valid Date-time values
MONTH Jan to Dec
DAY Starting date to end date
YEAR -4712 to 9999
SECOND 00 TO 59.9(n),where 9(n) is the rigor of time apportion seconds
MINUTE 00 TO 59
HOUR 00 TO 23
TIMEZONE_REGION Available from the dynamic performance view V$ TIMEZONE_NAMES
TIMEZONE_MINUTE 00 to 59
TIMEZONE_HOUR -12 to 14

Large object (LOB) Datatypes

shape Description

Large object(LOB) data types portray extensive data information such as video clips, images, sound waves and text. LOB data type sorts and provides more productive method to get the data. The  predefined  datatypes of PL/SQL LOB are.

Data Type Description Size
BLOB Huge binary objects will be stored 8 to 128 TB
CLOB Used for storing major portion of data character 8 to 128 terabytes
NCLOB Used for storing major portion of NHAR data 8 to 128 terra bytes
BFILE Used for storing binary objects in external database System dependent

PL/SQL User defined datatypes

shape Description

A subtype is described as a subset of different data type and is said to be the base type. A subtype contains similar actions as of base type.

PL/SQL features numerous subtypes in standard packages. For instance, PL/SQL features the subtypes that are INTEGER and CHARACTER.

shape Syntax

SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);

shape Examples

SQL> DECLARE
  2  SUBTYPE name IS char(20);
  3  SUBTYPE message IS varchar2(100);
  4  salutation name;
  5  greetings message;
  6  BEGIN
  7  salutation:='Reader';
  8  greetings:='Welcome to the world of PL/SQL';
  9  dbms_output.put_line('Hello'||saluation||greetings);
 10  END;
 11  /Hello Reader Welcome to the world of pl/sql
pl/sql procedure successfully completed.

Summary

shape Key Points

  • PLSQL Data Types – Data type is a programming language that contains a set of predefined characteristics.
  • Scalar datatypes – Scalar data types doesn’t contain any interior components.
  • Large object datatype – Large object data type describes extensive data information.
  • User defined datatypes – A subtype contains the subset of different data types and is said to be a base type.