Oracle Lesson 4 数据库对象

Oracle Lesson 4 数据库对象

                                                               Made by Zhangxu 2008.2.28

数据库对象简介

Oracle数据库对象又成为模式对象，数据库对象是逻辑结构的集合，

最基本的数据库对象是表！其他数据库对象还包括有同义词(sysnonym)

视图(view)，索引(index)以及序列(seguence)等等!

数据库对象的分类

同义词

同义词是现有对象的一个 别名，使用同义词有以下作用：

–         简化 SQL 语句

–         隐藏对象的名称和所有者

–         提供对对象的公共访问

同义词主要有两种类型，一种是私有同义词，另外一种是公有同义词。

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

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

如图所示：

创建或者替换现有的同义词：

Create or replace sysnonym emp__syn for SCOOT.emo;

删除同义词 :

Drop sysnonym emp;

Drop pubic sysnonym emp_syn; // 删除公有同义词

序列

序列是用于生成唯一，连续序号的对象，序列可以是升序的也可以是降序的

使用 Create sequence 语法来创建序列（类似与 Sql server 中的 identity 自动增长列）。

如： SQL> CREATE SEQUENCE toys_seq

       START WITH 10// 起始 10

       INCREMENT BY 10// 增长 10

       MAXVALUE 2000// 最大值 2000

       MINVALUE 10// 最小值 10

       NOCYCLE

       CACHE 10;

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

–         NEXTVAL 返回序列的下一个值

–         CURRVAL 返回序列的当前值

如：

SQL> INSERT INTO toys (toyid, toyname, toyprice)

     VALUES ( toys_seq. NEXTVAL, ‘ TWENTY ’ , 25);

SQL> INSERT INTO toys (toyid, toyname, toyprice)

     VALUES ( toys_seq .NEXTVAL , ’ MAGIC PENCIL ’ , 75);

SQL> SELECT toys_seq. CURRVAL FROM dual

检索当前值！

更改和删除序列则是通过 alter 和 drop 来实现

使用 ALTER SEQUENCE 语句修改序列，

不能更改序列的 START WITH 参数

SQL> ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE;

使用 DROP SEQUENCE 语句删除序列

SQL> DROP SEQUENCE toys_seq;

 

视图

视图以经过定制的方式显示来自一个或多个表的数据

视图可以视为“ 虚拟表” 或“ 存储的查询”

创建视图所依据的表称为“ 基表”

视图的优点有：

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

–         隐藏的数据的复杂性

–         简化的用户的SQL 命令

–         隔离基表结构的改变

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

创建视图

CREATE VIEW stud_view

AS SELECT studno, studname, subno

FROM Stud_details;

可以直接使用Select * from stud_viewl来查看视图

创建视图的语法为：

CREATE [OR REPLACE] [FORCE] VIEW

    view_name [(alias[, alias]...)]

 AS select_statement

 [WITH CHECK OPTION]

 [WITH READ ONLY];

使用 WITH CHECK OPTION 选项创建视图

CREATE OR REPLACE VIEW pause_view AS

SELECT * FROM order_master WHERE ostatus = 'p'

WITH CHECK OPTION CONSTRAINT chk_pv;

 

使用 ORDER BY 子句创建视图

CREATE OR REPLACE VIEW ord_ven AS

SELECT * FROM vendor_master ORDER BY venname;

 

创建带有错误的视图

CREATE FORCE VIEW ven AS

SELECT * FROM venmaster;

联接视图

CREATE VIEW Stud_sub_view AS

SELECT Studno, Studname, Submrks, Subname

FROM Stud_details, Sub_Details

WHERE Stud_details.Subno=Sub_details.Subno;

创建外连接视图

如图所示：

视图上的DML 语句

在视图上也可以使用修改数据的 DML 语句，如 INSERT 、 UPDATE 和 DELETE 等 … 但是有些限制

如：

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

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

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

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

视图中的函数

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

CREATE VIEW item_view AS

SELECT itemcode, LOWER(itemdesc) item_desc

FROM itemfile

使用 DROP VIEW 语句删除视图

SQL> DROP VIEW toys_view;

索引

使用索引有如下优点：

•         索引是与表相关的一个可选结构

•         用以提高 SQL 语句执行的性能

•         减少磁盘 I/O

•         使用 CREATE INDEX 语句创建索引

•         在逻辑上和物理上都独立于表的数据

•         Oracle 自动维护索引

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

如：

创建索引：

首先创建标准的索引 ： SQL> CREATE INDEX item_index ON itemfile (itemcode)

     TABLESPACE index_tbs;

重建索引

SQL> ALTER INDEX item_index REBUILD;

删除索引

SQL> DROP INDEX item_index;

唯一索引

唯一索引确保在定义索引的列中没有重复值

Oracle 自动在表的主键列上创建唯一索引

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

SQL> CREATE UNIQUE INDEX item_index

     ON itemfile (itemcode);

 

组合索引

组合索引是在表的多个列上创建的索引

索引中列的顺序是任意的

如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度

SQL> CREATE INDEX comp_index

     ON itemfile(p_category, itemrate);

 

反向键索引

反向键索引反转索引列键值的每个字节

通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上

创建索引时使用 REVERSE关键字

SQL> CREATE INDEX rev_index

     ON itemfile (itemcode) REVERSE;

SQL> ALTER INDEX rev_index REBUID NOREVERSE;

 

位图索引

位图索引适合创建在低基数列上

位图索引不直接存储ROWID，而是存储字节位到ROWID的映射

减少响应时间

