Oracle基础知识.docx

Oracle基础知识.docx

《Oracle基础知识.docx》由会员分享，可在线阅读，更多相关《Oracle基础知识.docx（17页珍藏版）》请在冰豆网上搜索。

Oracle基础知识

Oracle基础

1.ORACLE简介

Oracle数据库管理系统是一个以关系型和面向对象为中心管理数据的数据库管理软件系统，其在管理信息系统、企业数据处理、因特网及电子商务等领域有着非常广泛的应用。

因其在数据安全性与数据完整性控制方面的优越性能，以及跨操作系统、跨硬件平台的数据互操作能力，使得越来越多的用户将Oracle作为其应用数据的处理系统。

Oracle数据库是基于“客户端／服务器”（Client/Server）模式结构。

客户端应用程序执行与用户进行交互的活动。

其接收用户信息，并向“服务器端”发送请求。

服务器系统负责管理数据信息和各种操作数据的活动。

2.ORACLE体系结构

（1）Oracle数据库有如下几个强大的特性：

Ø支持多用户、大事务量的事务处理

Ø数据安全性和完整性的有效控制

Ø支持分布式数据处理

Ø可移植性很强

（2）OracleServer

通常所说的oracle主要是指OracleServer即oracle数据库服务器。

OracleServer主要是由实例（instance）和数据库（database）两部组成.一般来说一个实例只能加载一个数据库，在特殊情况下，比如RAC环境，一个库可以有多个例程访问。

实例：

一组内存结构（SGA）和后台进程的集合。

数据库：

一组数据文件的集合。

主要由数据文件，控制文件和重做日志文件.

*实例是一组内存结构（SGA）和后台进程的集合.

*一个实例只能打开一个库.

ORACLE体系结构

3.数据库对象

❑模式是对用户所创建的数据库对象（模式对象）的总称

❑通常一个模式对应一个用户。

❑主要介绍数据库对象

◆用户

◆表

◆视图

◆物化视图

◆索引

◆序列

◆同义词

◆触发器

◆存储过程

4.用户和权限

Oracle使用角色的方法来限定各种用户的权力，在系统中可以有许多用户，各种用户有各种不同的角色，拥有不同的权力。

实践表明与其很明确的赋一组权限给一个角色，不如赋给一个角色，然后把这个角色赋给一组用户。

这样就可以实现动态的权限管理，当这组用户的权限必须改变时，只需改变角色的权限就可以了。

❑要连接到Oracle数据库，就需要创建一个用户帐户

❑每个用户都有一个默认表空间和一个临时表空间

❑CREATEUSER命令用于创建新用户

CREATEUSERusername

IDENTIFIEDBYpassword

[DEFAULTTABLESPACEtablespace]

[TEMPORARYTABLESPACEtablespace];

用户和权限

❑权限指的是执行特定命令或访问数据库对象的权利

❑权限有两种类型，系统权限和对象权限

Ø系统权限允许用户执行某些数据库操作，如创建表就是一个系统权限

Ø对象权限允许用户对数据库对象（如表、视图、序列等）执行特定操作

❑角色是一组相关权限的组合，可以将权限授予角色，再把角色授予用户，以简化权限管理。

5.表

（1）oracle的基本数据类型

字符型

Char定长最大2000个字符

示例：

char（10）‘数据库’前6个字符存放‘数据库’，后面以4个空格填充补全。

Varchar2（）变长最大4000个字符

Char查询的速度快，但浪费空间，适用于查询比较多的数据

Varchar节省空间，但查询速度慢

Clob字符型大对象最大4G

数字型

Number范围-10的38次方到10的38次方

可以表示整数，也可以表示小数

Number（5,2）表示一个小数有5位有效数字，2位小数

范围：

-999.99到999.99

Number（4）

表示一个4位整数

范围：

-9999到9999

日期类型

Date包含年月日和时分秒

Timestamp可以精确到毫秒

二进制数据类型

Blob用于存放图片和声音4G

（2）数据完整性约束

表的记录（行row）和字段（列column）构成，是数据库中存储数据的结构。

要进行数据的存储和管理，首先要在数据库中创建表，即表的字段（列）结构。

表的数据有一定的取值范围和联系，多表之间的数据有时也有一定的参照关系。

在创建表和修改表时，可通过定义约束条件来保证数据的完整性和一致性。

约束条件是一些规则，在对数据进行插入、删除和修改时要对这些规则进行验证，从而起到约束作用。

完整性包括数据完整性和参照完整性，数据完整性定义表数据的约束条件，参照完整性定义数据之间的约束条件。

数据完整性由主键（PRIMARYKEY）、非空（NOTNULL）、惟一（UNIQUE）和检查（CHECK）约束条件定义，参照完整性由外键（FOREIGNKEY）约束条件定义。

