Les13-管理方案对象

1、十三十三.管理方案对象管理方案对象 目标目标l 完成本课后，应该掌握以下内容: l增加约束l创建索引l使用CREATE TABLE语句创建索引l创建基于函数的索引l删除列并且将列设置为UNUSEDl执行FLASHBACK操作l创建并且使用外部表ALTER TABLEALTER TABLE语句语句l使用 ALTER TABLE语句来完成:l增加列l修改已经存在的列l为新列定义默认值l删除列ALTER TABLEALTER TABLE语句语句l使用ALTER TABLE语句来增加，修改或者删除列ALTER TABLE tableADD (column datatype DEFAULT expr ,

2、 column datatype.);ALTER TABLE tableMODIFY (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableDROP (column);增加一列增加一列l使用ADD 语句增加一列l最后一列为新增加列ALTER TABLE dept80ADD (job_id VARCHAR2(9);Table altered.修改列修改列l可以修改一个列的数据类型，大小和默认值l改变默认值的操作只影响后续的insert，之前插入的值不变ALTER TABLEdept80MODIFY(last_name

3、 VARCHAR2(30);Table altered.删除列删除列l 使用 DROP COLUMN语句删除不再需要的列.ALTER TABLE dept80DROP COLUMN job_id; Table altered.ALTER TABLE SET UNUSED();ALTER TABLE SET UNUSED COLUMN ; SET UNUSED选项选项l使用使用SET UNUSED 选项标记一列或多列为选项标记一列或多列为unusedl使用使用DROP UNUSED COLUMNS 选项删除选项删除unused列列ORALTER TABLE DROP UNUSED COLUMNS

4、;增加约束语法增加约束语法l使用ALTER TABLE 语句来完成:l增加或者删除约束，但是不修改它的结构l启动或关闭约束l通过使用MODIFY语句增加约束ALTER TABLE ADD CONSTRAINT type ();ALTER TABLE emp2modify employee_id Primary Key;Table altered.增加约束增加约束l为emp2增加外键约束，但是emp2必须先有主键ALTER TABLE emp2ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(manager_id) REFERENCES emp2(employee_id

5、);Table altered.ON DELETE CASCADEl删除父键的同时删除外键ALTER TABLE Emp2 ADD CONSTRAINT emp_dt_fk FOREIGN KEY (Department_id) REFERENCES departments ON DELETE CASCADE); Table altered.延迟约束延迟约束l 约束有以下的属性:lDEFERRABLE 或者 NOT DEFERRABLE lINITIALLY DEFERRED 或者INITIALLY IMMEDIATEALTER TABLE dept2ADD CONSTRAINT dept2_

6、id_pk PRIMARY KEY (department_id)DEFERRABLE INITIALLY DEFERREDALTER SESSION SET CONSTRAINTS= IMMEDIATESET CONSTRAINTS dept2_id_pk IMMEDIATEDeferring constraint on creationChanging all constraints for a sessionChanging a specific constraint attribute删除约束删除约束l删除表emp2上的manager约束l删除DEPT2上的主键同时删除emp2上的外键

7、ALTER TABLE emp2DROP CONSTRAINT emp_mgr_fk;Table altered.ALTER TABLE dept2DROP PRIMARY KEY CASCADE;Table altered.关闭约束关闭约束l使用ALTER TABLE语句的DISABLE子句关闭约束l使用CASCADE选项关闭依赖的约束ALTER TABLEemp2DISABLE CONSTRAINT emp_dt_fk;Table altered.启动约束启动约束l使用ENABLE子句启动当前关闭的约束l如果启动唯一键或主键，唯一索引自动创建ALTER TABLEemp2ENABLE CO

8、NSTRAINTemp_dt_fk;Table altered.级联约束级联约束lCASCADE CONSTRAINTS 子句与DROP COLUMN 语句一起使用.lCASCADE CONSTRAINTS 子句删除引用此列的所有外键lCASCADE CONSTRAINTS 子句也删除引用此列的多列(multicolumn )约束l 示例:CREATE TABLE test1 ( pk NUMBER PRIMARY KEY, fk NUMBER, col1 NUMBER, col2 NUMBER, CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERE

9、NCES test1, CONSTRAINT ck1 CHECK (pk 0 and col1 0), CONSTRAINT ck2 CHECK (col2 0);以下语句报错：ALTER TABLE test1 DROP (pk); pk 是父键ALTER TABLE test1 DROP (col1); col1被多列（ multicolumn ）约束ck1引用级联约束级联约束级联约束级联约束ALTER TABLE emp2 DROP COLUMN employee_id CASCADE CONSTRAINTS;Table altered.ALTER TABLE test1DROP (pk

10、, fk, col1) CASCADE CONSTRAINTS;Table altered.ALTER TABLE test1DROP (col1) CASCADE CONSTRAINTS;Table altered.约束条件状态约束条件状态 ENABLENOVALIDATE ENABLEVALIDATE DISABLENOVALIDATE DISABLEVALIDATE 无无 DML 现有数据现有数据 新数据新数据 索引索引l索引创建:l自动lPRIMARY KEY 创建lUNIQUE KEY 创建l手动lCREATE INDEX 语句lCREATE TABLE 语句CREATE TABLE

11、语句创建索引语句创建索引 CREATE TABLE NEW_EMP(employee_id NUMBER(6) PRIMARY KEY USING INDEX (CREATE INDEX emp_id_idx ON NEW_EMP(employee_id),first_name VARCHAR2(20),last_name VARCHAR2(25);Table created.SELECT INDEX_NAME, TABLE_NAME FROM USER_INDEXESWHERE TABLE_NAME = NEW_EMP;CREATE INDEX upper_dept_name_idxON d

12、ept2(UPPER(department_name);Index created.SELECT *FROM dept2WHERE UPPER(department_name) = SALES;函数索引函数索引l函数索引是基于表达式的l表达式是从表列，常量，SQL函数和用户定义的函数中建立删除索引删除索引l使用DROP INDEX命令将索引从数据字典中删除l从数据字典中删除索引UPPER_DEPT_NAME_IDX l删除索引或者是索引的属主或者有DROP ANY INDEX 权限DROP INDEX upper_dept_name_idx;Index dropped.DROP INDEX i

13、ndex;DROP TABLE PURGEDROP TABLE dept80 PURGE; FLASHBACK TABLE语句语句l意外错误的修复工具l将表恢复到更早的时间点l优点: 易用,快速执行l语法:FLASHBACK TABLEschema.table, schema.table .TO TIMESTAMP | SCN expr ENABLE | DISABLE TRIGGERS ;FLASHBACK TABLE语句语句DROP TABLE emp2;Table droppedFLASHBACK TABLE emp2 TO BEFORE DROP;Flashback complete

14、SELECT original_name, operation, droptime, FROM recyclebin;外部表外部表为外部表创建目录为外部表创建目录l创建目录来存放外部表的数据CREATE OR REPLACE DIRECTORY emp_dir AS /emp_dir; GRANT READ ON DIRECTORY emp_dir TO hr;创建一个外部表创建一个外部表CREATE TABLE ( , ) ORGANIZATION EXTERNAL (TYPE DEFAULT DIRECTORY ACCESS PARAMETERS ( ) ) LOCATION () ) REJECT LIMIT 0 | | UNLIMITED; 创建外部表创建外部表CREATE TABLE oldemp ( fname char(25), lname CHAR(25) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY emp_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE NOBADFILE NOLOGFILE FIELDS TERMINATED BY , (fname POSITION ( 1:20)