oracle 9数据库,Oracle学习笔记9 数据库对象

Oracle学习笔记九 数据库对象

Oracle 数据库对象又称模式对象，数据库对象是逻辑结构的集合，最基本的数据库对象是表。

其他数据库对象包括：

同义词

同义词是现有对象的一个别名。它的作用有：

1.简化SQL语句

2.隐藏对象的名称和所有者(隐藏原始对象内容)

3.提供对对象的公共访问

同义词共有两种类型：

公有同义词可被所有的数据库用户访问。

私有同义词只能在其模式内访问，且不能与当前模式的对象同名。

创建或替换现有同义词

create or replace synonym emp_syn for scott.emp;

删除同义词

dropsynonym emp;drop public synonym emp_syn

使用同义词

Create public synonym Newer forscott.emp2;Select * from Newer ;

序列

序列是用于生成唯一、连续序号的对象，序列可以是升序的，也可以是降序的，可以用于表示数据的数量。我们可以使用CREATE SEQUENCE语句创建序列。

Createsequence 序列名

Startwith 1 --指序列开始大小，默认为1

Increment by 1 --每次的增量，默认为1

Maxvalue 2000 --序列的最大数

Minvalue 1 --序列的最小数

Nocycle --当序列最大了将不再生成整数(默认)

Cache 10; --保留在内存中整数的个数，默认20

注意：序列在使用的时候，如果给的是主键，为防止出现不连续的情况，应该设置为NOCACHE。

从user_sequences中查出所有的序列信息。

select * from user_sequences;

通过序列的伪列来访问序列的值

1.NEXTVAL 返回序列的下一个值

2.CURRVAL 返回序列的当前值

注意：序列在使用的时候，如果给的是主键，为防止出现不连续的情况，应该设置为NOCACHE。

INSERT INTOtoys (toyid, toyname, toyprice)VALUES ( toys_seq.NEXTVAL, ‘TWENTY’, 25);INSERT INTOtoys (toyid, toyname, toyprice)VALUES ( toys_seq.NEXTVAL, ’MAGIC PENCIL’, 75);--toys_seq.NEXTVAL 指定序列的下一个值

SELECT toys_seq.CURRVAL FROM dual; --检索序列的当前值

使用ALTER SEQUENCE语句修改序列，不能更改序列的START WITH参数

ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE;

使用DROP SEQUENCE语句删除序列

DROP SEQUENCE toys_seq;

视图

视图以经过定制的方式显示来自一个或多个表的数据，视图可以视为“虚拟表”或“存储的查询”，创建视图所依据的表称为“基表”。

视图的优点有：

1.提供了另外一种级别的表安全性

2.隐藏的数据的复杂性

3.简化的用户的SQL命令

4.隔离基表结构的改变

5.通过重命名列，从另一个角度提供数据

例：

CREATE VIEW stud_view AS SELECT studno, studname, subno FROM Stud_details;

通过这个视图，查询到的结果：

创建视图

创建视图的语法：

CREATE [OR REPLACE] [FORCE] VIEWview_name[(alias[, alias]...)]ASselect_statement[WITH CHECK OPTION][CONSTRAINT C_NAME]

[WITH READ ONLY]; --禁止对表进行更新删除操作--W ITH CHECK OPTION CONSTRAINT _NAME ：表示增加一个检查约束，INSERT和UPDATE时候必须要求:--INSERT时候要求插入的数据必须符合定义时候设置的where条件一致，UPDATE 时候只能改除where条件列以外的其他数据列或者修改的条件列的值符合where条件--

使用 WITH CHECK OPTION 选项创建视图

create or replace view pause_view as

select * from order_master WHERE ostatus = 'p'

with check option constaint chk_pv;

使用 ORDER BY 子句创建视图

create or replace view ord_ven as

select * from vendor_master order by venname;

联接视图:两个表的结果

create view Stud_sub_view as

selectStudno, Studname, Submrks, SubnamefromStud_details, Sub_Detailswhere Stud_details.Subno=Sub_details.Subno;

创建外联接视图

CREATE VIEW ven_ord_outj_view AS

SELECTvm.vencode, venname, orderno, odate, ostatusFROMvendor_master vm, order_master omWHERE vm.vencode = om.vencode(+);

等同于

SELECTvm.vencode, venname, orderno, odate, ostatusFROM vendor_master vm LEFT OUTER JOINorder_master omON vm.vencode = om.vencode;

视图上的DML语句

在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE

视图上的DML语句有如下限制：

1.只能修改一个底层的基表

2.如果修改违反了基表的约束条件，则无法更新视图

3.如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句，则将无法更新视图

4.如果视图包含伪列或表达式，则将无法更新视图

如果往视图中添加数据，数据可以添加到基表，但如果插入的数据不符合视图的where等条件的要求，则视图中不会显示。如果希望插入的数据要符合视图定义的时候，where要确则需要添加参数。

