《Oracle上机指南》PPT课件.ppt

数据库应用技术 Oracle 上机指南 郭东伟 2008年12月 目录 v软件与使用说明 v输入sql的注意事项 v练习一 v练习二 v练习三 v练习四 软件使用 v通常，在oracle客户端使用sqlplus作为标准 的命令行工具 需要安装oracle的客户端驱动 以前经常被使用，基于命令行，但使用不方便 在无图形模式下是第一选择 vsqldeveloper 一个基于java的图形化界面工具 无需驱动安装 SQL Developer v我们要使用的SQL Developer是一个基于 Java的图形界面的SQL执行工具，使用比较 方便。 v双击d:oraclisqldeveloper目录下面的 sqldeveloper.exe即可执行 v第一次执行时可能会询问java目录，请输入 C:Program FilesJavajdkbin（依赖于机房 环境，可能略有不同） SQL Developer的界面 SQL Developer的界面组成 v左上方是连接设置，以及连接后数据库对象 浏览树。 v右面上方的页签说明可以有多个待编辑的 SQL脚本。 v右上方是输入SQL的部分。 v右面中下是SQL语句执行结果的显示区域 v右下方显示其他信息。 SQL Developer建立连接 v右击左上角的 Connections，选择 new v在弹出的如右图所示 对话框中输入服务器 信息。包括用户名和 密码 v服务器名根据机房情 况而定 vSID部分必须输入 ORCL v可以使用test按钮进 行测试。使用 connect按钮连接 v注意，连接时间可能 比较长 v连接成功以后可以直 接点击相应的连接 name登录数据库 服务器IP: 40 必须是 ORCL SQL Developer连接后 v左上角是这 个数据库的 各个数据库 对象（当前 用户可见） v可以点击各 个对象，直 接查看对象 内容 常用操作 v第一次连接后右侧为输入SQL命令的子窗口 v如不慎关闭 右击左侧连接，Open SQL Worksheet v直接在Worksheet上面输入，每个SQL语句以分 号结尾 v点击工具栏上第一个按钮（或F9），执行当前 语句 v点击工具栏上第二个按钮（或F5），批量执行 输入框中所有语句 查询数据 v查询的结果是本用户权限下可见的数据；其 他会话修改但未提交的数据不能看到（读提 交）。 v如果有授权，可以查询其他用户的表(和视图) teacher用户下，有我们上课的示例表和数据。所 有用户可以查询，但不能修改 SELECT * FROM TEACHER.AGENTS; SQL注意事项 v修改密码 alter user 用户名 identified by 新密码 v利用SQL进行纯粹的计算，可以使用DUAL表 SELECT 1+2 FROM DUAL v查看当前用户下有哪些表 SELECT * FROM TAB; SELECT OWNER, TABLE_NAME FROM ALL_TABLES; v复杂的PL/SQL命令中可能需要使用/表示整个语 句结束。 日期格式 v缺省日期格式为简单格式，如 例所示 v可以使用TO_CHAR函数进行 调整 v可以使用ALTER SESSION命 令改变当前会话的缺省格式 v输入日期时要按照缺省格式或 者使用TO_DATE函数 vSELECT SYSDATE FROM DUAL; vSYSDATE v- v09-5月 -05 vSELECT TO_CHAR(SYSDATE, YYYY/MM/DD HH24:MI:SS) FROM DUAL; vALTER SESSION SET NLS_DATE_FORMAT=YYYY/MM/D D HH24:MI:SS; vSELECT SYSDATE FROM DUAL; vSYSDATE v- 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这样创建的表不继承任何约束，只有数据。 参考给出的文档创建表格 例题及习题基本查询 v例1：查询所有产品信息 v例2：查询ID为A01的代理商 v例3：查询名字以S开头的代理商 v例4：查询购买总金额在500和1000之间的不重复的 商品ID v例4：列出每个代理商名字的大写和薪水100的值 v例5：计算当前时间和12的值 v例6：列出地址为空的顾客信息，按姓名升序排列。 v例7：按照“Mary In NewYork”的样式用一个表达式列 出所有顾客名字和城市信息。城市为空用(NULL)表 示，按名字降序排列。(需使用NVL函数) 例题及习题连接 v例1：找出每个顾客购买的商品名称和数量； v例2：找出住在Dallas或Duluth的顾客购买的产品； 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的订单被称为大订单，只考虑大的 订单，按照产品计算平均销售额。 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的代理订货的订单号。 v例10(*)：没有通过A05订货的顾客姓名 v例11(*) ：通过所有代理商订货的顾客ID。 v例12(*)：列出购买过的产品集合完全相同的CID对（注：这个查询相当 难，请参考 /blog/?p=84 ） 练习二 复杂的SQL语句 vTOP-N问题 vDML语句 vDML与事务 2.1 复杂SELECT v使用各种方法试验解决TOP-N问题 以ORDERS表格为例，按照DOLLARS排序查找 TOPN记录 包括TOP-N问题的各种变形 v实验查询的集合运算 2.2 DML语句和事务 vDML语句执行后，本次会话数据暂时修改 v其他会话仍使用修改之前数据 v使用COMMIT命令提交修改 v使用ROLLBACK命令撤销修改 习题过程I 1.自己建立一个例子表 2.向表中插入数据，练习insert语句 3.如果插入语句没有错误，使用select查询此表，检 查数据。注意省略字段的值。 4.请同组其他同学查询自己的例子表，检查有无第 二步增加数据？ 5.输入COMMIT命令，然后重复第4步。 6.重复第2步，增加新的数据，然后执行 ROLLBACK命令。 7.使用select检查表中数据。 习题过程II 1.在自己建立的表中修改某行的值，注意UPDATE 语法 2.请同组另外同学修改同一个表的同一行数据，观 察现象。 3.在自己机器上COMMIT或者ROLLBACK，同时观 察第2步同学机器 4.练习DELETE语句 5.仿照课堂例子，完成从AGENTS表到ACOPY表的 同步 练习三 数据库对象 v建立各种数据库对象 v设计一套学生借书数据库应用系统表格（带 有约束），并实现之 v试验各种约束的作用。 v学习使用视图来简化查询。 v试验索引的创建 习题过程III 1.设计一套学生借书数据库应用系统表格（带有约 束） 。一种方案如下：学生信息表、书籍信息表 （包括总册数、已借出数）、学生借还书日志（ 包括学号、书号、借/还标识、时间等）。 2.使用CREATE TABLE等语句实现上面的设计 3.在这个设计上，建立适当的视图（如学生借书总 数、书被借阅次数）。 4.练习通过视图查询（如查询最常被借阅的书籍） 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按照右侧代码输入，注意 各行的;和最后的/ v输入完成后执行。如无错 误继续 v输入（将所有输入删除后 ） execute sp1; select * from spt1; v批量执行（F5）。会在输 出中看到spt1表中增加了 一行记录 create or replace procedure sp1 as BEGIN DECLARE v1 integer; BEGIN select count(*) into v1 from orders; insert into spt1(c1, c2, c3) values(sysdate, v1, test); END; END; / 存储过程调试 v如果输入有错误（包括语法错误等），该存储过 程依然被建立，但处于不可用的状态。 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 Developer管理存储过程 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;并执行 此命令的作用是开启调试输出， 并且执行后对该次会话均有效