ORACLE数据库设计.doc
《ORACLE数据库设计.doc》由会员分享，可在线阅读，更多相关《ORACLE数据库设计.doc（80页珍藏版）》请在冰点文库上搜索。
海颐软件股份有限公司ORACLE技术文档之一源码网整理：
目录
第1章Oracle数据库安装配置 3
1.1配置监听程序 3
1.2配置网络服务名 4
第2章管理用户访问 4
2.1 表空间、用户及方案概述 4
2.2 用户访问 5
2.3 创建用户 6
2.4 修改用户 6
2.5锁定用户账户 7
2.6解锁用户账户 7
2.7默认角色 7
2.8授予用户访问权 7
2.9 撤消用户 9
2.10 废除用户访问 9
2.11 创建角色 9
2.12新建的用户怎样才能成功创建一个表 10
第3章建立和管理表 11
3.1 简介 12
3.2 建表 13
3.3 修改表 14
3.4 截断和删除表 14
3.5 显示表信息 15
第4章Oracle操作符 16
第5章基本查询 19
5.1 简单查询语句 19
5.2 排序数据 21
第6章复杂查询 22
6.1 数据分组 22
6.2 连接查询 23
6.3 相等连接（包括SQL：
1999标准内连接、自然连接） 24
6.4 不等连接 24
6.5 自连接 24
6.6 [内连接和]外连接 24
6.6.1 内连接 25
6.6.2 左外连接 25
6.6.3 右外连接 25
6.6.4 完全外连接 25
6.7 子查询 25
6.7.1单行子查询（单列） 25
6.7.2多行子查询（单列） 25
6.7.3多列子查询 26
6.7.4其他子查询 26
6.8使用集合操作符 27
第7章常用SQL函数 27
第8章操纵数据 41
8.1插入数据 41
8.2更新数据 43
8.3删除数据 44
第9章使用事务 45
9.1概述 46
9.2事务分类 46
9.3回复修改 47
9.4回复部分事务 48
9.5事务和锁 49
第10章使用约束 49
10.1约束简介 49
10.2定义约束 50
10.3维护约束 51
10.5显示约束信息 52
第11章使用视图 53
11.1视图简介 53
11.2建立视图 54
11.3维护视图 55
11.4显示视图信息 55
第12章使用其它对象（索引序列同义词） 56
12.1使用索引 56
12.1.1建立索引 57
12.1.2维护索引 57
12.1.3显示索引信息 58
11.2使用序列 58
11.2． 1建立序列 58
11.2.2使用序列 58
11.2.3维护序列 59
11.2.4显示序列信息 59
11.3使用同义词 59
11.3.1建立同义词 59
11.3.2删除同义词 59
第13章PL/SQL语句 60
13.1PL/SQL简介 60
13.2PL/SQL块 62
13.2.1定义并使用变量 63
13.2.2异常 64
13.2.2游标 67
13.3过程函数包 71
第14章过程函数包及触发器 71
14.1过程 71
14.1.1建立过程 71
14.1.2显示过程代码 72
14.2函数 72
14.2.1建立函数 72
14.2.2删除函数 73
14.2.3显示函数代码 73
14.3包 73
14.3.1建立包规范 73
14.3.2建立包体 74
14.3.3删除包 75
14.3.4显示包代码 75
14.4触发器 75
14.4.1语句触发器 75
14.4.2行触发器 76
14.4.3使用触发器的注意事项 77
14.4.4编译触发器 77
14.4.5删除触发器 77
14.4.6显示触发器代码 77
第十五章使用EXP和IMP 77
15.1使用EXP 78
15.1.1导出表 78
15.1.2导出方案 78
15.1.3导出数据库 78
15.2使用IMP 78
15.2.1导入表 79
15.2.2导入方案 79
15.2.3导入数据库 79
第1章Oracle数据库安装配置
当安装OracleDatabase时，如果没有建立数据库，在安装完成之后可以使用DBCA工具建立数据库。
数据库配置助手（DatabaseConfigurationAssistant）用于建立数据库、配置数据库选项、删除数据库和管理模板。
当建立了Oracle数据库之后，为了使得服务器端可以监听该Oracle数据库，必须配置监听程序。
为了使得客户端可以访问该数据库，必须在客户端配置网络服务名。
只有合理地配置了监听程序和网络服务名之后，客户应用才能访问该数据库。
配置监听程序和网络服务名可以使用网络管理工具NetManager完成。
（源码网整理：
）
1.1配置监听程序
监听程序用于接收客户端的连接请求。
当客户应用访问OracleServer时，监听程序会接收并检查连接请求，以确定是否可以为该客户应用提供数据服务。
在建立了Oracle数据库之后，为了使得客户应用可以访问Oracle数据库，必须在监听程序中追加该数据库。
一个监听程序可以监听多个Oracle数据库，多个监听程序也可以监听同一个Oracle数据库。
当安装数据库产品时，会自动建立默认监听程序LISTENER。
配置监听程序的具体步骤如下：
l展开监听程序，并选中LISTENER节点，此时在NETMANAGER窗口右端会显示默认监听位置，其中“协议”用于指定监听程序要使用的网络协议（默认为TCP/IP）；“主机”用于指定服务器所在机器的主机名或IP地址；“端口”用于指定监听程序要使用的TCP/IP端口号（默认1521）。
l在NetManager窗口上端的下拉列表中选择数据库服务，此时会显示默认的数据库配置，“全局数据库名”用于指定数据库的全局数据库名；“Oracle主目录”用于指定Oracle数据库软件的安装路径；“SID”用于指数据库例程名。
为了监听新建的数据库（如DEMO），必须追加该数据库。
l单击“添加数据库”按钮，然后进行相应配置，在“全局数据库名”处输入DEMO数据库的初始化参数SERVICE—HOME所对应的值，在SID处输入DEMO数据库的例程名。
配置了监听程序之后，保存网络配置信息。
l保存了监听程序配置之后，为了使得其网络配置生效，必须重新启动监听程序。
（通过服务器管理器重新启动监听程序）
1.2配置网络服务名
l选中“服务命名”，然后单击+按钮，此时会显示“Net服务名”界面，建议使用数据库名作网络服务名。
l选取与监听程序一致的网络协议“TCP/IP”。
l指定数据库所在主机名及其监听端口号。
l指定监听程序所配置的全局数据库名或者SID。
l测试网络服务名配置是否成功，如果成功则表示网络服务名配置正确。
l完成网络服务名配置之后，保存网络配置信息。
第2章管理用户访问
本章主要内容：
l表空间、用户及方案概述
l用户访问
l创建用户
l修改用户
l授予用户访问权
l撤消用户
l废除用户访问
l创建角色
2.1表空间、用户及方案概述
表空间是数据库的逻辑组成部分。
从物理上说，数据库数据存放在数据文件中；从逻辑上说，数据库数据存放在表空间（tablespace）中，并且表空间是由一个或多个数据文件组成的。
一个表空间是由一个或多个数据文件组成的。
用户（也称为帐户）是定义在数据库中的一个名称，它是Oracle数据库的基本访问控制机制。
当连接到Oracle数据库时，默认情况下必须要提供用户名和口令。
只有在输入了正确的用户名和口令之后，才能够连接到数据库，并执行各种管理操作和数据访问操作。
方案（Schema）是用户所拥有数据库对象的集合。
在Oracle数据库中对象是以用户来组织的，用户与方案是一一对应的关系，并且二者名称相同。
例SCOTT用户所拥有的所有对象都属于SCOTT方案，而SYSTEM用户所拥有的所有对象都属于SYSTEM方案。
当访问数据库对象时，有一些注意事项：
l在同一个方案中不能存在同名对象，但不同方案可以具有同名对象。
l用户可以直接访问其方案对象，但如果要访问其他方案对象，则必须具有对象权限。
如用户SCOTT可以直接查询其方案表EMP，但如果用户SMITH要检索SCOTT方案的表EMP，则必须在EMP表上具有SELECT对象权限。
l当用户访问其他方案对象时，必须加方案名为前缀。
例，如用户SMITH要访问SCOTT方案的EMP表，则必须使用SCOTT.EMP。
2.2用户访问
在多用户环境里，一个数据库可能有多个用户同时在访问。
当有不同的用户同时访问数据库时，保护数据库安全，防范非授权访问非常重要。
因此，必须在数据库里创建用户，并为用户指定用户名和密码，这样可以保证只有经过授权的，即有正确用户名和密码的用户才能访问数据库。
数据库管理员（DBA）是最高级别的用户，他可以创建其他用户。
在创建了用户后，DBA需要按用户的需求为用户分配权限。
权限指用户执行特定语句的许可，这意味并非所有用户都被允许修改重要数据。
例如，某个用户可能只需要有连接数据库和查询某些表的记录的权限。
类似地，另一个用户可能要求有创建和修改表的权限。
DBA有访问数据库的一切权限，并有权为其他用户分配权限。
下表列出了部分DBA权限：
DBA权限
有权执行
CREATEUSER
创建新用户
DROPUSER
撤消用户
DROPANYTABLE
撤消表
BACKUPANYTABLE
为表制作备份
SELECTANYTABLE
查询数据库对象，如表和视图
CREATEANYTABLE
创建表
为了维护存储在数据库中数据的安全，Oracle提供了以下数据库安全措施：
l管理和控制数据库访问
l用Oracle数据字典验证权限
l为指定用户提供对数据库特定对象（表、视图和序列等）的访问
l为数据库对象提供同义词
可被数据库操作采用的数据库的安全策略包括：
l系统安全：
系统安全涉及系统级的访问，如允许用户通过指出用户名和密码连接Oracle，为用户分配磁盘空间，限定用户所能执行的操作。
用户能执行的操作包括：
查询数据库对象的内容、创建数据库对象和更改数据库对象。
l数据安全：
数据安全涉及对数据库对象的访问和使用，以及用户在数据库对象上所拥有的权限的程度。
2.3创建用户
CREATEUSER语句用于创建新用户。
在创建新用户时，必须为新用户指定用户名和密码。
为了使新用户能登录服务器和访问数据库，DBA必须显式地为用户分配权限。
只有拥有CREATEUSER权限的用户才能创建新用户。
例如，DBA创建了新用户Susan，但Susan无权创建其他新用户。
只有当Susan有了CREATEUSER这个权限后，她才能创建别的新用户。
创建用户的语法示例：
CREATEUSERuserIDENTIFIEDBYpassword
DEFAULTTABLESPACEdata01
TEMPORARYTABLESPACEtemp
QUOTA3MONdata01
PASSWORDEXPIRE;
其中IDENTIFIEDBY用于指定用户口令；DEFAULTTABLESPACE用于指定用户的默认表空间，当建立表或者索引时，如果不指定TABLESPACE子句，那么Oracle会自动在默认表空间上为这些对象分配空间；TEMPORARYTABLESPACE用于指定用户的临时表空间，当用户执行排序操作时，或临时数据超过PGA工作区，则会在该表空间上建立临时段；QUOTA用于指定表空间配额，即用户对象在表空间上可占用的最大空间；PASSWORDEXPIRE用于指定终止口令，最终强制用户在登录时改变口令。
当建立了新用户之后，需要注意以下问题：
l初始创建的数据库用户没有任何权限，不能执行任何数据库操作。
l如果在建立用户时不指定DEFAULTTABLESPACE子句，那么Oracle会将数据库默认表空间作为用户的默认表空间。
在OracleDatabase10g之前，如果不指定DEFAULTTABLESPACE子句，那么Oracle会将SYSTEM表空间作为用户的默认表空间。
l如果在建立用户时不指定TEMPORARYTABLESPACE子句，那么Oracle会将数据库默认临时表空间作为用户的临时表空间。
l如果在建立用户时没有为特定表空间指定QUOTA子句，那么用户在特定表空间上的配额为0，这样用户将不能在相应表空间上建立数据对象。
2.4修改用户
修改用户信息是使用ALTERUSER命令完成的。
一般情况下，该命令是由DBA来执行的，如果以其他用户身份修改用户信息，必须要具有ALTERUSER系统权限。
1．修改口令
创建用户时为每个用户指定一个初始密码。
之后可修改密码，修改密码的方法有两种：
l管理员修改：
ALTERUSERuserIDENTIFIEDBYpassword;（password为用户的新密码）
l用户自己修改：
用户登录后输入命令：
PASSWORD；
修改表空间配额
表空间配额用于限制用户对象在表空间上可占用的最大空间。
如果用户对象己经占满了表空间配额所允许的最大空间，那么该用户将不能在该表空间上分配新的空间。
此时如果执行了涉及到空间分配的SQL操作（如INSERT、UPDATE、CREATETABLE等），则会显示错误，修改表空间语法：
ALTERUSERuserQUOTA10MONdata01;
2.5锁定用户账户
为了禁止特定数据库用户用户访问数据库，DBA可以锁定用户账户，
ALTERUSERuserACCOUNTLOCK;
2.6解锁用户账户
为了使得数据库用户可以访问数据库，DBA可以解锁用户账户。
ALTERUSERuserACCOUNTUNLOCK；
2.7默认角色
当将多个角色授予数据库用户之后，通过使用ALTERUSER命令可以设置用户的默认角色。
（当为用户指定了默认角色后，以该用户身份登录时会自动激活其默认角色，并不激活非默认角色）
ALTERUSERuserDEFAULTROLEselect_role;
[补充：
]
默认角色和非默认角色的区别是什么?
这个理解有多种，第一种：
默认角色可以是我们创建数据库就可以见的connect/dba等，非默认角色需要我们自己创建；第二种：
一个用户可以有多个角色，默认的角色登录即生效，非默认的需要激活才能使用。
默认角色和权限集是Oracle安装过程中预先定义的。
每个版本的默认角色都有所变化。
CREATEUSERSMISIDENTIFIEDBYSMIS；
grantdba,connecttoSMIS;
createroler_px;
grantr_pxtoSMIS;
select*fromdba_role_privswheregrantee='SMIS';
alteruserSMISdefaultroleallexceptr_px;
select*fromdba_role_privswheregrantee='SMIS';
2.8授予用户访问权
用户创建后，数据库管理员需要为他分配权限。
权限关系到数据库的安全，它决定了用户在数据库上所能执行的操作。
GRANT语句用于为用户分配权限。
语法如下：
GRANTprivilegeTOuser;
[注释：
授予所有系统权限GRANTALLPRIVILEGEStotest_2;]
[注释：
授予所有对象权限GRANTALL[PRIVILEGES]ONDEMO.DEPTTOTEST_1;]
可分配给用户的权限有：
l系统权限：
允许用户访问数据库的权限称为系统权限。
l对象权限：
允许用户在数据库对象上执行查询、更新、删除或添加数据等操作的权限，此称为对象权限。
常用的系统权限有：
系统权限
有权执行
CREATESESSION
连接数据库
CREATETABLE
创建表
CREATEVIEW
创建视图
CREATEPUBLICSYNONYM
建立同义词
CREATESEQUENCE
创建序列
CREATEPROCEDURE
建立过程、函数和包
CREATETRIGGER
建立触发器
CREATECLUSTER
建立簇
CREATETYPE
建立对象类型
CREATEDATABASELINK
建立数据库链
另外，Oracle还提供了一类ANY系统权限，当用户具有该类系统权限时，可以在
任何方案中执行相应操作。
例如，如果用户具有SELECTANYTABLE系统权限，那么用户可以查询任何方案的表（除数据字典基表和数据字典视图DBA_XXX----DBA和特权用户专访）。
如：
GRANTCREATESESSION,CREATESEQUENCE,CREATEVIEWTOjohn;
上述命令执行后，用户john将拥有创建会话、视图和序列的系统权限。
*Oracle提供了100多种系统权限（ALTERTABLE，ALTERVIEW，ALTERPROCEDURE，DROPTABLE，DROPVIEW，DROPPROCEDURE等）。
一般情况下，授予系统权限是由DBA来完成的；如果要以其他用户身份授予系统权限，则要求该用户必须具有GRANTANYPRIVILEGE系统权限，或者具有相应系统权限及其转授系统权限选项（WITHADMINOPTION），授予系统权限是使用GRANT命令来完成的，其语法如下：
GRANTsystem_priv[,system_priv,…]
TO{user|role|public},[,{user|role|public}]...
[WITHADMINOPTION];
注：
user也可以是用户组PUBLIC；UNLIMITEDTABLESPACE权限不能被授予角色
下表列出了所有对象权限。
对象权限
适用于
ALTER
表、序列
DELETE
表、视图
EXECUTE
过程
INDEX
表
INSERT
表、视图
REFERENCES
表（基于表建立从表）
SELECT
表、视图、序列
UPDATE
表、视图
如果用户在某个同义词上拥有权限，而这个同义词又需要引用其他基表，那么用户
在同义词上拥有的权限会转换为在基表上的权限。
缺省地，用户对他所创建的对象拥有完全的权限。
如用户在用户模式上创建了一个表，那么缺省地，他在用户模式上拥有所创建的这个表的所有权限。
授予对象权限一般情况下是由对象所有者或者DBA用户来完成的；如果以其他用户身份授予对象权限，则要求用户必须具有该对象权限及转授对象权限选项（WITHGRANTOPTION），语法如下：
GRANT{object_priv[（columns）][,object_priv[（columns）]]…
|ALL[PRIVILEGES]}ON[schema.]object
TO{user|role|PUBLIC}[,{user|role|PUBLIC}]…
[WITHGRANTOPTION];
例：
grantupdateonscott..emptoblake;
Grantupdate（sal）onemptoblake;（只能在insert、update和references上授予列权限
2.9撤消用户
DROPUSER语句用于删除用户。
语法：
DROPUSERusername;
撤消用户时，用户创建的对象并没有被撤消。
为了撤消创建对象的用户
语法：
DROPUSERusernameCASCADE；
（不指定CASCADE不能撤消创建对象的用户）
2.10废除用户访问
一般情况下，收回系统权限是由DBA来完成的；如果以其他用户身份收回系统权限，则要求该用户必须具有相应系统权限及其转授系统权限选项（WITHADMINOPTION）。
收回系统权限是使用REVOKE命令来完成的。
语法如下：
REVOKEsystem_priv[,system_priv]…
FROM{user|role|PUBLIC}[,{user|role|PUBLIC}]…
用户的权限可使用REVOKE语句废除。
一旦某个用户的权限被撤消，由他创建的所有用户和从他那获得权限的所有用户也都被撤消。
如，Jim把CREATETABLE权限授给John。
当Jim的权限被撤消时，同时自动撤消John的权限。
如：
GRANTCREATETABLETOJohn;
REVOKECREATETABLEFROMJohn;（不能撤消自己的权限）
一般情况下，收回对象权限是由对象所有者或者DBA用户来完成的；如果以其他用户身份收回对象权限，则要求用户必须具有该对象权限及转授对象权限选项（WITHGRANTOPTION）。
语法如下：
REVOKE{object_priv[,object_priv]…|ALL[PRIVILEGES]}
ON[schema.]object
FROM{user|role|PUBLIC}[,{user|role|PUBLIC}]…
[CASCADECONSTRAINTS];
CASCADECONSTRAINTS用于删除任何与该对象相关的约束和对象，例如索引、触发器、权限、完整性约束等。
2.11创建角色
角色是相关权限的命名集合，使用角色的主要目的是为了简化权限管理。
角色可以是权限的组合，也可以是角色的组合。
角色包括预定义角色和自定义角色两类。
常用的预定义角色有：
（oracle10g）
lCONNECTRole：
分配给临时用户的角色。
通常，为只需要查询材料而无须创建表的用户分配这个角色。
lRESOURCERole：
这个角色分配给常规用户
lDBARole：
这个角色拥有一切系统权限，包括不加限制的表空间配额以及WITHADMINOPTION选项。
默认的DBA用户为SYS和SYSTEM，他们可以将任何系统权限授予其他用户。
读者需要注意，DBA角色不具备SYSDBA和SYSOPER特权。
创建角色的语法：
CREATEROLErole[NOTIDENTIFIED];不验证—用于公用角色或用户默认角色
或
CREATEROLErole
IDENTIFIEDBYpassword;数据库验证
为角色分配密码或修改角色密码：
ALTERROLErole
IDENTIFIEDBYpassword;（password为要设置的新密码）
如：
CREATEROLEAcadre;
ALTERROLEAcadreIDENTIFIEDBYsuccess;
为角色授予权限
GRANTprivilegeTOrole;
例：
只授予CONNECT权限给角色Acadre，这样他们不可能操作数据库。
GRANTconnectTOAcadre;
将角色指
升级会员 