21天学通Oracle-课后答案(第三版).docx

257第1章 Oracle安装配置1Oracle服务端安装好之后，在命令提示符下，利用echo %Path%命令查看此时的系统变量Path。在Windows系统中，单击【开始】|【运行】，并键入“cmd”，如下图所示：单击【确定】按钮，将进入Windows命令提示符，如下图所示：键入echo %path%，并按下回车键，将显示此时变量path的值，如下图所示：2利用java version命令，查看此时java环境的版本，以确认是否为Oracle安装时自带的Java文件。 在Windows的【Command】窗口中执行java version命令，将看到本机Java环境的版本，如下图所示：3Oracle数据库服务器安装之后，在硬盘上搜寻名为oradata的文件夹。其中包含了所有数据库的物理文件，查看已有数据库的子文件夹及文件。一个数据库的典型文件包括：后缀为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)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl) as sysdba登录成功的界面如下所示：2EZConnect是EasyConnect的简称。利用EZConnect可以在客户端以IP+SID的方式登录数据。利用NetManager配置EZConnect的连接方式，并利用客户端软件+EZConnect的方式登录数据库ORCL。首先保证Oracle数据库服务器支持EZConnect连接方式，如下图所示：然后，在【Command】窗口中，利用sqlplus登录数据库：sqlplus sys/abc123/192.168.16.5/ORCL as sysdba登录成功的界面如下所示：当然，也可以利用PL/SQL Developer进行登录：3客户端连接Oracle数据库连接时，默认端口为1521。创建一个新的监听程序，其端口为1522。然后将ORCL注册于该监听程序。1）在Net Manager中创建名为LISTENER_1522。为【监听位置】填写主机IP和端口号。注意其端口号为15222）选择监听程序的【数据库服务】，并为其输入ORCL。3）利用【文件】|【保持网络配置】，保存监听程序LISTENER_1522的信息。4）在Windows Command窗口中，启动监听程序LISTENER_1522。C:lsnrctl start listener_1522LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 31-7月 -2011 19:37:17Copyright (c) 1991, 2004, Oracle. All rights reserved.Starting tnslsnr: please wait.TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - ProductionSystem parameter file is D:oracleNETWORKADMINlistener.oraLog messages written to d:oraclenetworkloglistener_1522.logListening 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_1522Version TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - ProductionStart Date 31-7月 -2011 19:37:17Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File D:oracleNETWORKADMINlistener.oraListener Log File d:oraclenetworkloglistener_1522.logListening 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/SQL1利用sqlplus登录数据库，并查看数据库版本。利用SQL Plus登录数据库：C:sqlplus / as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on 星期日 7月 10 18:43:50 2011Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL在控制台上打印出的信息，可以清楚看到Oracle数据库的版本为10.1.0.2.0。2在对数据库进行重要操作时，首先应该确认数据库身份，以免在其他数据库上进行操作。尝试利用SQL Plus显示数据库实例名称。1）利用SQL Plus登录数据库：C:sqlplus / as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on 星期日 7月 10 18:43:50 2011Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL2）键入show parameter instance_name来查看实例名称SQL show parameter instance_nameNAME TYPE VALUE- - -instance_name string orcl3试着利用SQL Plus来创建一个新表test (id number, name varchar2(20)。可以通过如下步骤利用SQL Plus来创建一个新表：（1）利用SQL Plus登录数据库ORCL。在Windows的【开始】|【运行】的【打开】文本框中输入sqlplus scott/abc123orcl来登录数据库ORCL。（2）在SQL Plus命令行下输入如下命令来创建新表test：create table test (id number, name varchar2(20);（3）在SQL Plus会出现表创建成功的提示，如图所示。此时，证明表创建成功。第4章 Oracle数据库1在数据库中创建一个表lob_source(id number, description clob)。将表lob_test的数据导入另外一个数据表lob_dest(id number, description clob)。1）创建表lob_sourceSQL 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_destSQL 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该实例实际说明了针对lob类型的数据的操作方式。由于lob类型的数据的特殊性，因此在实现数据库迁移时，如果遇到棘手的lob类型处理，可以考虑利用本例所演示的方法。2利用exp/imp方式，将数据库orcl中users表的内容，迁移到数据库test中。1)在数据库orcl中，创建测试表usersSQL 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.bakC:exp system/abc123/192.168.16.5/orcl tables=(users) file=d:/users.bakExport: Release 10.1.0.2.0 - Production on 星期三 7月 13 00:06:22 2011Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path . . exporting table USERS 2 rows exportedExport terminated successfully without warnings.3）将d:user.bak的内容导入数据库testC:imp system/abc123/192.168.16.5/test tables=(users) file=d:/users.bakImport: Release 10.1.0.2.0 - Production on 星期三 7月 13 00:10:09 2011Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V10.01.00 via conventional pathimport done in ZHS16GBK character set and AL16UTF16 NCHAR character set. importing SYSTEMs objects into SYSTEM. . importing table USERS 2 rows importedImport 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_sizeNAME TYPE VALUE- - -db_recovery_file_dest_size big integer 2G2）修改其大小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:databaseoracletestsize_data.dbf size 2M 2 autoextend on next 1M 3 / Tablespace created2）创建一个数据表test_tablespace_size(test_data varchar2(100)SQL create table test_tablespace_size(test_data varchar2(100) tablespace testsize; Table created3）利用如下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 completed4）此时，表空间文件testsize_data.dbf将增长为3M。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 created2）向其中插入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 completedSQL commit; Commit complete3）利用exp命令备份该表C:exp system/abc123/192.168.16.5/orcl tables=(test_bak) file=d:/test_bak.bakExport: Release 10.1.0.2.0 - Production on 星期六 7月 16 14:51:54 2011Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path . . exporting table TEST_BAK 10 rows exportedExport terminated successfully without warnings.4）在数据库中删除表test_bak。SQL drop table test_bak; Table dropped5）将表test_bak重新导入数据库C:imp system/abc123/192.168.16.5/orcl tables=(test_bak) file=d:/test_bak.bakImport: Release 10.1.0.2.0 - Production on 星期六 7月 16 14:54:24 2011Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V10.01.00 via conventional pathimport done in ZHS16GBK character set and AL16UTF16 NCHAR character set. importing SYSTEMs objects into SYSTEM. . importing table TEST_BAK 10 rows importedImport terminated successfully without warnings.6）重新查询表test_bakSQL 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 ENABLED2）此时，在已有主键的情况下，首先删除主键SYS_C005015。SQL alter table customers drop primary key; alter table customers drop primary key ORA-02273: this unique/primary key is referenced by some foreign keys3）表customers中的主键与其他表的外键关联，可以利用cascade选项来删除关联约束。SQL alter table customers drop primary key cascade; Table altered4）重新创建基于列customer_id的主键。SQL alter table customers add primary key(customer_id); Table altered2在数据库中，创建表country(country_id, country_name)、city(city_id, country_id，city_name)，并建立city.country_id到country.country_id的外键关联。1）创建表country和citySQL 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 created2）在表country的country_id列上创建主键约束SQL alter table country add primary key(country_id); Table altered3）在表city上创建country_id到表country(country_id)的外键关联SQL alter table city add foreign key (country_id) references country(country_id); Table altered3验证所建外键关联的作用。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 inserted3）再次为表citry添加城市信息。SQL insert into city (city_id, city_name, country_id) values (1, 北京, 1); 1 row inserted第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 errors2创建一个物化视图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 created2）利用新表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 createdSQL select * from mv_user_objects; OBJECT_TYPE OBJECT_COUNT- -FUNCTION 7INDEX 189INDEX PARTITION 31LOB 24PACKAGE 2PACKAGE BODY 2PROCEDURE 9QUEUE 4SEQUENCE 25SYNONYM 8TABLE 197TABLE PARTITION 27TRIGGER 16TYPE 4VIEW 16 15 rows selected3分别启用/禁用物化视图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 rewrite3）重新执行相同的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 9 return FALSE; 10 end; 11 / Function createdto_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-FALSESQL select is_date(abc) as is_date from dual; IS_DATE-FALSESQL select is_date(20100512) is_date from dual; IS_DATE-TRUE2创建一个存储过程find_student，并传入参数学生姓名(studentName)，打印表students中所有同名的学生信息。如果未找到同名学生，那么打印“无名为xxx的学生”。1）利用如下SQL语句创建存储过程find_studentSQL 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_count0 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 created2）尝试查找名为“张三”的学生SQL exec find_student(张三); 共找到3个名为张三的学生！ PL/SQL procedure successfully completed3）尝试查找名为“李四”的学生SQL exec find_student(李四); 未找到名为李四的学生！ PL/SQL procedure successfully completed3利用PL/SQL Developer的Debug功能调试存储过程find_student。1）在PL/SQL Developer的Procedures下，找到存储过程find_student。2）在右键菜单中选择【Test】3）在参数栏内填入要传入的参数，并单击Debug按钮或者按下F9。4）此时，调试步骤按钮栏将变为可用。从左至右依次为：Run（继续执行，直至程序结束，或者下一个断点）Step into（进入存储过程/函数内部）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）打开游标时，传入参数studentNameopen 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; 18 / 17:张三18:张三21:张三 PL/SQL procedure successfully completed2声明一个名为studentname的变量，然后利用cursor for游标来实现习题1的功能。通过修改studentname的值，来模拟传入参数功能。SQL begin 2 declare 3 studentname varchar2(20); 4 begi