2023年教师实验报告大型数据库Oracle

大型数据库（Oracle）教师实验报告指导老师:周友学合用班级:信B1171/722023年8月25日制定

Emai1VarChar230是电子邮件departmentidVarChar23否员工部门号，外键表2departments表结构列名数据类型长度是否允许为空说明departmentIDVarChar23否部门编号，主键depNameVarChar220否部门名noteVarChar216是备注表3salary表结构列名数据类型长度是否允许为空说明enip1oyeeIDVarChar26否员工编号，主键incomenumber8,2否收入outcomenumber8,2否支出换验三表数据的插入、修改和删除一、实验目的.掌握修改、删除表的的方法。.掌握主键约束、外键约束、唯一约束和检查约束的建立及维护方法。.掌握外部表、临时表的使用方法。.掌握使用SQL语句对数据库表进行插入、修改和删除数据操作。二、实验内容及环节分别使用OEM和PL/SQL语句在实验2建立的表Employees.Departments和Salary中插入多行数据记录，然后修改和删除一些记录.使用PL/SQL语句分别向表Emp1oyees、Departments和Salary中

插入如下表所示数据记录。departmentIDdepartmentName1办公室2人力资源部3销售部departmentIDdepartmentName1办公室2人力资源部3销售部4财务部5生产部表2departments表数据employeeTDincomeoutcome0100013000100010002250050010003300050010021500050010031300050010005250050010041450050110001300050210021100050表3salary表数据emp1oyeeIDNameBirthdaySexdepartmentid010001王平1981-1-5女1010002王燕1980-3-6女1010003韦严平1979-11-5男2010021吴庆红1984-7-2女3010031李方1980-8-4男4010005李明1985-2-10男5010041林一凡1986-4-1男3110001张强1982-78男5210021赵琳1983-9-3女5注意：在OEM中分别打开表Employees>Departments和Salary,观测数据变化。.使用PL/SQL命令修改各表中的某个记录的字段值。(1)将表salary中编号为110001的职工收入改为2890；(2)将表Departments表中生产部的名称修改为生产计划部;(3)给每个职工增长收入100元。修改完后使用seiect语句观测数据的变化。3,删除所有性别为男的职工的记录。.创建约束⑴在employees表中创建主键约束，主码为emp1oyeelD,约束名为pk—employeesemp1oyeeTD.(2)创建外键约束,子表einp1oyees的departmentID字段参照主表departments中的departmentTD字段，约束名为fkempdepdeparttmentID.(3)创建检查约束，employees表中的sex字段只能输入字符〃男〃或〃女〃,约束名为ck_sex.(4)在departments表的departmentName字段上仓U建唯——约束undepartmcntNamc..修改及删除表⑴将employees表中的zip字段删除,然后再增长一个字段,字段名为QQ,15位的变长字符型.⑵将departmcnts表删除..将departments表中的数据以纯文本的形式保存到记事本中，并以逗号分隔，保存文献名为dp.txt,内容如下所示：1,办公室2,人力资源部3,销售部在oracle中创建dp.txt的外部表,然后用select语句进行简朴查询..创建事务临时表ttable保存cmp1oyees表的临时数据,创建会话临时表保存departments表的临时数据.比较两个表的差别.注:方法及命令格式请参照教材第10章，规定保存所有的SQL语句到文本文献形式的脚本中，以备检查。仅验四数据库的查询一、实验目的.掌握SELECT语句的基本语法;.掌握子查询的表达；.掌握连接查询的表达；.掌握数据汇总的方法；.掌握层次查询的方法；.掌握分析查询的方法。二、实验内容及环节1-4实验数据基于实验二给出的数据库表结构，及实验三给出的表数据。.SELECT语句的基本使用(1)查询每个雇员的所有数据；Select*fromcmp(2)查询每个雇员的地址和电话；Selectadd,telfromemp(3)查询Emp1oyeelD为010001的雇员的基本信息；Select*fromempwhereemp1oyeeid=010001(4)查询Employees表中女雇员的地址和电话，使用AS子句将结果中各列的标题分别指定为地址和电话；Selectaddas地址，telas电话fromempwheresex='女(5)计算每个雇员的实际收入；Selectin-outfromsalary(6)找出所有姓王的雇员的部门号;Selectdnofromempwhereename1iker王%’(7)找出所有收入在2023-3000之间的雇员号码。Selectemployeeidfromempwheresal>=2023andsa1<=3000.子查询的使用(1)查找在财务部工作的雇员的情况；(2)查找财务部年龄不低于销售部雇员年龄的雇员的姓名。.连接查询的使用(1)查询每个雇员的情况及其薪水情况；Select*fromsalary(2)查找财务部收入在2200元以上的雇员姓名及其薪水详情。Selectename,salfromemp,depWhereemp.deptno=dep.deptnoanddname='财务部.数据汇总(1)求各部门的雇员数;Selectcount(*)fromempgroupbydeptno(2)将各雇员的情况按收入由低到高排列；Select*fromempordeybysal(3)求财务部雇员的平均收入；Selectavg(sal)fromempWheredeptno=(selectdeptnofromdepwheredname=,财务部’)(4)求财务部雇员的平均实际收入；e1ectin—outfromempWheredeptno=(selectdeptnofromdepwheredname=’财务部’)(5)求财务部雇员的总人数。Selectcount(*)fromempWheredeptno=(selectdeptnofromdepwheredname='财务部').层次查询基于自行车结构的数据进行层次查询实验，脚本如下：createtablebicycle(part_idnumber(5)constraintpk_bicycle_part_idprimarykey,parent_idnumber(5)constraintfk_bicycle_pidreferencesbicycle(part_id),part_namevarchar2(30)notnu11,mp_costnumber(9,2),describevarchar2(30))；insertintobicyclevalues(1,null,'自行车'，379.28,装配')；insertintobicyclevalues(2,1,'导向系统，,101.11,制造'）;insertintobicyclevalues（3,1,'驱动系统'，159.56,制造'）；insertintobicyc1eva1ues（4,1,'其他部分'，118.61,'制造和采购'）；insertintobicyclevalues（5,2,'车把'，37.28,'制造'）；insertintobicyclevalues（6,2,'前叉'，24.35,'制造'）；insertintobicycleva1ues（7,2,'前轴'，19.67,'制造'）；insertintobicyc1evalues（8,2,f前轮'，19.81,'制造'）；insertintobicyclevalues（9,7,'前轴棍'，8.16,,制造，）；insertintobicyclevalues（10,7,'前轴身'，4.82,'制造'）；insertintobicyclevalues（11,7,'前轴碗'，6.69,'制造'）；insertintobicyc1eva1ues（12,10,'前轴管'，1.61,‘制造’）;insertintobicyclevalues（13,10,'前花盘'，3.21,‘制造'）；insertintobicyclevalues（14,3,'脚蹬',18.99,'制造'）;insertintobicycleva1ues（15,3,'中轴'，25.27,'制造'）；insertintobicycleva1ues（16,3,'链条'，21.65,'制造'）;insertintobicyclevalues（17,3,'飞轮',29.12,'制造'）；insertintobicyc1evalues（18,3,'后轴'，31.72,制造'）；insertintobicyclevalues（19,3,'后轮'，32.81,'制造'）;insertintobicyc1eva1ues（20,17,'外套'，9.35,'制造'）；insertintobicyclevalues（21,17,‘平档'，5.82,制造’）;insertintobicyclevalues（22,17,'芯子，，5.11,‘制造'）；insertintobicycleva1ues（23,17,'千斤'，6.56,'制造'）；insertintobicyclevalues（24,17,'钢珠，，2.28,‘采购'）；insertintobicyclevalues(25,4,'车架'，81.78,'制造')；insertintobicyclevalues(26,4,'车闸15.26,'制造');insertintobicyclevalues(27,4,r链罩'，7.10,‘采购')；insertintobicyclevalues(28,4,'车铃'，4.33,‘采购')；insertintobicyclevalues(29,4,‘车锁'，5.02,,采购，)；insertintobicyclevalues(30,4,'支架'，5.12,'制造')；(1)按自行车结构层次的先后顺序,查询自行车树状结构数据；⑵查询自行车导向系统分支的树状结构数据；(3)在自行车树状结构数据中显示成本小于100元的零部件信息；⑷对自行车的成本进行加密，然后显示出来，加密密钥可任意给定。7.分析查询基于某书店2023年的图书销售的数据进行分析查询实验，脚本如下：createtablesales_fact_2023(sa1e_yearnumber(4)notnull,sale_quarternumber(1)notnu11,salemonthnumber(2)notnull,sa1e_book_idvarchar2(20)notnu11,sale_regionvarchar2(10)notnull,sa1e_personvarchar2(10)notnu11♦sale_amountnumber(10,2)nu11,constraintpksales_f2023primarykey(sale_year,sa1e_quarter,sale_month,sale_book_id,sale_region,sale_person));insertintosales_fact_2023values(2023,1,1,JISBN7—X',‘北京'，'赵亦'，13526.12）;insertintosales_fact_2023va1ues（2023,1,2,'ISBN7-X','北京'，'钱尔'，8213.91）；insertintosa1es_fact_2023values（2023,1,3,'ISBN7-X',‘北京'，‘孙三'，33871.52）；insertintosa1es_fact_2023values（2023,2,4,1ISBN7-X',‘北京‘李斯'，22343.80）;insertintosales_fact_2023values（2023,2,5,TSBN7—X','上海'，'周武'，3455.93）；insertintosales_fact_2023values（2023,2,6,*ISBN7-X','上海'，'孙三'，23427.72）;insertintosa1es_fact_2023values（2023,3,7,'ISBN7-X',‘香港'，'杨琪'，897.15）;insertintosales_fact_2023values（2023,3,8,'ISBN7—X1,香港‘，'钱尔'，12345）;insertintosales_fact_2023values（2023,3,9,'ISBN7-X',‘澳门'，'冯久'，37817.12）；insertintosa1es_fact2023values（2023,4,10,'ISBN7-X',‘澳门‘，‘冯久'，6524.10）;insertintosales_fact_2023va1ues（2023,4,11,'ISBN7-X','台北'李斯'，93415.83）;insertintosales_fact2023values（2023,4,12,'TSBN7-X\'台北'，'孙三'，23232.82）；(1)查询每个销售员的销售额,并使用sum。分析函数对每个窗口执行累加运算的分析，窗口范围为当前行及其后两行；(2)制作一个总计表,表中涉及每一个销售人员在每一个销售区域的销售额以及该销售额占该销售区域销售总额的比率；(3)查询每个销售区域的销售额，并使用rank()函数计算每一行的相对位置。实验一Oracle安装与维护一、实验目的.了解并掌握Orac1e10的安装方法.了解并掌握测试安装好的Oracle10g的方法二、实验内容及环节.软件下载Oracle公司针对个人学习之用提供免费的学习版本，可直接到Orac1e公司的官方网站.com上免费下载。.安装环节(1)运营setup.exe,出现"OracleDatabase10g安装”画面。(2)“下一步"进入“OracleUniversalInsta1ler：指定文献位置”，设立源“途径”、“名称”和目的“途径”。(3)“下一步"进入"OracleUniversa1InstaIler：选择安装类型”，选择安装类型。(4)保持默认值，下一步,进入“OracleUniversalInstal1er：选择数据库配置”，选择数据库配置。(5)保持默认值，下一步,进入“0rac1eUniversa1InstaIler:指定数据库配置选项”，指定“全局数据库名”和“SID”，对这两个参数的指定一般相同，例如：oract。也可以将“全局数据库名”设立为域名。例如:假如选择“创建带样本方案的数据库,OUI会在数据库中创建HR、OE、SH等范例方案(sampleschema)(6)下一步,进入“Orac1eUniversalInstaHer：选择数据库管理选项”。(7)保持默认值，下一步，进入a0racleUniversalInsta11er:指定数据库文献存储选项”。实验五PL/SQL编程一、实验目的.掌握PL/SQL程序块的结构；.掌握时间戳的使用；.掌握记录、PL/SQL表的使用；.掌握显示游标的使用；.掌握异常解决的方法。二、实验内容及环节.PL/SQL程序块编写一个程序块,完毕10以内偶数的累积。.时间戳(1)显示一个时间戳,精确到纳秒。(2)创建一个只包含一个数值型字段的表,往表中插入10000条记录,运用时间戳计算所用时间。.记录、PL/SQL表(1)创建一个记录类型保存学生数据，类型名为STUDENT,包含XM,XB,BJ三个变量，然后定义一个该类型的变量STIDENU,输入学生本个的信息，并输出。(2)创建一个基类型为字符型的PL/SQL表类型，保存学生的姓名，然后定义一个该类型的变量STUDENT2,输入5名同学的姓名，并输出。.显示游标。编写一个程序块,定义一个游标，用于解决EMP表中工资大于1500的员工信息,假如员工的工资在1500到2023之间，则加上200,假如大于2023,则加上100,修改结果保存到EMP表中，并显示输出。定义解决数据的变量规定用到机ype。注：关于显示游标，由于教材没有作具体说明，请查阅相关资料或教学课件。.异常解决定义一个自定义异常too_many,当EMP表中的记录条数超过10条时,抛出异常,并输出“员工数最过多！”的信息。仅验六用户、角色一、实验目的.掌握使用用户实现数据库安全性；.掌握使用角色实现数据库安全性。二、实验内容及环节.创建用户(1)创建一个用户ph,密码为orac1e；(2)授予连接数据库权限、创建表权限,并允许其将权限转授予其它用户；(3)设立其默认表空间为USERS,并分派20M的配额；(4)连接到ph账户下，将创建表权限授予SCOTT；(5)在ph账户下创建表xs,字段为xm,cj;(6)将xs表的插入数据权限授予SCOTT；(7)连接到SC0H账户下,插入一条数据(lucy,80)o.创建角色(1)连接到system账户下,授予ph创建角色的权限；(2)创建一个以你的姓名的拼音首字母为名字的用户；(3)连接到ph账户下，创建一个角色student,密码为student;(4)授予student角色查询xs表的权限；⑸将student角色赋予你自己的名字的用户。供验七存储过程的使用一、实验目的.掌握存储过程的创建方法；.掌握存储过程的调用方法；二、实验内容及环节.创建添加职工记录的存储过程EmpAdd,通过参数接受职工信息，然后将数据插入到EMP表中。.调用EmpAdd,往EMP表中插入一条数据，具体数据由自己组织，然后用SQL语句查询EMP表，检查存储过程是不是对的执行。.区分in、out和inout三类参数(1)编写一个计算EMP表中指定部门职工人数的存储过程coun定义一个in类型的参数bm用于输入部门信息，定义一个out类型的参数person_num用于输出部门人数。(2)编写一个与(1)相同的存储过程c。unt_num2,但参数类型都定义为inout类型。(3)分别调用这两个过程，比较两者在参数使用上的不同之处。(8)保持默认值，下一步，进入"OracleUniversa1Insta1ler:指定备份和恢复选项”。(9)保持默认值，单击“下一步"，进入"OracleUniversalInsta11er:指定数据库方案的口令”，对不同的帐户设立不同的口令。(10)单击“下一步"，继续安装,进入uOrac1eUniversa1Insta1ler:概要”。(11)单击“安装”，开始安装过程，大约半小时。(12)数据库创建完毕时，显示“DatabaseConfigurationAssistant”窗口。(13)单击“口令管理”，进入“口令管理”窗口。(14)解锁用户HR、0E和SCOTT,输入HR和OE的口令，SCOTT的默认口令为tigero(15)单击“拟定"返回“DatabaseConfigurationAssistant”窗口。(16)在图15所示窗口单击“拟定”,进入uOracleUniversalInstaller：安装结束”窗口。(17)检查，单击“退出”，完毕安装。(18)在自动打开的浏览器中以sys账户sysdba身份登录10g的公司管理器。第一次要接受1icense,单击Iagree,以后就不用了。3.测试安装好的0rac1e10g在安装过程中，OUI会在VORACLE_HOME>\install下创建两个文献:readme.txt:记录各种Orac1e应用程序的URL与端口。Port1ist.ini：记录0racle应用程序所使用的端口。(1)登录EnterpriseManager10gDatabaseControl与以前的版本不同,Oracle公司管理器只有B/S模式。在浏览器中输入下列URL:http://<Orac1e服务器名称>:1158/em例如:http://loca1host:1158/em进入EnterpriseManager10g登录窗口。以SYSDBA身份登录Oracle数据库。(2)使用iSQL*Pls登录0racle数据库iSQL*Plus是B/S模式的客户端工具。在Mydb浏览器中输入下列URL：http:〃<0racle服务器名称>:5560/isq1p1us例如：http://localhost:5560/isqlp1us进入iSQL*Plus登录窗口。用systcm帐户登录0raclc数据库。(3)使用SQL*Pls登录Orac1e数据库SQL*Plus是C/S模式的客户端工具程序。单击“开始”>“所有程序”>“Oracle-Oracle10g_home">“ApplicationDevelopment”>“SQL*Plus”在登录窗口中输入system帐号与口令(4)使用命令行SQL*P1s登录Oracle数据库传统的SQL*Plus是一个命令行客户端程序。在命令窗口中输入命令进行测试。思考与练习：在Windows操作系统下安装Orac1e10g数据库以后，计算机的运营速度明显减少，可以采用哪些措施应对。实验二创建数据库和表一、实验目的.掌握使用DBCA创建数据库.掌握手工创建0racle数据库的方法.掌握创建数据表的方法二、实验内容及环节.使用DBCA创建数据库(1)打开DBCA组件,创建数据库sale.(2)安装完后进入E：\oracle\product\10.2.0\db_l\install目录，打开readme文献，查看打开OEM的URL。(3)登陆OEM,管理sale数据库。.手工创建Oracle数据库数据库名：mydb安装途径:d:\orac1e\product\10.2.0\(1)、手工创建相关目录D：\orac1e\product\10.2.0\admin\mydbD:\orac1e\product\10.2.0\admin\mydb\adumpD:\oracle\product\10.2.0\admin\mydb\bdumpD:\oracle\product\10.2.0\admin\mydb\udumpD：\orac1e\produet\10.2.0\admin\mydb\cdumpD:\oraclc\product\10.2.0\admin\mydb\ddumpD:\oracle\product\10.2.0\admin\mydb\pfi1eD:\oracle\product\10.2.0\oradata\mydb(2)、手工创建初始化参数文献D:\orac1e\product\10.2.O\admin\mydb\pfile\init.ora,内容可以copy别的实例init.ora文献后修改。(3)、在命令提醒符下，使用orapwd.exe命令，创建口令文献pwdmydb.ora,命令格式如下：orapwdfile=D:\oracle\product\10.2.O\db1\database\pwdmydb.orapeissword=123ontrics=5(4)、通过oradim.exe命令,在服务里生成一个新的实例管理服务,启动方式为手工setORACLESID=mydboradim-new-sidMYDB-pfileD:\oracle\product\l0.2.0\db_l\database\initmydb.ora(5)、在命令提醒符下打开sqlplus编辑器，创建服务器参数配置文献spfi1e.sq1plus/nologSQL>connect/asSYSDBASQL>createspfi1e=,D:\orac1e\product\10.2.O\db_l\database\spfilemydb.ora'FROMpfile=,D:\oracle\product\10.2.0\admin\mydb\pfi1e\init.ora*;SQL>startupnomountpfi1e=*D:\oracle\product\l0.2.0\admin\mydb\pfile\init.ora”;(6)、创建数据库，执行ereatedb.sql脚本命令,SQL>@Createdb.sqlCreatedb.sq1脚本内容如下:createdatabasemydbmaxinstances8maxloghistory1maxlogfiles16maxiogmembers3maxdatafiles100logfilegroup1'd：\orac1e\product\10.2.0\oradata\mydb\redo01.Iog'size10m,group2'd:\orac1e\product\10.2.0\oradata\mydb\redo02.1og*size10mdatafi1e*d:\oracle\product\10.2.0\oradata\mydb\system01.dbf'size100mautoextendonnext10mextentmanagementloca1sysauxdatafi1ed:\oracle\product\10.2.0\oradata\mydb\sysauxOl.db「size30mautoextendonnext10mdefaulttemporarytab1espacetemptempfile'd：\oracle\product\l0.2.0\temp.dbf'size10mautoextendonnext10mundotablespaceundotbsidatafilerd:\orac1e\product\10.2.0\oradata\mydb\undotbs1.dbf'size20mcharactersetzhs16gbknationalcharactorsetall6utf16usersysidentifiedbymydbusersystemidentifiedbymanager(7)、创建数据库数据文献,执行脚本createfi1es.sq1SQL>@createfiles.sqlCreatefi1es.sql脚本内容如下:CREATETABLESPACE〃INDX"LOGGINGDATAFILE'd：\oracle\product\10.2.0\oradata\mydb\indxOl.dbfJSIZE25MREUSEAL'TOEXTENDONNEXT1280KMAXSIZEUNLIMITEDEXTENTMANAGEMENTLOCAL;CREATETABLESPACE"TOOLS"LOGGINGDATAFILE'd：\oracle\product\10.2.0\oradata\mydb\tools01.dbf，SIZE10MREUSEAUTOEXTENDONNEXT320KMAXSIZEUNLIMITEDEXTENTMANAGEMENTLOCAL;CREATETABLESPACE〃USERS〃LOGGINGDATAFILE*d:\orac1e\prod