dbms_sql之通用general及bulk批量（大體積）type 型別

wisdomone1發表於2010-08-02
SQL型別

Types

General Types

Bulk SQL Types

 

#########下為以上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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章