Oracle: PLSQL_Syntaxes_DataType1.htm
PL/SQL  Data-Types and Syntaxes
  • PL/SQL (Oracle ) and
    • Procedural extension of Oracle-SQL
  • PL/PGSQL (PostgreSQL)
    • Extended SQL: Base-Types , Composite-Types, Domains, Pseudo-Types, and Polymorphic-types

 
Oracle : PL/SQL Block
Schema level stored function / PL/SQL Syntax
Data Type
http://www.tutorialspoint.com/plsql/plsql_data_types.htm

PL/SQL variables, constants and parameters must have a valid data type, which specifies a storage format, constraints, and valid range of values. This tutorial will take you through SCALAR and LOB data types available in PL/SQL and other two data types will be covered in other chapters.

Category Description
Scalar Single values with no internal components, such as a NUMBER, DATE, or BOOLEAN. Oracle does not have boolean type, but cab be simulated by using either char(1) or number(1).
Large Object (CLOB,BLOB) Pointers to large objects , such as text, graphic images, video clips, and sound waveforms,which are stored separately from other data items in a file. Oracle provides transactional support to Internal LOB. Binary Blob (BLOB) holds object like video clips, Character LOB (CLOB) large blocks of single-byte fixed with character data.
 
Composite: collection and records Data items that have internal components that can be accessed individually. For example, collections and records. In Collection, the data of same type, called elements , then create a variable with %type clause.
Reference Pointers to other data items.

 

PL/SQL Scalar Data Types and Subtypes

PL/SQL Scalar Data Types and Subtypes come under the following categories:

Date Type Description
Numeric Numeric values on which arithmetic operations are performed.
Character Alphanumeric values that represent single characters or strings of characters.
Boolean Logical values on which logical operations are performed.
Datetime Dates and times.

PL/SQL provides subtypes of data types. For example, the data type NUMBER has a subtype called INTEGER. You can use subtypes in your PL/SQL program to make the data types compatible with data types in other programs while embedding PL/SQL code in another program, such as a Java program.

PL/SQL Numeric Data Types and Subtypes

Following is the detail of PL/SQL pre-defined numeric data types and their sub-types:

Data Type Description
PLS_INTEGER Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
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(prec, scale) 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.
DEC(prec, scale) ANSI specific fixed-point type with maximum precision of 38 decimal digits.
DECIMAL(prec, scale) IBM specific fixed-point type with maximum precision of 38 decimal digits.
NUMERIC(pre, secale) Floating type with maximum precision of 38 decimal digits.
DOUBLE PRECISION ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
FLOAT ANSI and IBM specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
INT ANSI specific integer type with maximum precision of 38 decimal digits
INTEGER ANSI and IBM specific integer type with maximum precision of 38 decimal digits
SMALLINT ANSI and IBM specific integer type with maximum precision of 38 decimal digits
REAL Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)

Following is a valid declaration: