Oracle数据库应用技术实验指导书.docx
《Oracle数据库应用技术实验指导书.docx》由会员分享，可在线阅读，更多相关《Oracle数据库应用技术实验指导书.docx（31页珍藏版）》请在冰点文库上搜索。
Oracle数据库应用技术实验指导书
Oracle数据应用技术
实验指导书
湖南工程职业技术学院
（刘静）
2010年3月
实验1Oracle入门（2学时）
一、目标
1.能够使用OracleEnterpriseManagerConsole
2.能够创建表空间和用户账户
3.能够使用OracleNetConfigurationAssistant配置网络服务名
4.能够用SQL*Plus连接Oracle服务器
二、实验内容及要求
1.实验内容
（1）使用OracleEnterpriseManagerConsole
熟悉Oracle企业管理器控制台，能够通过图形化的用户界面查看Oracle数据库的物理组件和逻辑组件，加深对Oracle体系结构的理解。
分析：
要熟悉Oracle数据库，必须先安装Oracle服务器软件，或者安装客户端软件，再通过网络配置链接到一个远程的Oracle服务器。
假定已经在本机上安装了Oracle数据库，用户可以使用OracleEnterpriseManagerConsole来管理数据库。
使用企业管理器控制台可以执行各项任务，如管理Oracle实例、创建表空间、用户账户、表和同义词等。
用户需要以管理员身份登陆到Oracle数据库进行练习。
（2）创建表空间
GITM公司想要在数据库总存储员工信息、工资记录、部门信息和产品信息，这些信息将存储在不同的表中。
为了与其他建立在此数据库上的应用系统实现存储空间的独立，需要建一个名为GITM的表空间，此应用系统的中的表都应该存储在此表空间中。
（3）创建用户账户
在数据库中已创建GITM表空间用于存储公司员工和部门信息表。
现在需要一个特定用户，该用户能够访问相关信息，并可创建数据库对象，如表、视图和索引等。
要求创建一个用户，并向其授予连接数据库、创建模式对象和使用表空间的权限。
（4）使用OracleNetConfigurationAssistant
Oracle数据库服务器可以被多个用户访问以执行不同的操作。
要求使用OracleNetConfigurationAssistant工具来配置网络组件，使用户能够访问远程数据库。
（5）使用SQL*Plus和iSQL*Plus连接Oracle服务器
Oracle数据库网络配置成功之后，就可以使用Oracle工具连接和使用数据库了。
SQL*Plus是最常用的Oracle查询工具，要求用前面练习创建的用户登录数据库，以创建应用系统的表。
2.实验要求
根据实验内容指定的要去完成实训，并提交实训结果。
实验2SQL查询和SQL函数（2学时）
一、目标
1．能够构造select语句
2．能够测试SQL操作符
3．能够使用SQL函数
二、实验内容及要求
1.实验内容
（1）测试ROWID和ROWNUM伪列
用户表因为没有建立主键约束而插入了重复的记录，为了解决数据冗余问题，必须删除表中重复的记录。
用户应用程序只关心查询结果集中的一部分，需要限制查询返回的行数。
1）启动SQL*PLUS。
创建表myt_est:
CREATETABLEmy_test（
idNUMBER（3）,
nameVARCHAR2（20））;
2）向表中插入5条完全相同的命令。
INSERTINTOmy_testVALUES（1,'Thisisline1'）;
INSERTINTOmy_testVALUES（1,'Thisisline1'）;
INSERTINTOmy_testVALUES（1,'Thisisline1'）;
INSERTINTOmy_testVALUES（1,'Thisisline1'）;
INSERTINTOmy_testVALUES（1,'Thisisline1'）;
COMMIT;
查看表中记录：
SELECT*FROMmy_test;
3）测试ROWID伪列。
要求查看伪列、id、name，并利用伪列删除第三行记录，再执行查询语句:
SELECTROWNUM,id,nameFROMmy_test;查看第三行记录是否被删除。
4）测试ROWNUM伪列。
使用ROWNUM限制查询ROWNUM<3的行数。
（2）构造select语句
创建表employee：
CREATETABLEemployee（
empnoVARCHAR2（5）PRIMARYKEY,
enameVARCHAR2（25）,
designationVARCHAR2（20）,
dateofjoiningDATE,
branchcodeVARCHAR2（15）REFERENCESBranch_Details（branchcode）,
deptcodeVARCHAR2（15）REFERENCESDept_Details（deptcode））;
INSERTINTOemployeeVALUES（'E001','JohnSmith','Manager','1-2月-1998','BH01','DP02'）;
插入多条记录：
INSERTINTOemployeeVALUES（'E002','Jane','SalesManager','1-6月-2001','BH02','DP02'）;
INSERTINTOemployeeVALUES（'E003','Harry','Accountant','1-11月-2003','BH03','DP01'）;
INSERTINTOemployeeVALUES（'E004','JOE','Accountant','1-12月-2002','BH03','DP03'）;
INSERTINTOemployeeVALUES（'E005','Michael','Manager','1-1月-1999','BH03','DP02'）;
INSERTINTOemployeeVALUES（'E006','JackAranck','SalesManager','1-2月-2003','BH05','DP01'）;
INSERTINTOemployeeVALUES（'E007','George','Sr.Accountant','1-11月-2004','BH05','DP03'）;
INSERTINTOemployeeVALUES（'E008','JohnAranck','Sr.Accountant','1-2月-2005','BH01','DP02'）;
职员表employee中的信息需要做较大的调整，在调整之前要先对表中的总要记录做一个副本。
查询职员信息时需要指定各个列的别名，如以中文名称做标题等。
（3）测试SQL操作符
检查表Salary_Records的描述：
CREATETABLESalary_Records（
empnoVARCHAR2（5）REFERENCESEmployee（empno）,
working_DaysNUMBER,
empsalNUMBER）;
公司的财务部需要检查员工的工资，请使用SQL操作符编写语句，检索工资在15000到20000元之间的职员的信息。
（4）使用连接操作符
创建表order_master：
CREATETABLEORDER_MASTER（
ORDERNOVARCHAR2（5）PRIMARYKEY,
ODATEDATE,
VENCODEVARCHAR2（5）,
OSTATUSCHAR
（1）,
DEL_DATEDATE）;
INSERTINTOORDER_MASTERVALUES（'O001','12-8月-08','V001','y','22-8月-08'）;
INSERTINTOORDER_MASTERVALUES（'O002','5-7月-08','V008','n','6-7月-08'）;
INSERTINTOORDER_MASTERVALUES（'O003','12-9月-08','V012','n','22-9月-08'）;
COMMIT;
销售经理想查看vencode为V008的供应商的订单状态，要求使用连接操作符编写查询语句，使ORDERNO、ODATE、DEL_DATE在同一列显示，而非分列显示。
（5）使用集合操作符
要求编写语句以显示vendor_master和order_master表中不重复的vencode列值，只列出ostatus为“c”的vencode。
（6）使用SQL日期函数
公司决定提升2003年前半年之前就职的职员。
要求显示职员的就职年度，如果日期晚于“01-7月-2003”，则四舍五入到下一年。
编写语句以显示2003年7月1日之前就职职员的详细信息。
2.实验要求
根据实验内容指定的要去完成实训，并提交实训结果。
实验3锁和表分区（2学时）
一、目标
1．能够理解锁定的概念
2．能够测试行级锁
3．能够测试共享锁和行共享锁
4．能够创建表分区
二、实验内容及要求
1.实验内容
（1）测试行级锁
先创建表空间GITM，再创建用户：
ACCP，密码：
ACCP，用ACCP登陆SQL*PLUS、
CREATETABLEemployee（
empnoVARCHAR2（5）PRIMARYKEY,
enameVARCHAR2（25）,
designationVARCHAR2（20）,
dateofjoiningDATE,
branchcodeVARCHAR2（15）,
deptcodeVARCHAR2（15））;
INSERTINTOemployeeVALUES（'E001','JohnSmith','Manager','1-2月-1998','BH01','DP02'）;
INSERTINTOemployeeVALUES（'E002','Jane','SalesManager','1-6月-2001','BH02','DP02'）;
INSERTINTOemployeeVALUES（'E003','Harry','Accountant','1-11月-2003','BH03','DP01'）;
INSERTINTOemployeeVALUES（'E004','JOE','Accountant','1-12月-2002','BH03','DP03'）;
INSERTINTOemployeeVALUES（'E005','Michael','Manager','1-1月-1999','BH03','DP02'）;
INSERTINTOemployeeVALUES（'E006','JackAranck','SalesManager','1-2月-2003','BH05','DP01'）;
INSERTINTOemployeeVALUES（'E007','George','Sr.Accountant','1-11月-2004','BH05','DP03'）;
INSERTINTOemployeeVALUES（'E008','JohnAranck','Sr.Accountant','1-2月-2005','BH01','DP02'）;
授予SCOTT用户更新EMPLOYEE表的权限。
grantallonemployeetoscott;
ACCP用户执行下列命令：
select*fromemployeewhereempno=’E006’forupdate;
updateemployeesetdesignation=’Accountant’whereempno=’E006’;
打开SQLPLUS的另一个实例，以SCOTT身份登陆并更新employee表的E006行。
updateACCP.employeeSETdesignation=’Accountant’whereempno=’E006’;
这时候更新不成功，因为该行被ACCP用户锁定。
需等ACCP用户释放该锁。
切换到ACCP用户，执行COMMIT命令。
再切回SCOTT用户，执行更新操作。
（2）测试共享锁
案例：
GITM公司人力资源部的职员每个周末都要对employee表进行统计以形成周报表。
因为统计报表需要较长的时间，为了保证表的一致性，不希望在统计的时间段里有记录的更新。
使用什么方法能达到此效果？
分析：
采用共享锁。
共享锁锁定表，仅允许其他用户查询表中的行，但不允许插入、更新或删除行。
多个用户可以同时在同一表中放置共享锁，即允许资源共享，因此得名“共享锁”。
在此案例中，通过共享锁，人力资源部的职员读取表employee进行统计，那么其他用户无法更新该表。
在统计完成之后释放锁定，其他用户就可以进行更新了。
1）启动SQL*PLUS，以ACCP身份登录，执行以下命令来锁定employee表。
SQL>locktableemployeeinsharemode;
2）打开SQL*PLUS的另一个实例，以SCOTT用户身份登录，执行下列语句。
SQL>deletefromaccp.employeewhereempno='E005';
此时，SQL*PLUS停止响应删除操作等待释放锁。
3）切换到第一个SQL*PLUS实例并提交事务。
SQL>COMMIT;
4）此时，SCOTT用户的删除操作得到响应，并成功返回。
执行以下命令取消删除。
SQL>ROLLBACK;
5）以SCOTT用户身份锁定employee表。
SQL>LOCKTABLEaccp.employeeINSHAREMODE;
6）再以ACCP用户身份锁定employee表。
SQL>LOCKTABLEemployeeINSHAREMODE;
此时，表employee被两个用户同时锁定，所有用户都不能在此表上执行更新操作。
当所有用户执行COMMIT命令或ROLLBACK命令结束事务时，所才会被释放。
（3）测试行共享锁
案例：
GITM公司的多个部门都需要访问和更新employee表，这些用户都有权限更新表的行，但是有的用户使用了独占锁定表，使其他用户无法更新此表。
如何避免这种情况？
分析：
可以通过使用行共享锁来限制其他用户独占访问表。
以行共享方式锁定表时，允许多个用户同时更新同一表中的不同行。
1）在ACCP模式中执行下列命令，以行共享模式来锁定employee表。
SQL>LOCKTABLEemployeeINROWSHAREMODE;
2）通过输入下列命令来更新employee表。
SQL>UPDATEemployeeSETename='pam'WHEREempno='E001';
3）打开SQL*PLUS的另一个实例SCOTT，然后更新employee表中的记录。
SQL>UPDATEemployeeSETename='Sam'WHEREempno='E003';
4）执行以下语句，尝试独占锁定employee表。
SQL>LOCLTABLEACCP.employeeINEXCLUSIVEMODENOWAIT;
（4）创建范围分区
创建employee_details表，包含employee_id,ename,dateofjoining,deptname,branchname和branchcode等列，根据职员的就职日期对employee表进行分区。
createtableemployee_details（
emp_idvarchar2（5）,
enamevarchar2（25）,
jobvarchar2（20）,
dateofjoiningdate）
partitionbyrange（dateofjoining）
（partitondoj1valueslessthan（to_date（'01/04/2001','DD/MM/YYYY'））,
partitondoj2valueslessthan（to_date（'01/07/2003','DD/MM/YYYY'））,
partitondoj3valueslessthan（to_date（'01/09/2004','DD/MM/YYYY'））,
partitondoj4valueslessthan（to_date（'01/10/2005','DD/MM/YYYY'）））;
1）执行下列语句向分区表中插入测试记录。
SQL>INSERTINTOemployee_details
SELECTempno,ename,designation,dateofjoining
FROMemployee;
2）执行下列语句查询分区doj4中的记录。
SQL>SELECT*FROMemployee_detailspartition（doj4）;
（5）创建散列分区
问题：
GITM公司的销售部需要根据产品编号分析每个月的销售记录。
要求在monthly_sales表中创建散列分区快速完成次工作。
createtablemonthly_sales（
product_idvarchar2（5）notnull,
sales_datedatenotnull,
sales_costnumber）
partitonbyhash（product_id）
（partitonpid1,
partitonpid2,
partitonpid3）;
2.实验要求
根据实验内容指定的要去完成实训，并提交实训结果。
实验4数据库对象（2学时）
一、目标
1．能够创建和使用同义词
2．能够创建和使用序列
3．能够创建和使用视图
4．能够创建各种类型的索引
二、实验内容及要求
1.实验内容
（1）创建同义词
问题：
为表“ticket_header”创建一个名为“tick”的同义词。
1）创建表空间GITM，创建用户ACCP，密码ACCP。
以ACCP用户身份登陆SQL*PLUS。
2）在SQL提示符下执行语句：
createtableticket_header（
fleet_idnumber（5）,
ticket_novarchar2（6）,
originvarchar2（6）,
destinationvarchar2（6）,
adultsnumber
（1））;
3）执行语句：
createsynonymtickforticket_header;
提示：
createsynonym为创建同义词的关键词。
（2）创建公有同义词
问题：
要求所有用户都能够访问ticket_header表而不必知道该表属于哪个用户，请实现。
分析：
oracle支持两种类型的同义词：
私有同义词、公有同义词。
私有同义词只能由创建它的用户所使用；公有同义词可以由数据库中所有的用户使用。
练习1中创建的是私有同义词。
要创建公有同义词必须拥有相应的权限。
1）在用户ACCP下执行语句：
createpublicsynonympub_tickforticket_header;
提示：
createpublicsynonym为创建公有同义词的关键词。
执行上面语句，oracle将显示“权限不足”的提示消息。
2）再以system用户身份登陆到oracle数据库。
执行语句：
createpublicsynonympub_tickforaccp.ticket_header;
3）以ACCP用户登陆，执行语句：
select*frompub_tick;
（3）删除同义词
问题：
将练习2中创建的同义词tick删除
1）以用户ACCP登陆，执行语句：
droppublicsynonympub_tick;
Oracle将提示“权限不足”。
提示：
这是因为删除公有同义词必须拥有“droppublicsynonym”权限。
而ACCP用户不具有该权限。
2）在ACCP用户下，执行语句：
dropsynonymtick;
（4）创建序列
问题：
请创建一个名为“new_seq”的序列。
要求：
初始值为25，每次增加2，达到75时，又从25重新开始。
提示：
初始值25——startwith25
每次增加2——incrementby2
达到75，也就是说最大值为75——maxvalue75
那么最小值就是25——minvalue25
达到75后，又从25重新开始，也就是说该序列可以循环计数——cycle
执行下面语句：
createsequencenew_seq
incrementby2
startwith25
maxvalue75
minvalue25
cycle
nocache;
（5）访问序列
提示：
Oracle提供了两个伪列，可以用来访问伪列的值——currval和nextval
currval返回的是序列的当前值；nextval返回的是下一个序列值
在练习4完成的基础上，执行语句：
1）selectnew_seq.nextvaluefromdual;
2）selectnew_seq.currvaluefromdual;
（6）更改序列
1）在SQL提示符下执行下列语句：
ALTERSEQUENCEnew_seqINCREMENTBY5;
2）执行下列语句查看所作的修改：
SELECTnew_seq.NEXTVALFROMdual;
（7）删除序列
DROPSEQUENCEnew_seq;
（8）创建视图
问题：
用户经常只需要显示“fleet_header”表的day列和route_id列，请给出解决方案。
1）先创建基表
CREATETABLE fleet_header（
daydate,namevarchar2（20）,route_idnumber（5））;
2）创建视图
CREATEVIEWfleet（day,route_id）ASSELECTday,route_idFROMfleet_header;
3）访问视图
SELECT*FROMfleet;
（9）修改视图定义
将练习8中所创建的视图，修改为显示“fleet_header”表中所有的列。
CREATEORREPLACEVIEWfleetASSELECT*FROMfleet_header;
（10）创建带有错误的视图
在并不存在的表“product”上创建错误视图：
CREATEFORCEVIEWmyviewASSELECT*FROMproduce;
（11）删除视图
DROPVIEWm
升级会员 