Oracle课程实验指导书

1一、Oracle基础1、SQL语言基础实验准备1、提前找班主任拿到机房上机卡、登录服务器的用户名和密码。2、准时到指定机房，作好实验准备，进入xp64系统；3、按以下步骤登录数据库服务器：(1)登录本地数据库服务器：开始-运行，输入cmd进入DOS环境后输入：sqlplus/assysdba(2)查看SCOTT用户状态：selectusername,account_statusfromdba_userswhereusername='SCOTT';若account_status为‘LOCKED’，修改用户到‘OPEN’状态：alteruserSCOTTaccountunlock;(3)以SCOTT用户身份在sqlplus中登陆数据库：用户名：scott密码：tiger4、成功登录后，设置好sqlplus的使用环境;(1)setpagesize100(2)setlinesize100实验一查询语句练习1.实验内容与目的熟练使用select语句进行简单条件和复合条件的查询操作，掌握子查询、分组及函数的应用。2.实验步骤(1)通过查数据字典了解SCOTT用户下拥有那些数据库对象，并了解所有表的结构；(2)查询emp表中所有信息，并按照职员姓名的字母升序排列；2(3)显示所有部门中地址不以“N”字母打头的部门信息；(4)查询公司中所有职员所赚工资及所处的工资级别，(5)统计工资级别为4的总人数；(6)显示部门30中所有职员的工资总和，并使显示结果以TOTAL30为列头。(7)查询工资与佣金总和低于1000元的职员名宇、工资、佣金和所在部门号。(8)查询1981年6月30日之后受雇的职员的最高报酬（包括工资和佣金）。(9)查询平均工资大于1500元的部门的部门号，职员人数和平均工资。(10)显示各部门的部门号及所属厂职员的平均工资，并按平均值由大到小的次序排列。(11)查询EMP表中在部门10和部门20中工作，且在1981年3月30日以后雇用的职员的名字、工资、受雇日期、并按照受雇日期的先后顺序排序。(12)查询部门20中工资大于该部门平均工资的职员信息；(13)查询能获得红利（即在BONUS表中存在）的推销员的名字，受雇日期及工资额。(14)查询在第30号部门工作的所有受雇时间早于他的老板的职员的名字和受雇日期及其老板的名字及受雇日期。(15)查询最先受雇的职员姓名及受雇日期。3.实验答案(1)select*fromcat;descemp;descdept;descsalgrade;表结构如下：emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)dept(deptno,dname,loc)salgrade(grade,losal,hisal)bonus(ename,job,sal,comm);说明：comm为佣金注意：日期1981-3-30按格式’yyyy-mon-dd’显示，英文系统表示为‘1981-March-30’，而中文系统表示为’1981-3月-30’。(2)select*fromemporderbyenameasc;(3)select*fromdeptwherelocnotlike‘N%’;(4)selecte.ename,e.sal,s.gradefromempe,salgradeswheree.salbetweens.losalands.hisal;3(5)selectcount(*)fromempe,salgradeswheres.grade=4ande.salbetweens.losalands.hisal;(6)selectdeptno,ename,sal+nvl(comm,0)total30fromempwheredeptno=30;(7)selectename,sal,comm,deptnofromempwhere(sal+nvl(comm,0))1000;(8)selectmax(sal+nvl(comm,0))fromempwherehiredateto_date('1981-6-30','yyyy-mm-dd');(9)selectdeptno,count(*),avg(sal)fromempgroupbydeptnohavingavg(sal)1500;(10)selectdeptno,count(*),avg(sal)fromempgroupbydeptnoorderby3;注意：缺省为升序排列，3表示第三个变量，groupby中不能用；(11)selectename,sal,hiredatefromempwherehiredateto_date('1981-3-30','yyyy-mm-dd')and(deptno=10ordeptno=20)orderbyhiredate;(12)select*fromempwheredeptno=20andsal(selectavg(sal)fromempwheredeptno=20);(13)selectename,hiredate,salfromempwherejob=’SALESMAN’andenamein(selectenamefrombonus);(14)selecte.ename,e.hiredate,m.ename,m.hiredatefromempe,empmwheree.deptno=30ande.hiredatem.hiredateande.mgr=m.empno;(15)selectename,hiredatefromempwherehiredate=(selectmin(hiredate)fromemp);实验二DML/DDL语句练习1.实验内容与目的熟练使用insert、update、delete语句对数据进行维护，并掌握创建和删除数据库表和视图的方法。2.实验步骤(1)添加一条记录到EMP表中：基本信息如下：姓名：KELLY，部门号10，员工编号7935，工作为CLERK，经理编号为7782，雇用日期为82年5月23日，工资1300，没有佣金；(2)修改员工KELLY的信息，将其佣金改为0；(3)删除名字为KELLY的员工信息；4(4)撤销（3）中的删除操作，还原KELLY的信息；如果（3）中操作用commit提交是否还可以还原KELLY的信息，请自行实验验证。(5)用replace函数将job字段中的内容加上引号“”，查看效果，并回滚；(6)将部门30的所有职员的工资都提升10％。(7)创建表name(enamevarchar2(20),mgrnamevarchar2(20));(8)将emp表中职业为CLERK，且比他的上司受雇时间早的职工的名字及其上司的名字存入新建的表NAME中；查看name表中的内容。(9)，在system用户下创建表test_system，字段自己定义，然后插入一条数据，并将表test_system的查看权限授权给SCOTT用户，用SCOTT用户查看表test_system内容；(10)在system用户下撤销（9）中授予的权限，看看是否可以撤销该操作，并解释发生的现象；(11)在system用户下删除表test_system表，看看是否可以撤销该操作，并解释发生的现象；3.实验答案(1)insertintoempvalues(7935,'kelly','CLERK',7782,to_date('23-05-1982','dd-mm-yyyy'),1300,NULL,10);(2)updateempsetcomm=0whereename=’KELLY’;(3)deletefromempwhereename=’KELLY’;(4)rollback;注意：如果用commit提交了是不能回滚的。(5)updateempsetjob=replace(job,job,’”’||job||’”’);select*fromemp;rollback;注：job子段长度不够时须修改表结构altertableempmodify(jobvarchar2(20))。(6)updateempsetsal=sal*1.1wheredeptno=30;(7)createtablename(enamevarchar2(20),mgrnamevarchar2(20));(8)insertintonameselecte1.ename,e2.enamefromempe1,empe2wheree1.job='CLERK'ande1.mgr=e2.empnoande1.hiredatee2.hiredate;(9)修改system用户密码为systempass:开始-运行，输入cmd进入DOS环境后输入：sqlplus/assysdba5alteralterusersystemidentifiedbysystempass;以system用户身份登陆sqlplus后执行：Createtabletest_system(enamevarchar2(20),mgrnamevarchar2(20));Insertintotest_systemvalues(‘员工’,’领导’);select*fromtest_system;grantselectontest_systemtoSCOTT;(10)revokeselectontest_systemfromSCOTT;rollback;现象：不报错，但回滚没有起作用，revoke操作自动提交；(11)droptabletest_system;rollback;现象同上；2、数据库对象练习实验一PL/SQL语法训练1.实验内容与目的掌握基本的编程语法，编写PL/SQL程序完成简单的任务；2.实验步骤(1)用PL/SQL将EMP表中职员'SMITH'的工资增加100元，并提交给数据库;(2)若MARTIN的佣金不足1500元，则将其工资提为1500元;(3)查询职业为CLERK，且比他的上司受雇时间早的职工的名字及其上司的名字，并将存入新建的表NAME中。用两种方法来解答：①用光标WHILE循环实现；②用光标FOR循环实现。(4)步骤（3）中，设工作为CLERK的职员不能是公司中最先受聘的员工，若发生这种情况，将该职员提升为经理（MANAGER）。3.实验答案(1)程序如下：declarenamevarchar2(10):=’SMITH’;beginupdateempsetsal=sal+100whereename=name;commit;end;6(2)程序如下:declaresalarynumber(7,2);beginselectnvl(comm,0)intosalaryfromempwhereename=’MARTIN’;ifsalary1500thenupdateempsetsal=1500whereename=’MARTIN’;endif;commit;end;(3)程序如下:WHILE循环实现declarecursorc1isselecte1.ename,e2.enamefromempe1,empe2wheree1.job='CLERK'ande1.mgr=e2.empnoande1.hiredatee2.hiredate;empnameemp.ename%TYPE;mgrnameemp.ename%TYPE;beginopenc1;fetchc1intoempname,mgrname;whilec1%FOUNDloopinsertintonamevalues(empname,mgrname);fetchc1intoempname,mgrname;endloop;closec1;end;FOR循环实现:declarecursorc1isselecte1.enameempname,e2.enamemgrnamefromempe1,empe2wheree1.job='CLERK'ande1.mgr=e2.empnoand