如果要从视图中删除基表的数据，被删除的这条数据必须符合视图的查询条件，才能通过视图删除该数据。

键保留表

键保留表是理解连接视图修改限制的一个基本概念。该表的主键列全部显示在视图中,并且它们的值在视图中都是唯一且非空的。也就是说，表的键值在一个连接视图中也是键值，那么就称这个表为键保留表。

因为 Studno 既是Stud_details 中的主键，也是联接结果中的主键，因此Stud_details是键保留表

视图中的函数

视图中可以使用单行函数、分组函数和表达式

CREATE VIEW item_view AS

SELECT itemcode, LOWER(itemdesc) item_descFROM itemfile;

删除视图

使用DROP VIEW语句删除视图

DROP VIEW toys_view;

索引

索引是与表相关的一个可选结构，使用索引可以提高 SQL 语句执行的性能，减少磁盘I/O，索引在逻辑上和物理上都独立于表的数据，Oracle 能够自动维护索引。

索引的原理：索引的工作原理是与一个叫做ROWID的概念紧密相关的，Oracle在创建数据表时，默认会为每个数据表建立一个隐含字段，叫ROWID。在向数据表中插入记录时，系统将自动为每条记录分配惟一的一个ROWID号，利用这个ROWID号，就可以快速定位到记录。

操作索引

使用 CREATE INDEX 语句创建索引：

创建标准索引：Create index 索引名on 表名(列名);

CREATE INDEX item_index ONitemfile (itemcode)

TABLESPACE index_tbs;

重建索引

ALTER INDEX item_index REBUILD;

删除索引

DROP INDEX item_index;

索引的类型

索引有各种类型，除了标准索引外，还有一些特殊类型的索引：

唯一索引

唯一索引确保在定义索引的列中没有重复值，Oracle 自动在表的主键列上创建唯一索引

使用CREATE UNIQUE INDEX语句创建唯一索引

CREATE UNIQUE INDEXitem_indexON itemfile (itemcode);

组合索引

组合索引是在表的多个列上创建的索引，索引中列的顺序是任意的，如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度。

CREATE INDEXcomp_indexON itemfile(p_category, itemrate);

反向键索引

反向键索引反转索引列键值的每个字节，通常建立在值是连续增长的列上，可以使数据均匀地分布在整个索引上

创建索引时使用REVERSE关键字

CREATE INDEXrev_indexON itemfile (itemcode) REVERSE;

ALTER INDEX rev_index REBUID NOREVERSE;

位图索引

位图索引适合创建在低基数列上，位图索引不直接存储ROWID，而是存储字节位到ROWID的映射，使用位图索引可以减少响应时间，节省空间占用。

CREATE BITMAP INDEXbit_indexON order_master (orderno);

索引组织表

索引组织表的数据存储在与其关联的索引中，索引中存储的是行的实际数据，而不是ROWID，它基于主键访问数据。

CREATE TABLE 命令与 ORGANIZATION INDEX 子句一起用于创建索引组织表。

CREATE TABLEind_org_tab (

vencodeNUMBER(4) PRIMARY KEY,

vennameVARCHAR2(20)

)

ORGANIZATIONINDEX;

普通表与索引组织表的比较

普通表

索引组织表

ROWID 唯一地标识行

主键唯一地标识行

隐式的 ROWID 列

没有隐式的 ROWID 列

基于 ROWID 的访问

基于主键的访问

顺序扫描返回所有行

完全索引扫描返回所有行，并按主键顺序排列

支持分区

不支持分区

基于函数的索引

基于一个或多个列上的函数或表达式创建的索引，表达式中不能出现聚合函数，不能在LOB类型的列上创建，创建时必须具有 QUERY REWRITE 权限，当查询使用了函数的时候，索引将不再使用，所以要建立函数索引。

CREATE INDEXlowercase_idxON toys (LOWER(toyname));

SELECT toyid FROMtoysWHERE LOWER(toyname)='doll';

索引中的分区

我们可以将索引存储在不同的分区中。

与分区有关的索引有三种类型：

1.局部分区索引 － 在分区表上创建的索引，在每个表分区上创建独立的索引，索引的分区范围与表一致

2.全局分区索引 － 在分区表或非分区表上创建的索引，索引单独指定分区的范围，与表的分区范围或是否分区无关

3.全局非分区索引 － 在分区表上创建的全局普通索引，索引没有被分区

获取索引的信息

与索引有关的数据字典视图有：

1.USER_INDEXES － 用户创建的索引的信息

2.USER_IND_PARTITIONS － 用户创建的分区索引的信息

3.USER_IND_COLUMNS － 与索引相关的表列的信息

SELECTINDEX_NAME, TABLE_NAME, COLUMN_NAMEFROMUSER_IND_COLUMNSORDER BY INDEX_NAME, COLUMN_POSITION;