Oracle上机指南-XXXX

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