《oracle数据库管理系统》实验指导书

ORACLE数据库管理系统实验指导书编写主审计算机与信息技术学院信息科学系20111目录实验一熟悉SQL命令与SQLPLUS命令的应用1实验二SQL语言中的数据操纵、事务控制和数据定义语句4实验三PL/SQL编程7实验四数据库触发器、存储过程和存储函数11实验五数据表的管理14实验六索引的管理17实验七安全管理19实验八触发器和游标21前言ORACLE数据库是目前最为流行和成熟的几种大型关系数据库之一。ORACLE数据库管理系统课程以ORACLE9I为主，在学习数据库系统概论的基础上，进一步理解关系数据库的概念、原理。通过对ORACLE数据库系统的学习，使学生掌握数据库的基本理论和ORACLE数据库操作的基本方法，熟悉SQL基本命令的运用,了解运用ORACLE数据库知识处理复杂问题的方法。使学生具有运用ORACLE进行数据库服务器端的程序开发的基本能力。为了加强学生对ORACLE数据库系统的操作能力，特编写该实验指导书，希望给学生提供一定的指导。本指导书由李学贵编写，冯亚丽教授主审，由于时间仓促，作者的水平有限，书中难免有不足之处，恳请广大师生批评指正。编者2011年1月1实验一熟悉SQL命令与SQLPLUS命令的应用一、实验目的1、熟悉SQL语言交互式使用工具SQLPLUS的使用方法2、熟悉SQLPLUS常用命令的功能和使用方法3、掌握SQL语言中简单查询语句的功能和使用方法二、实验环境1、硬件设备计算机局域网，服务器1台，客户机若干台2、软件系统WINDOWS2003SERVER网络操作系统，WINDOWS2003/XP客户机操作系统；ORACLE9I服务端数据库系统，客户端工具。三、实验内容1、SQLPLUS的使用1进入（启动）SQLPLUS在WINDOWS环境下,双击SQLPLUS图标或从程序组找SQLPLUS，出现登录窗口输入正确的数据库用户名、密码和连接字符串后，若连接数据库成功，则会出现如下提示符SQL2退出SQLPLUSSQLEXIT则退回到WINDOWS桌面。3创建表结构CREATETABLE创建部门登记表DEPT和雇员登记表EMP的结构SQLCREATETABLEDEPTDEPTNONUMBER2NOTNULL，DNAMECHAR14，LOCCHAR13；SQLCREATETABLEEMPEMPNONUMBER4NOTNULL，ENAMECHAR9，2JOBCHAR10,MGRNUMBER4,HIREDATEDATE,SALNUMBER7,2,COMMNUMBER7,2,DEPTNONUMBER2NOTNULL；4显示表结构显示部门登记表DEPT和雇员登记表EMP的结构SQLDESCDEPTSQLDESCEMP5向表中插入记录向部门登记表DEPT中插入一条记录SQLINSERTINTODEPTVALUES60，COMPUTER，BEIJING；SQLSELECTFROMDEPT向部门登记表DEPT中连续插入多条记录SQLINSERTINTODEPTDEPTNO，DNAME，LOCVALUES6执行SQL缓冲区中的命令SQL/直接执行SQLRUN先显示命令的内容，再执行7执行磁盘上的命令文件A先调入缓冲区，再运行SQLGETFORADEPTSQLSQL/B用或START命令将指定命令文件调入缓冲区并执行。SQLFORADEPTSQL或SQLSTARTFORADEPTSQL2、简单查询语句1无条件简单查询查表中所有记录SQLSELECTDNAME,DEPTNO2FROMDEPT；对查询结果进行计算和统计SQLSELECTCOUNTALL_EMP，SUMSALALL_SAL2FROMEMP；2有条件简单查询查表中部分记录查在20号部门工作的雇员姓名和工资SQLSELECTENAME，SAL，DEPTNO2FROMEMP3WHEREDEPTNO20；查找工种是职员或分析员的雇员姓名和工种SQLSELECTENAME，JOB2FROMEMP3WHEREJOBINCLERK，ANALYST；3查找以“S”开头的雇员姓名和所在部门SQLSELECTENAME，DEPTNO2FROMEMP3WHEREENAMELIKES；按工资升序排列20号部门的雇员SQLSELECTENAME，SAL，DEPTNO2FROMEMP3WHEREDEPTNO204ORDERBYSALASC；查所有部门中工资大于2800美元的雇员SQLSELECTENAME，SALFROMACCOUNTWHERESAL2800UNIONSELECTENAME，SALFROMRESEARCHWHERESAL2800UNIONSELECTENAME，SALFROMSALESWHERESAL2800；查在所有部门中都存在的工种SQLSELECTJOBFROMACCOUNTINTERSECTSELECTJOBFROMRESEARCHINTERSECTSELECTJOBFROMSALES；查在ACCOUNT部门中有哪些职业，是SALES部门中所没有的SQLSELECTJOBFROMACCOUNTMINUSSELECTJOBFROMSALES；4实验二SQL语言中的数据操纵、事务控制和数据定义语句一、实验目的1、掌握SQL语言中数据操纵命令的功能及其使用方法2、掌握SQL语言中事务控制命令的功能及其使用方法3、掌握SQL语言中数据定义命令的功能及其使用方法二、实验环境1、硬件设备计算机局域网，服务器1台，客户机若干台2、软件系统WINDOWS2003SERVER网络操作系统，WINDOWS2003/XP客户机操作系统；ORACLE9I服务端数据库系统，客户端工具。三、实验内容1、数据操纵命令的使用1数据插入向DEPT表插入一行数据插入所有列时可省略列名。SQLINSERTINTODEPTVALUES60，COMPUTER，BEIJING向DEPT表中一次插入多个纪录所有字段可用代替。SQLINSERTINTODEPTDEPTNO，DNAME，LOCVALUES将DEPT表中所有记录插入到空表DEPARTMENT中。SQLINSERTINTODEPARTMENTSELECTFROMDEPT向EMP表中插入一条与SMITH内容相同的记录，但姓名改为RICHARD，雇员号改为9999。SQLINSERTINTOEMPEMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNOSELECT9999,RICHARD,JOB,MGR,HIREDATE,SAL,COMM,DEPTNOFROMEMPWHEREENAMESMITH2数据修改将MARTIN提升为经理，工资加1000。SQLUPDATEEMPSETJOBMANAGER，SALSAL1000WHEREENAMEMARTIN将30号部门所有雇员工资和奖金，变为20号部门雇员的最高工资和奖金。SQLUPDATEEMPSETSAL，COMMSELECTMAXSAL，MAXNVLCOMM，0FROMEMPWHEREDEPTNO20WHEREDEPTNO30；3数据删除将MARTIN从公司删除。5SQLDELETEFROMEMPWHEREENAMEMARTIN；2、事务控制命令的使用1事务提交命令COMMITSQLCOMMIT；2事务回退命令ROLLBACKSQLROLLBACK；3、数据定义命令的使用1表操作手工创建表DEPT结构。SQLCREATETABLEDEPTDEPTNONUMBER2NOTNULL，DNAMECHAR14，LOCCHAR13；通过复制创建表DEPTPARTMENT结构，其结构与DEPT相同。SQLCREATETABLEDEPTPARTMENTASSELECTFROMDEPTWHERE12；将DEPT表中DNAME列宽度加大到20个字符。SQLALTERTABLEDEPTMODIFYDNAMECHAR20；为表DEPT增加一列ALL_EMP用来存放部门人数。SQLALTERTABLEDEPTADDALL_EMPNUMBER3；删除DEPT表。SQLDROPTABLEDEPT；2视图操作为20号部门的雇员创建一个视图，包括姓名,工资,部门号。SQLCREATEVIEWEMP_VIEWNAME，SALARY，NOASSELECTENAME，SAL，DEPTNOFROMEMPWHEREDEPTNO20；3索引操作为EMP表建立EMPNO列上的唯一索引。SQLCREATEUNIQUEINDEXEMPNO_INDEXONEMPEMPNO4同义词操作为GX1用户的DEPT表建立同义词NEW_DEPT。SQLCREATESYNONYMNEW_DEPTFORGX1DEPT5序列生成器操作创建一个序列生成器DEPTNO_SEQ，起始值为50,间隔为10。SQLCREATESEQUENCEDEPTNO_SEQ6STARTWITH50INCREMENTBY10；列出DEPTNO_SEQ的下次值第一次使用时为50SQLSELECTDEPTNO_SEQNEXTVALNEXT_VALFROMDUAL；7实验三PL/SQL编程一、实验目的1、了解PL/SQL块的基本结构与功能2、掌握PL/SQL块中各种SQL命令的使用方法3、掌握PL/SQL块中流程控制语句的使用方法4、掌握PL/SQL块中游标的使用方法5、掌握PL/SQL块中异常处理的使用方法二、实验环境1、硬件设备计算机局域网，服务器1台，客户机若干台2、软件系统WINDOWS2003SERVER网络操作系统，WINDOWS2003/XP客户机操作系统；ORACLE9I服务端数据库系统，客户端工具。三、实验内容1、PL/SQL块中查询命令的使用将7788号雇员的工资和奖金作为SMITH的工资和奖金。SQLDECLAREV_EMPNOEMPEMPNOTYPE7788；V_ENAMEEMPENAMETYPESMITH；V_SALEMPSALTYPE；V_COMMEMPCOMMTYPE；BEGINSELECTSAL，COMMINTOV_SAL，V_COMMFROMEMPWHEREEMPNOV_EMPNO；UPDATEEMPSETSALV_SAL，COMMV_COMMWHEREENAMEV_ENAME；COMMITEND；查询SMITH的情况。SQLDECLAREEMP_RECEMPROWTYPE；V_ENAMEEMPENAMETYPESMITH；BEGINSELECTINTOEMP_RECFROMEMPWHEREENAMEV_ENAME；END；2、PL/SQL块中数据操纵命令的使用8向EMP表插入一新雇员。SQLDECLAREV_DEPTNODEPTDEPTNOTYPENOTNULL50；V_DNAMEDEPTDNAMETYPECOMPUTER；V_LOCDEPTLOCTYPEBEIJING；BEGININSERTINTODEPTDEPTNO，DNAME，LOCVALUESV_DEPTNO，V_DNAME，V_LOC；COMMITWORKEND；修改7788号雇员的工资。SQLDECLAREV_EMPNOEMPEMPNOTYPE7788；V_ADDSALEMPSALTYPE；BEGINV_ADDSAL1000；UPDATEEMPSETSALSALV_ADDSALWHEREEMPNOV_EMPNO；COMMIT；END；从EMP表中删除7788号雇员。SQLDECLAREBEGINDELETEFROMEMPWHEREEMPNO7788；COMMIT；END；3、PL/SQL块中流程控制语句的使用1条件控制语句的使用将EMP表中名为SMITH的雇员的工资进行修改，若原工资大于2000，则加500，否则加1000。SQLDECLAREV_ENAMEEMPENAMETYPESMITH；V_ADDSALEMPSALTYPE；V_SALEMPSALTYPE；BEGINSELECTSALINTOV_SALFROMEMPWHEREENAMEV_ENAME；IFV_SAL20OOTHENV_ADDSAL500；ELSE9V_ADDSAL1000；ENDIF；UPDATEEMPSETSALLSALV_ADDSALWHEREENAMEV_ENAME；COMMIT；END；2循环控制语句的使用给10号部门增加新雇员，只确定雇员号，其它信息忽略。SQLDECLAREV_EMPNOEMPEMPNOTYPE8000；BEGINLOOPINSERTINTOEMPDEPTNO,EMPNOVALUES10，V_EMPNO；V_EMPNOV_EMPNOL00；EXITWHENV_EMPNO9000；ENDLOOP；END；3GOTO控制语句的使用给10号部门增加新雇员，只确定雇员号，其它信息忽略。SQLDECLREV_EMPNOEMPEMPNOTYPE8000；BEGINLAB1INSERTINTOEMPDEPTNO,EMPNOVALUESL0，V_EMPNO；V_EMPNOV_EMPNO100；IFV_EMPNODECLAREV_DEPTNOEMPDEPTNOTYPE；V_ENAMEEMPENAMETYPE；V_SALEMPSALTYPE；CURSORC1ISSELECTENAME，SALFROMEMPWHEREDEPTNOV_DEPTNO；BEGINV_DEPTNO10；10OPENCL；FETCHC1INTOV_ENAME，V_SAL；CLOSEC1；END；查询10号部门所有雇员姓名、工资，并插入到一临时表TMP中。SQLDECLAREV_DEPTNOEMPDEPTNOTYPE10；CURSORC1ISSELECTENAME，SALFROMEMPWHEREDEPTNOV_DEPTNO；EMP_ECC1ROWTYPE；BEGINFOREMP_RECINC1LOOPINSERTINTOTMPENAME，SALVALUESEMP_RECENAME，EMP_RECSAL；ENDLOOP；GOMMITWORK；END；5、PL/SQL块中异常处理的使用从EMP表中删去SMITH的信息。SQLDECLAREV_ENAMEEMPENAMETYPESMITH；BEGINDELETEFROMEMPWHEREENAMEV_ENAME；COMMITWORK；EXCEPTIONWHENNO_DATA_FOUNDTHENROLLBACKWORK；INSERTINTOTEMPMESSAGEVALUESSMITHISNOTFOUND；COMMITWORK；WHENTOO_MANY_ROWSTHENROLLBACKWORK；INSERTINTOTEMPMESSAGEVALUESFOUNDTOOMANYROWSINEMP；COMMITWORK；WHENOTHERSTHENROLLBACKWORK；INSERTINTOTEMPMESSAGEVALUESOTHERERROROCCURRED；COMMITWORK；END；11实验四数据库触发器、存储过程和存储函数一、实验目的1、掌握数据库触发器的功能与使用方法2、掌握存储过程的功能与使用方法3、掌握存储函数的功能与使用方法二、实验环境1、硬件设备计算机局域网，服务器1台，客户机若干台2、软件系统WINDOWS2003SERVER网络操作系统，WINDOWS2003/XP客户机操作系统；ORACLE9I服务端数据库系统，客户端工具。三、实验内容1、数据库触发器的使用创建一个数据库触发器，当任何时候某个部门从DEPT表中删除时，该触发器将从EMP表中删除该部门的所有雇员。SQLCREATETRIGGERDEL_EMP_DEPTNO_TRIGBEFOREDELETEONDEPTFOREACHROWBEGINDELETEFROMEMPWHEREDEPTNOOLDDEPTNOENDDEL_EMP_DEPTNO_TRIG；2、存储过程的使用创建一个统计各部门雇员人数的据库存储过程。SQLCREATEPROCEDURECOUNT_PROCIN_DEPTNOINEMPDEPTNOTYPE，OUT_NUMOUTNUMBERASBEGINIFIN_DEPTNO10THENSELECTCOUNTDEPTNOINTOOUT_NUMFROMEMPWHEREDEPTNOIN_DEPTNO；ELSIFIN_DEPTNO20THENSELECTCOUNTDEPTNOINTOOUT_NUMFROMEMP12WHEREDEPTNOIN_DEPTNO；ELSIFIN_DEPTNO30THENSELECTCOUNTDEPTNOINTOOUT_NUMFROMEMPWHEREDEPTNOIN_DEPTNO；ELSESELECTCOUNTDEPTNOINTOOUT_NUMFROMEMPWHEREDEPTNO40；ENDIF；ENDCOUNT_PROC；3、存储函数的使用创建一个统计不同部门雇员人数的据库存储函数SQLCREATEFUNCTIONCOUNT_FUNCIN_DEPTNOEMPDEPTNOTYPERETURNNUMBERASOUT_NUMNUMBERBEGINIFIN_DEPTNO10THENSELECTCOUNTDEPTNOINTOOUT_NUMFROMEMPWHEREDEPTNOIN_DEPTNO；ELSIFIN_DEPTNO20THENSELECTCOUNTDEPTNOINTOOUT_NUMFROMEMPWHEREDEPTNOIN_DEPTNO；ELSIFIN_DEPTNO30THENSELECTCOUNTDEPTNOINTOOUT_NUMFROMEMPWHEREDEPTNOIN_DEPTNO；ELSESELECTCOUNTDEPTNOINTOOUT_NUMFROMEMPWHEREDEPTNO40；ENDIF；RETURNOUT_NUM；ENDCOUNT_FUNC；13用实际参数代替形式参数。SQLEXECUTEEMPDNUMCOUNT_PROC10；14实验五数据表的管理一、实验目的1、利用SQL语句（2种）创建表；2、掌握在OEM中创建表；3、掌握定义表的约束、添加、删除和激活约束；4、掌握修改表的各种操作。二、实验环境1、硬件设备计算机局域网，服务器1台，客户机若干台2、软件系统WINDOWS2003SERVER网络操作系统，WINDOWS2003/XP客户机操作系统；ORACLE9I服务端数据库系统，客户端工具。三、实验内容一、实验内容和步骤1、创建数据表CREATETABLE列级完整性约束条件，列级完整性约束条件，参数设置2、创建带约束的数据表CREATETABLE列级完整性约束条件，列级完整性约束条件，参数设置AS子查询3定义约束（1）列级约束语法格式CONSTRAINTCONSTRAINT_NAMECONSTRAINT_TYPECONDITION（2）表级约束语法格式CONSTRAINTCONSTRAINT_NAMECONSTRAINT_TYPECOLUMN1_NAME,COLUMN2_NAME,|CONDITION3、表约束的修改ALTERTABLE15ADDMODIFYENABLEDISABLEDROP4、字段的的添加、删除、修改ALTERTABLEADDRENAMECOLUMNOLDNAMETONEWNAMESETUNUSEDCOLUMNCOLUMN/SINGLECOLUMNSETUNUSEDCOLUMNS（COLUMN1，COLUMN2）DROPCOLUMN/SINGLECOLUMNDROP/MULTICOLUMNDROPUNUSEDCOLUMNS5、利用OEM创建表、删除表、修改表和查询表1617实验六索引的管理一、实验目的1、熟悉利用SQL语句创建索引2、掌握在OEM中创建索引3、掌握修改索引的各种操作二、实验环境1、硬件设备计算机局域网，服务器1台，客户机若干台2、软件系统WINDOWS2003SERVER网络操作系统，WINDOWS2003/XP客户机操作系统；ORACLE9I服务端数据库系统，客户端工具。三、实验内容1、使用命令创建索引CREATEUNIQUEBITMAPINDEXON次序,次序REVERSEPARAMETER_LIST2、使用命令修改索引ALTERINDEXCOALESCEALTERINDEXREBUILDERALTERINDEXMONITORINGUSAGEALTERINDEXRENAMETO3、删除索引DROPINDEX4、使用OEM创建索引、删除索引、修改索引和查询索引1819实验七安全管理一、实验目的1、熟悉利用SQL语句对用户、权限和角色进行管理2、掌握在OEM中对用户、权限和角色进行管理二、实验环境1、硬件设备计算机局域网，服务器1台，客户机若干台2、软件系统WINDOWS2003SERVER网络操作系统，WINDOWS2003/XP客户机操作系统；ORACLE9I服务端数据库系统，客户端工具。三、实验内容1、CREATEUSERUSER_NAMEIDENTIFIEDBYPASSWORD|EXTERNALLY|GLOBALLYASEXTERNAL_NAMEDEFAULTTABLESPACETABLESPACE_NAMETEMPORARYTABLESPACETEMP_TABLESPACE_NAMEQUOTANK|M|UNLIMITEDONTABLESPACE_NAMEPROFILEPROFILE_NAMEPASSWORDEXPIREACCOUNTLOCK|UNLOCK2、ALTERUSERUSER_NAMEIDENTIFIEDBYPASSWORD|EXTERNALLY|GLOBALLYASEXTERNAL_NAMEDEFAULTTABLESPACETABLESPACE_NAMETEMPORARYTABLESPACETEMP_TABLESPACE_NAMEQUOTANK|M|UNLIMITEDONTABLESPACE_NAMEPROFILEPROFILE_NAMEDEFAULTROLEROLE_LIST|ALLEXCEPTROLE_LIST|NONEPASSWORDEXPIREACCOUNTLOCK|UNLOCK3、DROPUSERUSER_NAMECASCADE4、CREATEROLEROLE_NAMENOTIDENTIFIEDIDENTIFIEDBYPASSWORD5、ALTERROLEROLE_NAMENOTIDENTIFIEDIDENTIFIEDBYPASSWORD；6、DROPROLEROLE_NAME7、GRANTSYS_PRIV_LISTTOUSER_LIST|ROLE_LISTPUBLICWITHADMINOPTION208、REVOKESYS_PRIV_LISTFROMUSER_LIST|ROLE_LIST9、GRANTOBJ_PRIV_LIST|ALLONSCHEMAOBJECTTOUSER_LIST|ROLE_LISTWITHGRANTOPTION10、REVOKEOBJ_PRIV_LIST|ALLONSCHEMAOBJECTFROMUSER_LIST|ROLE_LIST11、利用OEM对用户、权限和角色进行管理。21实验八触发器和游标一、实验目的1、了解触发器的概念。2、熟悉触发器的基本用法。3、了解游标的概念。4、熟悉游标的基本用法。二、实验环境1、硬件设备计算机局域网，服务器1台，客户机若干台2、软件系统WINDOWS2003SERVER网络操作系统，WINDOWS2003/XP客户机操作系统；ORACLE9I服务端数据库系统，客户端工具。三、实验内容1、声明显式游标声明一个游标用来读取基表EMP中部门号是20且工作为分析员的职工DECLARECURSORC1ISSELECTENAME,SAL,HIREDATEFROMEMPWHEREDEPTNO20ANDJOBANALYSTV_ENAMEVARCHAR210V_SALNUMBER7,2V_HIREDATEDATEBEGINOPENC1FETCHC1INTOV_ENAME,V_SAL,V_HIREDATECLOSEC1END2、使用游标使用游标属性判断游标是否打开IFC1OPENTHENFETCHC1INTOV_ENAME,V_SAL,V_HIREDATEELSEOPENC1ENDIF利用循环读取数据LOOPFET