· 226·
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;
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
·227·
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
第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 精品文档
· 228·
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
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 未启用查询重写功能时，其执行计划如下所示：
精品文档
·229·
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 9 return 'FALSE'; 10 end; 11 /
Function created
to_date (nvl (param, ' '), 'yyyy-mm-dd hh24:mi:ss')用于将字符串参数param转换为日期时间型，如果转换成功，则返回“TRUE”；exception则用于处理异常情况，如果发生异常，函数将返回“TRUE”。
精品文档
· 230·
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('张三');
共找到3个名为张三的学生！
PL/SQL procedure successfully completed
3）尝试查找名为“李四”的学生
SQL> exec find_student('李四');
未找到名为李四的学生！
精品文档
21天学通Oracle 课后答案（第三版）
