第9章 基本对象管理

Oracle11g+ASP.NET数据库系统开发案例教程Oracle的数据类型

序列管理

数据表管理索引管理视图管理基本对象信息查询

基本对象管理

第9章9.1Oracle的数据类型

字符类型类型格式说明CHARCHAR[(size[BYTE|CHAR])]定长存贮的字符类型，size为长度，BYTE|CHAR为单位，默认为字节。CHAR变量的最大长度是32767字节，CHAR数据库表列的最大长度是2000字节。VARCHAR2VARCHAR2(size[BYTE|CHAR])变化存贮的字符类型。VARCHAR2变量的最大长度是32767字节，VARCHAR2数据库表列的最大长度是4000字节。NCHARNCHAR[(size)]取值范围与CHAR相同，但NCHAR中的L始终是按字符指定的。NVARCHAR2NVARCHAR2(size)取值范围和VARCHAR2相同，但NVARCHAR2中的L始终是按字符指定的。9.1Oracle的数据类型

数值类型类型格式说明NUMBERNUMBER[(precision[，scale])]存储整数或浮点数。其中precision是精度（指数值中所有数字的个数），scale是刻度（指小数点右边数字的个数），scale取负数表示由小数点开始向左计算数字的个数。precision和scale都是可选的，但是如果指定了刻度，则必须指定精度。BINARY_FLOAT32位浮点数字。此数据类型需要5个字节，包括长度字节。BINARY_DOUBLE64位浮点数字。此数据类型需要9个字节，包括长度字节。9.1Oracle的数据类型

日期时间类型类型格式说明DATE

存储日期和时间数据，包括世纪、年、月、日、小时、分和秒（但不存储秒的小数部分），缺省格式为DD-MON-YY。DATE数据占用7个字节，每个部分占一个字节。函数SYSDATE返回系统当前日期和时间。有效的日期和时间范围是公元前4712年1月1日～公元9999年12月31日。TIMESTAMPTIMESTAMP[(P)]时间戳数据类型是日期数据类型的扩展。存储年、月、日、小时、分和秒，但还可存储秒的小数部分。其中P是秒小数部分的精度，范围是0～9，缺省值是6。9.1Oracle的数据类型

大对象类型类型格式说明BLOB在数据库内存储大型的二进制对象，存储最大尺寸不超过4GB。CLOB在数据库内存储大型的字符型数据，存储最大尺寸不超过4GB。NCLOB在数据库内存储大型的NCHAR类型数据，存储最大尺寸不超过4GB。序列（SEQUENCE）是可被多个用户使用的用于产生一系列唯一整数的数据库对象。序列是一个连续的数字生成器，其定义存储在数据字典中。使用序列的好处是自动产生主键的键值，从而可以简化用户的输入工作量。当序列第一次被查询调用时，它将返回一个预定值。在随后的每次查询中，序列将产生一个按其指定的增量增长的值。序列可以是循环的，或者是连续增加的，直到指定的最大值为止。9.2序列管理序列的创建9.2序列管理CREATESEQUENCE[schema.]sequence[{INCREMENTBY|STARTWITH}integer|{MAXVALUEinteger|NOMAXVALUE}|{MINVALUEinteger|NOMINVALUE}|{CYCLE|NOCYCLE}|{CACHEinteger|NOCACHE}|{ORDER|NOORDER}]；语法说明：schema：模式名即用户名。sequence：序列名。STARTWITHinteger：integer为用于指定序列起始值。INCREMENTBYinteger：integer用于指定序列递增量，默认为1。MAXVALUEinteger|NOMAXVALUE：用于指定序列最大值，默认为NOMAXVALUE即不限最大值。MINVALUEinteger|NOMINVALUE：用于指定序列最小值，必须小于等于起始值和小于最大值。CYCLE|NOCYCLE：CYCLE用于指定序列到达最大值后是否循环，默认为NOCYCLE不循环。CACHEinteger|NOCACHE：CACHE用于指定是否在内存中预分配序列号，默认缓存20个序列号；NOCACHE用于指定在高速缓存中不预分配序列号。ORDER|NOORDER：ORDER用于指定按顺序生成序列号。

