数据库实验答案

实验四

五、实验方法和步骤：

1.向数据库的USERS表空间添加一个大小为10M的数据文件userdata0

2.dbf。

Alter tablespace users add datafile ‘c:\oracle\oradata\orcl\userdata02.dbf’ size 10M;

2.向数据库的TEMP表空间添加一个大小为5N的临时数据文件temp02.dbf。

Alter tablespace temp add tempfile’c:\oracle\oradata\orcl\temp02.dbf’ size 5M;

3.修改userdata02.dbf为自动增长方式，每次增长512k，没有限制大小。

Alter database datafile ‘c:\oracle\oradata\orcl\userdata02.dbf’ autoextend on next 512k maxsize unlimited;

4.取消userdata02.dbf的自动增长方式。

Alter database datafile ‘c:\oracle\oradata\orcl\userdata02.dbf’ autoextend off;

5.将userdata02.dbf大小设置为8MB。

Alter database datafile ‘c:\oracle\oradata\orcl\userdata02.dbf’ resize 8M;

6.将userdata02.dbf设置为脱机。

Alter database datafile ‘c:\oracle\oradata\orcl\userdata02.dbf’ offline;

7.将userdata02设置为联机。

Alter database datafile ‘c:\oracle\oradata\orcl\userdata02.dbf’ online;

8.改变userdata02的名称。

(1)alter tablespace users offline;

(2)将userdata02.dbf重命名为userdata002.dbf;

(3)alter tablesace users rename datafile

‘c:\oracle\oradata\orcl\userdata02.dbf’,

‘c:\oracle\oradata\orcl\userdata002.dbf’;

(4)alter tablespace users online;

9.查询数据文件信息（包括数据文件动态信息；数据文件详细信息；临时数据文件信息）。

(1)select name,file#,status,checkpoint_change# from V$DATAFILE;

(2)select tablespace_name,bytes,autoextensible,file_name from DBA_DATA_FILES;

(3)select tablespace_name,file_name,autoextensible from DBA_TEMP_FILES;

实验五表空间、段、区、块的管理

五、实验方法和步骤：

1.编辑一个脚本文件TEST，脚本文件内容为创建一个永久性的Student表空间，区自动扩展，段采用手动管理方式，并执行脚本文件。

Create tablespace student datafile ‘c:\oracle\oradata\ora404\orcltbs1_1.dbf’

2.为数据库的student表空间添加一个大小为10MB的新的数据文件。

Alter tablespace student add datafile ‘c:\oracle\oradata\ora404\orcltbs1_2.dbf’ size 10M

3.创建一个临时表空间mytemp，并为临时表空间添加一个大小为10MB的临时数据文件。

Create temporary tablespace mytemp tempfile ‘c:\oracle\oradata\ora404\orcltemp1_1.dbf’size 20M extent management local uniform size 16M;

Alter tablespace mytemp add tempfile c:\oracle\oradata\ora404\orltemp1_2.dbf’ size 10M

4.将student表空间新添加的数据文件的大小改为20MB。

Alter database datafile c:\oracle\oradata\ora404\orcltbs1_2.dbf’ resize 20M

5.将studnet表空间设置为offline；再设置为online。

Alter tablespace student offline;

Alter tablespace student online;

6.将student表空间设置为只读状态。

Alter tablespace student read only;

7.为student表空间创建一个回退段。

Create rollback segment student_roll tablespace student;

8.删除student表空间及其所有内容。

Drop tablespace student including contents;

9.查询表空间基本信息。

Select tablespace_name, extent_management,a llocation_type, contents from DBA_TABLESPACE;

10.查询表空间数据文件信息。

Select file_name, blocks, tablespace_name from DBA_DATA_FILES;

11.统计表空间空闲空间信息。

SELECT TABLESAPCE_NAME “TABLESPACE”, FILE_ID, COUNT(*) “PIECES”, MAX(BLOCKS) “MAXIMUM”, MIN(BLOCKS) “MINIMUM”, AVG(BLOCKS) “AVERAGE”, SUM(BLOCKS) “TOTAL” FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME, FILE_ID;

12.查询表空间空闲空间大小。

SELECT TABLESPACE_NAME, SUM(BYTES) FREE_SPACES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

13.查询scott用户emp表的某个员工元组的物理地址（ROWID）。

SELECT ROWID, EMPNO FROM SCOTT.EMP WHERE EMPNO=7369;

实验六模式对象管理—基础表

五、实验方法和步骤：

1、创建一个表

目标：创建一个名为STUDENT的表，该表有ID（学号）、NAME（姓名）、SEX（性别）、COURSE（课程）、SCORE（成绩）字段，并保存在USER01表空间。

（1）在SQL*PLUS中执行使用CREATE TABLE语句在某一表空间中创建STUDENTS表，并通过INSERT、SELECT、UPDATE、DELETE语句对表进行操作，各举一例。

（2）通过OEM控制台新的表，并查看新表情况。

Create table student(

Id number primary key,

Name varchar2(20),

Sex char(1),

Course varchar2(20),

Score number(5,2)

) tablespace users

