# PL and SQL data types and operators

- 2020-05-27 07:25:50
- OfStack

Scalar (scalar) data types have no internal components, and they can be roughly divided into the following four categories:

. number

. character

date/time

. boolean

Table 1 shows the numeric data types; Table 2 shows the character data types; Table 3 shows the date and the Boolean data type.

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 is required to be less than the NUMBER value.

Subtypes used to limit scope (SUBTYPE):

NATURAL: for non-negative Numbers

POSITIVE: only for positive Numbers

NATURALN: only for non-negative and non-NULL values

POSITIVEN: 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. Precision and calibration mode can be selected. Syntax:

number [(

[and]].

The default precision is 38 and scale is 0.

PLS_INTEGER -2147483647-2147483647 It is essentially the same as BINARY_INTEGER, but PLS_INTEGER provides better performance with machine calculations.

The NUMBER digital model can hold both integer and real values, and can define the precision and value range

The BINARY_INTEGER numeric type stores signed integers and optimizes performance for integer calculations

Subtype of DEC numeric NUMBER, decimal

DOUBLE PRECISION number type NUMBER subtype, high precision real number

A subtype of INTEGER numeric NUMBER, an integer

INT numeric NUMBER subtype, integer

A subtype of NUMERIC numeric NUMBER, equivalent to NUMBER

A subtype of REAL numeric NUMBER, equivalent to NUMBER

A subtype of SMALLINT numeric NUMBER with a smaller range of values than INTEGER

The VARCHAR2 character type holds variable-length strings with a maximum length

Table 2 character data types

CHAR character type fixed-length string

LONG variable-length character string with a maximum length of 32,767

The DATE date type stores date values in the same format as the database

BOOLEAN Boolean TRUE OR FALSE

ROWID ROWID stores the row number of the database

Table 3 DATE and BOOLEAN

LOB data type

The LOB(large object,Large object) data type is used to store large data objects such as images and sounds. The LOB data object can be binary data or character data with a maximum length of no more than 4G. The LOB data type supports arbitrary access, while LONG only supports sequential access. LOB is stored in a single location, while a "LOB locator "(LOB locator), which is a pointer to the actual data, is stored in the original table. The LOB data objects are manipulated in PL/SQL using the package DBMS_LOB.LOB data types can be divided into the following four categories:

. BFILE

. BLOB

. CLOB

. NCLOB

The operator

Like other programming languages, PL/SQL has a series of 1 operators. Operators fall into the following categories:

. Arithmetic operator

. Relationship operator

Comparison operator

.logical operator

The arithmetic operators are shown in table 4

operator operation + add - Reduction of / In addition to * take ** chengfang

The relationship operator is mainly used for conditional judgment statements or for where substrings. The relationship operator checks whether the conditions and results are true or false. Table 5 shows the relationship operators in PL/SQL

operator operation < Less than operator < = Less than or equal to operator > Greater than operator > = Greater than or equal to operator = Equals operator != Not equal to operator < > Not equal to operator := Assignment operator

Table 6 shows the comparison operators

operator operation IS NULL Returns TRUE if the operand is NULL LIKE Compare string values BETWEEN Verify that the value is within the range IN Verify that the operand is in the set 1 series of values

Table 7.8 shows the logical operators

operator operation AND Both conditions must be met OR You just have to satisfy one of the two conditions NOT The not