Oracle数据库数据对象中最基本的是表和视图其他还有约束序列函数存储过程包触发器等对数据库的操作可以基本归结为对数据对象的操作理解和掌握Oracle数据库对象是学习Oracle的捷径
表和视图
Oracle中表是数据存储的基本结构ORACLE引入了分区表和对象表ORACLEi引入了临时表使表的功能更强大视图是一个或多个表中数据的逻辑表达式本文我们将讨论怎样创建和管理简单的表和视图
管理表
表可以看作有行和列的电子数据表表是关系数据库中一种拥有数据的结构用CREATE TABLE语句建立表在建立表的同时必须定义表名列以及列的数据类型和大小例如
CREATE TABLE products
( PROD_ID NUMBER()
PROD_NAME VAECHAR()
STOCK_QTY NUMBER()
);
这样我们就建立了一个名为products的表 关键词CREATE TABLE后紧跟的表名然后定义了三列同时规定了列的数据类型和大小
在创建表的同时你可以规定表的完整性约束也可以规定列的完整性约束在列上普通的约束是NOT NULL关于约束的讨论我们在以后进行
在建立或更改表时可以给表一个缺省值缺省值是在增加行时增加的数据行中某一项值为null时oracle即认为该值为缺省值
下列数据字典视图提供表和表的列的信息
DBA_TABLES
DBA_ALL_TABLES
USER_TABLES
USER_ALL_TABLES
ALL_TABLES
ALL_ALL_TABLES
DBA_TAB_COLUMNS
USER_TAB_COLUMNS
ALL_TAB_COLUMNS
表的命名规则
表名标识一个表所以应尽可能在表名中描述表oracle中表名或列名最长可以达个字符串表名应该以字母开始可以在表名中包含数字下划线#$等
从其它表中建立表
可以使用查询从基于一个或多个表中建立表表的列的数据类型和大小有查询结果决定建立这种形式的表的查询可以选择其他表中所有的列或者只选择部分列在CREATE TABLE语句中使用关键字AS例如:
SQL>CREATE TABLE emp AS SELECT * FROM employee
TABLE CREATED
SQL> CREATE TABLE Y AS SELECT * FROM X WHERE no=
需要注意的是如果查询涉及LONG数据类型那么CREATE TABLEAS SELECT将不会工作
更改表定义
在建立表后有时候我们可能需要修改表比如更改列的定义更改缺省值增加新列删除列等等ORACLE使用ALTER TABLE语句来更改表的定义
增加列
语法
ALTER TABLE [schema] table_name ADD column_definition
例
ALTER TABLE orders ADD order_date DATE;
TABLE ALTER
对于已经存在的数据行新列的值将是NULL
更改列
语法
ALTER TABLE [schema] table_name MODIFY column_name new_attributes;
例
ALTER TABLE orders MODITY (quantity number()status varchar());
这个例子中我们修改了表orders将STATUS列的长度增加到将QUANTITY列减小到;
修改列的规则如下
可以增加字符串数据类型的列的长度数字数据类型列的精度
减少列的长度时该列应该不包含任何值所有数据行都为NULL
改变数据类型时该列的值必须是NULL
对于十进制数字可以增加或减少但不能降低他的精度
删除数据列
优化ORACLE数据库唯一的方法是删除列重新建立数据库在ORACLEi中有很多方法删除列你可以删除未用数据列或者可以标示该列为未用数据列然后删除
删除数据列的语法是
ALTER TABLE [schema] table_name DROP {COLUM column_names | (column_names)}[CASCADE CONSTRAINS]
要注意的是在删除列时关于该列的索引和完整性约束也同时删除注意关键字CASCADE CONSTRAINS如果删除的列是多列约束的一部分那么这个约束条件相对于其他列也同时删除
如果用户担心在大型数据库中删除列要花太多时间可以先将他们标记为未用数据列标记未用数据列的语法如下
ALTER TABLE [schema] table_name SET UNUSED {COLUM column_names | (column_names)}[CASCADE CONSTRAINS]
这个语句将一个或多个数据列标记为未用数据列但并不删除数据列中的数据也不释放占用的磁盘空间但是未用数据列在视图和数据字典中并不显示并且该数据列的名称将被删除新的数据列可以使用这个名称基于该数据列的索引约束统计等都将被删除
删除未用数据列的语句是
ALTER TABLE [schema] table_name DROP {UNUSED COLUM | COLUMN CONTINUE}
删除表和更改表名
删除表非常简单但它是一个不可逆转的行为
语法
DROP TABLE [schema] table_name [CASCADE CONSTRAINTS]
删除表后表上的索引触发器权限完整性约束也同时删除ORACLE不能删除视图或其他程序单元但oracle将标示他们无效如果删除的表涉及引用主键或唯一关键字的完整性约束时那么DROP TABLE语句就必须包含CASCADE CONSTRAINTS子串
更改表名
RENAME命令用于给表和其他数据库对象改名ORACLE系统自动将基于旧表的完整性约束索引权限转移到新表中ORACLE同时使所有基于旧表的数据库对象比如视图程序函数等为不合法
语法
RENAME old_name TO new_name;
例
SQL> RENAME orders TO purchase_orders;
TABLE RENAMED
截短表
TRUNCATE命令与DROP命令相似 但他不是删除整个数据表所以索引完整性约束触发器权限等都不会被删除缺省情况下将释放部分表和视图空间如果用户不希望释放表空间TRUNCATE语句中要包含REUSE STORAGE子串TRUNCATE命令语法如下
TRUNCATE {TABLE|CLUSTER} [schema] name {DROP|REUSE STORAGE}
例
SQL> TRUNCATE TABLE t;
TABLE truncate
管理视图
视图是一个或多个表中的数据的简化描述用户可以将视图看成一个存储查询（stored query)或一个虚拟表(virtual table)查询仅仅存储在oracle数据字典中实际的数据没有存放在任何其它地方所以建立视图不用消耗其他的空间视图也可以隐藏复杂查询比如多表查询但用户只能看见视图视图可以有与他所基于表的列名不同的列名用户可以建立限制其他用户访问的视图
建立视图
CREATE VIEW命令创建视图定义视图的查询可以建立在一个或多个表或其他视图上查询不能有FOR UPDATE子串在早期的ORACLEi版本中不支持ORDER BY子串现在的版本中CREATE VIEW可以拥有ORDER BY子串
例
SQL> CREATE VIEW TOP_EMP AS
SELECT empno EMPLOYEE_IDename EMPLOYEE_NAMEsalary
FROM emp
WHERE salary >
用户可以在创建视图的同时更改列名方法是在视图名后立即加上要命名的列名重新定义视图需要包含OR REPLACE子串
SQL> CREATE VIEW TOP_EMP
（EMPLOYEE_IDEMPLOYEE_NAMESALARY) AS
SELECT empno ename salary
FROM emp
WHERE salary >
如果在创建的视图包含错误在正常情况下视图将不会被创建但如果你需要创建一个带错误的视图必须在CREATE VIEW语句中带上FORCE选项如
CREATE FORCE VIEW ORDER_STATUS AS
SELECT * FROM PURCHASE_ORDERS
WHERE STATUS=APPPOVE;
SQL>/
warning :View create with compilation errors
这样将创建了一个名为ORDER_STATUS的视图但这样的视图的状态是不合法的如果以后状态发生变化则可以重新编译其状态也变成合法的
从视图中获得数据
从视图中获得数据与从表中获得数据基本一样用户可以在连接和子查询中使用视图也可以使用SQL函数以及所有SELECT语句的字串
插入更新删除数据
用户在一定的限制条件下可以通过视图更新插入删除数据如果视图连接多个表那么在一个时间里只能更新一个表所有的能被更新的列可以在数据字典USER_UPDATETABLE_COLUMNS中查到
用户在CREATE VIEW中可以使用了WITH子串WITH READ ONLY子串表示创建的视图是一个只读视图不能进行更新插入删除操作WITH CHECK OPTION表示可以进行插入和更新操作但应该满足WHERE子串的条件这个条件就是创建视图WHERE子句的条件比如在上面的例子中用户创建了一个视图TOP_EMP在这个视图中用户不能插入salary小于的数据行
删除视图
删除视图使用DROP VIEW命令同时将视图定义从数据字典中删除基于视图的权限也同时被删除其他涉及到该视图的函数视图程序等都将被视为非法
例
DROP VIEW TOP_EMP;
完整性约束
完整性约束用于增强数据的完整性Oracle提供了种完整性约束
Check
NOT NULL
Unique
Primary
Foreign key
完整性约束是一种规则不占用任何数据库空间完整性约束存在数据字典中在执行SQL或PL/SQL期间使用用户可以指明约束是启用的还是禁用的当约束启用时他增强了数据的完整性否则则反之但约束始终存在于数据字典中
禁用约束使用ALTER语句
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
或
ALTER TABLE policies DISABLE CONSTRAINT chk_gender
