Oracle上机指南

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