（3）创建表的语法

表的创建需要CREATETABLE系统权限，表的基本创建语法如下：

CREATETABLE表名

（列名数据类型（宽度）[DEFAULT表达式][COLUMN

CONSTRAINT],

...

[TABLECONSTRAINT]

[TABLE_PARTITION_CLAUSE]

）；

通过子查询创建表:

如果要创建一个同已有的表结构相同或部分相同的表，可以采用以下的语法：

CREATETABLE表名（列名...）ASSQL查询语句;

（4）修改表结构

•增加新列

ALTERTABLE表名

ADD列名数据类型[DEFAULT表达式][COLUMNCONSTRAINT];

如果要为表同时增加多列，可以按以下格式进行：

ALTERTABLE表名

ADD（列名数据类型[DEFAULT表达式][COLUMN

CONSTRAINT]...）;

•修改列

ALTERTABLE表名

MODIFY列名数据类型[DEFAULT表达式][COLUMNCONSTRAINT]

如果要对表同时修改多列，可以按以下格式进行：

ALTERTABLE表名

MODIFY（列名数据类型[DEFAULT表达式][COLUMNCONSTRAINT]...）;

其中，列名是要修改的列的标识，不能修改。

如果要改变列名，只能先删除该列，然后重新增加。

其他部分都可以进行修改，如果没有给出新的定义，表示该部分属性不变。

•删除列

ALTERTABLE表名

DROPCOLUMN列名[CASCADECONSTRAINTS]；

如果要同时删除多列，可以按以下格式进行：

ALTERTABLE表名

DROP（COLUMN列名数据类型[DEFAULT表达

式][COLUMNCONSTRAINT]...）

[CASCADECONSTRAINTS];

当删除列时，列上的索引和约束条件同时被删除。

但如果列是多列约束的一部分，则必须指定CASCADECONSTRAINTS才能删除约束条件。

（5）分区表

在某些场合会使用非常大的表，比如人口信息统计表。

如果一个表很大，就会降低查询的速度，并增加管理的难度。

一旦发生磁盘损坏，可能整个表的数据就会丢失，恢复比较困难。

根据这一情况，可以创建分区表，把一个大表分成几个区（小段），对数据的操作和管理都可以针对分区进行，这样就可以提高数据库的运行效率。

分区可以存在于不同的表空间上，提高了数据的可用性。

（6）表分区的依据

分区的依据可以是一列或多列的值，这一列或多列称为分区关键字或分区列。

所有分区的逻辑属性是一样的（列名、数据类型、约束条件等），但每个分区可以有自己的物理属性（表空间、存储参数等）。

分区有三种：

范围分区、哈希分区和列表分区。

范围分区（RANGEPARTITIONING）：

根据分区关键字值的范围建立分区。

比如，根据省份为人口数据表建立分区。

哈希分区（HASHPARTITIONING）：

在分区列上使用HASH算法进行分区。

列表分区（ListPARTITIONING）：

根据预先定义好的分区键数值确定记录被分配到哪个分区。

（7）视图的概念

视图是基于一张表或多张表或另外一个视图的逻辑表。

视图不同于表，视图本身不包含任何数据。

表是实际独立存在的实体，是用于存储数据的基本结构。

而视图只是一种定义，对应一个查询语句。

视图的数据都来自于某些表，这些表被称为基表。

通过视图来查看表，就像是从不同的角度来观察一个（或多个）表。

视图有如下一些优点：

*可以提高数据访问的安全性，通过视图往往只可以访问数据库中表的特定部分，限制了用户访问表的全部行和列。

*简化了对数据的查询，隐藏了查询的复杂性。

视图的数据来自一个复杂的查询，用户对视图的检索却很简单。

*一个视图可以检索多张表的数据，因此用户通过访问一个视

图，可完成对多个表的访问。

*视图是相同数据的不同表示，通过为不同的用户创建同一个表的不同视图，使用户可分别访问同一个表的不同部分。

视图可以在表能够使用的任何地方使用，但在对视图的操作上同表相比有些限制，特别是插入和修改操作。

对视图的操作将传递到基表，所以在表上定义的约束条件和触发器在视图上将同样起作用。

（8）视图的创建

创建视图需要CREAEVIEW系统权限，视图的创建语法如下：

CREATE[ORREPLACE][FORCE|NOFORCE]VIEW视图名[（别名

1[，别名2...]）]

AS子查询

[WITHCHECKOPTION[CONSTRAINT约束名]]

[WITHREADONLY]

其中：

ORREPLACE表示替代已经存在的视图。

FORCE表示不管基表是否存在，创建视图。

NOFORCE表示只有基表存在时，才创建视图，是默认值。

USER_VIEWS字典中包含了视图的定义。

（9）物化视图

