第1章Oracle安装配置
1．Oracle服务端安装好之后，在命令提示符下，利用echo %Path%命令查看此时的系统变量Path。
在Windows系统中，单击【开始】|【运行】，并键入“cmd”，如下图所示：
单击【确定】按钮，将进入Windows命令提示符，如下图所示：
键入echo %path%，并按下回车键，将显示此时变量path的值，如下图所示：
2．利用java –version命令，查看此时java环境的版本，以确认是否为Oracle安装时自带的Java 文件。
在Windows的【Command】窗口中执行java –version命令，将看到本机Java环境的版本，如下图所示：
3．Oracle数据库服务器安装之后，在硬盘上搜寻名为oradata的文件夹。其中包含了所有数据库
·217·的物理文件，查看已有数据库的子文件夹及文件。
一个数据库的典型文件包括：后缀为CTL的控制文件；后缀为LOG的重做日志；后缀为DBF的数据文件。
第2章Oracle常用工具
1．有时无法连接数据库，是由于多次安装了服务端/客户端，而导致客户端软件寻找TNS配置文件时，混淆了当前有效的路径。此时，可以利用将DNS描述直接作为参数传递给客户端软件的方式来登录数据库，从而不再使用TNS配置文件。尝试利用数据库ORCL的TNS描述直接登录数据库。
利用sqlplus+TNS配置登录数据库的命令如下所示：
sqlplus
sys/abc123@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.16.5)(P ORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl))) as sysdba 登录成功的界面如下所示：
2．EZConnect是EasyConnect的简称。利用EZConnect可以在客户端以IP+SID的方式登录数据。利用NetManager配置EZConnect的连接方式，并利用客户端软件+EZConnect的方式登录数据库ORCL。
首先保证Oracle数据库服务器支持EZConnect连接方式，如下图所示：
·218·
然后，在【Command】窗口中，利用sqlplus登录数据库：sqlplus sys/abc123@//192.168.16.5/ORCL as sysdba
登录成功的界面如下所示：
当然，也可以利用PL/SQL Developer进行登录：
·219·
3．客户端连接Oracle 数据库连接时，默认端口为1521。创建一个新的监听程序，其端口为1522。然后将ORCL 注册于该监听程序。
1）在Net Manager 中创建名为LISTENER_1522。为【监听位置】填写主机IP 和端口号。注意其端口号为
1522
2）选择监听程序的【数据库服务】，并为其输入ORCL 。
3）利用【文件】|【保持网络配置】，保存监听程序LISTENER_1522的信息。
·220·
4）在Windows Command窗口中，启动监听程序LISTENER_1522。
C:\>lsnrctl start listener_1522
LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 31-7月-2011 19:37:17
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
System parameter file is D:\oracle\NETWORK\ADMIN\listener.ora
Log messages written to d:\oracle\network\log\listener_1522.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.16.5)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.16.5)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias listener_1522
Version TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
Start Date 31-7月-2011 19:37:17
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oracle\NETWORK\ADMIN\listener.ora
Listener Log File d:\oracle\network\log\listener_1522.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.16.5)(PORT=1522)))
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
这样，即使没有其他监听程序的存在，或者1521端口冲突。Oracle也会自动通过监听程序LISTENER_1522来连接数据库ORCL。
第3章SQL Plus和PL/SQL
1．利用sqlplus登录数据库，并查看数据库版本。
利用SQL Plus登录数据库：
C:\>sqlplus / as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on 星期日7月10 18:43:50 2011
Copyright (c) 1982, 2004, Oracle. All rights reserved.
·221·
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
在控制台上打印出的信息，可以清楚看到Oracle数据库的版本为10.1.0.2.0。
2．在对数据库进行重要操作时，首先应该确认数据库身份，以免在其他数据库上进行操作。尝试利用SQL Plus显示数据库实例名称。
1）利用SQL Plus登录数据库：
C:\>sqlplus / as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on 星期日7月10 18:43:50 2011
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Releas e 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
2）键入show parameter instance_name来查看实例名称
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string orcl
3．试着利用SQL Plus来创建一个新表test (id number, name varchar2(20))。
可以通过如下步骤利用SQL Plus来创建一个新表：
（1）利用SQL Plus登录数据库ORCL。
在Windows的【开始】|【运行】的【打开】文本框中输入sqlplus scott/abc123@orcl来登录数据库ORCL。
（2）在SQL Plus命令行下输入如下命令来创建新表test：
create table test (id number, name varchar2(20));
（3）在SQL Plus会出现表创建成功的提示，如图所示。
·222·
此时，证明表创建成功。
第4章Oracle数据库
1．在数据库中创建一个表lob_source(id number, description clob)。将表lob_test的数据导入另外一个数据表lob_dest(id number, description clob)。
1）创建表lob_source
SQL> create table lob_source(id number, description clob);
Table created.
2)向表lob_source中插入测试数据
SQL> insert into lob_source values(1, 'a clob text from source');
1 row created.
3)创建测试表lob_dest
SQL> create table lob_dest(id number, description clob);
Table created.
4)向测试表lob_dest中插入测试数据，但是不包含clob类型的description列
SQL> insert into lob_dest(id) values(1);
1 row created.
5）利用表lob_source中的description信息，更新表lob_dest中的description信息。
SQL> update lob_dest set description = (select description from lob_source source where source.id = lob_dest.id);
1 row updated.
SQL> select * from lob_dest;
ID DESCRIPTION
-------------------- ------------------------------
1 a clob text from source
·223·
该实例实际说明了针对lob类型的数据的操作方式。由于lob类型的数据的特殊性，因此在实现数据库迁移时，如果遇到棘手的lob类型处理，可以考虑利用本例所演示的方法。
2．利用exp/imp方式，将数据库orcl中users表的内容，迁移到数据库test中。
1)在数据库orcl中，创建测试表users
SQL> create table users(user_id number, user_name varchar(20));
Table created.
SQL> insert into users values(1, 'allen');
1 row created.
SQL> insert into users values(2, 'mike');
1 row created.
SQL> commit;
Commit complete.
2）导出表users到d:\user.bak
C:\>exp system/abc123@//192.168.16.5/orcl tables=(users) file='d:/users.bak'
Export: Release 10.1.0.2.0 - Production on 星期三7月13 00:06:22 2011
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table USERS 2 rows exported
Export terminated successfully without warnings.
3）将d:\user.bak的内容导入数据库test
C:\>imp system/abc123@//192.168.16.5/test tables=(users) file='d:/users.bak'
Import: Release 10.1.0.2.0 - Production on 星期三7月13 00:10:09 2011
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
·224·
. . importing table "USERS" 2 rows imported
Import terminated successfully without warnings.
3．如果数据库处于归档模式，那么，随着时间的累积，归档日志将会占用很大空间。一旦达到默认大小20G，那么将导致数据库挂起，在告警日志中一般会有如下提示：ORA-00257: archiver error. Connect internal only, until freed。利用修改参数db_recovery_file_dest_size的方式，快速解决数据库无法归档的问题。
1）查看默认空间大小
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
--------------------------- --------- ---------------
db_recovery_file_dest_size big integer 2G
2）修改其大小
SQL> alter system set db_recovery_file_dest_size=3G scope=both;
System altered.
这一用法，适合于快速处理现场由于归档日志过大导致的数据库挂起。
第5章Oracle数据表对象
1．创建一个表空间testsize，其数据文件大小为2M，并设置自动增长尺寸为1M。在表空间中建立一个数据表，并向其中插入大量数据，观察表空间文件的变化。
1）创建一个大小为2M，自动增长尺寸为1M的表空间
SQL> create tablespace testsize datafile 'e:\database\oracle\testsize_data.dbf' size 2M
2 autoextend on next 1M
3 /
Tablespace created
2）创建一个数据表test_tablespace_size(test_data varchar2(100))
SQL> create table test_tablespace_size(test_data varchar2(100)) tablespace testsize;
Table created
3）利用如下SQL语句向表test_tablespace_size中插入数据
SQL> begin
2 for i in 1..100000 loop
3 insert into test_tablespace_size values('0123456789');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed
4）此时，表空间文件testsize_data.dbf将增长为3M。
·225·
2．删除表空间testsize，同时删除其物理文件。
删除表空间应该使用drop tablespace命令，同时删除物理文件，应使用including contents and datafiles。
SQL> drop tablespace testsize including contents and datafiles;
Tablespace dropped.
3．在数据库中创建一个表test_bak，并向其中插入10条记录。利用exp/imp命令来实现该数据表的备份/恢复。
1）在数据库中创建表test_bak(id number)。
SQL> create table test_bak(id number);
Table created
2）向其中插入10条数据。
SQL> begin
2 for i in 1..10 loop
3 insert into test_bak values(i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
3）利用exp命令备份该表
C:\>exp system/abc123@//192.168.16.5/orcl tables=(test_bak) file='d:/test_bak.bak'
Export: Release 10.1.0.2.0 - Production on 星期六7月16 14:51:54 2011
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_BAK 10 rows exported
Export terminated successfully without warnings..
4）在数据库中删除表test_bak。
SQL> drop table test_bak;
Table dropped
5）将表test_bak重新导入数据库
C:\>imp system/abc123@//192.168.16.5/orcl tables=(test_bak) file='d:/test_bak.bak'
·226·
Import: Release 10.1.0.2.0 - Production on 星期六7月16 14:54:24 2011
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
. . importing table "TEST_BAK" 10 rows imported
Import terminated successfully without warnings.
6）重新查询表test_bak
SQL> select * from test_bak;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected
第6章约束
1．查看表customers的主键状况，如果有，则重建其主键，如果没有，选择其中一列创建主键。
1）利用如下SQL语句查看表customers的主键状况：
SQL> select table_name, constraint_name, constraint_type, status from user_constraints
2 where table_name = 'CUSTOMERS' and constraint_type='P';
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
------------------------------ ------------------------------ ------------------------------ ------------
CUSTOMERS SYS_C005015 P ENABLED
2）此时，在已有主键的情况下，首先删除主键SYS_C005015。
SQL> alter table customers drop primary key;
·227·alter table customers drop primary key
ORA-02273: this unique/primary key is referenced by some foreign keys
3）表customers中的主键与其他表的外键关联，可以利用cascade选项来删除关联约束。
SQL> alter table customers drop primary key cascade;
Table altered
4）重新创建基于列customer_id的主键。
SQL> alter table customers add primary key(customer_id);
Table altered
2．在数据库中，创建表country(country_id, country_name)、city(city_id, country_id，city_name)，并建立city.country_id到country.country_id的外键关联。
1）创建表country和city
SQL> create table country(country_id number, country_name varchar2(50));
Table created
SQL> create table city(city_id number, city_name varchar2(50), country_id number);
Table created
2）在表country的country_id列上创建主键约束
SQL> alter table country add primary key(country_id);
Table altered
3）在表city上创建country_id到表country(country_id)的外键关联
SQL> alter table city add foreign key (country_id) references country(country_id);
Table altered
3．验证所建外键关联的作用。
1）尝试向表city中添加城市信息。
SQL> insert into city (city_id, city_name, country_id) values (1, '北京', 1);
insert into city (city_id, city_name, country_id) values (1, '北京', 1)
ORA-02291: integrity constraint (SYSTEM.SYS_C005086) violated - parent key not found
由于表country中并不存在country_id为1的值，因此，将导致添加失败。
2）向表country中添加country_id为1的信息。
SQL> insert into country values(1, '中国');
1 row inserted
3）再次为表citry添加城市信息。
SQL> insert into city (city_id, city_name, country_id) values (1, '北京', 1);
1 row inserted
·228·
第7章视图
1．在数据库中不存在表animals(animal_id, animal_name, animal_type)的情况下，强制创建视图vw_animal_cat(animal_id, animal_name)。该视图中，仅含有animal_type=’cat’的猫科动物的信息。
SQL> create or replace force view vw_animal_cat(animal_id, animal_name)
2 as
3 select animal_id, animal_name, animal_type from animals where ainmal_type='cat'
4 /
Warning: View created with compilation errors
2．创建一个物化视图mv_user_objects(object_type, objectCount)，其数据来源于user_objects(owner, count(object_name))，也就是对每种object类型统计其object的数目。
1）因为物化视图中，不能使用子查询。而关系视图又被当做子查询看待。因此，首先需要获得user_objects的拷贝，创建一个新表tmp_user_objects。
SQL> create table tmp_user_objects as select * from user_objects;
Table created
2）利用新表tmp_user_objects来创建物化视图
SQL> create materialized view mv_user_objects
2 as
3 select object_type, count(object_name) object_count from tmp_user_objects
4 group by object_type
5 /
Materialized view created
SQL> select * from mv_user_objects;
OBJECT_TYPE OBJECT_COUNT
-------------------------------- -----------------------
FUNCTION 7
INDEX 189
INDEX PARTITION 31
LOB 24
PACKAGE 2
PACKAGE BODY 2
PROCEDURE 9
QUEUE 4
SEQUENCE 25
SYNONYM 8
TABLE 197
TABLE PARTITION 27
TRIGGER 16
TYPE 4
VIEW 16
·229·
15 rows selected
3．分别启用/禁用物化视图mv_user_objects ，来查看select object_type, count(object_name) object_count from tmp_user_objects 的执行效率。
1）对于SQL 语句，select owner, count(object_name) from dba_objects group by owner
未启用查询重写功能时，其执行计划如下所示：
2）利用enable query rewrite 选项，启用物化视图mv_user_objects 的查询重写功能
alter materialized view mv_user_objects enable query rewrite
3）重新执行相同的SQL 语句，查看此时的执行计划
第8章 函数与存储过程
1．创建一个函数is_date ，并传入一个字符串函数。如果该字符串可以转换为“YYYY-MM-DD hh24:mi:ss ”形式的日期，那么返回为真，否则返回为假。
1）首先利用create or replace function 命令创建is_date 函数
SQL> create or replace function is_date (param varchar2) return varchar2 is 2 d date; 3 begin
4 d:=to_date (nvl (param, ' '), 'yyyy-mm-dd hh24:mi:ss');
5 return 'TRUE'; 6
7 exception
8 when others then
·230·
9 return 'FALSE';
10 end;
11 /
Function created
to_date (nvl (param, ' '), 'yyyy-mm-dd hh24:mi:ss')用于将字符串参数param转换为日期时间型，如果转换成功，则返回“TRUE”；exception则用于处理异常情况，如果发生异常，函数将返回“TRUE”。
2）可以利用如下语句测试is_date()函数。
SQL> select is_date('2010') as is_date from dual;
IS_DATE
--------------------------------------------------------------------------------
FALSE
SQL> select is_date('abc') as is_date from dual;
IS_DATE
--------------------------------------------------------------------------------
FALSE
SQL> select is_date('20100512') is_date from dual;
IS_DATE
--------------------------------------------------------------------------------
TRUE
2．创建一个存储过程find_student，并传入参数学生姓名(studentName)，打印表students中所有同名的学生信息。如果未找到同名学生，那么打印“无名为xxx的学生”。
1）利用如下SQL语句创建存储过程find_student
SQL> create or replace procedure find_student(studentName varchar2)
2 as
3
4 begin
5 declare student_count number;
6 begin
7 select count(*) into student_count from students where student_name=studentName;
8 if student_count>0 then
9 dbms_output.put_line('共找到' || student_count || '个名为' || studentName || '的学生！');
10 else
11 dbms_output.put_line('未找到名为' || studentName || '的学生！');
12 end if;
13 end;
14 end;
15 /
Procedure created
2）尝试查找名为“张三”的学生
SQL> exec find_student('张三');
·231·
共找到3个名为张三的学生！
PL/SQL procedure successfully completed
3）尝试查找名为“李四”的学生
SQL> exec find_student('李四');
未找到名为李四的学生！
PL/SQL procedure successfully completed
3．利用PL/SQL Developer 的Debug 功能调试存储过程find_student
。 1）在PL/SQL Developer 的Procedures 下，找到存储过程find_student
。 2）在右键菜单中选择【Test
】
3）在参数栏内填入要传入的参数，并单击Debug 按钮或者按下F9。
4）此时，调试步骤按钮栏将变为可用。
从左至右依次为：
Run （继续执行，直至程序结束，或者下一个断点） Step into （进入存储过程/函数内部）
·232·
Step Over（执行当前语句，在下一条语句处停止）
Step out（跳出当前存储过程/函数）
Run to next exception（执行直至下次抛出异常）
4）利用这5个按钮，即可进行存储过程的调试。
第9章游标
1．声明一个游标cu_sutdnet，并向该游标传递参数studentName，来获得所有与参数同名的学生信息。对该游标依次执行打开、获取、关闭的步骤来依次打印获得的学生信息。
1）声明带有参数的游标时，应将参数列表置于小括号内
declare cursor cu_student_id_name(studentName)
2）声明变量临时存取学生姓名和学号
student_id students.student_id%type;
student_name students.student_name%type;
3）打开游标时，传入参数studentName
open cu_student_id_name('张三');
4）获取游标数据
fetch cu_student_id_name into student_id, student_name;
5）循环打印学生信息
while cu_student_id_name %found loop
dbms_output.put_line(student_id || ':' || student_name);
fetch cu_student_id_name into student_id, student_name;
end loop;
6）关闭游标
close cu_student_id_name;
7）执行结果如下
SQL> declare cursor cu_sutdnet(studentName in varchar2) is
2 select student_id, student_name
3 from students where student_name=studentName;
4
5 student_id students.student_id%type;
6 student_name students.student_name%type;
7 begin
8 open cu_sutdnet('张三');
9 fetch cu_sutdnet into student_id, student_name;
10
11 while cu_sutdnet%found loop
12 dbms_output.put_line(student_id || ':' || student_name);
13 fetch cu_sutdnet into student_id, student_name;
14 end loop;
15
16 close cu_sutdnet;
17 end;
·233·
18 /
17:张三
18:张三
21:张三
PL/SQL procedure successfully completed
2．声明一个名为studentname的变量，然后利用cursor for游标来实现习题1的功能。通过修改studentname的值，来模拟传入参数功能。
SQL> begin
2 declare
3 studentname varchar2(20);
4 begin
5 studentname := '张三';
6 for student in (select * from students where student_name = studentname) loop
7 dbms_output.put_line(student.student_id || ':' ||
8 student.student_name);
9 end loop;
10 end;
11 end;
12 /
17:张三
18:张三
21:张三
通过修改studentname的值，可以查找特定学生名称的所有信息。
3．创建函数get_student_cursor，返回表students中的所有学生信息的集合
1）利用如下SQL语句创建名为get_student_cursor的函数
SQL> create or replace function get_student_cursor
2 return SYS_REFCURSOR
3 is
4 student_cursor sys_refcursor;
5 begin
6 open student_cursor for select * from students;
7 return student_cursor;
8 end;
9 /
Function created
2）利用返回的游标类型，打印所有学生姓名
SQL> begin
2 declare student_cursor sys_refcursor;
3 student students%rowtype;
4 begin
5 student_cursor := get_student_cursor();
6 loop
7 fetch student_cursor into student;
8 dbms_output.put_line(student.student_name);
·234·
9 exit when (student_cursor%notfound);
10 end loop;
11 end;
12 end;
13 /
王松
金瑞
钟君
王山
刘迪
钟会
张玉
柳青
胡东
商乾
王蒙
周兵
王云
刘兵
胡玉
张洁
张三
张三
王云
张军
张三
张三
PL/SQL procedure successfully completed
第10章触发器
1．现有一个海量数据表products(product_id number, product_name varchar2(120))，代表产品表。有一个小数据表popular_prodcuts(id, product_id, product_name)，代表那些畅销产品。由于products 数据表具有海量数据，因此，popular_prodcuts是一个合理的冗余。但是，现在要求表products中的product_name改变之后，表popular_products中的product_name随之改变。创建一个行级触发器tr_product来实现该功能。
1）创建表products，并插入实例数据
SQL> create table products(product_id number, product_name varchar2(120));
Table created
SQL> insert into products values(1, '海尔冰箱');
·235·
1 row inserted
SQL> insert into products values(2, '格力空调');
1 row inserted
SQL> insert into products values(3, '戴尔电脑');
1 row inserted
SQL> commit;
Commit complete
SQL> create table popular_products(id number, product_id number, product_name varchar2(120));
Table created
SQL> insert into popular_products values(1, 2, '格里空调');
1 row inserted
SQL> commit;
Commit complete
2）在表products上创建触发器tr_product，一旦表products中的产品信息发生了改变，那么将同步更新到表popular_products中。
SQL> create or replace trigger tr_product
2 after update
3 on products for each row
4 begin
5 update popular_products set product_name=:new.product_name where product_id=:new.product_id;
6 end;
7 /
Trigger created
其中，after update代表该触发器是在表products的update动作之后触发；update popular_products set product_name=:new.product_name where product_id=:new.product_id则利用更新表products之后的新的product_name来更新表popular_products中的数据。
3）尝试更新products中的“格力空调”为“格力中央空调”。
SQL> update products set product_name = '格力中央空调' where product_id=2;
1 row updated
SQL> commit;
Commit complete
4）验证两个表中的数据是否一致