序列的创建9.2序列管理【例9.1】以user01身份登入Oracle系统，为部门信息表department的主键deptid生成一个序列，使得该主键的键值主动增长，起始值为1，增长步长为1，不需要循环，不在内存中缓存序列号码。--9.1为部门信息表department的主键deptid生成一个序列，使得该主键的键值主动增长CREATESEQUENCEseq_depidSTARTWITH1INCREMENTBY1NOCACHE8NOCYCLE；序列的修改9.2序列管理ALTERSEQUENCE[schema.]sequence[{INCREMENTBY|STARTWITH}integer|{MAXVALUEinteger|NOMAXVALUE}|{MINVALUEinteger|NOMINVALUE}|{CYCLE|NOCYCLE}|{CACHEinteger|NOCACHE}|{ORDER|NOORDER}]；--9.2修改序列seq_deptid，使其在内存中缓存8个序列号码ALTERSEQUENCEseq_depidCACHE8；序列的使用9.2序列管理Sequence.CURRVALSequence.NEXTVAL

；序列一经创建就成了Oracle的一个对象，用户可以访问序列的当前值和即将生成的下一个值。语法说明：sequence：序列名。CURRVAL：序列的当前值，该值类似于内部函数，可以出现在任何表达式中。NEXTVAL：即将生成的下一个值，该值类似于内部函数，可以出现在任何表达式中，每访问一次其值自动增长一次。序列的使用9.2序列管理--9.3查询序列seq_depid的当前值SELECTseq_depid.currvalFROMdual;--9.4查询序列seq_depid即将生成的最新值SELECTseq_depid.nextvalFROMdual;【提示】：dual表是Oracle与数据字典一起自动创建的一个表，这个表只有1列（dummy），数据类型为VERCHAR2(1)，dual表中只有一个数据'X'，Oracle有内部逻辑保证dual表中永远只有一条数据。dual表中数据没有实际意义，所以大家称之为“虚拟”表，常用在没有目标表的Select语句块中使用，用来保证SELECT的语法规则完整性，主要用来选择系统变量或求一个表达式的值。9.2序列管理序列的删除9.2序列管理DROPSEQUENCE[schema.]sequence_name;--9.5删除序列seq_depidDROPSEQUENCEseq_depid;表的创建9.3

