Pl/sql data type and operator _oracle

Source: Internet
Author: User
Tags arithmetic arithmetic operators numeric logical operators scalar sql using
Scalar (scalar) data type

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

. Number
. Character
. Date/time
. Boolean

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

   Table 1 Scalar types:numeric
Datatype Range Subtypes Description
Binary_integer -214748-2147483647 NATURAL
Used to store a single byte integer.
Required storage length lower than number value.
Subtype used to limit scope (subtype):
NATURAL: for non-negative numbers
POSITIVE: only for positive numbers
NATURALN: For non-negative and non-null values only
Positiven: For positive numbers only, not for null values
Signtype: Only values:-1, 0, or 1.
Number 1.0e-130-9.99e125 DEC

Stores numeric values, including integers and floating-point numbers. You can choose the precision and scale method, syntax:
number[( [, ])].
The default precision is 38,scale is 0.

Pls_integer -2147483647-2147483647 Basically the same as Binary_integer, but Pls_integer provides better performance when machine operations are used.

Number type can hold integer and real values, and can define precision and range of values
Binary_integer numeric type can store signed integers, optimizing performance for integer computations
The sub-type of DEC numeric number, decimal
DOUBLE PRECISION subtype of numeric number, high precision real
The subtype of the integer number type, integer
The subtype of the INT numeric number, integer
The subtype of the NUMERIC number, which is equivalent to
The subtype of real numeric number, which is equivalent to
SMALLINT numeric number type, with a value range smaller than integer
VARCHAR2 character type holds variable long string with maximum length

table 2 character data types

datatype rang subtype description
CHAR Maximum length 32767 bytes CHARACTER stores a fixed-length string, and if the length is not determined, the default is 1
LONG Maximum length 2147483647 bytes   Store variable-length string
RAW Maximum length 32767 bytes   is used to store binary data and byte strings, and when passed between two databases, RAW data is not converted between character sets. The
longraw Maximum length 2147483647   is similar to a Long data type and cannot be converted between character sets.
ROWID 18 bytes   is the same as the database ROWID pseudo column type, can store a row identifier, you can view the row designator as a database Unique key value for each row.
VARCHAR2 Maximum length 32767 bytes stringvarchar similar to varchar data type, storing variable-length characters String. Declaring methods the same as varchar

CHAR character fixed-length string
Long character variable length string with a maximum length of up to 32,767
Date Date type holds dates in the same format as the database
Boolean Boolean TRUE OR FALSE
ROWID ROWID The line number that holds the database

  Table 3 Date and Boolean

DataType Range Description
BOOLEAN True/false Store logical value TRUE or FALSE, no parameters
DATE 01/01/4712 BC Store fixed-length date and time values, date values contain time

LOB data type

LOB (Large object, Large object) data type is used to store large data objects such as images, sounds, and LOB data objects can be binary or character data with a maximum length of no more than 4G. LOB data types support arbitrary access, and long only supports sequential access. The LOB is stored in a separate location, while a lob locator (LOB locator) is stored in the original table, which is a pointer to the actual data. Manipulating LOB Data Objects in Pl/sql using the package Dbms_lob provided by Oracle. LOB data types can be grouped into the following four categories:

. Blob
. Clob


As with other programming languages, Pl/sql has a series of operators. The operators are grouped into the following categories:

. Arithmetic operator

. Relational operators

. comparison operator

. logical operators

The arithmetic operators are shown in table 4

operator Operation
+ Add
- Reducing
/ Except
* By
** Squares

Relational operators are used primarily for conditional judgment statements or for a where substring, the relational operator checks whether the condition and the result are true or false, and table 5 is the relational operator 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 operator
operator Operation
Is NULL Returns true if the operand is null
Like Comparing String values
BETWEEN Verify that the value is within range
In Verify that the operand is in a set number of values

Table 7.8 shows the logical operators
operator Operation
and All two conditions must be met
OR As long as one of the two conditions is met
Not Take the counter

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.