PL/SQL data types and operators

Scalar Data Type

Scalar data types have no internal components. They can be divided into the following four categories:

. Number
. Character
. Date/time
. Boolean

Table 1 shows the numeric data type, table 2 shows the character data type, and table 3 shows the date and boolean data types.

　　Table 1 Scalar Types: Numeric

Datatype	Range	Subtypes	Description
BINARY_INTEGER	-214748-2147483647	NATURAL NATURAL NPOSITIVE POSITIVEN SIGNTYPE	Used to store single-byte integers. The storage length must be less than the NUMBER value. SUBTYPE used to limit the range ): NATURAL: used for non-negative numbers POSITIVE: only used for POSITIVE numbers Natura1n: used only for non-negative and non-NULL values POSITIVEN: used only for positive numbers, not for NULL values SIGNTYPE: only values:-1, 0, or 1.
NUMBER	1.0E-130-9.99E125	DEC DECIMAL DOUBLE PRECISION FLOAT INTEGERIC INT NUMERIC REAL SMALLINT	Stores numeric values, including integers and floating-point numbers. You can select the precision and scale methods. Syntax: Number [( [, ])]. The default precision is 38, And the scale is 0.
PLS_INTEGER	-2147483647-2147483647		It is basically the same as BINARY_INTEGER, but when using machine operations, PLS_INTEGER provides better performance.

NUMBER numeric type can store integer and real value, and can define precision and value range
The BINARY_INTEGER numeric type can store signed integers to optimize integer computing performance.
Decimal NUMBER subtype
Double precision numeric NUMBER subtype, high PRECISION real NUMBER
The subtype of an integer number.
The subtype of the INT numeric NUMBER. It is an integer.
The subtype of the numeric number, which is equivalent to the NUMBER.
Subtype of real number, which is equivalent to NUMBER
The subtype of the SMALLINT numeric NUMBER. The value range is smaller than INTEGER.
VARCHAR2 character type stores variable-length strings with maximum length

Table 2-character data type

Datatype	Rang	Subtype	Description
CHAR	Maximum length: 32767 bytes	CHARACTER	Storage of fixed-length strings. If the length is not determined, the default value is 1.
LONG	Maximum length: 2147483647 bytes		Store variable-length strings
RAW	Maximum length: 32767 bytes		It is used to store binary data and byte strings. When transmitted between two databases, RAW data is not converted between character sets.
LONGRAW	Maximum length 2147483647		Similar to the LONG data type, it cannot be converted between character sets.
ROWID	18 bytes		The row identifier is the same as the row ID pseudo-column type in the database. A Row identifier can be stored as a unique key value for each row in the database.
VARCHAR2	Maximum length: 32767 bytes	STRINGVARCHAR	Similar to the VARCHAR data type, it stores variable-length strings. The declaration method is the same as that of VARCHAR.

CHAR character string with Fixed Length
LONG string with a length up to 32,767 characters
DATE type stores DATE values in the same database format
BOOLEAN TRUE OR FALSE
ROWID: row number for storing the database

　　Table 3 DATE and BOOLEAN

Datatype	Range	Description
BOOLEAN	TRUE/FALSE	The storage logic value is TRUE or FALSE, with no parameter.
DATE	01/01/4712 BC	Store fixed date and time values. The date value contains the time.

LOB Data Type

LOB (Large object, Large object) data types are used to store Large data objects such as images and sounds. LOB data objects can be binary data or character data, the maximum length cannot exceed 4 GB. The LOB data type supports any access method, and LONG only supports sequential access. LOB is stored in a separate location, and a "LOB locator" (LOB locator) is stored in the original table. This locator is a pointer to the actual data. To operate LOB Data Objects in PL/SQL, use the DBMS_LOB.LOB package provided by ORACLE. The data types can be divided into the following four types:

. BFILE
. BLOB
. CLOB
. NCLOB

Operator

Like other programming languages, PL/SQL has a series of operators. Operators are divided into the following types:

. Arithmetic Operator

. Relational operators

. Comparison operator

. Logical operators

Arithmetic Operators are shown in table 4.

Operator	Operation
+	Add
-	Subtraction
/	Division
*	Multiplication
**	Multiplication party

Relational operators are mainly used for condition judgment statements or for where substrings. Relational operators check whether the conditions and results are true or false. Table 5 is the Relational operators in PL/SQL.

Operator	Operation
<	Less than Operator
<=	Operator less than or equal
>	Greater than Operator
> =	Operator greater than or equal
=	Equal to operator
! =	Not equal to operator
<>	Not equal to operator
: =	Value assignment operator

Table 6 displays comparison operators.

Operator	Operation
IS NULL	Returns TRUE if the operand is NULL.
LIKE	Compare string values
BETWEEN	Whether the verification value is within the range
IN	Verify that the operands are in the set series of values.

Table 7.8 displays logical operators.

Operator	Operation
AND	Both conditions must be met
OR	If either of the two conditions is met
NOT	Invert