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.
Scaler functions contain exceptional values that do not contain any interior elements such as date/boolean and number.
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.
The interior parts of informational items can be achieved independently such as records and collections.
Gives reference to different informational items.
Description
PL/SQL scaler datatypes do not contain any interior components and the data type sorts goes under the accompanying classes such as:
- Numeric
- Character
- Boolean
- Date time
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 |
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 |
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
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 |
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 |
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.
Syntax
SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);
Examples
[c]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.[/c]
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.