《Oracle上机指南-2011》由会员分享，可在线阅读，更多相关《Oracle上机指南-2011（33页珍藏版）》请在金锄头文库上搜索。
1、数据库应用技术 Oracle 上机指南郭东伟 2008年12月目录v软件与使用说明v输入sql的注意事项v练习一v练习二v练习三v练习四软件使用v通常，在oracle客户端使用sqlplus作为标准 的命令行工具需要安装oracle的客户端驱动以前经常被使用，基于命令行，但使用不方便在无图形模式下是第一选择vsqldeveloper一个基于java的图形化界面工具无需驱动安装SQL Developerv我们要使用的SQL Developer是一个基于 Java的图形界面的SQL执行工具，使用比较 方便。v双击d:oraclisqldeveloper目录下面的 sqldeveloper.exe即
2、可执行v第一次执行时可能会询问java目录，请输入 C:Program FilesJavajdkbin（依赖于机房 环境，可能略有不同）SQL Developer的界面SQL Developer的界面组成v左上方是连接设置，以及连接后数据库对象 浏览树。v右面上方的页签说明可以有多个待编辑的 SQL脚本。v右上方是输入SQL的部分。v右面中下是SQL语句执行结果的显示区域v右下方显示其他信息。SQL Developer建立连接v右击左上角的 Connections，选择 newv在弹出的如右图所示 对话框中输入服务器 信息。包括用户名和 密码v服务器名根据机房情 况而定vSID部分必须输入 O
3、RCLv可以使用test按钮进 行测试。使用 connect按钮连接v注意，连接时间可能 比较长v连接成功以后可以直 接点击相应的连接 name登录数据库服务器IP: 192.168.5.240必须是 ORCLSQL Developer连接后v左上角是这 个数据库的 各个数据库 对象（当前 用户可见）v可以点击各 个对象，直 接查看对象 内容常用操作v第一次连接后右侧为输入SQL命令的子窗口v如不慎关闭右击左侧连接，Open SQL Worksheetv直接在Worksheet上面输入，每个SQL语句以分 号结尾v点击工具栏上第一个按钮（或F9），执行当前 语句v点击工具栏上第二个按钮（或F5
4、），批量执行 输入框中所有语句查询数据v查询的结果是本用户权限下可见的数据；其 他会话修改但未提交的数据不能看到（读提 交）。v如果有授权，可以查询其他用户的表(和视图)teacher用户下，有我们上课的示例表和数据。所 有用户可以查询，但不能修改SELECT * FROM TEACHER.AGENTS;SQL注意事项v修改密码 alter user 用户名 identified by 新密码v利用SQL进行纯粹的计算，可以使用DUAL表 SELECT 1+2 FROM DUALv查看当前用户下有哪些表 SELECT * FROM TAB; SELECT OWNER, TABLE_NAME F
5、ROM ALL_TABLES;v复杂的PL/SQL命令中可能需要使用/表示整个语 句结束。日期格式v缺省日期格式为简单格式，如 例所示v可以使用TO_CHAR函数进行 调整v可以使用ALTER SESSION命 令改变当前会话的缺省格式v输入日期时要按照缺省格式或 者使用TO_DATE函数vSELECT SYSDATE FROM DUAL;vSYSDATEv-v09-5月 -05vSELECT TO_CHAR(SYSDATE, YYYY/MM/DD HH24:MI:SS) FROM DUAL;vALTER SESSION SET NLS_DATE_FORMAT=YYYY/MM/D D HH24
6、:MI:SS;vSELECT SYSDATE FROM DUAL;vSYSDATEv-v2005/05/09 01:52:10练习一v各组分别领取数据库用户名和密码，组内修 改密码v登录SQL Developer，掌握基本使用v使用SELECT语句完成后面列出的例题和习 题标有*号的题目较难数据来源v可使用teacher用户下面的示例表，如SELECT * FROM TEACHER.AGENTS;v也可以在自己创建的同样表格上执行快速创建表vCREATE TABLE MY_AGENTS(目标表) AS SELECT * FROM TEACHER.AGENTS(子查询)v这样创建的表不继承任何约
7、束，只有数据。参考给出的文档创建表格例题及习题基本查询v例1：查询所有产品信息v例2：查询ID为A01的代理商v例3：查询名字以S开头的代理商v例4：查询购买总金额在500和1000之间的不重复的 商品IDv例4：列出每个代理商名字的大写和薪水100的值v例5：计算当前时间和12的值v例6：列出地址为空的顾客信息，按姓名升序排列。v例7：按照“Mary In NewYork”的样式用一个表达式列 出所有顾客名字和城市信息。城市为空用(NULL)表 示，按名字降序排列。(需使用NVL函数)例题及习题连接v例1：找出每个顾客购买的商品名称和数量； v例2：找出住在Dallas或Duluth的顾客购
8、买的产品； v例3：列出每个代理商及其经理的姓名； v例4：找出在同一城市居住的顾客对；v例5：找出比Smith工资高的代理商；v例6：写出购买过商品的顾客姓名和产品名称； v例7：写出购买单价1元的产品的顾客名单 ；v例8：找出与经理在同一城市的代理商；v例9 (*) ：列出7天内至少购买过两次产品的CID例题及习题聚组函数v例1：当前有多少订单 ；v例2：当前LOCATIONS内有多少个邮编（可以重复）？v例3：有多少个顾客购买过产品？v例4：列出每一个顾客ID，总购买额。 v例5：列出每一个顾客ID，姓名及总购买额。v例6：单笔金额超过500的订单被称为大订单，只考虑大的 订单，按照产品
9、计算平均销售额。v例7：计算平均销售额大于700的各种产品及总销售额。 v例8：求至少有两次大订单的顾客ID及大订单总金额。（ 能否求订单总额？）v例9：按产品分类，总销售额最高值是多少？例题及习题子查询v例1：查询和A01工资相同的其他人。 v例2：查询比Smith工资高的人。（测试出现同名的后果）v例3：查询比平均工资高的人员信息。v例4：销售量第二高的单笔销售额。v例5：找出比工作在L01的所有人工资高的人员。v例6：求通过居住在L01的代理商购买货物的顾客ID。 v例7：没有订货的顾客ID和姓名。v例8：薪水最高的AID及姓名。v例9：找出住在Duluth 的顾客通过位于L01的代理订
10、货的订单号。v例10(*)：没有通过A05订货的顾客姓名v例11(*) ：通过所有代理商订货的顾客ID。v例12(*)：列出购买过的产品集合完全相同的CID对（注：这个查询相当 难，请参考 http:/www.skywind.name/blog/?p=84 ）练习二 复杂的SQL语句vTOP-N问题vDML语句vDML与事务2.1 复杂SELECTv使用各种方法试验解决TOP-N问题以ORDERS表格为例，按照DOLLARS排序查找 TOPN记录包括TOP-N问题的各种变形v实验查询的集合运算2.2 DML语句和事务vDML语句执行后，本次会话数据暂时修改v其他会话仍使用修改之前数据v使用CO
11、MMIT命令提交修改v使用ROLLBACK命令撤销修改习题过程I1.自己建立一个例子表2.向表中插入数据，练习insert语句3.如果插入语句没有错误，使用select查询此表，检 查数据。注意省略字段的值。4.请同组其他同学查询自己的例子表，检查有无第 二步增加数据？5.输入COMMIT命令，然后重复第4步。6.重复第2步，增加新的数据，然后执行 ROLLBACK命令。7.使用select检查表中数据。习题过程II1.在自己建立的表中修改某行的值，注意UPDATE 语法2.请同组另外同学修改同一个表的同一行数据，观 察现象。3.在自己机器上COMMIT或者ROLLBACK，同时观 察第2步同
12、学机器4.练习DELETE语句5.仿照课堂例子，完成从AGENTS表到ACOPY表的 同步练习三 数据库对象v建立各种数据库对象v设计一套学生借书数据库应用系统表格（带 有约束），并实现之v试验各种约束的作用。v学习使用视图来简化查询。v试验索引的创建习题过程III1.设计一套学生借书数据库应用系统表格（带有约 束） 。一种方案如下：学生信息表、书籍信息表 （包括总册数、已借出数）、学生借还书日志（ 包括学号、书号、借/还标识、时间等）。2.使用CREATE TABLE等语句实现上面的设计3.在这个设计上，建立适当的视图（如学生借书总 数、书被借阅次数）。4.练习通过视图查询（如查询最常被借阅
13、的书籍）5.考虑这个系统中索引的设计练习四 PL/SQL的使用v练习PL/SQL的使用v练习创建存储过程和函数v执行存储过程和函数v建立触发器v初步学习如何调试存储过程。建立存储过程举例v将存储过程代码直接在PLSQL- Developer的SQL窗口中加入， 并执行(F5)v如果没有拼写错误，输出栏会 提示（procedure sp1 Compiled.）v否则提示（Warning: 执行完毕, 但带有警告）v下面先建立一个表spt1，如右 侧例子CREATE TABLE SPT1 ( C1 DATE, C2 INT, C3 CHAR(10) );建立存储过程举例v按照右侧代码输入，注意 各
14、行的;和最后的/v输入完成后执行。如无错 误继续v输入（将所有输入删除后 ） execute sp1; select * from spt1;v批量执行（F5）。会在输 出中看到spt1表中增加了 一行记录create or replace procedure sp1 as BEGIN DECLAREv1 integer; BEGINselect count(*) into v1 from orders;insert into spt1(c1, c2, c3)values(sysdate, v1, test);END; END; /存储过程调试v如果输入有错误（包括语法错误等），该存储过 程依
15、然被建立，但处于不可用的状态。v提示 procedure sp1 Compiled. Warning: 执行完毕, 但带有警告v错误信息存储在user_errors表中，用户可以通 过select * from user_errors来查询v该表只保存最后一次的错误信息vOracle的错误信息可读性不强，需要仔细检查执行存储过程v在SQL输入窗口使用EXECUTE 过程名可以 执行，但必须使用（Run Script F5）来执行v可以使用类似下面语法，作为一条命令执行 （F9），随后一行一定要加一个，作为 PL/SQL的结束标志begin sp1; end; 使用PL/SQL Develope
16、r管理存储过程v在左侧的目录中找到Procedures，按右键选择新建v经过一个向导对话框之后，会有一个编辑窗口，并 且直接有PL/SQL的基本框架代码，用户可直接在此 基础上修改v修改完成后，首先选择工具栏中的“Compile”，如有 错误，会直接出现在下方v编译成功后，可以按Run来运行v编译好的存储过程，可以在左侧目录中通过“右键- Edit”来修改在存储过程中输出调试信息v通常，存储过程的结果会体现在数据库的表 中。v在复杂程序中，我们可能需要知道运行过程 中变量值等，来方便调试v可以在存储过程中使用 Dbms_output.put_line函数来产生调试信息。v可以在DBMS Output窗口查看调试信息在存储过程中输出调试信息v修改过程sp1，加入红色的行。v在SQL输入窗口中，输入set serveroutput on;并执行此命令的
