Oracle外部表 索引表 嵌套表.docx

Oracle外部表External Table Example The following statement creates an external table thatrepresents a subset of the sample table hr.departments. The opaque_format_specis shown in italics. Please refer to Oracle Database Utilities for information on theORACLE_LOADER access driver and how to specify values for the opaque_format_spec.CREATE TABLE dept_external (deptno NUMBER(6),dname VARCHAR2(20),loc VARCHAR2(25))ORGANIZATION EXTERNAL(TYPE oracle_loaderDEFAULT DIRECTORY adminACCESS PARAMETERS(RECORDS DELIMITED BY newlineBADFILE ulcase1.badDISCARDFILE ulcase1.disLOGFILE ulcase1.logSKIP 20FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY (deptno INTEGER EXTERNAL(6),dname CHAR(20),loc CHAR(25)))LOCATION (ulcase1.ctl))REJECT LIMIT UNLIMITED;Index-Organized Table Example The following statement is a variation of the sampletablehr.countries, which is index organized:CREATE TABLE countries_demo( country_id CHAR(2)CONSTRAINT country_id_nn_demo NOT NULL, country_nameVARCHAR2(40), currency_nameVARCHAR2(25), currency_symbolVARCHAR2(3), region VARCHAR2(15), CONSTRAINT country_c_id_pk_demoPRIMARY KEY (country_id ) )ORGANIZATION INDEXINCLUDING country_namePCTTHRESHOLD 2STORAGE( INITIAL 4KNEXT 2KPCTINCREASE 0MINEXTENTS 1MAXEXTENTS 1 )OVERFLOWSTORAGE( INITIAL 4KNEXT 2KPCTINCREASE 0MINEXTENTS 1MAXEXTENTS 1 );Nested Table Example The following statement shows how the sample tablepm.print_mediawas created with a nested table column ad_textdocs_ntab:CREATE TABLE print_media( product_id NUMBER(6), ad_idNUMBER(6), ad_composite BLOB, ad_sourcetext CLOB, ad_finaltext CLOB, ad_fltextn NCLOB, ad_textdocs_ntabtextdoc_tab, ad_photo BLOB, ad_graphic BFILE, ad_headeradheader_typ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;Oracle外部表就好像是一张视图，在数据库中可以像试图一样进行查询等操作。这个试图允许用户在外部数据上运行任何的SQL语句，而不需要先将外部表中的数据装载进数据库中。　　外部表是指不在数据库中的表，如操作系统上的一个按一定格式分割的文本文件或者其他类型的表。这个外部表对于Oracle数据库来说，就好像是一张视图，在数据库中可以像试图一样进行查询等操作。这个试图允许用户在外部数据上运行任何的SQL语句，而不需要先将外部表中的数据装载进数据库中。不过需要注意是，外部数据表都是只读的，不能够更改。不过在使用外部表时仍然有不少的限制。　　限制一：