关键字: oracle 模式 对象 索引 序列 同义词 用户 拥有 表 聚簇 链接
数据库模式对象:
- TABLE 表 用于存储数据的基本结构
- VIEW 视图 以不同的侧面反映表的数据，是一种逻辑上的表
- INDEX 索引 加快表的查询速度
- CLUSTER 聚簇 将不同表的字段并用的一种特殊结构的表集合
- SEQUENCE 序列 生成数字序列，用于在插入时自动填充表的字段
- SYNONYM 同义词 为简化和便于记忆，给对象起的别名
- DATABASE LINK 数据库链接 为访问远程对象创建的通道
- STORED PROCEDURE、FUNCTION 存储过程和函数 存储于数据库中的可调用的程序和函数
- PACKAGE、PACKAGE BODY 包和包体 将存储过程、函数及变量按功能和类别进行捆绑
- TRIGGER 触发器 由DML操作或数据库事件触发的事件处理程序
Oracle数据库的索引
索引(INDEX)是为了加快数据的查找而创建的数据库对象，特别是对大表，索引可以有效地提高查找速度，也可以保证数据的惟一性。索引是由Oracle自动使用和维护的，一旦创建成功，用户不必对索引进行直接的操作。索引是独立于表的数据库结构，即表和索引是分开存放的，当删除索引时，对拥有索引的表的数据没有影响。
在创建PRIMARY KEY和UNIQUE约束条件时，系统将自动为相应的列创建惟一(UNIQUE)索引。索引的名字同约束的名字一致。
索引有两种：B*树索引和位图(BITMAP)索引。
B*树索引是通常使用的索引，也是默认的索引类型。在这里主要讨论B*树索引。B*树是一种平衡2叉树，左右的查找路径一样。这种方法保证了对表的任何值的查找时间都相同。
B*树索引可分为：惟一索引、非惟一索引、一列简单索引和多列复合索引。
创建索引一般要掌握以下原则：只有较大的表才有必要建立索引，表的记录应该大于50条，查询数据小于总行数的2%～4%。虽然可以为表创建多个索引，但是无助于查询的索引不但不会提高效率，还会增加系统开销。因为当执行DML操作时，索引也要跟着更新，这时索引可能会降低系统的性能。一般在主键列或经常出现在WHERE子句或连接条件中的列建立索引，该列称为索引关键字。
索引的创建
创建索引不需要特定的系统权限。建立索引的语法如下：
CREATE [{UNIQUE|BITMAP}] INDEX 索引名 ON 表名(列名1[，列名2，...])；
其中：
UNIQUE代表创建惟一索引，不指明为创建非惟一索引。
BITMAP 代表创建位图索引，如果不指明该参数，则创建B*树索引。
列名是创建索引的关键字列，可以是一列或多列。
删除索引的语法是：
DROP INDEX 索引名；
删除索引的人应该是索引的创建者或拥有DROP ANY INDEX系统权限的用户。索引的删除对表没有影响。
创建和删除索引。
步骤1：创建索引：
- CREATE INDEX EMP_ENAME ON EMP(ENAME);
执行结果：
索引已创建。
步骤2：查询中引用索引：
- SELECT ENAME,JOB,SAL FROM EMP WHERE ENAME='SCOTT';
执行结果：
- ENAME JOB SAL
- ------------ ------------------- --------------------
- SCOTT ANALYST 3000
步骤3：删除索引：
- DROP INDEX EMP_ENAME;
执行结果：
索引已丢弃。
说明：本例创建的是B*树非惟一简单索引。索引关键字列是ENAME。在步骤2中，因为WHERE条件中出现了索引关键字，所以查询中索引会被自动引用，但是由于行数很少，因此不会感觉到查询速度的差别。
创建复合索引。
步骤1：创建复合索引：
- CREATE INDEX EMP_JOBSAL ON EMP(JOB,SAL);
执行结果：
索引已创建。
步骤2：查询中引用索引：
- SELECT ENAME,JOB,SAL FROM EMP WHERE JOB='MANAGER'AND SAL>2500;
执行结果：
- ENAME JOB SAL
- -------------- --------------------- -----------------
- BLAKE MANAGER 2850
- CLARK MANAGER 2850
- JONES MANAGER 2975
说明：在本例中创建的是包含两列的复合索引。JOB是主键，SAL是次键。WHERE条件中引用了JOB和SAL，而且是按照索引关键字出现的顺序引用的，所以在查询中，索引会被引用。
如下的查询也会引用索引：
- SELECT ENAME,JOB,SAL FROM EMP WHERE JOB='CLERK';
但以下查询不会引用索引，因为没有先引用索引关键字的主键：
- SELECT ENAME,JOB,SAL FROM EMP WHERE SAL>2500;
查看索引
通过查询数据字典USER_INDEXES可以检查创建的索引。
通过查询数据字典USER_IND_COLUMNS可以检查索引的列。
显示emp表的索引：
- SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='EMP';
执行结果：
- INDEX_NAME
- -------------------------------------- ---------------------------------------- -----------------------------------
- EMP_JOBSAL NORMAL NONUNIQUE
- PK_EMP NORMAL UNIQUE
说明：由本训练可见，emp表共有两个索引，其中EMP_JOBSAL是刚刚创建的，属于非惟一索引。PK_EMP为生成主键时系统创建的索引，属于惟一索引。
显示索引的列。
- SELECT COLUMN_NAME FROM USER_IND_COLUMNS
- WHERE INDEX_NAME='EMP_JOBSAL';
执行结果：
- COLUMN_NAME
- --------------------------------------------------------------------------------
- JOB
- SAL
说明：该查询显示出索引“EMP_JOBSAL”拥有两列：JOB和SAL。
序列的创建
序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。其主要的用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。
创建序列需要CREATE SEQUENCE系统权限。序列的创建语法如下：
CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n|NOMAXVALUE}]
[{MINVALUE n|NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}]；
其中：
INCREMENT BY 用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表序列的值是按照此步长递减的。
START WITH 定义序列的初始值(即产生的第一个值)，默认为1。
MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方；对于递减序列，最大值是-1。
MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方；对于递增序列，最小值是1。
CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值；对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。
CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。
删除序列的语法是：
DROP SEQUENCE 序列名;
删除序列的人应该是序列的创建者或拥有DROP ANY SEQUENCE系统权限的用户。序列一旦删除就不能被引用了。
序列的某些部分也可以在使用中进行修改，但不能修改SATRT WITH选项。对序列的修改只影响随后产生的序号，已经产生的序号不变。修改序列的语法如下：
ALTER SEQUENCE 序列名
[INCREMENT BY n]
[{MAXVALUE n|NOMAXVALUE}]
[{MINVALUE n|NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}]；
创建和删除序列。
步骤1：创建序列：
- CREATE SEQUENCE ABC INCREMENT BY 1 START WITH 10 MAXVALUE 9999999 NOCYCLE NOCACHE;
执行结果：
序列已创建。
步骤2：删除序列：
- DROP SEQUENCE ABC;
执行结果：
序列已丢弃。
说明：以上创建的序列名为ABC，是递增序列，增量为1，初始值为10。该序列不循环，不使用内存。没有定义最小值，默认最小值为1，最大值为9 999 999。
序列的使用
如果已经创建了序列，怎样才能引用序列呢？方法是使用CURRVAL和NEXTVAL来引用序列的值。
调用NEXTVAL将生成序列中的下一个序列号，调用时要指出序列名，即用以下方式调用:
序列名.NEXTVAL
CURRVAL用于产生序列的当前值，无论调用多少次都不会产生序列的下一个值。如果序列还没有通过调用NEXTVAL产生过序列的下一个值，先引用CURRVAL没有意义。调用CURRVAL的方法同上，要指出序列名，即用以下方式调用:
序列名.CURRVAL.
产生序列的值。
步骤1：产生序列的第一个值：
- SELECT ABC.NEXTVAL FROM DUAL;
执行结果：
- NEXTVAL
- ------------------
- 10
步骤2：产生序列的下一个值：
- SELECT ABC.NEXTVAL FROM DUAL;
执行结果：
- NEXTVAL
- -------------------
- 11
步骤3：产生序列的当前值：
- SELECT ABC.CURRVAL FROM DUAL;
执行结果：
- CURRVAL
- --------------------
- 11
说明：第一次调用NEXTVAL产生序列的初始值，根据定义知道初始值为10。第二次调用产生11，因为序列的步长为1。调用CURRVAL，显示当前值11，不产生新值。
序列的应用：产生图书序列号。
步骤1：创建序列：
- CREATE SEQUENCE BOOKID INCREMENT BY 1 START WITH 10 MAXVALUE 9999999 NOCYCLE NOCACHE;
执行结果：
序列已创建。
步骤2：使用序列生成新的图书编号：
- INSERT INTO 图书 VALUES('A'||TO_CHAR(BOOKID.NEXTVAL, 'fm0000'), '多媒体制作', 'INSERT INTO 图书 VALUES(‘A’||TO_CHAR(BOOKID.NEXTVAL, ‘fm0000’), ‘网页制作精选’,‘01’,‘刘莹’,4,26.50);
- 执行结果：
- 已创建 1 行。
- 已创建 1 行。
- 步骤2：显示插入结果：
- <PRE class=sql name="code">SELECT * FROM 图书;</PRE>
- <BR> 执行结果：
- <BR> <PRE class=sql name="code">图书 图书名称 出 作者 数量 单价
- ------- --------------------------------------------- ------------------------ ------------------- -----------
- 01', '高建',3,28.00);</PRE>
- <BR> A0001 计算机原理 01 刘勇 5 25.3
- <BR> A0002 C语言程序设计 02 马丽 1 18.75
- <BR> A0003 汇编语言程序设计 02 黄海明 15 20.18
- <BR> A0005 软件工程 01 冯娟 5 27.3
- <BR> A0010 多媒体制作 01 高建 3 28
- <BR> A0011 网页制作精选 01 刘莹 4 26.5
说明：根据序列定义可知，序列产生的初始值为10，函数TO_CHAR将数字10转换为字符。格式字符串“fm0000”表示转换为4位的字符串，空位用0填充。fm表示去掉转换结果的空格。故10将被转换成为字符串“0010”。连接运算后的图书编号为“A0010”。第二次调用则产生“A0011”，以此类推。
注意：通过查询看到插入的序号是连续的，但如果在插入的过程中使用了回退或发生了系统崩溃等情况，可能会产生序号的间隔。
查看序列
同过数据字典USER_OBJECTS可以查看用户拥有的序列。
通过数据字典USER_SEQUENCES可以查看序列的设置。
【训练1】 查看用户的序列：
SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER FROM
USER_SEQUENCES;
执行结果：
- SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
- ------------------------------------ ------------------- --------------------- ----------------------- ----------------------
- ABC 1 9999999 1 12
- BOOKID 1 9999999 1 12
说明：当前用户拥有两个序列：ABC和BOOKID。
同义词:
模式对象的同义词
同义词(SYNONYM)是为模式对象起的别名，可以为表、视图、序列、过程、函数和包等数据库模式对象创建同义词。同义词有两种：公有同义词和私有同义词。公有同义词是对所有用户都可用的。创建公有同义词必须拥有系统权限CREATE PUBLIC SYNONYM；创建私有同义词需要CREATE SYNONYM系统权限。私有同义词只对拥有同义词的账户有效，但私有同义词也可以通过授权，使其对其他用户有效。同义词通过给本地或远程对象分配一个通用或简单的名称，隐藏了对象的拥有者和对象的真实名称，也简化了SQL语句。
如果同义词同对象名称重名，私有同义词又同公有同义词重名，那么，识别的顺序是怎样的呢？如果存在对象名，则优先识别，其次识别私有同义词，最后识别公有同义词。比如,执行以下的SELECT语句：
SELECT * FROM ABC;
如果存在表ABC，就对表ABC执行查询语句；如果不存在表ABC，就去查看是否有私有同义词ABC，如果有就对ABC执行查询(此时ABC是另外一个表的同义词)；如果没有私有同义词ABC，则去查找公有同义词；如果找不到，则查询失败。
同义词的创建和使用
同义词的创建语法如下：
CREATE [PUBLIC] SYNONYM 同义词名
FOR [模式名.]对象名[@数据库链路名]；
其中：
PUBLIC代表创建公有同义词，若省略则代表创建私有同义词。
模式名代表拥有对象的模式账户名。
数据库链路名是指向远程对象的数据库链接。
删除同义词的语法如下
DROP SYNONYM 同义词名；
删除同义词的人必须是同义词的拥有者或有DROP ANY SYNONYM权限的人。删除同义词不会删除对应的对象。
创建同义词
步骤1：创建私有同义词：
- CREATE SYNONYM BOOK FOR 图书；
执行结果：
同义词已创建。
步骤2：创建公有同义词(先要获得创建公有同义词的权限)：
- CREATE PUBLIC SYNONYM BOOK FOR SCOTT.图书；
执行结果：
同义词已创建。
步骤3：使用同义词：
- SELECT * FROM BOOK;
执行结果：
- 图书 图书名称 出 作者 数量 单价
- -------- ---------------------------------------------- --- --------------- ------------- -----------
- A0001 计算机原理 01 刘勇 5 25.3
- A0002 C语言程序设计 02 马丽 1 18.75
- A0003 汇编语言程序设计 02 黄海明 15 20.18
- A0005 软件工程 01 冯娟 5 27.3
- A0010 多媒体制作 01 高建 3 28
- A0011 网页制作精选 01 刘莹 4 26.5
说明：对“BOOK”的查询等效于对“图书”的查询。如果同义词只是用户自己使用，则对象名前的模式名可以省略，如步骤1。如果是为其他用户使用，则必须添加模式名，如步骤2。
同义词的查看
通过查询数据字典USER_OBJECTS和USER_SYNONYMS，可以查看同义词信息。
查看用户拥有的同义词：
- SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='SYNONYM';
执行结果：
- OBJECT_NAME
- --------------------------------------------------------------------------------
- BOOK
- QHBOOK
系统定义同义词
系统为常用的对象预定义了一些同义词，利用它们可以方便地访问用户的常用对象。这些同义词如下所示。
- DICT DICTIONARY 数据字典
- CAT USER_CATALOG 用户拥有的表、视图、同义词和序列
- CLU USER_CLUSTERS 用户拥有的聚簇
- IND USER_INDEXES 用户拥有的索引
- OBJ USER_OBJECTS 用户拥有的对象
- SEQ USER_SEQUENCES 用户拥有的序列
- SYN USER_SYNONYMS 用户拥有的私有同义词
- COLS USER_TAB_COLUMNS 用户拥有的表、视图和聚簇的列
- TABS USER_TABLES 用户拥有的表
查看用户拥有的表：
- SELECT TABLE_NAME FROM TABS;
执行结果：
- TABLE_NAME
- ------------------------------
- BONUS
- DEPT
- EMP
聚簇
所谓聚簇(CLUSTER)，形象地说，就是生长在一起的表。聚簇包含一张或多张表，表的公共列被称为聚簇关键字，在公共列上具有同一值的列物理上存储在一起。那么在什么情况下需要创建聚簇呢？通常在多个表有共同的列时，应使用聚簇。比如有一张学生基本情况表，其中包含学生的学号、姓名、性别、住址等信息。另外，还设计了一张学生成绩表，其中除了包含学生成绩，也包含学生的学号、姓名、性别。那么这两张表共同的列就可以创建成聚簇。这样两张表的共同的学号、姓名和性别，就存放在了一起，相同的值只存放一次。如果两个表通过聚簇列进行联合，则会大大提高查询的速度，但对于插入等操作则会降低效率。
创建聚簇后，要创建使用聚簇的表，对聚簇还应该建立索引。如果不对聚簇建立索引，则不能对聚簇表进行插入、修改和删除操作。
创建聚簇需要CREATE CLUSTER系统权限。创建聚簇的语法如下：
CREATE CLUSTER 聚簇名(列名1 [，列名2]...)
SIZE n
TABLESPACE 表空间名；
列名是构成聚簇关键字的列集合。
SIZE 指明存储所有含有相同聚簇关键字的行的平均存储空间数(聚簇逻辑块的大小)。
TABLESPACE定义聚簇使用的表空间。
删除聚簇使用如下语法：
DROP CLUSTER 聚簇名 [INCLUDING TABLES [CASCADE CONSTRAINTS]];
其中：
INCLUDING TABLES表示一同删除聚簇表。如果不指明此选项，则必须手工删除聚簇表后才能删除聚簇本身。
CASCADE CONSTRAINTS表示删除聚簇表时，一起删除同其他表之间的约束关系。
创建和使用聚簇。
步骤1：创建聚簇：
- CREATE CLUSTER COMM(STUNO NUMBER(5),STUNAME VARCHAR2(10),SEX VARCHAR2(2))
- SIZE 500
- TABLESPACE USERS;
执行结果：
已创建数据簇。
步骤2：创建第一张聚簇表：
- CREATE TABLE STUDENT(
- STUNO NUMBER(5),
- STUNAME VARCHAR2(10),
- SEX VARCHAR2(2),
- ADDRESS VARCHAR2(20),
- E_MAIL VARCHAR2(20)
- )
- CLUSTER COMM(STUNO,STUNAME,SEX);
执行结果：
表已创建。
步骤3：创建第二张聚簇表：
- CREATE TABLE SCORE(
- STUNO NUMBER(5),
- STUNAME VARCHAR2(10),
- SEX VARCHAR2(2),
- CHINESE NUMBER(3),
- MATH NUMBER(3),
- ENGLISH NUMBER(3) )
- CLUSTER COMM(STUNO,STUNAME,SEX);
执行结果：
表已创建。
步骤4：为聚簇创建索引：
- CREATE INDEX INX_COMM ON CLUSTER COMM;
步骤5：向表中插入数据：
- INSERT INTO STUDENT VALUES(10001,'黄凯','男','宝安','HK123@163.COM');
- INSERT INTO STUDENT VALUES(10002,'苏丽','女','罗湖','SL99@163.COM');
- INSERT INTO STUDENT VALUES(10003,'刘平平','男','南山','PP2003@SHOU.COM');
- INSERT INTO SCORE VALUES(10001,'黄凯','男',70,85,93);
- INSERT INTO SCORE VALUES(10002,'苏丽','女',65,74,83);
- INSERT INTO SCORE VALUES(10003,'刘平平','男',88,75,69);
执行结果：略。
步骤6：删除聚簇及聚簇表：
- DROP CLUSTER COMM INCLUDING TABLES CASCADE CONSTRAINTS;
执行结果：
数据簇已丢弃。
说明：在本例的两个表中，为其三个共同列STUNO、STUNAME和SEX创建了聚簇，在创建表时说明了使用的聚簇，创建聚簇后为其创建了索引，然后插入了一些数据。
数据库链接
数据库链接(DATABASE LINK)是在分布式环境下，为了访问远程数据库而创建的数据通信链路。数据库链接隐藏了对远程数据库访问的复杂性。通常，我们把正在登录的数据库称为本地数据库，另外的一个数据库称为远程数据库。有了数据库链接，可以直接通过数据库链接来访问远程数据库的表。常见的形式是访问远程数据库固定用户的链接，即链接到指定的用户，创建这种形式的数据库链接的语句如下：
CREATE DATABASE LINK 链接名 CONNECT TO 账户 IDENTIFIED BY 口令
USING 服务名;
创建数据库链接，需要CREATE DATABASE LINK系统权限。
数据库链接一旦建立并测试成功，就可以使用以下形式来访问远程用户的表。
表名@数据库链接名
在局域网上创建和使用数据库链接。
步骤1：创建远程数据库的服务名，假定局域网上另一个数据库服务名为MYDB_REMOTE。
步骤2：登录本地数据库SCOTT账户，创建数据库链接：
- CONNECT SCOTT/TIGER@MYDB
- CREATE DATABASE LINK abc CONNECT TO scott IDENTIFIED BY tiger USING 'MYDB_REMOTE';
执行结果为：
数据库链接已创建。
步骤3：查询远程数据库的数据：
- SELECT * FROM emp@abc;
结果略。
步骤4：一个分布查询：
- SELECT ename,dname FROM emp@abc e,dept d WHERE e.deptno=d.deptno;
结果略。
说明：在本例中，远程数据库服务名是MYDB_REMOTE，创建的数据库链接名称是abc.emp@abc表示远程数据库的emp表。步骤4是一个联合查询，数据来自本地服务器的dept表和远程服务器的emp表。
扫一扫
489
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
