中北大学ORACLE数据库技术实验指导书.doc

中北大学ORACLE数据库技术实验指导书 ORACLE数据库技术实验指导书目录实验一SQL*PLUS用法及SQL语言中的简单查询语句实验二SQL语言中的分组查询和连接查询语句实验三SQL语言中的子查询及函数的使用实验四SQL语言中的数据操纵、事务控制和数据定义语句实验五SQL/PL块中SQL命令的使用实验六SQL/PL块中流程控制语句、游标、例外处理实验七数据库触发器、存储过程和存储函数实验一SQL*PLUS用法及SQL语言中的简单查询语句 一、实验目的 1、熟悉SQL语言交互式使用工具SQL*PLUS的使用方法 2、熟悉SQL*PLUS常用命令的功能和使用方法 3、掌握SQL语言中简单查询语句的功能和使用方法 二、实验环境 1、硬件设备计算机局域网，服务器1台，客户机100台 2、软件系统Windows2000Server网络操作系统，Windows2000/XP客户机操作系统；Oracle9i服务端数据库系统，客户端工具。 三、实验内容 1、SQL*PLUS的使用 (1)进入（启动）SQL*PLUS在Windows环境下,双击SQL*PLUS图标或从程序组找SQL*PLUS，出现登录窗口输入正确的数据库用户名、密码和连接字符串后，若连接数据库成功，则会出现如下提示符SQL (2)退出SQL*PLUS SQLexit则退回到Windows桌面。 (3)创建表结构Create table创建部门登记表dept和雇员登记表emp的结构SQLCreate tabledept(deptno number (2)not null，dname char (14)，loc char (13)；SQLCreate tableemp(empno number (4)not null，ename char (9)，job char (10),mgr number (4),Hiredate date,sal number(7,2),m number(7,2),deptno number (2)not null)； (4)显示表结构显示部门登记表dept和雇员登记表emp的结构SQLdesc deptSQLdesc emp (5)向表中插入记录向部门登记表dept中插入一条记录SQLinsert into dept Values(60，Computer，Beijing)；SQLselect*from dept;向部门登记表dept中连续插入多条记录SQLinsert intodept(deptno，dname，loc)Values(&deptno，&dname，&loc); (6)执行SQL缓冲区中的命令SQL/(直接执行)SQLrun(先显示命令的内容，再执行) (7)执行磁盘上的命令文件a.先调入缓冲区，再运行SQLget f:oradept.sql SQL/b.用或start命令将指定命令文件调入缓冲区并执行。 SQLf:oradept.sql或SQLstart f:oradept.sql 2、简单查询语句 (1)无条件简单查询查表中所有记录SQLselect Dname,Deptno2From dept；对查询结果进行计算和统计SQLselect count(*)ALL_EMP，sum(sal)ALL_Sal2From emp； (2)有条件简单查询查表中部分记录查在20号部门工作的雇员姓名和工资SQLselect ename，sal，deptno2From emp3where deptno=20；查找工种是职员或分析员的雇员姓名和工种SQLselect ename，job2From emp3where jobIN(Clerk，Analyst)；查找以“S”开头的雇员姓名和所在部门SQLselect ename，deptno2From emp3where enamelikeS%；按工资升序排列20号部门的雇员SQLselect ename，sal，deptno2From emp3where deptno=204order bysal asc；查所有部门中工资大于2800美元的雇员SQLselect ename，sal from Aount wheresal2800union select ename，sal fromResearch Where sal2800Union Select ename，sal From sales Where sal2800；查在所有部门中都存在的工种SQLselect Job from AountIntersect select JobfromResearch IntersectSelect Job Fromsales；查在Aount部门中有哪些职业，是Sales部门中所没有的SQLselectJob实验二SQL语言中的分组查询和连接查询语句 一、实验目的 1、掌握SQL语言中分组查询命令的功能及其使用方法 2、掌握SQL语言中连接查询命令的功能及其使用方法 二、实验环境 1、硬件设备计算机局域网，服务器1台，客户机100台 2、软件系统Windows2000Server网络操作系统，Windows2000/XP客户机操作系统；Oracle9i服务端数据库系统，客户端工具。 fromAountminus SelectJobFromsales； 三、实验内容 1、分组查询命令的使用 (1)不带条件的分组查询每组只返回一条记录。 计算每个部门的工资总和SQLselect deptno，sum(sal)2From emp3Group bydeptno；查每部门每个工种的雇员数。 SQLselect deptno，job，count(*)2From emp3Group bydeptno，job； (2)带条件的分组查询部门工资总和超过$9000的部门,并按工资总和升序排列。 SQLselect deptno，sum(sal)2From emp3Group bydeptno4Having sum(sal)90005Order bysum(sal)； 2、连接查询命令的使用 (1)无条件连接SQLSelect Dept.Deptno，Dname，Empno，Ename，Sal2From Dept，Emp； (2)等值连接参与连接的表在公共列上有相同值。 查看工资高于￥3000的雇员及所在部门情况。 SQLSelect ename，sal，emp.deptno，loc，dname2From emp，dept3Where Emp.deptno=Dept.deptno andsal3000； (3)自连接查询每个雇员的经理的名字。 SQLselect worker.ename，worker.empno，manager.ename manager,manager.empno mgrFrom empworker，emp managerWhere worker.mgr=manager.empno； (4)外连接可避免连接时丢失信息SQLSelect ename，dept.deptno，loc From emp，dept Whereemp.deptno=dept.deptno；SQLSelect ename，dept.deptno，loc From emp，dept Whereemp.deptno(+)=dept.deptno；SQLSelect ename，dept.deptno，loc From emp，dept Wheredept.deptno=emp.deptno(+)；实验三SQL语言中的子查询及函数的使用 一、实验目的 1、掌握SQL语言中子查询命令的功能及其使用方法 2、掌握子查询命令中的函数的使用方法 二、实验环境 1、硬件设备计算机局域网，服务器1台，客户机100台 2、软件系统Windows2000Server网络操作系统，Windows2000/XP客户机操作系统；Oracle9i服务端数据库系统，客户端工具。 三、实验内容 1、子查询命令的使用 (1)单行值子查询子查询返回一条纪录查询工资高于7698号雇员，并且工种与他相同的雇员情况。 SQLselectename，sal，job，empno From emp Wheresal(Select sal From emp Where empno=7698)and job=(Select jobFrom emp Where mpno=7698)；查询工资高于或等于20号部门工资额最高的雇员情况。 SQLSelect ename，sal，deptno From emp Wheresal=(Select max(sal)From emp Where deptno=20); (2)多行值子查询子查询返回一组纪录查工资在￥2000到￥2900之间的雇员所在部门的雇员。 SQLSelect ename，sal，job，deptno From emp Where deptno IN(Select deptno Fromemp Wheresalbetween2000and2900);查工资在￥2000到￥2900之间的雇员所在部门的雇员。 SQLSelect ename，sal，job，deptno FromempWhere deptno=ANY(Select deptno FromempWheresalbetween2000and2900)Order bysal desc； (3)多列值子查询查询工资、奖金与20号部门雇员匹配(相同)的雇员情况。 SQLSelect ename，sal，m，deptnoFromempWhere(NVL(m，0)，sal)IN(Select NVL(m，0)，salFromempWheredeptno=20)； 2、子查询命令中函数的使用将所有雇员的名字用大小写形式显示。 SQLSelect empno，Upper(ename)，Initcap(ename)Fromemp；计算所有雇员的日工资（设一个月为25个工作日）。 SQLSelectename，round(sal/25，0)，round(sal/25，-1)Fromemp；显示当前日期，dual是系统提供的一张空表。 SQLSelect sysdateFrom dual；显示当前日期。 SQLSelect to_char(sysdate，yyyy-mm-dd)From dual；分部门统计所有雇员的中收入。 SQLSelect deptno，sum(sal+m)，sum(sal+NVL(m，0)FromempGroup bydeptno；根据输入的值将雇员信息排序若输入1，则按ename排序；若输入2，则按SAL排序；缺省时，按ename排序。 SQLSelect*from emporder bydecode(&ob，1，ename，2，sal，ename)；实验四SQL语言中的数据操纵、事务控制和数据定义语句 一、实验目的 1、掌握SQL语言中数据操纵命令的功能及其使用方法 2、掌握SQL语言中事务控制命令的功能及其使用方法 3、掌握SQL语言中数据定义命令的功能及其使用方法 二、实验环境 1、硬件设备计算机局域网，服务器1台，客户机100台 2、软件系统Windows2000Server网络操作系统，Windows2000/XP客户机操作系统；Oracle9i服务端数据库系统，客户端工具。 三、实验内容 1、数据操纵命令的使用 (1)数据插入向Dept表插入一行数据(插入所有列时可省略列名)。 SQLinsert intodept Values(60，Computer，Beijing);向dept表中一次插入多个纪录(所有字段可用*代替)。 SQLinsert intodept(deptno，dname，loc)Values(&deptno，&dname，&loc);将dept表中所有记录插入到空表department中。 SQLinsert intodepartment select*from dept;向Emp表中插入一条与Smith内容相同的记录，但姓名改为Richard，雇员号改为9999。 SQLinsert intoemp(empno,ename,job,mgr,hiredate,sal,m,deptno)select9999,Richard,job,mgr,hiredate,sal,m,deptno from emp whereename=Smith; (2)数据修改将Martin提升为经理，工资加￥1000。 SQLupdate empSet job=Manager，sal=sal+1000Where ename=Martin;将30号部门所有雇员工资和奖金，变为20号部门雇员的最高工资和奖金。 SQLupdate empSet(sal，m)=(Select Max(sal)，max(NVL(m，0)FromempWheredeptno=20)Wheredeptno=30； (3)数据删除将Martin从公司删除。 SQLdelete fromempWhereename=Martin； 2、事务控制命令的使用 (1)事务提交命令Commit SQLmit； (2)事务回退命令Rollback SQLRollback； 3、数据定义命令的使用 (1)表操作手工创建表dept结构。 SQLCreate tabledept(deptno number (2)not null，dname char (14)，loc char (13)；通过复制创建表deptpartment结构，其结构与dept相同。 SQLCreate tabledeptpartment asselect*from deptwhere1=2；将dept表中Dname列宽度加大到20个字符。 SQLAlter tabledept modifydname char (20)；为表dept增加一列All_emp用来存放部门人数。 SQLAlter tabledept addall_emp number (3)；删除dept表。 SQLdrop tabledept； (2)视图操作为20号部门的雇员创建一个视图，包括姓名,工资,部门号。 SQLCreate viewemp_view(name，salary，no)As Selectename，sal，deptnoFromemp where deptno=20； (3)索引操作为emp表建立empno列上的唯一索引。 SQLcreate uniqueindex empno_index onemp(empno); (4)同义词操作为gx1用户的dept表建立同义词New_dept。 SQLcreate synonymnew_dept forgx1.dept; (5)序列生成器操作创建一个序列生成器deptno_seq，起始值为50,间隔为10。 SQLcreate sequencedeptno_seq Startwith50increment by10；列出deptno_seq的下次值(第一次使用时为50)SQLselect deptno_seq.nextval NEXT_VAL Fromdual； (6)数据库链操作创建一个数据库链Link_xsdb，用来连接远程销售数据库xsdb，其用户名为scott，口令为tiger。 SQLcreate DatabaseLink Link_xsdb connectto scottidentified bytiger Usingxsdb；实验五SQL/PL块中SQL命令的使用 一、实验目的 1、了解SQL/PL块的基本结构与功能 2、掌握SQL/PL块中各种SQL命令的使用方法 二、实验环境 1、硬件设备计算机局域网，服务器1台，客户机100台 2、软件系统Windows2000Server网络操作系统，Windows2000/XP客户机操作系统；Oracle9i服务端数据库系统，客户端工具。 三、实验内容 1、SQL/PL块中查询命令的使用将7788号雇员的工资和奖金作为Smith的工资和奖金。 SQLDECLARE v_empno emp.empno%TYPE:=7788；v_ename emp.ename%TYPE:=Smith；v_sal emp.sal%TYPE；v_m emp.m%TYPE；BEGIN SELECTsal，m INTO v_sal，v_m FROMEMP WHEREempno=v_empno；update empset sal=v_sal，m=v_m whereename=v_ename；mit;END；查询SMITH的情况。 SQLDECLARE emp_rec EMP%ROWTYPE；v_ename EMP.ename%TYPE=SMITH；BEGIN SELECT*INTO emp_rec FROMEMP WHEREENAME=v_ename；-END； 2、SQL/PL块中数据操纵命令的使用向EMP表插入一新雇员。 SQLDECLARE v_deptno dept.deptno%TYPE notnull:=50；v_dname dept.dname%TYPE:=Computer；v_loc dept.loc%TYPE:=Beijing；BEGIN insertintodept(deptno，dname，loc)values(v_deptno，v_dname，v_loc)；mit work;END；修改7788号雇员的工资。 SQLDECLARE v_empno emp.empno%TYPE:=7788；v_addsal emp.sal%TYPE；BEGIN v_addsal:=1000；UPDATE EMPSET sal=sal+v_addsal WHEREempno=v_empno；MIT；END；从EMP表中删除7788号雇员。 SQLDECLARE BEGIN DELETE FROMEMP WHEREempno=7788；MIT；END；实验六SQL/PL块中流程控制语句、游标、例外处理 一、实验目的 1、掌握SQL/PL块中流程控制语句的使用方法 2、掌握SQL/PL块中游标的使用方法 3、掌握SQL/PL块中例外处理的使用方法 二、实验环境 1、硬件设备计算机局域网，服务器1台，客户机100台 2、软件系统Windows2000Server网络操作系统，Windows2000/XP客户机操作系统；Oracle9i服务端数据库系统，客户端工具。 三、实验内容 1、SQL/PL块中流程控制语句的使用 (1)条件控制语句的使用将EMP表中名为SMITH的雇员的工资进行修改，若原工资大于$2000，则加$500，否则加$1000。 SQLDECLARE v_ename emp.ename%TYPE:=SMITH；v_addsal emp.sal%TYPE；v_sal emp.sal%TYPE；BEGIN SELECTsal INTOv_sal FROMEMP WHEREENAME=v_ename；IF v_sal20OO THENv_addsal:=500；ELSE v_addsal:=1000；END IF；UPDATE EMPSET sall=sal+v_addsal WHEREENAME=v_ename；mit；END； (2)循环控制语句的使用给10号部门增加新雇员，只确定雇员号，其它信息忽略。 SQLDECLARE v_empno emp.empno%TYPE:=8000；BEGIN LOOP INSERT INTOemp(deptno,empno)VALUES(10，v_empno)；v_empno:=v_empno+l00；EXIT WHENv_empno=9000；END LOOP；END； (3)GOTO控制语句的使用给10号部门增加新雇员，只确定雇员号，其它信息忽略。 SQLDECLRE v_empno emp.empno%TYPE:=8000；BEGINlab1INSERT INTOemp(deptno,empno)VALUES(l0，v_empno)；v_empno:=v_empno+100；IF v_empnoDECLARE v_deptno emp.deptno%TYPE；v_ename emp.ename%TYPE；v_sal emp.sal%TYPE；CURSOR C1IS SELECTename，sal FROMEMP WHEREDEPTNO=v_deptno；BEGIN v_deptno:=10；OPEN Cl；FETCH C1INTOv_ename，v_sal；CLOSE C1；END；查询10号部门所有雇员姓名、工资，并插入到一临时表TMP中。 SQLDECLARE v_deptno emp.deptno%TYPE:=10；CURSOR C1IS SELECTename，sal FROMEMP WHEREdeptno=v_deptno；emp_ec C1%ROWTYPE；BEGIN FORemp_rec INC1LOOPINSERT INTO Tmp(ename，sal)VALUES(emp_rec.ename，emp_rec.sal)；END LOOP；GOMMIT WORK；END； 3、SQL/PL块中例外处理的使用从EMP表中删去SMITH的信息。 SQLDECLARE v_ename emp.ename%TYPE:=SMITH；BEGINDELETEFROM empWHERE ename=v_ename；MIT WORK；EXCEPTION WHENNO_DATA_FOUND THEN ROLLBACK WORK；INSERT INTOTEMP(message)VALUES(SMITH isnot found)；MIT WORK；WHEN TOO_MANY_ROWS THENROLLBACK WORK；INSERT INTOTEMP(message)VALUES(Found toomany rowsin emp)；MIT WORK；WHEN OTHERSTHENROLLBACKWORK；INSERTINTOTEMP(message)VALUES(Other errorourred)；MIT WORK；END；实验七数据库触发器、存储过程和存储函数 一、实验目的 1、掌握数据库触发器的功能与使用方法 2、掌握存储过程的功能与使用方法 3、掌握存储函数的功能与使用方法 二、实验环境 1、硬件设备计算机局域网，服务器1台，客户机100台 2、软件系统Windows2000Server网络操作系统，Windows2000/XP客户机操作系统；Oracle9i服务端数据库系统，客户端工具。 三、实验内容 1、数据库触发器的使用创建一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员。 SQLCreate triggerdel_emp_deptno_trig Beforede