PL/SQLの事前定義のデータ型

この付録は、パッケージSTANDARDに事前定義されたデータ型ファミリ(データ型とサブタイプ)でグループ分けされています。

定数

この定数は、名前の最大長を定義します。

ORA_MAX_NAME_LEN CONSTANT PLS_INTEGER := 128;

BFILEデータ型ファミリ

type BFILE is BFILE_BASE;

BLOBデータ型ファミリ

type BLOB is BLOB_BASE;

subtype "BINARY LARGE OBJECT" is BLOB;

BOOLEANデータ型ファミリ

type BOOLEAN is (FALSE, TRUE);

CHARデータ型ファミリ

type VARCHAR2 is new CHAR_BASE;
type MLSLABEL is new CHAR_BASE;
type UROWID   is new CHAR_BASE;

DBMS_ID and DBMS_QUOTED_ID define the length of identifiers in objects for SQL, PL/SQL and users.
subtype DBMS_ID              is VARCHAR2(ORA_MAX_NAME_LEN);
subtype DBMS_QUOTED_ID       is VARCHAR2(ORA_MAX_NAME_LEN+2);

DBMS_ID_30 and DBMS_QUOTED_ID_30 define the length of SQL objects whose limits is 30 bytes.
subtype DBMS_ID_30           is VARCHAR2(30);
subtype DBMS_QUOTED_ID_30    is VARCHAR2(32);

subtype VARCHAR              is VARCHAR2;
subtype STRING               is VARCHAR2;
subtype LONG                 is VARCHAR2(32760);
subtype RAW                  is VARCHAR2;
subtype "LONG RAW"           is RAW(32760);
subtype ROWID                is VARCHAR2(256);
subtype CHAR                 is VARCHAR2;
subtype CHARACTER            is CHAR;
subtype "CHARACTER VARYING"  is VARCHAR;
subtype "CHAR VARYING"       is VARCHAR;
subtype "NATIONAL CHARACTER" is CHAR CHARACTER SET NCHAR_CS;
subtype "NATIONAL CHAR"      is CHAR CHARACTER SET NCHAR_CS;
subtype "NCHAR"              is CHAR CHARACTER SET NCHAR_CS;
subtype "NVARCHAR2"          is VARCHAR2 CHARACTER SET NCHAR_CS;

CLOBデータ型ファミリ

type CLOB is CLOB_BASE;

subtype "CHARACTER LARGE OBJECT"          is CLOB;
subtype "CHAR LARGE OBJECT"               is CLOB;
subtype "NATIONAL CHARACTER LARGE OBJECT" is CLOB CHARACTER SET NCHAR_CS;
subtype "NCHAR LARGE OBJECT"              is CLOB CHARACTER SET NCHAR_CS;
subtype "NCLOB"                           is CLOB CHARACTER SET NCHAR_CS;

DATEデータ型ファミリ

type DATE                             is     DATE_BASE;

type TIMESTAMP                        is new DATE_BASE;

type "TIMESTAMP WITH TIME ZONE"       is new DATE_BASE;
type "INTERVAL YEAR TO MONTH"         is new DATE_BASE;
type "INTERVAL DAY TO SECOND"         is new DATE_BASE;
type "TIMESTAMP WITH LOCAL TIME ZONE" is new DATE_BASE;

subtype TIME_UNCONSTRAINED          is TIME(9);
subtype TIME_TZ_UNCONSTRAINED       is TIME(9) WITH TIME ZONE;
subtype TIMESTAMP_UNCONSTRAINED     is TIMESTAMP(9);
subtype TIMESTAMP_TZ_UNCONSTRAINED  is TIMESTAMP(9) WITH TIME ZONE;
subtype YMINTERVAL_UNCONSTRAINED    is INTERVAL YEAR(9) TO MONTH;
subtype DSINTERVAL_UNCONSTRAINED    is INTERVAL DAY(9) TO SECOND (9);
subtype TIMESTAMP_LTZ_UNCONSTRAINED is TIMESTAMP(9) WITH LOCAL TIME ZONE;

NUMBERデータ型ファミリ

type NUMBER is NUMBER_BASE;

subtype FLOAT              is NUMBER; -- NUMBER(126)
subtype REAL               is FLOAT;   -- FLOAT(63)
subtype "DOUBLE PRECISION" is FLOAT;

subtype INTEGER  is NUMBER(38,0);
subtype INT      is INTEGER;
subtype SMALLINT is NUMBER(38,0);

subtype DECIMAL is NUMBER(38,0);
subtype NUMERIC is DECIMAL;
subtype DEC     is DECIMAL;

subtype BINARY_INTEGER is INTEGER range '-2147483647'..2147483647;
subtype NATURAL        is BINARY_INTEGER range 0..2147483647;
subtype NATURALN       is NATURAL not null;
subtype POSITIVE       is BINARY_INTEGER range 1..2147483647;
subtype POSITIVEN      is POSITIVE not null;
subtype SIGNTYPE       is BINARY_INTEGER range '-1'..1;  -- for SIGN functions
subtype PLS_INTEGER    is BINARY_INTEGER;

type BINARY_FLOAT  is NUMBER;
type BINARY_DOUBLE is NUMBER;

subtype SIMPLE_INTEGER is BINARY_INTEGER NOT NULL;
subtype SIMPLE_FLOAT   is BINARY_FLOAT   NOT NULL;
subtype SIMPLE_DOUBLE  is BINARY_DOUBLE  NOT NULL;