Oracle数据库的开发与应用
目
录
1
1
2
3
4 5
6
Oracle数据库的安装配置 .............................................................................................................................. 5 1.1 配置监听程序 ................................................................................................................................... 5 1.2 配置网络服务名 ............................................................................................................................... 5 管理用户访问 ................................................................................................................................................... 6 2.1 表空间、用户及方案概述 ............................................................................................................... 6 2.2 用户访问 ........................................................................................................................................... 6 2.3 创建用户 ........................................................................................................................................... 7
2.3.1 命令创建用户 ........................................................................................................................... 7 2.3.2 管理工具创建用户 ................................................................................................................... 8 2.3.3 修改用户 ................................................................................................................................... 8 2.3.4 授予用户访问权 ....................................................................................................................... 8 建立和管理表 ................................................................................................................................................. 10 3.1 简介 ................................................................................................................................................. 10 3.2 建表 ..................................................................................................................................................11 3.3 修改表 ............................................................................................................................................. 12 3.4 截断和删除表 ................................................................................................................................. 13 3.5 显示表信息 ..................................................................................................................................... 13 Oracle操作符 ................................................................................................................................................ 14 基本查询 ......................................................................................................................................................... 18 5.1 简单查询语句 ................................................................................................................................. 18 5.2 排序数据 ......................................................................................................................................... 20 复杂查询 ......................................................................................................................................................... 20 6.1 数据分组 ......................................................................................................................................... 20 6.2 连接查询 ......................................................................................................................................... 22 6.3 相等连接（包括SQL：1999标准内连接、自然连接） .............................................................. 22 6.4 不等连接 ......................................................................................................................................... 22 6.5 自连接 ............................................................................................................................................. 23 6.6 [内连接和]外连接 ......................................................................................................................... 23
6.6.1 内连接 ..................................................................................................................................... 23 6.6.2 左外连接 ................................................................................................................................. 23 6.6.3 右外连接 ................................................................................................................................. 24 6.6.4 完全外连接 ............................................................................................................................. 24 6.7 子查询 ............................................................................................................................................. 24
6.7.1 单行子查询（单列） ............................................................................................................. 24 6.7.2 多行子查询（单列） ............................................................................................................. 24 6.7.3 多列子查询 ............................................................................................................................. 25 6.7.4 其他子查询 ............................................................................................................................. 25
6.7.4.1 相关子查询（通过EXISTS谓词实现） ..................................................................... 25 6.7.4.2 在FROM子句中使用子查询（子查询作为视图对待，又称内嵌视图） ................. 25 6.7.4.3 在DML语句中使用子查询........................................................................................... 25
6.7.4.3.1 在INSERT语句中使用子查询 ......................................................................... 25 6.7.4.3.2 UPDATE中 .......................................................................................................... 26 6.7.4.3.3 DELETE中 .......................................................................................................... 26 6.7.4.4 在DDL语句中使用子查询........................................................................................... 26
2
7 8
9
10
11
12
13
6.7.4.4.1 在CREATE TABLE语句中使用子查询 ............................................................. 26 6.7.4.4.2 在CREATE VIEW语句中使用子查询 ............................................................... 26
6.8 使用集合操作符 ............................................................................................................................. 26 常用SQL函数 ................................................................................................................................................ 26 操纵数据 ......................................................................................................................................................... 35 8.1 插入数据 ......................................................................................................................................... 35 8.2 更新数据 ......................................................................................................................................... 36 8.3 删除数据 ......................................................................................................................................... 38 使用事务 ......................................................................................................................................................... 39 9.1 概述 ................................................................................................................................................. 39 9.2 事务分类 ......................................................................................................................................... 39 9.3 回复修改 ......................................................................................................................................... 40 9.4 回复部分事务 ................................................................................................................................. 41 9.5 事务和锁 ......................................................................................................................................... 42
使用约束 ................................................................................................................................................. 42 10.1 约束简介 ......................................................................................................................................... 42 10.2 定义约束 ......................................................................................................................................... 43 10.3 维护约束 ......................................................................................................................................... 44 10.4 显示约束信息 ................................................................................................................................. 45
使用视图 ................................................................................................................................................. 46 11.1 视图简介 ......................................................................................................................................... 46 11.2 建立视图 ......................................................................................................................................... 46 11.3 维护视图 ......................................................................................................................................... 48 11.4 显示视图信息 ................................................................................................................................. 48
使用其它对象（索引序列同义词） ..................................................................................................... 49 12.1 使用索引 ......................................................................................................................................... 49
12.1.1 建立索引 ................................................................................................................................ 60 12.1.2 维护索引 ................................................................................................................................ 60 12.1.3 显示索引信息 ........................................................................................................................ 61 12.2 使用序列 ......................................................................................................................................... 61
12.2.1 建立序列 ................................................................................................................................ 61 12.2.2 使用序列 ................................................................................................................................ 62 12.2.3 维护序列 ................................................................................................................................ 63 12.2.4 显示序列信息 ........................................................................................................................ 63 12.3 使用同义词 ..................................................................................................................................... 63
12.3.1 建立同义词 ............................................................................................................................ 63 12.3.2 删除同义词 ............................................................................................................................ 64 PL/SQL语句 ............................................................................................................................................ 64 13.1 PL/SQL简介 .................................................................................................................................... 64 13.2 PL/SQL块 ........................................................................................................................................ 66
13.2.1 定义并使用变量 .................................................................................................................... 68 13.2.2 异常 ........................................................................................................................................ 69 13.2.3 游标 ........................................................................................................................................ 71 13.3 过程函数包 ..................................................................................................................................... 89
3
14 15 16
PLSQL中的过程 ...................................................................................................................................... 89 PLSQL中的触发器 .................................................................................................................................. 89 使用EXP和IMP .................................................................................................................................... 90 16.1 使用EXP .......................................................................................................................................... 90
16.1.1 导出表 .................................................................................................................................... 90 16.1.2 导出方案 ................................................................................................................................ 91 16.1.3 导出数据库 ............................................................................................................................ 91 16.2 使用IMP .......................................................................................................................................... 91
16.2.1 导入表 .................................................................................................................................... 91 16.2.2 导入方案 ................................................................................................................................ 92 16.2.3 导入数据库 ............................................................................................................................ 92 16.3 批处理：导出、导入 ..................................................................................................................... 92 16.4 导出表的部分数据：带查询条件的导出 ...................................................................................... 92 16.5 linux下导入备份文件： .............................................................................................................. 93
16.5.1 多个导入文件一起导入 ........................................................................................................ 93 16.5.2 多表导入：不加括号 ............................................................................................................ 93 16.5.3 单独分区导入 ........................................................................................................................ 93 16.5.4 分区导出 ................................................................................................................................ 94
17 PLSQL的导入导出 .................................................................................................................................. 94
17.1 PLSQL导入命令 .............................................................................................................................. 94 17.2 PLSQL工具栏导入、导出表 .......................................................................................................... 94 17.3 PLSQL工具栏导出数据对象 .......................................................................................................... 94 18 数据库链 ................................................................................................................................................. 94 19 Sql优化 .................................................................................................................................................. 95
19.1 RBO的两个规则 .............................................................................................................................. 96 19.2 基础表 ............................................................................................................................................. 96 19.3 索引 ................................................................................................................................................. 97 19.4 sql优化措施 .................................................................................................................................. 97
19.4.1 正确建立并使用索引 ............................................................................................................ 97 19.4.2 监控索引使用情况 ................................................................................................................ 99 19.4.3 避免全表扫描 ...................................................................................................................... 100 19.4.4 注意sql的写法 .................................................................................................................... 101
4
1 Oracle数据库的安装配置
当建立了Oracle数据库之后，为了使得服务器端可以监听该Oracle数据库，必须配置监听程序。为了使得客户端可以访问该数据库，必须在客户端配置网络服务名。只有合理地配置了监听程序和网络服务名之后，客户应用才能访问该数据库。配置监听程序和网络服务名可以使用网络管理工具Net Manager完成。
1.1 配置监听程序
监听程序用于接收客户端的连接请求。当客户应用访问Oracle Server时，监听程序会接收并检查连接请求，以确定是否可以为该客户应用提供数据服务。在建立了Oracle数据库之后，为了使得客户应用可以访问Oracle数据库，必须在监听程序中追加该数据库。一个监听程序可以监听多个Oracle数据库，多个监听程序也可以监听同一个Oracle数据库。当安装数据库产品时，会自动建立默认监听程序LISTENER。
配置监听程序有两种方法 方法一具体步骤如下： ? 展开监听程序，并选中LISTENER节点，此时在NET MANAGER窗口右端会显示默认监听位置，
其中“协议”用于指定监听程序要使用的网络协议（默认为TCP/IP）；“主机”用于指定服务器所在机器的主机名或IP地址；“端口”用于指定监听程序要使用的TCP/IP端口号（默认1521）。 ? 在Net Manager窗口上端的下拉列表中选择数据库服务，此时会显示默认的数据库配置，“全局数
据库名”用于指定数据库的全局数据库名；“Oracle主目录”用于指定Oracle数据库软件的安装路径；“SID”用于指数据库例程名。为了监听新建的数据库（如DEMO），必须追加该数据库。 ? 单击“添加数据库”按钮，然后进行相应配置，在“全局数据库名”处输入DEMO数据库的初
始化参数SERVICE—HOME所对应的值，在SID处输入DEMO数据库的例程名。配置了监听程序之后，保存网络配置信息。
? 保存了监听程序配置之后，为了使得其网络配置生效，必须重新启动监听程序。（通过服务器管
理器重新启动监听程序） 方法二具体步骤如下：
? 打开ORACLE配置和移植工具中的Net Configuration Assistant，选中监听程序配置，下一步，选
中添加，下一步，输入监听程序名，下一步，选定的协议选TCP，下一步，选中使用标准端口号1521，下一步至完成，这样新的监听程序就建立了。
? 当然方法二中也可以对已经配置好的监听程序进行重新配置或者重新命名、删除、测试。
1.2 配置网络服务名
配置网络服务名有两种方法。 方法一具体步骤如下： ? 选中“服务命名”，然后单击+按钮，此时会显示“Net 服务名”界面，建议使用数据库名作网络
服务名。
? 选取与监听程序一致的网络协议“TCP/IP”。 ? 指定数据库所在主机名及其监听端口号。
? 指定监听程序所配置的全局数据库名或者SID。
? 测试网络服务名配置是否成功，如果成功则表示网络服务名配置正确。 完成网络服务名配置之后，保存网络配置信息
5