Oracle上机指南-XXXX

数据库应用技术Oracle上机指南郭东伟2010年12月目录软件与使用说明输入sql的注意事项练习一练习二练习三练习四软件使用通常，在oracle客户端使用sqlplus作为标准的命令行工具需要安装oracle的客户端驱动以前经常被使用，基于命令行，但使用不方便在无图形模式下是第一选择sqldeveloper一个基于java的图形化界面工具无需驱动安装SQLDeveloper我们要使用的SQLDeveloper是一个基于Java的图形界面的SQL执行工具，使用比较方便。双击d:\oracli\sqldeveloper目录下面的sqldeveloper.exe即可执行第一次执行时可能会询问java目录，请输入C:\ProgramFiles\Java\jdk\bin（依赖于机房环境，可能略有不同）SQLDeveloper的界面SQLDeveloper的界面组成左上方是连接设置，以及连接后数据库对象浏览树。右面上方的页签说明可以有多个待编辑的SQL脚本。右上方是输入SQL的部分。右面中下是SQL语句执行结果的显示区域右下方显示其他信息。SQLDeveloper－建立连接右击左上角的Connections，选择new在弹出的如右图所示对话框中输入服务器信息。包括用户名和密码服务器名根据机房情况而定SID部分必须输入ORCL可以使用test按钮进行测试。使用connect按钮连接注意，连接时间可能比较长连接成功以后可以直接点击相应的连接name登录数据库服务器IP:192.168.8.240必须是ORCLSQLDeveloper－连接后左上角是这个数据库的各个数据库对象（当前用户可见）可以点击各个对象，直接查看对象内容常用操作第一次连接后右侧为输入SQL命令的子窗口如不慎关闭右击左侧连接，OpenSQLWorksheet直接在Worksheet上面输入，每个SQL语句以分号结尾点击工具栏上第一个按钮（或F9），执行当前语句点击工具栏上第二个按钮（或F5），批量执行输入框中所有语句查询数据查询的结果是本用户权限下可见的数据；其他会话修改但未提交的数据不能看到（读提交）。如果有授权，可以查询其他用户的表(和视图)teacher用户下，有我们上课的示例表和数据。所有用户可以查询，但不能修改SELECT*FROMTEACHER.SALES;按照范例建自己的表CREATETABLECUSTOMERSASSELECT*FROMTEACHER.CUSTOMERSSQL注意事项修改密码alteruser用户名identifiedby新密码利用SQL进行纯粹的计算，可以使用DUAL表SELECT1+2FROMDUAL查看当前用户下有哪些表SELECT*FROMTAB;SELECTOWNER,TABLE_NAMEFROMALL_TABLES;复杂的PL/SQL命令中可能需要使用/表示整个语句结束。日期格式缺省日期格式为简单格式，如例所示可以使用TO_CHAR函数进行调整可以使用ALTERSESSION命令改变当前会话的缺省格式输入日期时要按照缺省格式或者使用TO_DATE函数SELECTSYSDATEFROMDUAL;SYSDATE----------09-5月-05SELECTTO_CHAR(SYSDATE,'YYYY/MM/DDHH24:MI:SS')FROMDUAL;ALTERSESSIONSETNLS_DATE_FORMAT='YYYY/MM/DDHH24:MI:SS';SELECTSYSDATEFROMDUAL;SYSDATE-------------------2005/05/0901:52:10练习一各组分别领取数据库用户名和密码，组内修改密码登录SQLDeveloper，掌握基本使用使用SELECT语句完成后面列出的例题和习题各个数据来源均可以使用teacher用户下面的示例表也可以在自己创建的同样表格上执行标有*号的题目较难例题及习题－基本查询例1：查询所有产品信息例2：查询ID为‘A01’的销售员例3：查询名字以’S’开头的销售员例4：查询购买总金额在500和1000之间的不重复的商品ID例4：列出每个销售员名字的大写和薪水＋100的值例5：列出当前时间和1＋2的值例6：列出地址为空的顾客信息，按姓名升序排列。例7：按照“MaryInNewYork”的样式用一个表达式列出所有顾客名字和城市信息。城市为空用’(N/A)’表示，按名字降序排列。(需使用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对（注：这个查询相当难，请参考）练习二复杂的SQL语句TOP-N问题DML语句DML与事务2.1复杂SELECT使用各种方法试验解决TOP-N问题以ORDERS表格为例，按照DOLLARS排序查找TOP－N记录包括TOP-N问题的各种变形参考资料：实验查询的集合运算这一点我们没有讲，作为选作内容参考：DML语句执行后，本次会话数据暂时修改其他会话仍使用修改之前数据使用COMMIT命令提交修改使用ROLLBACK命令撤销修改习题过程－I1.自己建立一个例子表2.向表中插入数据，练习insert语句3.如果插入语句没有错误，使用select查询此表，检查数据。注意省略字段的值。4.请同组其他同学查询自己的例子表，检查有无第二步增加数据？5.输入COMMIT命令，然后重复第4步。6.重复第2步，增加新的数据，然后执行ROLLBACK命令。7.使用select检查表中数据。习题过程－II1.在自己建立的表中修改某行的值，注意UPDATE语法2.请同组另外同学修改同一个表的同一行数据，观察现象。3.在自己机器上COMMIT或者ROLLBACK，同时观察第2步同学机器4.练习DELETE语句5.仿照课堂例子，完成从AGENTS表到ACOPY表的同步练习三建立各种数据库对象设计一套学生借书数据库应用系统表格（带有约束），并实现试验各种约束的作用。当修改后的数据学习使用视图来简化查询试验索引的创建习题过程－III1.设计一套学生借书数据库应用系统表格（带有约束）。一种方案如下：学生信息表、书籍信息表（包括总册数、已借出数）、学生借还书日志（包括学号、书号、借/还标识、时间等）。2.使用CREATETABLE等语句实现上面的设计3.在这个设计上，建立适当的视图（如学生借书总数、书被借阅次数）。4.练习通过视图查询（如查询最常被借阅的书籍）5.考虑这个系统中索引的设计练习四PL/SQL的使用练习PL/SQL的使用练习创建存储过程和函数执行存储过程和函数建立触发器初步学习如何调试存储过程。建立存储过程举例将存储过程代码直接在PLSQL-Developer的SQL窗口中加入，并执行(F5)如果没有拼写错误，输出栏会提示（proceduresp1Compiled.）否则提示（Warning:执行完毕,但带有警告）下面先建立一个表spt1，如右侧例子CREATETABLESPT1(C1DATE,C2INT,C3CHAR(10));建立存储过程举例按照右侧代码输入，注意各行的;和最后的/输入完成后执行。如无错误继续输入（将所有输入删除后）executesp1;select*fromspt1;批量执行（F5）。会在输出中看到spt1表中增加了一行记录createorreplaceproceduresp1asBEGINDECLAREv1integer;BEGINselectcount(*)intov1fromorders;insertintospt1(c1,c2,c3)values(sysdate,v1,'test');END;END;/存储过程调试如果输入有错误（包括语法错误等），该存储过程依然被建立，但处于不可用的状态。提示proceduresp1Compiled.Warning:执行完毕,但带有警告错误信息存储在user_errors表中，用户可以通过select*fromuser_errors来查询该表只保存最后一次的错误信息Oracle的错误信息可读性不强，需要仔细检查执行存储过程在SQL输入窗口使用EXECUTE过程名可以执行，但必须使用（RunScriptF5）来执行可以使用类似下面语法，作为一条命令执行（F9），随后一行一定要加一个／，作为PL/SQL的结束标志beginsp1;end;／使用PL/SQLDeveloper管理存储过程在左侧的目录中找到Procedures，按右键选择新建经过一个向导对话框之后，会有一个编辑窗口，并且直接有PL/SQL的基本框架代码，用户可直接在此基础上修改修改完成后，首先选择工具栏中的“Compile”，如有错误，会直接出现在下方编译成功后，可以按Run来运行编译好的存储过程，可以在左侧目录中通过“右键-Edit”来修改在存储过程中输出调试信息通常，存储过程的结果会体现在数据库的表中。在复杂程序中，我们可能需要知道运行过程中变量值等，来方便调试可以在存储过程中使用Dbms_output.put_line函数来产生调试信息。可以在DBMSOutput窗口查看调试信息在存储过程中输出调试信息修改过程sp1，加入红色的行。在SQL输入窗口中，输入setserveroutputon;并执行此命令的作用是开启调试输出，并且执行后对该次会话均有效输入Executesp1，并执行（F5)找到下方的DBMSOutput子窗口，在里面可以看到v1的值已经输出。createorreplaceproceduresp1asBEGINDECLAREv1integer;BEGINselectcount(*)intov1fromorders;dbms_output.put_line(‘v1=’||v1);insertintospt1(c