oracle大型数据库开发技术.docx

大型数据库开发技术教学讲稿 一、教学目的和基本要求 数据库技术是计算机科学技术的重要分支。数据库已经成为信息基础设施的核心技术和重要基础。通过本课程学习，使学生系统地掌握数据库系统的基本原理的同时，熟练掌握Oracle11g的基本应用和操作；熟练使用SQL语言；熟练掌握PL/SQL编程和存储过程；掌握数据库设计方法和步骤，使学生初步具有设计数据库模式以及开发数据库应用系统的基本能力。 实验课程要求部分：要求学生在学习数据库原理理论知识的同时，一定要以Oracle11g为实验环境，掌握相关实用工具的使用，完成要求的实际操作和应用编程，熟悉开发数据库应用系统的流程和基本技巧。 二、实验项目与学时分配 实验编号实验项目实验类型性质学时1Oracle11g的安装、使用和卸载演证性必开22SqlPlu和PL/SQL DEVELOPER的使用演证性必开43SQL语言演证性必开64数据库的安全性和完整性演证性必开45PL/SQL语言演证性必开46通过JDBC方式访问数据库综合性必开27查询优化和Oracle11g的备份与恢复演证性必开48XML语言及应用演证性必开4总学时 30 三、教材、讲义及参考书 Oracle数据库设计与实现 作者：陆云帆 出版社： 机械工业出版社 出版日期：2011年6月 四、评分依据 1、实验预习和表现：上机实验前，学生必须事先根据题目的内容编好程序，然后在实验时输入程序、调试程序、直至运行结果正确为止。在实验中，教师可根据学生编程操作能力、观察和分析及运用知识能力、程序编制正确性以及学生的课堂纪律、实验态度、保持实验室卫生等方面的表现进行综合考核。 2、实验报告：学生实验后应按时完成实验报告。实验报告应包括以下内容：实验目的、实验要求，实验题目、程序清单、运行结果、实验小结。 以综合性实验为基础，开发一个具有实际意义的小系统，从而更深层次地了解oracle，并提高实际动手动脑能力。 通过考试了解学生对基本理论掌握的程度和实际水平，教师出实验考试题目，让学生在规定的时间完成。 五、实验项目 实验一：Oracle11g的安装、使用和卸载 实验目的 1 熟悉Oracle11g的安装过程、参数配置、注意事项和常见问题的解决。 2 熟悉对DBMSOracle11g的基本概念、术语和常用操作，了解其工作原理和系统构架。3Oracle11g的卸载。4在实验室搭建实验环境。5Oracle11gOracleEnterpriseDatabaseManager、SQL*PLUS的基本操作。实验内容及要求1 Oracle11g的安装和启动：机房已经安装好，有计算机的同学自己亲自安装一遍。按照向导安装即可，注意以下参数的配置：安装步骤：n选择安装的产品n选择安装类型：n选择数据库配置类型：如果不想创建数据，选择只安装软件。n全局数据库名和SID、实例名相同，一个数据库必须有一个SID（SystemIDentifier）来引用。n设置数据文件存放目录n选择数据库字符集ZHS16GBKn设置网络配置n验证Oracle11g是否安装成功？n刚安装完Oracle11g时，一定要记下两个重要的链接：http:/localhost:5560/isqlplus和http:/localhost:5500/emn2安装目录：lOracle11g的安装路径中不能包含汉字，否则安装会出错。lOracle11g的ORACLE_HOME为oracleproduct11.1.0。loracleproduct11.1.0oradata存放Oracle数据库，一个数据库orcl对应一个文件夹orcl。3环境变量PATH：因为Oracle11g自动安装JDK1.4.2和JRE1.4.2，注意和机器上已经安装的JDK版本冲突问题。PATHC:oracleproduct11.1.0Db_1bin;C:oracleproduct11.1.0Db_1jre1.4.2binclient;C:oracleproduct11.1.0Db_1jre1.4.2bin;4Oracle11g的基本概念：User：用户为了使用Oracle，必须启动一个应用程序并用账号和口令与Oracle建立一个连接。Oracle默认建立许多内置用户，一般除sys,system外均处于锁定状态。SYS用户是Orcle数据库的超级用户，拥有数据库的超级权限，能够执行所有的数据库操作。SYSTEM用户一般作为普通的数据库管理员DBA用户，也拥有很多的管理权限。连接身份：Normal正常或一般登录；sysoper系统操作员；sysdba数据库管理员。要想创建和管理数据库，需要以数据库的特权身份sysdba)登录。lSchema（模式或方案）：Schema是逻辑地（而不是物理存储）组织数据库的对象。是组织在用户下的所有数据库对象的集合。模式为一个数据库用户所有，并且有与该用户名相同的名称。lOracle数据库对象包括：l Table（表）：l Index（索引）：为排序而生，加速对于表中的数据的查询。l Constrainer（约束）：用于保证数据的一些完整性规则。l View（视图）：把一个查询的SQL语句存储成一个数据库对象。l Sequence（序列）：产生类似递增或递减的不重复的数字。l Synonym（同义词）：一个数据库对象的别名。l StoredProcedure（存储过程）：一个存储在数据库中的PL/SQL程序。l Function（函数）：一具存储在数据库中的PL/SQL函数。l Trigger（触发器）：可以由某些事件而触发去执行某些PL/SQL的程序l Package（包）：一个存储过程和存储函数的集合。l 权限Priority：分为系统权限和用户权限。l 角色Role：为了方便权限的管理，引入角色Role的概念。l5Oracle11g卸载：开始/程序/Oracle-OraDb11g_home1/OracleInstallationProducts/UniversalInstaller6实验环境的构建：l用桌面/快捷方式/飞鸽传书接收SQL脚本文件：DBINIT.sql保存在D:下，进行Sql*plus环境：SQLd:DBINIT.sqll lNetmeeting:confl l因为重启机器后信息全部丢失，以后每次上机均需执行以上步骤。7Windows中相关的Oracle服务的设置：控制面板/管理工具/服务：为提高机器运行效率，通常将Oracle11g相关的8个服务设为手动启动。一般情况下，使用Oracle11g时只需要启动两个服务即可：OracleServiceORCL：与Oracle实例名ORA相关的Windows服务。OracleOraDb11g_home1TNSListener：用来启动Oracle服务器端的网络监听服务功能。OracleDBConsoleorcl和OracleCSService：通过http:/localhost:5500/em启动OracleEnterpriseManagerDatabaseControl时必须启动。OracleOraDb11g_home1iSQL*Plus：通过http:/localhost:5560/isqlplus启动iSQL*Plus时必须启动。8OralceEnterpriseDatabaseManagerControl：lOracle9i中的基于Java框架的企业管理器已经被基于WEB的OralceEnterpriseDatabaseManagerControl所取代，可以用来管理、诊断和调优数据库。l登录：http:/localhost:5500/em，输入用户名、口令、连接身份。l修改SYS密码：主目录/首选项l创建用户：主目录/设置/创建NETCA（NetConfigurationAssistant）/NETMGR（NetManager）：图形化网络配置工具：NETCA采用向导的方式，一步一步采集参数，NETMGR采用菜单界面的方式一次性的在一个界面中配置很多参数。9DatabaseConfigurationAssistan：开始/程序/Oracle-OraDb11g_home1/ConfigurationandMigrationTools/DatabaseConfigurationAssistant，11Sql*plus：是Oracle内嵌工具之一，提供了SQL语句执行的环境，也可以用于管理数据库。l Dos环境下进入方法：Dos提示符下输入sqlplus或sqlplussys/zyj2009orclassysdbal Windows环境：开始/程序/Oracle-OraDb11g_home1/ApplicationDevelopment/SQLPlus/，HostStringl 浏览器地址栏：http:/localhost:5560/isqlplus，连接标识符：如果用户只安装一个数据库的话，连接标识符和主机字符串可以为空，否则必须指定登录数据库的SID。l 常用命令：详见下一次实验。根据以上要求认真填写实验报告，记录所有的实验用例、遇到的问题及其解决方法。实验二：SqlPlus和PL/SQLDEVELOPER的使用实验目的：1掌握SQL*PLUS的常用命令。2.掌握PL/SQLDEVELOPER7.0的基本使用。实验内容和要求：1.进入和退出SQL*Plus：l Dos环境下进入方法：Dos提示符下输入sqlplus或sqlplussys/zyj2009orclassysdbal Windows环境：开始/程序/Oracle-OraDb11g_home1/ApplicationDevelopment/SQLPlus/，HostStringl 浏览器地址栏：http:/localhost:5560/isqlplus，连接标识符：如果用户只安装一个数据库的话，连接标识符和主机字符串可以为空，否则必须指定登录数据库的SID。l 退出SQL*Plusl SQLexit或注销2常用Sql*plus命令：详见SQLPLUS命令的使用大全.docl 在sqlplus下列出全部SQL命令和SQL*Plus命令帮助信息SQLhelpindex;l 列出某个特定的命令的信息SQLhelpell 连接Oracle数据库命令connectusername/passwordoracledbassysdba|sysoperl 显示Sql*plus参数的当前设置：showall;Windows下Options/Environmentl显示当前的用户名showuserl 查询一个用户下的对象SQLselect*fromtab;SQLselect*fromuser_objects;l查询一个用户下的所有的表SQLselect*fromuser_tables;l查询一个用户下的所有的索引SQLselect*fromuser_indexes;l显示系统日期：SQLselectsysdatefromdual;l查看当前用户下所有的表SQLselect*fromuser_tables;查看表user_tables的结构;SQLdescuser_tables;l查看某表的所有记录SQLselect*from&table_name;或SQLselect*fromdept;l 显示表结构命令DESCRIBE，使用最频繁的命令，可以返回Oracle数据库中所有存储对象的描述，尤其是表和视图，可以列出每一列的名称和类型。SQLDESC表名l 查看某表的大小selectsum(bytes)/(1124*1124)assize(M)fromuser_segmentswheresegment_name=upper(&table_name);l 查看序列号，last_number是当前值SQLselect*fromuser_sequences;l 查看视图的名称SQLselect view_name from user_views; l l 用系统管理员，查看当前数据库有几个用户连接： SQL select username,sid,serial# from v$session; l l 运行SQL脚本文件(*.sql) SQLSTART test SQLc:dbinit.sql l l 将显示的内容输出到指定文件 SQL SPOOL file_name 在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。 关闭spool输出 SQL SPOOL OFF 只有关闭spool输出，才会在输出文件中看到输出的内容。 l l 将sql buffer中的sql语句保存到一个文件中 SAVE file_name l l 执行一个存储过程 EXECUTE procedure_name l l 写一个注释 REMARK text l l 将指定的信息或一个空行输出到屏幕上 PROMPT text l l 将执行的过程暂停，等待用户响应后继续执行 PAUSE text SqlPAUSE Adjust paper and press RETURN to continue. 3. PLSQL Developer的使用：以后主要在此环境下学习和开发，重点掌握。 l l 软件：PLSQL Develope绿色版，不建议使用中文版（出错太多）。 l l 详细请参考：PLSQL Developer70 用户指南.pdf 主要应该掌握的操作： l l 连接Oracle数据库、注销、重新登录。 l l 输入、编辑、运行SQL语句，并保存SQL文件。 l l 查看方案对象：MY OBJECT l 修改、查看表结构。 l l 建立运行存储过程、函数。 根据以上要求认真填写实验报告，记录所有的实验用例、遇到的问题及其解决方法。 实验三：SQL语言 一实验目的 1熟悉Oracle提供的运算符、数据类型和函数。 2掌握数据库Oracle的DDL。 3掌握数据库Oracle的DCL 4熟悉掌握数据库Oracle的DML 5完成SQL语句的上机训练作业。 实验内容和要求： 1.实验环境配置： l l 开机选择：Winxp2由于机房安装时采用Ghost广播和硬盘写保护，每次上机必须进行配置Oracle11g才能使用：用户sys,system,scott密码：zyj2009,zyj2009,tigerl配置服务和监听器：开始/程序/Oracle-OraHome92/ConfigurationandMigrationTools/NetManager将服务wj的主机名改为本机名：computer5将监听器的主机名改为本机名：computer5l手动启动Oralce服务：开始/程序/管理工具/服务一般情况下Oracle11g需要启动的Windows服务通常有两个：OracleServiceORA：与Oracle实例名ORA相关的Windows服务。OracleOraHome92TNSListener：用来启动Oracle服务器端的网络监听服务功能OracleDBConsoleorcl：通过http:/localhost:5500/em启动OracleEnterpriseManagerDatabaseControl时必须启动。OracleOraDb11g_home1iSQL*Plus：通过http:/localhost:5560/isqlplus启动iSQL*Plus时必须启动。l在sqlplus环境中执行DBINIT.sql文件建立表和基本测验数据：sqlc:dbinit.sql用以下命令检测是否成功：sqlselecttable_namefromuser_tables;sqldescstudent;sqlselect*fromstudent;2 Oracle的DDL：3Oracle的DML：Oracle的运算符、数据类型和函数。LEN,NVL,Select语句：l 单表查询l 连接查询：内联接、外联接、交叉联接、等值联接、自然联接、自联接。ll 嵌套查询ll 集合查询l Insert语句l Update语句Delete语句4Oracle的DCL：l 事务提交：COMMITll 事务回滚：ROLLBACKll 设立保存点：SAVEPOINTl ROLLBACKTO保存点名称l grant, revoke l l Lock/Unlock 5上机作业： 5.1 在Oracle 11g中建立学生课程数据库（P82），在其中建立student,course,sc表并输入基本数据。 5.2 P74习题5 SPJ数据库，并建立S、P、J、SPJ四个表，并输入实验数据。进行实验所要求的各种操作，所有的SQL操作均在此建立的新库里进行。 5.3 P127习题5.4 综合SQL训练： l 查找公司员工编号Employee_id ，用户名（first_name与last_name连接成一个字符串），职位编号job_id及last_name的长度，要求职位job_id从第四位起匹配ACCOUNT，同时last_name中至少包含一个e字母。 l l 查询员工的编号Employee_id ，姓名first_name，以及部门名称department_name(分别使用Oracle语法；自然连接usingon子句)。 l l 查询部门名称为Shipping的员工的编号、姓名及所从事的工作job_id。 l l 查询所有工资salary大于等于6000元的员工姓名及其经理的姓名、工资。要求查询结果中在员工和直接领导人之间加入字符串“works for”。 l l 查询员工的编号，姓名，以及部门名称，包括没有员工的部门。 l l 查询不属于任何部门的员工的编号，姓名。 l l 查询各部门平均工资在8000元以上的部门名称及平均工资。 l l 查询工作编号中不含有“SA_”字符串及平均工资在8000元以上的员工编号及平均工资，并按平均工资降序排序。 l l 查询部门人数在4人以上的部门的部门名称及最低工资和最高工资。 l l 查询工作不为AD_PRES，工资的和大于等于25000的工作编号和每种工作工资的和。 l l 显示经理号码，这个经理所管理员工的最低工资，不包括经理号为空的，不包括最低工资小于3000的，按最低工资由高到低排序。 l l 查询last_name是Chen的员工的信息。 l l 查询参加工作时间在1997-7-9之后，并且不从事IT_PROG工作的员工的信息。 l l 查询员工last_name的第三个字母是a的员工的信息。 l l 查询除了11、20、111号部门以外的员工的信息。 l l 查询部门号为50号员工的信息，先按工资降序排序，再按姓名升序排序。 l l 查询没有上级管理的员工(经理号为空)的信息。 l l 查询员工表中工资大于等于4500并且部门为50或者60的员工的姓名(last_name), 工资，部门号。 l l 查询工资高于编号为113的员工工资，并且和112号员工从事相同工作的员工的编号、姓名及工资。 l l 工资最高的员工姓名和工资。 l l 查询部门最低工资高于110号部门最低工资的部门的编号、名称及部门最低工资。 l l 查询员工工资为其部门最低工资的员工的编号和姓名及工资。 l l 显示经理是KING的员工姓名，工资。 l l 试创建视图v_emp_80，包含80号部门的员工编号，姓名，年薪列。 l l 从视图v_emp_80中查询年薪在12万元以上的员工的信息。 l l 创建试图v_dml，包含部门编号大于110号的部门的信息。 根据以上要求认真填写实验报告，记录所有的实验用例、遇到的问题及其解决方法。 实验四：数据库的安全性和完整性 实验目的： 1熟悉、掌握通过SQL语句对Oralce数据库进行安全性控制。 2熟悉、掌握通过SQL语句对Oralce数据库进行完整性控制。（三类完整性、CHECK短语、CONSTRAIN子句、触发器）。进行违约操作，用实验证实，当操作违反了完整性约束条件时，系统是如何进行违约处理的。 实验内容和要求 1Oracle 11g的权限。 2Oracle的角色。 3Oracle的用户，用户权限的授权与回收（GRANT 语句和 REVOKE 语句）。 4使用SQL对数据进行完整性控制（3类完整性、CHECK短语、CONSTRAIN子句、触发器）。用实验证实，当操作违反了完整性约束条件时，系统是如何处理的。 5上机作业：P148-8,9 6P164 习题6的上机实践。Employees,Departments的定义 根据以上要求认真填写实验报告，记录所有的实验用例、遇到的问题及其解决方法。 实验五：PL/SQL语言 实验目的： 1熟悉PL/SQL语言的变量声明、数据类型、控制结构等基本概念。 2掌握PL/SQL语言块、函数、过程、包、触发器的编写方法。 3掌握使用游标来进行数据库应用程序的设计。 实验内容及要求： 1. 从部门表中找到最大的部门号，将其输出到屏幕 2. 定义变量代表员工表中的员工号，根据员工号获得员工工资，如果工资小于4000，输出到屏幕上的内容为员工姓名和增涨11以后的工资，否则输出到屏幕上的内容为员工姓名和增涨5以后的工资 3. 对学生课程数据库，编写存储过程，完成下面功能： 1）统计离散数学的成绩分布情况，即按照各分数段统计人数； 2）统计任意一门课的平均成绩。 3）将学生选课成绩从百分制改为等级制（即A、B、C、D、E）； 根据以上要求认真填写实验报告，记录所有的实验用例、遇到的问题及其解决方法。提交源程序并标识必要的注释。保证程序能正确编译和运行，认真填写实验报告。 实验六：通过JDBC方式访问数据库 实验目的： 1熟悉通过JDBC访问数据库完成CRUD等操作的步骤，熟练掌握Statement对象、PrepareStatement对象、用CallableStatement调用存储过程的编程。 2事务管理的编程实现。 3使用Java语言和Oracle数据库在Netbeans6.0平台完成C/S通讯录软件的设计和编程实现。 实验内容及要求： 1 知识准备：JDBC是由一组用Java语言编写的类