节省空间占用

SQL> CREATE BITMAP INDEX bit_index

     ON order_master (orderno);

 

索引组织表

索引组织表的数据存储在与其关联的索引中

索引中存储的是行的实际数据，而不是ROWID

基于主键访问数据

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

SQL> CREATE TABLE ind_org_tab (

       vencode NUMBER(4) PRIMARY KEY,

       venname VARCHAR2(20)

     )

     ORGANIZATION INDEX;

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

如图：

基于函数的索引

基于一个或多个列上的函数或表达式创建的索引

表达式中不能出现聚合函数

不能在LOB类型的列上创建

创建时必须具有 QUERY REWRITE 权限

SQL> CREATE INDEX lowercase_idx

     ON toys (LOWER(toyname));

SQL> SELECT toyid FROM toys

     WHERE LOWER(toyname)='doll';

 

索引中的分区

•         可以将索引存储在不同的分区中

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

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

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

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

 

获取索引的信息

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

–         USER_INDEXES － 用户创建的索引的信息

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

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

SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME

     FROM USER_IND_COLUMNS

     ORDER BY INDEX_NAME, COLUMN_POSITION;

 

总结

同义词是现有数据库对象的别名

序列用于生成唯一、连续的序号

视图是基于一个或多个表的虚拟表

索引是与表相关的一个可选结构，用于提高 SQL 语句执行的性能

索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引

索引组织表基于主键访问数据

 

示例代码

-- ϰ 1

CREATE TABLE ticket_header(

 fleet_id number(5),

 ticket_no varchar2(6),

 origin varchar2(6),

 destination varchar2(6),

 adults number(1)

);

 

CREATE SYNONYM tick FOR ticket_header;

 

-- ϰ 2

CREATE PUBLIC SYNONYM pub_tick FOR ticket_header;

 

CREATE PUBLIC SYNONYM pub_tick FOR accp.ticket_header;

 

SELECT * FROM pub_tick;

 

-- ϰ 3

DROP PUBLIC SYNONYM pub_tick;

 

DROP SYNONYM tick;

 

-- ϰ 4

CREATE SEQUENCE new_seq

 INCREMENT BY    2

 START WITH 25

 MAXVALUE    75

 MINVALUE    25

 CYCLE

 NOCACHE ;

 

-- ϰ 5

SELECT new_seq.NEXTVAL FROM dual;

 

SELECT new_seq.CURRVAL FROM dual;

 

-- ϰ 6

ALTER SEQUENCE new_seq INCREMENT BY 5;

 

SELECT new_seq.NEXTVAL FROM dual;

 

-- ϰ 7

DROP SEQUENCE new_seq;

 

-- ϰ 8

CREATE TABLE fleet_header(

 day date, name varchar2(20),

 route_id number(5), fleet_id number(5));

 

CREATE VIEW fleet (day, route_id)

AS SELECT day, route_id FROM fleet_header;

 

SELECT * FROM fleet;

 

-- ϰ 9

CREATE OR REPLACE VIEW fleet AS SELECT * FROM fleet_header;

 

-- ϰ 10

CREATE FORCE VIEW myview AS SELECT * FROM product;

 

-- ϰ 11

CREATE VIEW double(fleet_id,origin, ticket_no, destination)

AS SELECT f.fleet_id, origin, ticket_no, destination

FROM fleet_header f, ticket_header t

WHERE f.fleet_id = t.fleet_id;

 

-- ϰ 12

DROP VIEW double;

 

-- ϰ 13

CREATE INDEX aud_index ON ticket_header(adults);

 

-- ϰ 14

CREATE TABLE place_header(

 place_id number primary key,

 Place_name varchar2(20),

 Bus_station varchar2(20));

 

CREATE UNIQUE INDEX place_ind ON place_header(place_name);

 

-- ϰ 15

CREATE TABLE route_header(

 route_id number,

 route_no varchar2(10),

 cat_code char(2)

);

CREATE INDEX comp_index ON route_header(route_id, route_no);

 

-- ϰ 16

CREATE INDEX rev_ind ON route_header(route_no) REVERSE;

 

-- ϰ 17

CREATE BITMAP INDEX bit_ind ON route_header(cat_code);

 

-- ϰ 18

CREATE INDEX ucase_name_ind ON fleet_header (UPPER(name));

 

SELECT * FROM fleet_header WHERE UPPER(name)='SMITH';

 

-- ϰ 19

DROP INDEX emp_ind1;

 

 

-- 1.

CREATE VIEW vendor_view

AS SELECT vencode, venname, venadd1||venadd2||venadd3 venaddress

FROM vendor_master;

 

-- 2.

CREATE SEQUENCE my_seq START WITH 1000 INCREMENT BY 10

MINVALUE 1000 MAXVALUE 1100, CYCLE;

 

-- 3.

CREATE UNIQUE INDEX ind_ord_item

ON order_detail (orderno, itemcode);

 

 

 

 

-- 建立主键

alter table ORDER_MASTER

 add constraint pk_ord_master primary key (ORDERNO);

 

alter table VENDOR_MASTER

 add constraint pk_ven_master primary key (VENCODE);

 

-- 重新编译视图

alter view VEN_ORD_VIEW compile;

 

-- 查看可更新的列

SELECT * FROM user_updatable_columns WHERE table_name ='VEN_ORD_VIEW';

 

UPDATE ven_ord_view SET odate = odate+1 WHERE vencode ='V003';

 

UPDATE ven_ord_view SET venname = 'Michael' WHERE vencode = 'V004';