Insert into student(id) values(1);

Select id from student;

Update student set id=2 where id=1;

Delete from student where id=2;

2、创建一个临时表

目标：创建一个名为TEMP01的事务级别临时表，该表有ID（学号）、NAME（姓名）字段，并保存在temp表空间中。

Create global temporary table temp01( id number（2） primary key, name varchar(20)) on commit delete rows;

3、修改表

目标：修改STUDENTS表的存储参数；为表分配新的存储空间；添加一个新的列；设置列为无用状态（UNUSED）。

（1）在SQL*PLUS中执行ALTER TABLE语句修改STUDENTS表的存储参数(修改pctfree和pctused分别为20和40）。

Alter table student pctfree 20;

Alter table student ptused 40;

（2）在SQL*PLUS中执行ALTER TABLE语句在STUDENTS表分配大小为400KB的新区。

Alter table student allocate extent(size 400k);

（3）在SQL*PLUS中执行ALTER TABLE语句在STUDENTS表新增加一个AGE列，列的类型为NUMBER(2)。

Alter table student add(age number(2));

（4）在SQL*PLUS中执行ALTER TABLE语句在将STUDENTS表中的AGE设置为无用状态。

Alter table student set unused(age);

4、截断表

目标：删除表STUDENTS中的所有记录。

（1）在SQL*PLUS中执行TRUNCATE TABLE语句删除STUDENTS表中的所有记录。

Truncate table student;

（2）在SQL*PLUS中执行SELECT语句查看STUDENTS表中的内容。

Select * from student;

5、查询ORACLE数据库的模式对象信息

目标：使用管理工具（SQL*PLUS或OEM）获得有关数据库模式对象的有关信息。

（1）用SCOTT帐户登录SQL*PLUS，显示用户，并通过用户视图USER_TABLES、USER_VIEWS、USER_INDEXS、USER_OBJECTS、USER_TRIGGERS、USER_SEQUENCES等模式对象信息。

Show user

Select table_name from user_tables;

Select view_name from user_views;

Select index_name from user_indexes;

Select object_name from user_objects;

Select trigger_name from user_triggers;

Select sequence_name from user_sequence;

（2）使用SCOTT帐户登录数据库，在OME控制台中查看SCOTT用户的各种模式对象信息。

6、给表student表的nane列添加一个唯一值约束，并删除该约束

Alter table student add constraint p_uk unique(name);

Alter table student drop drop constraint p_uk;

实验七模式对象管理—视图

五、实验方法和步骤：

1、创建基础表BOOKS，包含BOOK_ID,数字型，BOOK_NAME,可变长50位，AUTHOR,可变长20位，PRESS 可变长50位,Price数字型，PUBLISH_DATE，日期型；并插入10条数据。

Create table books(

Book_id number primary key,

Book_name varchar2(50) not null,

Author varchar2(20),

Press varchar2(50),

Price number,

Publishdate date

);

2、关系视图

（1）建立关系视图V_BOOKS，包含书名、作者、出版社。

Create view v_books as

Select book_id 编号，author 作者，press 出版社from books;

（2）查看视图定义。

Select text from user_views where view_name=’VW_BOOKS’；

（3）查看视图内容。

Select * from vw_books;

（4）修改视图，增加一个列出版日期。

Create or replace view VW_BOOKS As

select book_id 编号，author 作者，press 出版社，publishdate 出版日期from books;

（5）向基础表增加一个属性列“价格”，数字型。查看视图的可用性状态。

alter table boks add price int;

Select object_name,status from user_objects where object_name=’VW_BOOKS’ and

object_type=’VIEW’;

（6）重新编译V_BOOKS视图。再查看视图的可用性状态

alter view vw_books compile

Select object_name,status from user_objects where object_name=’VW_BOOKS’ and

object_type=’VIEW’;

（7）删除该视图。

drop view vw_books;

3、内嵌视图

（1）查询价格由高到低处于前三位的书本信息，注意采用内嵌视图的方式实现。

select * from

(select book_id,book_name ,price

from books

order by price desc)

where rownum<=3;

（2）利用内嵌视图将所有书籍的价格增加10元。

update

(select price

from books

)

set price=price+10

实验十 PL/SQL程序结构

五、实验方法和步骤：

1. 在SQL*Plus中使用PL/SQL块处理EMP表中职工号7788的职工，如果工资小于5000那么把工资更改为5000。

（1）首先采用SCOTT用户登录；

（2）查询7788员工的工资是多少；

（3）定义声明块和执行块，修改该员工工资；

（4）再查询该员工工资是多少；

SQL>DECLARE

X NUMBER(7,2);

BEGIN

SELECT sal INTO x FROM emp WHERE empno = 7788;

IF x < 5000

THEN UPDATE emp SET sal = 5000 WHERE empno = 7788;

END IF;

END;

2. 利用三种循环控制结构向表中插入150条记录。

（1）创建一张表，用来记录循环指针的值；

Create table temp_table (num_col number, info_col char(10));

（2）利用LOOP循环向表中插入前50条记录；

DECLARE

v_counter BINARY_INTEGER :=1;

BEGIN

LOOP

INSERT INTO temp_table values(v_counter,’loop index’);

v_counter := v_counter+1;

EXIT WHEN v_counter>50;

END LOOP;

END;

（3）利用WHILE循环向表中插入51-100条记录；

DECLARE

v_counter BINARY_INTEGER :=1;

BEGIN

WHILE v_counter<100 LOOP

INSERT INTO temp_table values(v_counter,’loop index’);

v_counter := v_counter+1;

END LOOP;

END;

（4）利用FOR循环向表中插入101-150条记录；

DECLARE

v_counter BINARY_INTEGER :=1;

BEGIN

FOR v_counter IN 101..150 LOOP

INSERT INTO temp_table values(v_counter,’loop index’);

END LOOP;

END;

（5）查看表中的信息。

SLELECT * FROM TEMP_TABL

实验十一游标

五、实验方法和步骤：

1．声明显式游标

声明一个游标用来读取基表EMP中部门号是20且工作为分析员的职工：

DECLARE

Cursor c1 IS

SELECT ename, sal, hiredate FROM emp

WHERE deptno = 20 AND job = 'ANALYST';

v_ename VARCHAR2(10);

v_sal NUMBER(7,2);

v_hiredate date;

begin

OPEN c1;

FETCH c1 INTO v_ename, v_sal, v_hiredate;

CLOSE c1;

end;

2．游标的应用

（1）利用游标修改数据，如果EMP中部门号是20，工作为分析员的职工工资小于5000，更改为5000：DECLARE

CURSOR c1 IS

SELECT empno, sal, hiredate, rowid

FROM emp WHERE deptno = 20 AND job = 'ANALYST'

FOR UPDATE OF sal;

emp_record c1%ROWTYPE;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO emp_record;

EXIT WHEN c1%NOTFOUND;

IF emp_record.sal <5000 THEN

UPDATE emp set sal = 5000 where empno = emp_record.empno;

END IF;

END LOOP;

CLOSE c1;

END;

（2）利用游标，如果部门是SALES，地址不是DALLAS的，地址更改为DALLAS；如果部门不是SALES，地址不是NEW YORK的，地址更改为NEW YORK：

DECLARE CURSOR c1 IS

SELECT dname, loc FROM dept

FOR UPDATE OF loc;

Dept_rec c1%ROWTYPE;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO dept_rec;

EXIT WHEN c1%NOTFOUND;

IF dept_rec.dname = 'SALES' AND dept_rec.loc != 'DALLAS'

THEN UPDATE dept SET LOC = 'DALLAS' WHERE CURRENT OF C1;

ELSE IF DEPT_REC.DNAME!= 'SALES' AND DEPT_REC.LOC!= 'NEW YORK'

THEN UPDATE dept SET LOC = 'NEW YORK' WHERE CURRENT OF C1;

END IF;

END LOOP;

CLOSE c1;

END;

实验十二存储过程和函数

1. 编写一个存储过程，用它显示scott.dept、scott.emp表中各部门的名称、员工总

数以及员工的工资总和和平均工资。

set serverout on;

create or replace procedure outputEMP

as

begin

declare

cursor cEMP is

select dname,count(*),avg(sal),sum(sal)

from scott.emp,scott.dept

where scott.emp.deptno=scott.dept.deptno

group by dname;

d_name varchar2(14);

count_EMP number;

sum_sal number(7,2);

avg_sal number(7,2);

begin

open cEMP;

fetch cEMP into d_name,count_EMP,avg_sal,sum_sal;

while cEMP %found loop

dbms_output.put_line(d_name||','||count_EMP||','||avg_sal||','||sum_sal);

fetch cEMP into d_name,count_EMP,avg_sal,sum_sal;

end loop;

close cEMP;

end;

end outputEMP;

2. 编写一个函数，用它计算0到指定数字（作为函数输入参数）之间的整数和。

set sertverout on;

create or replace function get_sum(maxNUM number)

return number as

begin

declare sumNUM number;

i number;

begin

sumNUM:=0;

i:=maxNum;

while i>0 loop

sumNUM:=sumNUM+i;

i:=i-1;

end loop;

return sumNUM;

end;

end get_sum;

实验十三触发器

1. 编写一个触发器，用它把数据库系统的关闭事件记录到一个表(表结构自己设计)内。

Create table shutdown_log(shut_user varchar2(20),

shut_time date,

shut_action varchar2(20));

create or replace trigger shutdown_record

before shutdown

on database

begin

insert into shutdown_log values(user,sysdate,'关闭数据库');

end;

2. 编写一个行触发器，把emp表增、删、改操作前后员工的姓名和工资前后变化情况显示处理。create or replace trigger trg_emp_dml_row

Before insert or update or delete

on scott.emp

For each row

Begin

If inserting then

Dbms_output.put_line(:new.empno||’ ’||:new.ename);

Elsif updating then

Dbms_output.put_line(:old.sal||’ ’||:new.sal);

Else

Dbms_output.put_line(:old.empno||’ ’||:old.ename);

End if;

End trg_emp_dml_row;