Oracle实训报告

ORACLE数据库开发实践报告要求

1．实践名称：Oracle数据库开发

2．实践题目：

（1）Oracle数据库的操作

（2）小型数据库系统的设计与开发

3．实践目的：

初步掌握Oracle关系数据库语言；掌握 Oracle的操作与使用；数据库的建立与管理、数据表的建立与操作等；PL/SQL语言的使用与编程。

4．实践内容与结果：

4.1 Oracle数据库的操作

4.1.1实践操作1

1、熟悉Oracle运行环境，理解存储管理器、方案管理器和安全管理器的作用及操作过程。

要求：

（1）．登录到存储管理器中，查看表空间和回滚段中的情况。（2）．登录到方案管理器中，查看SYS方案下的ACCESS$表。

（3）．在SQL Plus环境下，键入下面内容，执行并观察结果。

•SQL>SET SERVEROUTPUT ON;

•SQL>DECLARE

• 2 n1 NUMBER:=18;

• 3 n2 NUMBER:=6;

• 4 BEGIN

• 5 IF n2<=10 THEN

• 6 n1:=n1+n2;

•7 DBMs_Output.Put_Line(To_Char(n1));

•8 END IF;

•9 END;

•10 /

•

4.1.2实践操作2

1、建立数据库（熟悉DBCA工具的使用）

2、利用OEM创建新用户

SOL语句：

CREATE USER "MENG072" PROFILE "DEFAULT"

IDENTIFIED BY "m" DEFAULT TABLESPACE "USERS"

ACCOUNT UNLOCK;

GRANT UNLIMITED TABLESPACE TO "MENG072" GRANT "CONNECT" TO "MENG072";

GRANT "DBA" TO "MENG072";

3、利用OEM创建Departments表

（1）设置一般信息

（2）设置约束条件

SQL语句：

CREATE TABLE "MENG072"."DEPARTMENTS" ("DEPARTMENTID" CHAR(3) NOT

NULL, "DEPARTMENGTNAME" CHAR(20) NOT NULL, "NOTE"

V ARCHAR2(100) NOT NULL, PRIMARY KEY("DEPARTMENTID")) 4、创建Employees表

SOL语句：

CREATE TABLE "MENG072"."EMPLOYEES" ("EMPLOYEEID" CHAR(6) NOT NULL, "NAME" CHAR(10) NOT NULL, "BIRTHDAY" DATE NOT NULL, "SEX"

NUMBER(1) NOT NULL, "ADDRESS" CHAR(20) NOT NULL, "ZIP"

CHAR(6) NOT NULL, "PHONENUMBER" CHAR(12) NOT NULL, "DEPARTMENTID"

CHAR(3) NOT NULL, PRIMARY KEY("EMPLOYEEID"))

5、创建Salary表

SOL语句：

CREATE TABLE "MENG072"."SALARY" ("EMPLOYEEID" CHAR(6) NOT NULL, "INCOME"

NUMBER(8, 2) NOT NULL, "OUTCOME" NUMBER(8, 2) NOT NULL,

PRIMARY KEY("EMPLOYEEID"))

6、用PL/SQL语句创建表Departments、表Employees、表Salary：

4.1.3实践操作3

1、用OEM向表Employ、表Departments、表Salary中各插入记录：（1）表Departments插入记录

SOL语句：

INSERT INTO "DEPARTMENTS" V ALUES ('1' ,'财务部' ,'1' ); INSERT INTO "DEPARTMENTS" V ALUES ('2' ,'人力资源部' ,'2' ); INSERT INTO "DEPARTMENTS" V ALUES ('3' ,'经理办公室' ,'3' ); INSERT INTO "DEPARTMENTS" V ALUES ('4' ,'研发部' ,'4' ); INSERT INTO "DEPARTMENTS" V ALUES ('5' ,'市场部' ,'5' ); （2）表Employ插入记录

SOL语句：

INSERT INTO "MENG072"."EMPLOYEES" ("EMPLOYEEID" ,"NAME" ,"BIRTHDAY" ,"SEX" ,"ADDRESS" ,"ZIP" ,"PHONENUMBER" , "DEPARTMENTID" ) V ALUES ('000001' ,'王林' ,TO_DA TE('23-1月-1966', 'dd-Mon-yyyy HH:MI:SS AM') ,1 ,'中山路32-1-508' ,'210003' ,'83355668' ,'2' )

INSERT INTO "MENG072"."EMPLOYEES" ("EMPLOYEEID" ,"NAME" ,"BIRTHDAY" ,"SEX" ,"ADDRESS" ,"ZIP" ,"PHONENUMBER" , "DEPARTMENTID" ) V ALUES ('010008' ,'伍荣华' ,TO_DA TE('28-3月-1976', 'dd-Mon-yyyy HH:MI:SS AM') ,1 ,'北京东路100-2' ,'210001' ,'83321321' ,'4' )

INSERT INTO "MENG072"."EMPLOYEES" ("EMPLOYEEID" ,"NAME" ,"BIRTHDAY" ,"SEX" ,"ADDRESS" ,"ZIP" ,"PHONENUMBER" , "DEPARTMENTID" ) V ALUES ('020010' ,'王向荣' ,TO_DATE('09-12月-1982', 'dd-Mon-yyyy HH:MI:SS AM') ,1 ,'四牌楼10-0-108' ,'210006' ,'83792361' ,'1' )

INSERT INTO "MENG072"."EMPLOYEES" ("EMPLOYEEID" ,"NAME" ,"BIRTHDAY" ,"SEX" ,"ADDRESS" ,"ZIP" ,"PHONENUMBER" , "DEPARTMENTID" ) V ALUES ('020018' ,'李莉' ,TO_DA TE('30-7月-1976', 'dd-Mon-yyyy HH:MI:SS AM') ,0 ,'中山东路102-2' ,'210002' ,'83413301' ,'1' )

INSERT INTO "MENG072"."EMPLOYEES" ("EMPLOYEEID" ,"NAME" ,"BIRTHDAY" ,"SEX" ,"ADDRESS" ,"ZIP" ,"PHONENUMBER" , "DEPARTMENTID" ) V ALUES ('102201' ,'刘明' ,TO_DA TE('02-9月-1965', 'dd-Mon-yyyy HH:MI:SS AM') ,1 ,'虎踞路100-2' ,'210013' ,'83606608' ,'5' )

INSERT INTO "MENG072"."EMPLOYEES" ("EMPLOYEEID" ,"NAME" ,"BIRTHDAY" ,"SEX" ,"ADDRESS" ,"ZIP" ,"PHONENUMBER" , "DEPARTMENTID" ) V ALUES ('102208' ,'朱俊' ,TO_DA TE('10-8月-1979', 'dd-Mon-yyyy HH:MI:SS AM') ,1 ,'牌楼巷5-3-106' ,'210004' ,'84708817' ,'5' )

INSERT INTO "MENG072"."EMPLOYEES" ("EMPLOYEEID" ,"NAME" ,"BIRTHDAY" ,"SEX" ,"ADDRESS" ,"ZIP" ,"PHONENUMBER" , "DEPARTMENTID" ) V ALUES ('108991' ,'忠民' ,TO_DA TE('01-10月-1974', 'dd-Mon-yyyy HH:MI:SS AM') ,1 ,'中山路10-3-105' ,'210003' ,'83346722' ,'3' )

（3）表Salary中插入记录