Oracle数据库基础教程课后实验---实验报告.doc

Oracle数据库基础教程实验报告专 业 计算机科学与技术 姓 名 李文成 学 号 2010 指导老师 肖卓宇 实验一 创建数据库和表目的与要求（1） 了解oracle数据库的逻辑结构和物理结构。（2） 了解Oracle Enterprise Manager的使用情况。（3） 学习关闭和启动数据库实例的方法。（4） 学会使用SQL语句创建数据库的方法。（5） 学会使用SQL语句删除数据库的方法。实验准备 首先要了解Oracle数据库的逻辑结构，包括方案对象，数据块、区间、段、和表空间等。数据库由若干个表空间组成，表空间由表组成，表由段组成，段由区间组成，区间则由数据块组成。Oracle数据库的物理结构由构成数据库的操作系统文件所决定。每个Oracle数据库都由3种类型的文件组成：数据文件、日志文件和控制文件。这些数据库文件为数据库信息提供真正的物理存储。Enterprise Manager 10g是Oracle 10g提供的新的管理工具，简称EM。使用它可以完成启动、关闭数据库，创建、删除数据库等功能。只有系统管理员或拥有CREATE DATABASE权限的用户才能创建数据库。可以在Enterprise Manager中通过图形界面创建数据库，也可以使用CREATE DATABASE语句创建数据库。实验内容oracle 服务器端的安装先解压10201_client_win32.zip压缩包，双击setup.exe。 如下图：稍等之后进入欢迎界面，如下图：点击下一步选择“管理员”安装类型，点击下一步：选择安装的详细目录后点击下一步。点击安装：选择执行典型配置：点击下一步：完成。 创建数据库使用OEM创建表图1-1创建Employees表图1-2成功创建Employees表图1-3成功删除Employees表图1-4创建Departments表图1-5成功创建Departments表图1-6 成功删除Departments表图1-7创建Salary表图1-8成功创建Salary表图1-9成功删除Salary表在PL/SQL语句创建表1.创建Employees表create table Employee(EmployeeID char(6) NOT NULL PRIMARY KEY,Name char(10) NOT NULL,Bithday date NOT NULL,Sex number(1) NOT NULL,Address char(20) NULL,Zip char(6) NULL,PhoneNumber char(12) NULL,DepartmentID char(3) NOT NULL REFERENCES Departments(DepartmentID)tablespace users;图1-10 使用SQL语句创建Employees表2.创建Departments表create table Departments(DepartmentID char(3) NOT NULL PRIMARY KEY,Departmentname char(20) NOT NULL,Note varchar2(100) NULL)tablespace users;图1-11 使用SQL语句创建Departments表3.创建Salary表create table Salary(EmployeeID char(6) NOT NULL PRIMARY KEY,InCome number(8,2) NOT NULL,OutCome number(8,2) NOT NULL)tablespace users;图1-11 使用SQL语句创建Salary表实验二 角色和用户管理目的与要求（1） 了解Oracle数据库用户和角色的概念。（2） 学习使用SQL语句创建Oracle用户。（3） 学习使用SQL语句创建Oracle角色。（4） 学习使用SQL语句指定用户的角色。实验准备（1） 了解Oracle数据库用户可以分为6种类型，即数据库管（2） 了解角色是对用户的一种分类管理办法，不同权限的用户可以分为不同的角色。（3） 了解使用CREATE ROLE语句创建角色的方法。（4） 了解使用DROP ROLE语句删除角色的方法。（5） 了解使用GRANT语句指定用户角色的方法。（6） 了解使用CREATE USER语句创建用户的方法。（7） 了解使用DROP USER语句删除用户的方法。实验内容1. 创建用户在YGGL 数据库中创建一个用户manager 授予dba角色和sysdba系统权限，他可以替代system 系统用户。Connect system/mrl as sysdba /*以sysdba 身份登录*/Create user manager Profile default Identified by manager Default tablespace users Temporary tablespace temp Account unlock;图2-1图2-2/*授予权限*/Grant sysdba to manager;Grant connect to manager;Grant dba to manager;Alter user manager grant connect through system;图2-32. 创建角色在YGGL 数据库中创建一个角色admin 授予dba角色和sysdba系统权限Create role admin Identified by manager; Grant sysdba to admin;图2-4Grant sysoper to admin;Grant connect to admin;Grant dba to admin;图2-53. 创建概要文件在YGGL数据库中创建概要文件YGGLPROFILE 并分配给用户MANAGER。Create profile yggl_profile limitComposite_limit default failed_login_attempts 3Password_lock_time 20 password_grace_time 60Password_life_time 30 password_reuse_max unlimitedPassword_reuse_time 120 password_verify_function default;图2-6实验三 表和视图管理目的与要求（1） 了解Oracle表和视图的概念。（2） 学习使用SQL语句创建表。（3） 学习使用SELECT语句查询数据。（4） 学习使用SQL语句创建视图。实验准备了解表是数据库中最常用的存储单元，它包括所有用户可以访问的数据。作为关系型数据库，Oracle表由行和列组成。 视图是一个虚拟的表，它在物理上并不存在。视图可以把表或其他视图的数据按照一定的条件组合起来所以也可以把它看成是一个存储的查询。视图并不包含数据，它只是从基本表中读取数据。了解使用CREATE TABLE语句创建表的方法。了解使用SELECT语句查询数据的方法。了解使用SQL语句创建视图的方法。实验内容使用PL/SQL语句，在实验1建立的数据库的三个表Employees、Departments和Salary中分别插入多行数据记录，然后修改和删除一些记录。使用PL/SQL进行有限制的修改和删除。使用PL/SQL命令操作数据（1） 使用PL/SQL命令分别向Employees、Departments和Salary表中插入一行记录。在启动SQL*PLUS窗口中输入以下PL/SQL语句并执行。INSERT INTO Employees VALUES(011112,罗林,TO_DATE(19730626,YYYYMMDD),1,解放路100号 ,210002,4055663,5);select * from Employees;图3-1INSERT INTO Departments VALUES(2,人力资源部,NULL);select * from Departments;INSERT INTO Salary VALUES(011112,1200.09,50);select * from Salary;图3-2（2） 使用PL/SQL命令命令修改表Salary中的某个记录的字段值。UPDATE Salary SET InCome=2890 WHERE EmployeeID=011112;查询修改结果：select * from Salary;图3-3（3） 修改表employees和表departments的记录值，注意完整性。UPDATE Employees SET Address=中南林业科技大学涉外学院 WHERE EmployeeID=011112;select * from Employees;UPDATE Departments SET Departmentname=中情局 WHERE DepartmentID=2;select * from Departments;图3-5（4） 使用PL/SQL命令修改表Salary中的所有记录的字段值。UPDATE Salary SET InCome=InCome+100;查询修改结果，与之前的内容对比。图3-6（5） 使用DELETE语句删除Salary表中一行记录。查询删除前后对比。DELETE from Salary Where EmployeeID=011112;图3-7（6） 使用TRANCATE TABLE语句删除表中所有行。TRUNCATE TABLE Salary;图3-87、SELECT语句的基本使用（1） 对于实验2给出的数据表结构，查询每个雇员的所有数据。在SQL*PLUS窗口中输入如下的语句并执行：SELECT *FROM Employees;图3-9用select语句查询department表和salary表所有记录。select * from departments;图3-10select * from salary;图3-11（2） 查询每个雇员的地址和电话。SELECT Address,PhoneNumber From Employees;图3-12用select语句查询department表和salary表一列或若干列。select departmentid,departmentname from departments;图3-13select employeeid,income from salary;图3-14（3） 查询EmployeeID为000001的雇员的地址和电话。SELECT Address,PhoneNumber From Employees WHERE EmployeeID=011112;select departmentid,departmentname from departments where departmentid=1;select employeeid,income from salary where employeeid=000001;图3-15（4） 查询Employees表中所有女雇员的地址和电话，使用AS子句将结果中各列的标题分别指定为地址和电话。SELECT Address AS 地址 , PhoneNumber AS 电话FROM Employees WHERE sex=0;图3-16（5） 计算每个雇员的实际收入。SELECT EmployeeID,InCome-OutCome AS 实际收入 From Salary;图3-17（6） 找出所有姓王的雇员的部门号。SELECT DepartmentID FROM Employees WHERE name LIKE王%;图3-18找所有地址中含有中山的雇员的豪门及部门号。SELECT employeeid,DepartmentID FROM Employees WHERE address LIKE%中山%;图3-19（7） 找出所有收入在2000元3000元之间的雇员号码。Select EmployeeID from Salary where Income between 2000 and 3000;图3-20找出所有在部门 1 或部门2 工作的雇员的号码。Select EmployeeID from Salary where departmentid =1 or departmentid =2;图3-211. 子查询的使用（1） 查询在财务部工作的雇员的情况SELECT * FROM Employees WHERE DepartmentID=(SELECT DepartmentID from Departments WHERE DepartmentName=财务部);图3-22用子查询的方法找出所有收入在2500元以下的雇员的情况。select * from employeeswhere employeeid in( select employeeid from salarywhere income2500.00);图3-23（2） 查找财务部年龄不低于所有研发部雇员年龄的雇员的姓名。select Name from Employeeswhere DepartmentID IN( select departmentid from departments where departmentName=财务部)AND Birthday all(select income from salary where employeeid in(select employeeid from employeeswhere departmentid IN( select departmentid from departments where departmentname=财务部);图3-25查找比所有财务部的雇员收入都高的雇员的姓名。select name from employeeswhere employeeid in(select employeeidfrom salarywhere incomeall( select income from salary where employeeid in( select employeeid from employees where departmentid=(select departmentid from departments where departmentname=财务部);图3-26用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名。select Name from Employeeswhere Birthday 2000;图3-30查询研发部在1966年以前出生的雇员姓名及薪水详情。Select name,income,outcome From employees,salary,departmentsWhere employees.employeeid=salary.employeeid and Employees.departmentid=departments.departmentid and Departmentname=研发部and birthday 2000;图3-374. GROUP BY和ORDER BY子句的使用（1） 求各部门的雇员数。Select departmentid as 部门号, count(employeeid) as 部门人数From employeesGroup by departmentid;Select count(employeeid) From salaryGroup by departmentid;图3-38统计各个部门收入在2000以上的雇员的数量。select departmentid as 部门号, count(employeeid) as 收入大于2000人数 from employeeswhere employeeid in (Select employeeid From salarywhere income 2000)Group by departmentid;图3-39（2） 将各雇员的情况按收入由低到高排列。Select employees.*,salary.*From employees,salaryWhere employees.employeeid=salary.employeeidOrder by income;图3-40将各个雇员的情况按出生时间先后排序。Select employees.*,salary.*From employees,salaryWhere employees.employeeid=salary.employeeidOrder by birthday;图3-415. 使用视图（1） 创建视图限制查看雇员的某些情况。Create or replace view cx_employeesAsSelect employeeid,name,birthday,sex,departmentidFrom employees;图3-42限制各部门经理只能查找本部雇员的薪水情况，如限制财务部经理想查看自己部门雇员姓名及其薪水详情。Create or replace view cx_salaryAsSelect name,income,outcomeFrom employees,salary,departmentsWhere employees.employeeid=salary.employeeid and Employees.departmentid=departments.departmentid and Departmentname=财务部;图3-43（2） 使用视图查询财务部雇员信息。Select *from cx_employees;图3-44查询财务部雇员薪水情况。Select * from cx_salary;图3-45向表employees中插入一条记录。Insert into cx_employees values(510888,张无忌,to_date(19780823,YYYYMMDD),1,3);图3-46图3-38将张无忌从经理办公室转到市场部。Update cx_employees set departmentid=5Where name=张无忌;图3-47图3-48把张无忌从表employees中删除。Delete from cx_employees where name=张无忌;图3-49图3-50实验四 管理索引和序列目的和要求（1） 了解管理的概念和作用。（2） 了解序列的概念和作用。（3） 学习使用SQL语句创建索引。（4） 学习使用SQL语句创建序列。实验准备（1） 了解索引是对数据库表中一个或多个列的值进行排序的结构。可以利用索引快速访问数据库表中的特定信息。（2） 了解序列好是一个Oracle整数，最多可有38个数字。序列的作用是自动生成整型数值，作为表中标识字段的值。有许多表在创建时定义了一个标识字段，此字段的值需要由系统自动生成，每当插入一条新记录时，此字段的值自动加1.在Oracle中，这个功能有序列来实现。（3） 了解使用CREATE INDEX语句创建索引的方法。（4） 了解使用CREATE SEQUENCE语句创建序列的方法。实验内容1. 建立索引对YGGL数据库中Employees表的DepartmentID列建立索引。CREATE INDEX PK_XS_BAK ON Employees(DepartmentID) TABLESPACE USERS PCTFREE 48 INITRANS 10 MAXTRANS 100 STORAGE(INITIAL 64k NEXT 64K MINEXTENTS 5 MAXEXTENTS 20 PCTINCREASE 10 FREELISTS 1 FREELIST GROUPS 1) PARALLEL(DEGREE DEFAULT); 图4-12. 实现域完整性为YGGL数据库中Employees表的PhoneNumber列建立CHECK约束ALTER TABLE EmployeesADD(CONSTRAINT CH_PHONE CHECK(PhoneNumber BETWEEN 0 AND 9);图4-23. 实现实体完整性（1） 使用SQL语句创建表Departments_2,DepartmentID列为主键CREATE TABLE Departments_2(DepartmentID CHAR(3) NOT NULL,DepartmentName CHAR(20) NOT NULL,Note VARCHAR2(100) NULL,CONSTRAINT PK_Departments PRIMARY KEY(DepartmentID) );图4-3（2） 为表Departments的DepartmentName列建立唯一性索引ALTER TABLE DepartmentsADD(CONSTRAINT UN_Departments UNIQUE(DepartmentName);图4-44. 实现参照完整性为表Departments表中的DepartmentID列建立外键ALTER TABLE EmployeesADD (CONSTRAINT FK_Employees FOREIGN KEY(DepartmentID)REFERENCES Departments(DepartmentsID);图4-5实验五 PL/SQL编程目的与要求（1） 了解PL/SQL语言的结构。（2） 了解PL/SQL变量和常量的声明和使用方法。（3） 学习条件语句的使用方法。（4） 学习分支语句的使用方法。（5） 学习循环语句的使用方法。（6） 学习使用Oracle系统函数。实验准备首先要了解PL/SQL语言是结构化程序设计语言。块是PL/SQL程序中基本的结构，所有PL/SQL程序都是由块组成的。PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成。在PL/SQL中，常量和变量在使用前必须声明，可以使用DECLARE对变量进行声明。实验内容1. 条件结构的使用（1） 查询总学分大于50的学生人数。SET SERVEROUTPUT ON;DeclareV_num number(3);BeginSelect count(*) into V_numFrom xsbWhere zxf 50;If v_num 0 thenDbms_output.put_line(总学分50的人数为：|to_char(V_num);End if;End;说明：执行语句前需要使用set serveroutput on 打开输出缓冲。图5-1（2） 判断计算机系总学分大于40的人数是否超过10人。SET SERVEROUTPUT ON;DECLAREv_num number(3);BEGINSELECT COUNT(*) INTO v_numFROM xsbWHERE zxf40 AND zy=计算机;IF v_num0 THENIF v_num10 THENdbms_output.put_line(计算机系总学分40的人数超过10人);END IF;END IF;END;图5-2（3） 如果“数据库原理”课程的平均成绩高于75，则显示“平均成绩大于75”，否则显示“平均成绩小于75”。SET SERVEROUTPUT ON;DECLARE v_avg number(4,2);BEGINSELECT AVG(cj) INTO v_avgFROM xsb,cjb,kcbWHERE xsb.xh=cjb.xh AND cjb. kch=kcb.kch AND kcb.kcm=数据库原理;IF v_avg75 THENdbms_output.put_line(平均成绩大于75);ELSEdbms_output.put_line(平均成绩小于75);END IF;END;图5-3（4） 求ax2+bx+c=0的根。SET SERVEROUTPUT ON;DECLARE a number; b number; c number; x1 number; x2 number; d number;BEGIN a:=1; b:=4; c:=3; d:=b*b-4*a*c; IF a=0 THEN x1:=-c/b; dbms_output.put_line(只有一个平方根 | to_char(x1); ELSIF d10 THEN EXIT;END IF;END LOOP;dbms_output.put_line(10的阶乘为：|to_char(n);END;图5-5（2） 求10的阶乘。SET SERVEROUTPUT ON;DECLARE n NUMBER:=1;count1 NUMBER:=2;BEGIN LOOPn:=n*count1;count1:=count1+1;EXIT WHEN count1=11;END LOOP;dbms_output.put_line(10的阶乘为：|to_char(n);END;图5-6（3） 求10的阶乘。SET SERVEROUTPUT ON;DECLAREn NUMBER:=1;count1 NUMBER:=2;BEGINWHILE count1=10 LOOPn:=n*count1;count1:=count1+1;END LOOP;dbms_output.put_line(10的阶乘为：|to_char(n);END;图5-7（4） 求10的阶乘。SET SERVEROUTPUT ON;DECLAREn NUMBER:=1;count1 NUMBER;BEGINFOR count1 IN 2.10LOOPn:=n*count1;END LOOP;dbms_output.put_line(10的阶乘为：|to_char(n);END;图5-82. 选择和跳转语句（1） CASE语句应用。SET SERVEROUTPUT ON;DECLAREV_kch char(3);v_Result varchar2(16);BEGINSELECT kch INTO v_kchFROM kcb WHERE kkxq=1;CASE v_kch/*判断v_kch的值，并给出结果 */WHEN 101 THEN v_Result:=计算机基础;WHEN 102 THEN v_Result:=程序设计语言;WHEN 206 THEN v_Result:=离散数学;WHEN 208 THEN v_Result:=数据结构;ELSEv_Result:=Nothing;END CASE;dbms_output.put_line(第一学期的开课课程为：|v_result);END;图5-9（2） 设有一表temp(xh char(6),xb char(2),xm char(8),初始化表temp。SET SERVEROUTPUT ON;DECLAREv_counter BINARY_INTEGER:=1; v_xh number(6); /* */BEGIN v_xh:=100001; LOOP INSERT INTO temp(xh,xb) VALUES(to_char(v_xh), 男);v_counter:=v_counter+1; v_xh:=v_xh+1;IF v_counter=10 THENGOTO loop_end;END IF;END LOOP; dbms_output.put_line(Init Ok);END; 图5-10图5-113. 自定义函数的使用（1） 定义一个函数实现如下功能。对于一个给定的DepartmentID值，查询该值在departments表中是否存在，若存在返回0，否则返回-1。SET SERVEROUTPUT ON;Create or Replace function check_id (did in char)Return numberAsNum number;X number;BeginSelect count(*) into XFrom departmentsWhere departmentid=did;If X0 thenNum:= 0;ElseNum:= -1;End if;Return(num);End;图5-12图5-13图5-14（2） 写一段PL/SQL脚本程序调用上述函数。当向employees表插入一条记录时，首先调用函数CHECK_ID检索该记录的departmentid值在表departments的departmentid字段中是否存在对应的值，若存在，则该记录插入employees表。SET SERVEROUTPUT ON;DECLARE vnumb number(6); BEGIN vnumb:=check_id (1); dbms_output.put_line(to_char(vnumb);END;SET SERVEROUTPUT ON;DeclareNum number;BeginNum:=check_id(2);If num=0 thenInsert into employees values(990210,张文,to_date(19820324,YYYYMMDD),0,南京镇江路2号,210009,3498534,2);End if;End;图5-14图5-15图5-16图5-17图5-18实验6 使用游标、存储过程和触发器目的与要求：（1） 了解游标的概念和工作原理。（2） 了解存储过程的分类和使用方法。（3） 了解触发器的概念。（4） 学习编写和执行自定义过程。（5） 学习编写和执行自定义函数。（6） 学习创建和使用触发器。实验准备：首先要了解游标是映射在结果集中一行数据上的具体实体，有了游标，用户就可以访问结果集中的任意一行数据了。将游标放置到某行后，即可对该数据进行操作，最常见的操作是提取当前行数据。 了解PL/SQL包括3种存储过程，即过程、函数和程序包。 了解触发