☐物化视图是一种特殊的物理表，“物化”（Materialized）视图是相对普通视图而言的。

普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询。

这样对整体查询性能的提高，并没有实质上的好处。

物化视图的类型ONDEMAND、ONCOMMIT。

二者的区别在于刷新方法的不同，ONDEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新（REFRESH），即更新物化视图，以保证和基表数据的一致性；而ONCOMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。

物化视图可以分为以下三种类型：

包含聚集的物化视图；只包含连接的物化视图；嵌套物化视图。

三种物化视图的快速刷新的限制条件有很大区别，而对于其他方面则区别不大。

☐创建物化视图时可以指定多种选项

创建方式（BuildMethods）：

包括BUILDIMMEDIATE和BUILDDEFERRED两种。

BUILDIMMEDIATE是在创建物化视图的时候就生成数据，而BUILDDEFERRED则在创建时不生成数据，以后根据需要在生成数据。

默认为BUILDIMMEDIATE。

查询重写（QueryRewrite）：

包括ENABLEQUERYREWRITE和DISABLEQUERYREWRITE两种。

分别指出创建的物化视图是否支持查询重写。

查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。

默认为DISABLEQUERYREWRITE。

在建立物化视图的时候可以指定ORDERBY语句，使生成的数据按照一定的顺序进行保存。

不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。

☐物化视图和普通视图的区别

Ø普通视图不存放数据，物化视图存放数据

Ø在使用视图进行查询时，是通过View中的SQL查询原表的数据，不能提高查询性能。

在通过物化视图进行查询时，直接读取物化视图的数据，提高查询性能。

Ø对普通视图应用更新、删除操作会影响原表的数据。

只读物化视图不能进行DML操作。

可更新物化视图可进行DML操作，并且在刷新时操作会被推送回原表。

可写物化视图可进行DML操作，但刷新后所有修改操作在刷新后全部丢失

（10）Oracle数据库的索引

索引（INDEX）是为了加快数据的查找而创建的数据库对象，

特别是对大表，索引可以有效地提高查找速度，也可以保证数据

的惟一性。

❑索引是对数据库表中一列或多列的值进行排序的一种结构使用索引可快速访问数据库表中的特定信息。

❑索引不论逻辑上和物理上都与相关的表的数据无关，索引需要独立的存储空间，所以索引在创建之初就需要设置对应的表空间

在创建PRIMARYKEY和UNIQUE约束条件时，系统将自动为相应的列创建惟一（UNIQUE）索引。

索引的名字同约束的名字一致。

•索引的创建

创建索引不需要特定的系统权限。

建立索引的语法如下：

CREATE[{UNIQUE|BITMAP}]INDEX索引名ON表名（列名1[，

列名2，...]）；

其中：

UNIQUE代表创建惟一索引，不指明为创建非惟一索引。

BITMAP代表创建位图索引，如果不指明该参数，则创建B*树索引。

列名是创建索引的关键字列，可以是一列或多列。

删除索引的语法是：

DROPINDEX索引名；

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

❑唯一索引

1、何时创建：

当某列任意两行的值都不相同

2、当建立PrimaryKey（主键）或者Uniqueconstraint（唯一约束）时，唯一索引将被自动建立

3、语法：

CREATEUNIQUEINDEXindexONtable（column）;

❑组合索引

1、何时创建：

当两个或多个列经常一起出现在where条件中时，则在这些列上同时创建组合索引

2、组合索引中列的顺序是任意的，也无需相邻。

但是建议将最频繁访问的列放在列表的最前面

3、语法：

CREATEINDEXindexONtable（column1，column2）;

索引

❑位图索引

1、何时创建：

列中有非常多的重复的值时候。

例如某列保存了“性别”信息。

Where条件中包含了很多OR操作符。

较少的update操作，因为要相应的更新所有的bitmap

2、结构：

位图索引使用位图作为键值，对于表中的每一行数据位图包含了TRUE

（1）、FALSE（0）、或NULL值。

3、优点：

位图以一种压缩格式存放，因此占用的磁盘空间比标准索引要小得多

4、语法：

CREATEBITMAPINDEXindexONtable（column[,column]...）;

❑范围索引

SQL>CREATE[UNIQUE]INDEXINDEX_NAMEONTABLE（COLUMN）

globalpartitionbyrange（column）

（

partitionpart_idx_01valuelessthan（firstrangevalue）tablespaceindex_space01,

partitionpart_idx_02valuelessthan（secondrangevalue）tablespaceindex_space02,

partitionpart_idx_03valuelessthan（maxvalue）tablespaceindex_space03）

散列索引

SQL>CREATE[UNIQUE]INDEXINDEX_NAMEONTABLE（COLUMN,[COLUMN2]）

globalpartitionbyhash（column,[column2]）

（

partitionpart_idx_01tablespaceindex_space01,