数据表管理CREATETABLE[schema.]table({columndatatype[DEFAULTexpr][inline_constraint[inline_constraint]...]|out_of_line_constraint}[，{columndatatype[DEFAULTexpr][inline_constraint[inline_constraint]...]|out_of_line_constraint]...)[TABLESPACEtablespace];表（TABLE）是Oracle数据库中的主要对象，是数据库中数据存储的基本单位，存储着与应用程序相关的一些信息，其他许多数据库对象（如索引、视图）都以表为基础。每个表是具有一个表名和若干列的集合，每列有一个列名、数据类型、宽度或精度、比例，每行是对应单个记录的列信息的集合。。语法说明：schema：模式名即用户名。table：表名。column：列名即字段名。datatype：数据类型。DEFAULTexpr：为列设定一个默认值，expr为默认值的表达式。9.3

数据表管理inline_constraint：使用inline_constraint为列定义一序列的完整性约束。您可以创建UNIQUE，PRIMARYKEY，REFERENCES，NOTNULL和CHECK约束。基于同一列的列级约束可以有多个。out_of_line_constraint：定义表级约束。其语法结构如下：[CONSTRAINTconstraint_name]{UNIQUE(column[，column]...)|PRIMARYKEY(column[，column]...)|FOREIGNKEY(column[，column]...)REFERENCES[schema.]{object_table|view}[(column[，column]...)][ONDELETE{CASCADE|SETNULL}]|CHECK(condition)}TABLESPACEtablespace：为创建的表指定存放的表空间，tablespace为表空间名，注意表的属主应该有tablespace表空间的使用配额。如果没有指定表空间，则在用户默认的表空间中创建表。9.3

数据表管理Oracle中用commenton命令给表（视图）或字段加以说明，以帮助开发人员了解表结构。COMMENTON{TABLE[schema.]{table|view}|COLUMN[schema.]{table.|view.}column}IS'text'schema：模式名即用户名。table：表名。view：视图名。column：列名即字段名。'text'：说明文字。9.3

数据表管理--9.6以system身份登录进OracleCREATETABLEuser01.department(dep_codeNUMBER(2)PRIMARYKEY,dep_nameVARCHAR2(32)UNIQUE);--给部门表加注释COMMENTONTABLEuser01.departmentIS'部门信息';COMMENTONCOLUMNuser01.department.dep_codeIS'部门ID，作为主键';COMMENTONCOLUMNuser01.department.dep_nameIS'部门名称，要求唯一';

9.3

数据表管理--9.7以user01身份登录进Oracle，建立职工信息表CREATETABLEstaff(sta_codeVARCHAR2(8)PRIMARYKEY,sta_nameVARCHAR2(32),telVARCHAR2(16),pwdVARCHAR2(16),sexVARCHAR2(2)DEFAULT'm',birthdayVARCHAR2(10),dep_codeNUMBER(2)CONSTRAINTfk_staff_departmentREFERENCESdepartment(dep_code));COMMENTONTABLEstaffIS'职工信息';COMMENTONCOLUMNstaff.birthdayIS'出生年月,格式为YYYY-MM-DD';COMMENTONCOLUMNstaff.dep_codeIS'部门编号，是一个外键';9.3

数据表管理--9.8创建与职工工资相关的表：工资栏目表、工资表CREATETABLEwage_item(itme_snNUMBER(8)PRIMARYKEY,sta_codeVARCHAR2(8)CONSTRAINTfk_wage_item_staffreferencesstaff(sta_code),basy_payNUMBER(6,1)DEFAULT0,performance_payNUMBER(6,1)DEFAULT0,job_payNUMBER(6,1)DEFAULT0,insuranceNUMBER(6,1)DEFAULT0,scotNUMBER(6,1)DEFAULT0);COMMENTONTABLEwage_itemIS'工资项目';9.3

数据表管理--工资信息表CREATETABLEwages(monthsVARCHAR2(6),wages_noNUMBER(8)PRIMARYKEY,sta_codeVARCHAR2(8)CONSTRAINTfk_wages_staffreferencesstaff(sta_code),base_payNUMBER(6,1)DEFAULT0,performance_payNUMBER(6,1)DEFAULT0,job_payNUMBER(6,1)DEFAULT0,insuranceNUMBER(6,1)DEFAULT0,scotNUMBER(6,1)DEFAULT0);COMMENTONTABLEwagesIS'工资信息';COMMENTONCOLUMNwages.monthsIS'年月:默认值取系统时间的年份+月份';表的修改修改表结构主要包括修改表名、更新字段（增加、修改、删除、改名）、更新约束（增加、修改、删除、改名）、禁用/启用触发器、禁用/启用表锁等方面。RENAMEtableTOnewtable9.3

数据表管理修改表名--9.9修改表名RENAMEstaffTOstaffs;更新字段更新字段包括为表增加字段、修改字段的数据类型和长度、删除字段、修改字段名ALTERTABLE[schema.]tableADD(columndatatype[DEFAULTexpr][{inline_constraint[inline_constraint]...}][,columndatatype[DEFAULTexpr][{inline_constraint[inline_constraint]...}]]...)[column_properties]9.3

数据表管理增加字段--9.10为职工信息表staffs增加一个手机号码字段，并添加一个CHECK约束ALTERTABLEstaffsADD(mobileVARCHAR2(16)CHECK(LENGTH(mobile)<=11));ALTERTABLE[schema.]tableMODIFY(column[datatype][DEFAULTexpr][inline_constraint[inline_constraint]...][,column[datatype][DEFAULTexpr][inline_constraint[inline_constraint]...]])9.3

数据表管理修改字段--9.11修改部门表department的deptid字段、职工信息表staffs的department字段，将字段的长度由原来的9修改成4ALTERTABLEdepartmentMODIFYdep_codeNUMBER(4);ALTERTABLEstaffsMODIFYdep_codeNUMBER(4);ALTERTABLE[schema.]tableDROP{COLUMNcolumn|(column[，column]...)}[CASCADECONSTRAINTS]9.3

数据表管理删除字段--9.12删除职工表staffs的电话字段telALTERTABLEstaffsDROPCOLUMNtel；ALTERTABLE[schema.]tableRENAMECOLUMNold_nameTOnew_name9.3

数据表管理修改字段名--9.13将职工信息表staffs的mobile字段改名为mobile_phoneALTERTABLEstaffsRENAMECOLUMNmobileTOmobile_phone；更新约束更新完整性约束包括为表增加约束、修改原有的约束、删除已有的约束、修改约束名。ALTERTABLE[schema.]tableADDout_of_line_constraint[out_of_line_constraint]...9.3

数据表管理增加约束--9.14为工资表wages增加一个检查约束，检查基本工资金额不能少于200元ALTERTABLEwagesADDCHECK(base_pay>=200);ALTERTABLE[schema.]tableMODIFY{CONSTRAINTconstraint|PRIMARYKEY|UNIQUE(column[，column]...)}[ENABLE|DISABLE][VALIDATE|NOVALIDATE]9.3

数据表管理修改约束ENABLEVALIDATE：指定所有新旧数据也符合该约束。启用验证约束可以保证所有数据将继续有效。如果表中的任何行违反了完整性约束，该约束将保持禁用状态，Oracle将返回一个错误。如果所有行都遵守约束，Oracle将使用该约束。随后，如果新的数据违反该约束，Oracle不会执行该语句，并返回错误，指示违反完整性约束。ENABLENOVALIDATE：可确保对约束数据的所有新DML操作遵守约束。此子句不确保表中的现有数据符合该约束。DISABLEVALIDATE：禁用约束和删除基于约束的索引，但保持有效的约束。此功能在数据仓库的情况下最有用，因为它可以让你在没有索引的情况下加载大量的数据，同时还节省空间。DISABLENOVALIDATE：表示Oracle使尽一切努力保持该约束（因为它被禁用），并不能保证约束是真实的（因为它不正在验证）。您不能删除正在由一个外键引用的主键，即使在禁用NOVALIDATE状态的外键约束的表。进一步，优化程序可以在禁用NOVALIDATE状态使用约束。9.3

数据表管理--9.15修改约束的状态--插入一条记录，正常提交INSERTINTOdepartmentVALUES(1,'市场部');COMMIT;--插入第2条记录，会报违反约束，因为部门名称不能同名。如图9-1所示。INSERTINTOdepartmentVALUES(2,'市场部');COMMIT;--修改唯一约束，禁用该约束ALTERTABLEdepartmentMODIFYUNIQUE(dep_name)DISABLENOVALIDATE;--再一次插入，成功提交INSERTINTOdepartmentVALUES(2,'市场部');COMMIT;--修改唯一约束，启用该约束会报错，因为原来的数据违反此约束。如图9-2所示。ALTERTABLEdepartmentMODIFYUNIQUE(dep_name)ENABLEVALIDATE;--删除名称相同的第2条记录DELETEdepartmentWHEREdep_code=2;COMMIT;--再一次启用该约束会，正常启用ALTERTABLEdepartmentMODIFYUNIQUE(dep_name)ENABLEVALIDATE;ALTERTABLE[schema.]tableDROP{{PRIMARYKEY|UNIQUE(column[，column]...)}[CASCADE][{KEEP|DROP}INDEX]|CONSTRAINTconstraint[CASCADE]}9.3

数据表管理删除约束-9.16首先为职工信息表增加个唯一性约束ALTERTABLEstaffsADDCONSTRAINTuq__staffs_nameUNIQUE(sta_name);--然后将该约束删除ALTERTABLEstaffsDROPCONSTRAINTuq__staffs_name;禁用/启用触发器ALTERTABLE[schema.]table{ENABLE|DISABLE}ALLTRIGGERS--9.18禁用职工信息表staffs的所有触发器ALTERTABLEstaffsDISABLEALLTRIGGERS；9.3

数据表管理删除表DROPTABLE[schema.]table[CASCADECONSTRAINTS][PURGE]；CASCADECONSTRAINTS：如果删除的表中有作为主键的字段，则必须带此选项，将所有参考此主键的外键约束取消。PURGE：此选项表示在删除表的同时清空其相关联的存储空间，如果您指定此选项，则数据库不会将表和其依赖的对象放到回收站。9.3

数据表管理--9.20删除本节中创建的所有表DROPTABLEwagesCASCADECONSTRAINTSPURGE;DROPTABLEwage_itemCASCADECONSTRAINTSPURGE;DROPTABLEstaffsCASCADECONSTRAINTSPURGE;DROPTABLEdepartmentCASCADECONSTRAINTSPURGE;索引分类9.4索引管理单列索引和复合索引：单列索引是基于单个列创建的索引，复合索引是基于两列或多列所创建的索引。唯一索引和不唯一索引：唯一索引是索引值不能重复的索引，非唯一索引是索引列值可以重复的索引。无论是唯一索引还是非唯一索引，索引列都允许NULL。标准索引（B树索引）与位图索引：B树索引是Oracle中最常用的一种索引，默认创建的就是B树索引。位图索引为每一个唯一的索引字段值建立一个位图。在这个位图中为表中每一行使用一个位元来表示该行是否包含该位图的索引列的取值。对于基数很小的列，通过在CREATEINDEX语句中显式地指定BITMAP关键字可以创建位图索引。列索引与函数索引：在Oracle中不仅能够对表中的字段建立索引，还可以对包含有字段的函数或表达式创建索引。这种索引称为“函数索引”。函数索引既可以是B树索引，也可以是位图索引。创建索引CREATE[UNIQUE|BITMAP]INDEX[schema.]indexON[schema.]table(index_expr[ASC|DESC][，index_expr[ASC|DESC]]...)[TABLESPACEtablespace][{LOGGING|NOLOGGING}]；UNIQUE：表示创建唯一性索引，不能与BITMAP同时选择，要求索引列的值是唯一。BITMAP：表示建立位图索引，针对不同的键值建立位图，而不是单独索引的每一行。index_expr：用于建立索引的列或列表达式，如果是列表达式则建立的索引为函数索引。ASC｜DESC：表示索引项值的排序方式，ASC是升序，DESC是降序，默认为ASC。TABLESPACEtablespace：指定容纳索引的表空间名称，如果没有指定，则索引保存在用户方案的默认表空间中。表的属主应该有tablespace表空间的使用配额。LOGGING|NOLOGGING：在创建索引时需要生产重做日志，默认是LOGGING。--9.21在职工信息表staffs中以职工移动mobile_phone电话为索引项建立索引CREATEINDEXidx_stafss_telONstaffs(mobile_phone)；--9.22在职工信息表staffs中以部门编号为索引项建立位图索引CREATEBITMAPINDEXidx_staffs_departmentONstaffs(dep_code);9.4索引管理修改索引ALTERINDEX[schema.]index{{LOGGING|NOLOGGING}|REBUILD|RENAMETOnew_name}；--9.23将职工信息表staffs中的索引idx_stafss_tel重建ALTERINDEXidx_stafss_telREBUILD；9.4索引管理删除索引DROPINDEX[schema.]index；--9.24删除职工信息表中的idx_stafss_tel索引DROPINDEXidx_stafss_tel；9.4索引管理创建视图CREATE[ORREPLACE][[NO]FORCE]VIEW[schema.]view[(alias[，alias]...)]ASsubquery[WITH{READONLY|CHECKOPTION}]；ORREPLACE：ORRELACE是可选的。如果省略，则创建时不允许数据库中有同名的视图；如果使用，则会先删除同名的视图，然后创建新的视图。FORCE：表示在创建视图时无需考虑构建视图的基表是否存在或包含该视图的方案所有者是否有创建视图的权限。WITHREADONLY：视图中不能执行任何更新操作，只能执行检索操作。WITHCHECKOPTION：如果视图定义时包括条件（即在SELECT语句中带WHERE子句）并且其意图是确保任何引用该视图的INSERT或UPDATE语句都应用WHERE子句，则必须使用WITHCHECKOPTION定义该视图。这个选项可以确保数据库中正在修改的数据的完整性，强制所有在该视图上使用的数据更新语句满足定义该视图时的条件。如果在INSERT或UPDATE操作期间违反了条件，则返回SQL错误。--9.25创建一个职工信息视图，视图包含工号、姓名、部门名。CREATEORREPLACEVIEWv_staff(code,name,department)ASSELECTs.sta_code,s.sta_name,d.dep_nameasdepartmentFROMstaffss,departmentdWHEREs.dep_code=d.dep_code;9.5视图管理--9.26创建一个男职工信息视图，视图包含工号、姓名、移动电话。CREATEORREPLACEVIEW