dbms_sql之通用general及bulk批量（大体积）type 类型

Types

General Types

• DESC_REC, DESC_TAB

• VARCHAR2A, DESC_REC2

• VARCHAR2_TABLE

Bulk SQL Types

• BFILE_TABLE

• BINARY_DOUBLE_TABLE

• BLOB_TABLE

• CLOB_TABLE

• DATE_TABLE

• INTERVAL_DAY_TO_SECOND_TABLE

• INTERVAL_YEAR_TO_MONTH_TABLE

• NUMBER_TABLE

• TIME_TABLE

• TIME_WITH_TIME_ZONE_TABLE

• TIMESTAMP_TABLE

• TIMESTAMP_WITH_LTZ_TABLE

• UROWID_TABLE

• VARCHAR2_TABLE

 

#########下为以上dbms_sql引用特殊类型的具体定义

BFILE_TABLE

TYPE bfile_table IS TABLE OF BFILE INDEX BY BINARY_INTEGER;

BINARY_DOUBLE_TABLE

TYPE binary_double_table IS TABLE OF BINARY_DOUBLE  INDEX BY BINARY_INTEGER;

BINARY_FLOAT_TABLE

TYPE binary_float_table IS TABLE OF BINARY_FLOAT INDEX BY BINARY_INTEGER;

BLOB_TABLE

TYPE blob_table IS TABLE OF BLOB INDEX BY BINARY_INTEGER;

CLOB_TABLE

TYPE clob_table IS TABLE OF CLOB INDEX BY BINARY_INTEGER;

DATE_TABLE

type date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER; 

INTERVAL_DAY_TO_SECOND_TABLE

TYPE interval_day_to_second_Table IS TABLE OF
    dsinterval_unconstrained INDEX BY binary_integer;

INTERVAL_YEAR_TO_MONTH_TABLE

TYPE interval_year_to_month_table IS TABLE OF yminterval_unconstrained INDEX BY BINARY_INTEGER;

DESC_REC, DESC_TAB

TYPE desc_rec IS RECORD (
      col_type            BINARY_INTEGER := 0,
      col_max_len         BINARY_INTEGER := 0,
      col_name            VARCHAR2(32)   := '',
      col_name_len        BINARY_INTEGER := 0,
      col_schema_name     VARCHAR2(32)   := '',
      col_schema_name_len BINARY_INTEGER := 0,
      col_precision       BINARY_INTEGER := 0,
      col_scale           BINARY_INTEGER := 0,
      col_charsetid       BINARY_INTEGER := 0,
      col_charsetform. BINARY_INTEGER := 0,
      col_null_ok         BOOLEAN        := TRUE);
TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;

NUMBER_TABLE

TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

TIME_TABLE

TYPE time_table IS TABLE OF time_unconstrained INDEX BY BINARY_INTEGER;

TIME_WITH_TIME_ZONE_TABLE

TYPE time_with_time_zone_table IS TABLE OF TIME_TZ_UNCONSTRAINED INDEX 
BY BINARY_INTEGER;

TIMESTAMP_TABLE

TYPE timestamp_table IS TABLE OF timestamp_unconstrained INDEX BY BINARY_INTEGER;

TIMESTAMP_WITH_LTZ_TABLE

TYPE timestamp_with_ltz_table IS TABLE OF
    TIMESTAMP_LTZ_UNCONSTRAINED INDEX BY binary_integer;

UROWID_TABLE

TYPE urowid_table IS TABLE OF UROWID INDEX BY BINARY_INTEGER;

VARCHAR2_TABLE

TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

VARCHAR2A, DESC_REC2

TYPE varchar2a IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
  TYPE desc_rec2 IS RECORD (
        col_type            binary_integer := 0,
        col_max_len         binary_integer := 0,
        col_name            varchar2(32767) := '',
        col_name_len        binary_integer := 0,
        col_schema_name     varchar2(32)   := '',
        col_schema_name_len binary_integer := 0,
        col_precision       binary_integer := 0,
        col_scale           binary_integer := 0,
        col_charsetid       binary_integer := 0,
        col_charsetform. binary_integer := 0,
        col_null_ok         boolean        := TRUE);
  TYPE desc_tab2 IS TABLE OF desc_rec2 INDEX BY BINARY_INTEGER;

VARCHAR2S

TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;

 

 

 

这些特殊数据类型适用于一些特定场合，实为有用，备记！

 

比如用于查看某个表的结构（功能同于describle emp) dbms_sql.describle_columns其中所用的desc_rec及desc_tab

 

 

 

以上层次结构为：

type is record(基本的数据类型）---〉desc_rec--&gtdesc_tab

 

另：所有dbms_sql所用的特殊数据类型：全是type 特殊数据类型 is table of 基本数据类型 index by binary_integer

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-670016/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-670016/