partitionpart_idx_02tablespaceindex_space02,

partitionpart_idx_03tablespaceindex_space03）

DBA查看索引分区

select*fromdba_ind_partitions

USER查看索引分区

select*fromuser_ind_partitions

DBA查看索引分区类型

select*fromdba_part_indexes

USER查看索引分区类型

select*fromuser_part_indexes

（11）序列

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

序列可以是升序的，也可以是降序的

使用CREATESEQUENCE语句创建序列

删除序列的语法如下

DROPSEQUENCE序列名；

（12）同义词

同义词（SYNONYM）是为模式对象起的别名，可以为表、视图、序

列、过程、函数和包等数据库模式对象创建同义词。

同义词有两种：

公有同义词和私有同义词。

公有同义词是对所有用户都可用的。

创建公有同义词必须拥有系统权限CREATEPUBLICSYNONYM；创建私有同义词需要CREATESYNONYM系统权限。

私有同义词只对拥有同义词的账

•同义词的创建和使用

同义词的创建语法如下：

CREATE[PUBLIC]SYNONYM同义词名

FOR[模式名.]对象名[@数据库链路名]；

其中：

PUBLIC代表创建公有同义词，若省略则代表创建私有同义

词。

模式名代表拥有对象的模式账户名。

同义词

数据库链路名是指向远程对象的数据库链接。

删除同义词的语法如下

DROPSYNONYM同义词名；

删除同义词的人必须是同义词的拥有者或有DROPANY

SYNONYM权限的人。

删除同义词不会删除对应的对象。

（13）触发器

触发器是特定事件出现的时候，自动执行的代码块。

类似于存储过程，触发器与存储过程的区别在于：

存储过程是由用户或应用程序显式调用的，而触发器是不能被直接调用的。

A.触发器组成:

触发事件：

引起触发器被触发的事件。

例如：

DML语句（INSERT,UPDATE,DELETE语句对表或视图执行数据处理操作）、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

触发时间：

即该TRIGGER是在触发事件发生之前（BEFORE）还是之后（AFTER）触发，也就是触发事件和该TRIGGER的操作顺序。

触发操作：

即该TRIGGER被触发之后的目的和意图，正是触发器本身要做的事情。

例如：

PL/SQL块。

触发对象：

包括表、视图、模式、数据库。

只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。

触发条件：

由WHEN子句指定一个逻辑表达式。

只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

触发频率：

说明触发器内定义的动作被执行的次数。

即语句级（STATEMENT）触发器和行级（ROW）触发器。

B.触发器的类型：

语句级（STATEMENT）触发器：

是指当某触发事件发生时，该触发器只执行一次；

行级（ROW）触发器：

是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

DML触发器：

ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。

替代触发器：

由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。

所以给出了替代触发器。

它就是ORACLE8专门为进行视图操作的一种处理方法。

系统触发器：

ORACLE8i提供了第三种类型的触发器叫系统触发器。

它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。

C.创建触发器的一般语法：

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER }

{INSERT | DELETE | UPDATE [OF column [, column …]]}

[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.]table_name | [schema.]view_name 

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ]

[WHEN condition]

PL/SQL_BLOCK | CALL procedure_name;

REFERENCING子句说明相关名称，在行触发器的PL/SQL块和WHEN子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。

触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号（:

），但在WHEN子句中则不能加冒号。

不同类型的触发器（如DML触发器、INSTEADOF触发器、系统触发器）的语法格式和作用有较大区别。

D.dml触发器示例：

限定只对部门号为80的记录进行行触发器操作

CREATE OR REPLACE TRIGGER tr_emp_sal_comm

BEFORE UPDATE OF salary, commission_pct

       OR DELETE

ON HR.employees

FOR EACH ROW

WHEN （old.department_id = 80）

BEGIN

 CASE

     WHEN UPDATING （'salary'） THEN

        IF :

NEW.salary < :

old.salary THEN

           RAISE_APPLICATION_ERROR（-20001, '部门80的人员的工资不能降'）;

        END IF;

     WHEN UPDATING （'commission_pct'） THEN

        IF :

NEW.commission_pct < :

mission_pct THEN

           RAISE_APPLICATION_ERROR（-20002, '部门80的人员的奖金不能降'）;

        END IF;

     WHEN DELETING THEN

          RAISE_APPLICATION_ERROR（-20003, '不能删除部门80的人员记录'）;

     END CASE;

END; 

（14）数据库事务

☐事务的概念：

事务用于保证数据的一致性,它由一组相关的dml语句组成,该组的dml语句要么全部成功。

要么全部失败。

DML（数据操作语言[增删改]）

事务的特性有四个：

ACID

原子性atomicity:

语句级原子性，过程级原子性，事务级原子性

一致性consistency:

状态一致，同一事务中不会有两种状