In this section, we will talk about the Data Types in PL/SQL. The PL/SQL factors, constants and boundaries should have a legitimate information type, which indicates a capacity arrangement, requirements, and a substantial scope of qualities. We will zero in on the SCALAR and the LOB information types in this section. The other two information types will be canvassed in different parts.
S.No | Category & Description |
---|---|
1 |
Scalar Single values with no internal components, such as a NUMBER, DATE, or BOOLEAN. |
2 |
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. |
3 |
Composite Data items that have internal components that can be accessed individually. For example, collections and records. |
4 |
Reference Pointers to other data items. |
PL/SQL Scalar Data Types and Subtypes
PL/SQL Scalar Data Types and Subtypes go under the accompanying classes −
S.No | Date Type & Description |
---|---|
1 |
Numeric Numeric values on which arithmetic operations are performed. |
2 |
Character Alphanumeric values that represent single characters or strings of characters. |
3 |
Boolean Logical values on which logical operations are performed. |
4 |
Datetime Dates and times. |
PL/SQL gives subtypes of information types. For instance, the information type NUMBER has a subtype called INTEGER. You can utilize the subtypes in your PL/SQL program to make the information types viable with information types in different projects while installing the PL/SQL code in another program, for example, a Java program.
PL/SQL Numeric Data Types and Subtypes
Following table records out the PL/SQL pre-characterized numeric information types and their sub-types −
S.No | Data Type & Description |
---|---|
1 |
PLS_INTEGER Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits |
2 |
BINARY_INTEGER Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits |
3 |
BINARY_FLOAT Single-precision IEEE 754-format floating-point number |
4 |
BINARY_DOUBLE Double-precision IEEE 754-format floating-point number |
5 |
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 |
6 |
DEC(prec, scale) ANSI specific fixed-point type with maximum precision of 38 decimal digits |
7 |
DECIMAL(prec, scale) IBM specific fixed-point type with maximum precision of 38 decimal digits |
8 |
NUMERIC(pre, secale) Floating type with maximum precision of 38 decimal digits |
9 |
DOUBLE PRECISION ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits) |
10 |
FLOAT ANSI and IBM specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits) |
11 |
INT ANSI specific integer type with maximum precision of 38 decimal digits |
12 |
INTEGER ANSI and IBM specific integer type with maximum precision of 38 decimal digits |
13 |
SMALLINT ANSI and IBM specific integer type with maximum precision of 38 decimal digits |
14 |
REAL Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits) |
Following is a legitimate assertion −
DECLARE
num1 INTEGER;
num2 REAL;
num3 DOUBLE PRECISION;
BEGIN
null;
END;
/
At the point when the above code is arranged and executed, it delivers the accompanying outcome −
PL/SQL procedure successfully completed
PL/SQL Character Data Types and Subtypes
Following is the detail of PL/SQL pre-characterized character information types and their sub-types −
S.No | Data Type & Description |
---|---|
1 |
CHAR Fixed-length character string with maximum size of 32,767 bytes |
2 |
VARCHAR2 Variable-length character string with maximum size of 32,767 bytes |
3 |
RAW Variable-length binary or byte string with maximum size of 32,767 bytes, not interpreted by PL/SQL |
4 |
NCHAR Fixed-length national character string with maximum size of 32,767 bytes |
5 |
NVARCHAR2 Variable-length national character string with maximum size of 32,767 bytes |
6 |
LONG Variable-length character string with maximum size of 32,760 bytes |
7 |
LONG RAW Variable-length binary or byte string with maximum size of 32,760 bytes, not interpreted by PL/SQL |
8 |
ROWID Physical row identifier, the address of a row in an ordinary table |
9 |
UROWID Universal row identifier (physical, logical, or foreign row identifier) |
PL/SQL Boolean Data Types
The BOOLEAN information type stores coherent qualities that are utilized in intelligent tasks. The intelligent qualities are the Boolean qualities TRUE and FALSE and the worth NULL.
Be that as it may, SQL has no information type comparable to BOOLEAN. Along these lines, Boolean qualities can't be utilized in −
- SQL articulations
- Inherent SQL capacities, (for example, TO_CHAR)
- PL/SQL capacities summoned from SQL articulations
PL/SQL Datetime and Interval Types
The DATE datatype is utilized to store fixed-length datetimes, which remember the hour of day for seconds since 12 PM. Substantial dates range from January 1, 4712 BC to December 31, 9999 AD.
The default date design is set by the Oracle introduction boundary NLS_DATE_FORMAT. For instance, the default may be 'DD-MON-YY', which incorporates a two-digit number for the day of the month, a condensing of the month name, and the last two digits of the year. For instance, 01-OCT-12.
Each DATE incorporates the century, year, month, day, hour, moment, and second. The accompanying table shows the substantial qualities for each field −
Field Name | Valid Datetime Values | Valid Interval Values |
---|---|---|
YEAR | -4712 to 9999 (excluding year 0) | Any nonzero integer |
MONTH | 01 to 12 | 0 to 11 |
DAY | 01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale) | Any nonzero integer |
HOUR | 00 to 23 | 0 to 23 |
MINUTE | 00 to 59 | 0 to 59 |
SECOND | 00 to 59.9(n), where 9(n) is the precision of time fractional seconds | 0 to 59.9(n), where 9(n) is the precision of interval fractional seconds |
TIMEZONE_HOUR | -12 to 14 (range accommodates daylight savings time changes) | Not applicable |
TIMEZONE_MINUTE | 00 to 59 | Not applicable |
TIMEZONE_REGION | Found in the dynamic performance view V$TIMEZONE_NAMES | Not applicable |
TIMEZONE_ABBR | Found in the dynamic performance view V$TIMEZONE_NAMES | Not applicable |
PL/SQL Large Object (LOB) Data Types
Huge Object (LOB) information types allude to huge information things, for example, text, realistic pictures, video clasps, and sound waveforms. Heave information types permit productive, irregular, piecewise admittance to this information. Following are the predefined PL/SQL LOB information types −
Data Type | Description | Size |
---|---|---|
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 |
PL/SQL User-Defined Subtypes
A subtype is a subset of another information type, which is called its base sort. A subtype has similar legitimate activities as its base sort, however just a subset of its substantial qualities.
PL/SQL predefines a few subtypes in bundle STANDARD. For instance, PL/SQL predefines the subtypes CHARACTER and INTEGER as follows −
SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);
You can characterize and utilize your own subtypes. The accompanying system shows characterizing and utilizing a client characterized subtype −
DECLARE
SUBTYPE name IS char(20);
SUBTYPE message IS varchar2(100);
salutation name;
greetings message;
BEGIN
salutation := 'Reader ';
greetings := 'Welcome to the World of PL/SQL';
dbms_output.put_line('Hello ' || salutation || greetings);
END;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Hello Reader Welcome to the World of PL/SQL
PL/SQL procedure successfully completed.
NULLs in PL/SQL
PL/SQL NULL qualities address absent or obscure information and they are not a number, a character, or some other explicit information type. Note that NULL isn't equivalent to a vacant information string or the invalid character esteem '\0'. An invalid can be allocated however it can't be compared with anything, including itself.