PL and SQL data types and operators

Scalar (scalar) data type

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

datatype rang subtype description CHAR The maximum length is 32,767 bytes CHARACTER Stores a fixed-length string, and if the length is not determined, the default is 1 LONG The maximum length is 2147483647 bytes Stores variable-length strings RAW The maximum length is 32,767 bytes Used to store binary data and byte strings, RAW data is not converted between character sets when passed between two databases. LONGRAW The maximum length is 2147483647 Similar to the LONG data type, it is also unable to convert from one character set to another. ROWID 18 bytes The same as the database ROWID pseudo-column type, it is able to store one row identifier, which can be treated as the only 1 key value per row in the database. VARCHAR2 The maximum length is 32,767 bytes STRINGVARCHAR Similar to the VARCHAR data type, it stores strings of variable length. The declaration method is the same as VARCHAR

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

datatype range description BOOLEAN TRUE/FALSE Stores the logical values TRUE or FALSE without arguments DATE 01/01/4712 BC Stores a fixed long date and time value that contains the time
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