Oracle数据库技术与实验指导

Oracle数据库技术与实验指导

2011.08

第0章实用Oracle数据库技术

Oracle的官方网站为www.oraclo.com；

其次http://metalink.oracle,com/这里有很多权威的解决方案和补丁；

然后就是一些著名网站如：>www.orafaq.net>,这

里有很多经验之谈。

遇到问题了还可以第一时间找,这里会给你最详细的解释。

Oracle10g/llg数据库都分为标准版(StandardEdition)>标准版1(Standard

EditionOne)以及企业版(EnterpriseEdition)。可从如下网址下载、学习或试用Oracle：

http://www.oracle,com/technology/global/cn/software/products/database/orac

lelOg/index.html

http://www.oracle,com/technology/global/cn/software/products/database/inde

x.html

http://www.oracle,com/technology/software/index.html

/technology/software/products/database/index.html

SQLDeveloper也可以蝉独免费下载安爨的。下载地Ik址为：

/technology/global/cn/software/products/sql/index.html

SQL*PlusInstantClient(SQL*Plus即时客户端)

SQL*PlusInstantClient下

载:http://www.oracle,com/technology/global/cn/software/tech/oci/instantclient/

index,html。

实验1数据库系统基础操作

OracleDatabase11g第2版(.0)的下载地址:

/technology/global/cn/software/products/database/index.html

适用于MicrosoftWindows(32位)的OracleDatabase11g第2版(.0)的下载地

址：

/technology/global/cn/software/products/database/oraclellg/_win32s

oft.html

实验2数据库基础操作

手工建库

手工建库须要经过几个步骤，每一个步骤都非常关键。它包括：

1、创建相关目录(数据文件和跟踪文件)(假设要创建KCGL数据库，Oracle已安装

于小：\app\qxz”目录)

在c:\app\qxz\admin这个目录之下创建KCGL文件夹；

在C:\app\qxz\admin\KCGL这个目录之下创建adump文件夹；

在C:\app\qxz\admin\KCGL这个目录之下创建dpdump文件夹；

在C:\app\qxz\admin\KCGL这个目录之下创建pfile文件夹；

在C:\app\qxz\oradata这个目录之下创建KCGL文件夹；

2、创建初始化参数文件

通过复制现有的初始化参数文件C:\app\qxz\admin\orc1\pfi1e这个目录下的参数文

件“3nit.ora.*"(*为数字扩展名)到C:\app\qxz\product\ll.2.O\dbhome_l\database

这个目录，修改名为initKCGL.ora,最后用记事本打开这个参数文件，修改如下几个参数

的值：

audit_file_dest=C:\app\qxz\admin\KCGL\adump

db_name=KCGL

control_files=("C:\app\qxz\oradata\KCGL\control01.ctl","C:\app\qxz\oradata

\KCGL\control02.ctl")

3、打开DOS窗口，设置环境变量：Setoracle_sid=KCGL

4、创建服务：Oradim-new-sidKCGL

5、创建口令文件

Orapwdfile=C:\app\qxz\product\ll.2.0\dbhome_l\database\pwdKCGL.ora

Password=12345

6^启动服务器：Sqlplus/nolog

Conn/assysdba

Startupnomount

7、执行建库脚本：

CREATEDATABASEKCGL

datafile*c:\app\qxz\oradata\KCGL\system01.dbf*size300m

autoextendonnext10mextentmanagementlocal

SysauxdatafileJc:\app\qxz\oradata\KCGL\sysaux01.dbf,size120m

undotablespaceundotbsl

datafile?c:\app\qxz\oradata\KCGL\undotbs01.dbfJsize100m

defaulttemporarytablespacetemptbsl

tempfile*c:\app\qxz\oradata\KCGL\temp01,dbf*size50m

logfilegroup1(Jc:\app\qxz\oradata\KCGL\redo01.log*)size50m,

group2(，c:\app\qxz\oradata\KCGL\redo02.log，)size50m,

group3(Jc:\app\qxz\oradata\KCGL\redo03.log，)size50m;

用记事本编辑以上内容，假定保存为C:\CREATEKCGL.sql文件，然后执行这个脚本。

StartC:\CREATEKCGL.sql

不管出现哪种错误，都要删除C:\app\qxz\oradata\KCGL目录下创建的所有文件，改

正错误后，重新启动实例，再执行建库脚本。

8、创建数据字典和包

StartC:\app\qxz\product\ll.2.0\dbhomel\RDBMS\ADMIN\catalog

StartC:\app\qxz\product\ll.2.0\dbhome_l\RDBMS\ADMIN\catproc

9^执行pupbld.sql脚本文件

切换成system用户执行如下命令：Connsystem/manager

StartC:\app\qxz\product\l1.2.O\dbhome_l\sqlplus\admin\pupbld

10、执行scott脚本创建scott方案

StartC:\app\qxz\product\l1.2.0\dbhome_l\RDBMS\ADMIN\scott.sql

这时需要修改密码：Conn/assysdba

Alteruserscottidentifiedbytiger;

再连接scott：Connscott/tiger

11、select*fromdept;

能显示出dept表的结果，表示新数据库KCGL已安装成功了。

2.2查看数据库

1、查看表空间的名称及大小

selecttab1espace_name,min_extents,max_extents,pctincrease,statusfrom

dbatablespaces;

selecttablespacename,initialextent,nextextent,contents,logging,

extent_management,allocationtypefromdbatablespacesorderbytablespacename;

selectt.tablespacename,round(sum(bytes/(1024*1024)),0)tssizefrom

dbci_tablespacest,dba_data_fil.esdwheret.tablespace_ncime=d.tablespace_name

groupbyt.tablespace_name;

2、查看表空间物理文件的名称及大小

columndb_block_sizenew_valueblksznoprint

selectvaluedb_block_sizefromv$parameterwherename='db_block_size';

columntablespace_nameformatal6;

columnfile_nameformata60;

setlinesize160;--为sqlplus命令

selectfile_name,round(bytes/(1024*1024),0)

total_space,autoextensible,increment_by*&blksz/(1024*1024)as

incement,maxbytes/(1024*1024)asmaxsizefromdbadatafilesorderby

tablespace_name;-blksz一般为8192

selecttablespacename,fileid,filename,round(bytes/(1024*1024),0)

total_space

fromdba_data_filesorderbytablespace_name;

3、查看回滚段名称及大小

selecta.owner||'.||a.segmentnameroll_name,a.tablespace_name

tablespace,to_char(a.initial_extent)||'/'||to_char(a.next_extent)

in_extents,to_char(a.min_extents)I|'/'||to_char(a.max_extents)m_extents,

a.statusstatus,b.bytesbytes,b.extentsextents,d.shrinksshrinks,d.wraps

wraps,d.optsizeoptfromdbarollback_segsa,dbasegmentsb,v$rollnamec,

v$rollstatdwherea.segmentname=b.segment_nameanda.segmentname=c.name(+)

andc.usn=d.usn(+)orderbya.segment_name;

selectsegment_name,tablespace_name,r.status,(initial_extent/1024)

InitialExtent,(next_extent/1024)NextExtent,max_extents,v.curext

CurExtentFromdba_rollback_segsr,v$rollstatvWherer.segment_id=v.usn(+)order

bysegment_name;

4、查看控制文件

selectnamefromv$controlfile;

5、查看日志文件

selectmemberfromv$logfile;

6、查看表空间的使用情况

select*from(selectsum(bytes)/(1024*1024)as

^freespace(m)tablespacenamefromdbafreespacegroupbytablespacename)

orderby〃free_space(m)〃；

SELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTES

FREE,(B.BYTES*100)/A.BYTES〃%USED",(C.BYTES*100)/A.BYTESFREE〃FROM

SYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREE

CWHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEAND

A.TABLESPACE_NAME=C.TABLESPACE_NAME;

7、查看数据库库对象

selectowner,object_type,status,count(*)count#fromallobjectsgroupby

owner,object_type,status;

8、查看数据库的版本

select*fromv$version;

SelectversionFROMProduct_component_versionWhere

SUBSTR(PRODUCT,1,6)=Oracle，;

9、查看数据库的创建日期和归档方式

selectcreated,logmode,log_modefromv$database;

10、查看临时数据库文件

selectstatus,enabled,namefromv$tempfile;

常用数据库信息查看命令

(1)oracle中怎样查看总共有哪些用户

select*fromallusers;

(2)查看oracle当前连接数

怎样查看oracle当前的连接数呢？只需要用下面的SQL语句查询一下就可以了。

select*fromv$sessionwhereusernameisnotnullselect

username,count(username)fromv$sessionwhereusernameisnotnullgroupby

username并查看不同用户的连接数

selectcount(*)fromv$session#连接数

Selectcount(*)fromv$sessionwherestatus='ACTIVE'#并发连接数

(3)列出当前数据库建立的会话情况

selectsid,serial#,username,program,machine,statusfromv$session;

实验3表与视图的基础操作

3.1创建基本表

例3-1创建学生、课程、选课三个表，在SQLPLUS的启动界面输入以下代码：

SQL>CreateTableS(SnoVarchar2(10)PrimaryKey,SnameVarchar2(10)Not

Null,SsexChar(2),SageNumber,SdeptVarchar2(40));

SQL>CreateTableCourse(CnoVarchar2(10),CnameVarchar2(50),Ccredit

Number,ConstraintPk_CPrimaryKey(Cno));

SQL>CreateTableSC(SnoVarchar2(10),CnoVarchar2(10),ScoreNumber

Default0Check(ScoreBetween0And100),ConstraintPk_SPrimaryKey

(Sno,Cno))TABLESPACE"Testspace”;一使用Testspace表空间

3.2修改表

i、修改表空间的相关操作

1)增加表空间中的数据文件

AlterTablespaceTestspaceAddDatafile'c:\app\qxz\file_3.dbf'size

100m;

2)删除表空间中的数据文件

AlterTablespaceTestspaceDropDatafile'c:\app\qxz\file_3.dbf';

3)修改表空间文件的数据文件大小

AlterDatabaseDatafile'c:\app\qxz\file_2.dbf'Resize50m;

4)修改表空间数据文件的自动增长属性。

AlterDatabaseDatafile,c:\app\qxz\file_l.dbf1AutoextendOff;--Off不能自

动增长

2、修改表结构的相关操作：

1)插入属性

例3-2在S表插入新属性地址。

SQL>AlterTableSAdd(AddressVarchar(100));

2)修改属性

例3-3对上述性别属性的数据类型进行修改，并且默认值为男”。

SQL>AlterTableSModify(SsexVarchar2(2)Default'男')；

3)删除表属性

例3-4删除上述表中的地址属性。命令为:SQL>AlterTableSDrop(Address);

注意：通常在系统不忙的时候删除不使用的字段，可以先设置字段为unused；

AlterTableSSetUnusedColumnAddress;

系统不忙时再执行删除:AlterTableSDropUnusedColumn;

4)表重命名

例3-5把表SC改名为Learn。命令为：SQL>RenameScToLearn;

5)清空表中的数据

例3-6清空学生表的信息。命令为：SQL>TruncateTableS;

6)给表增加注释

例3-7对表S添加注释为'thisIsATestTable'

SQL>CommentOnTableSIs'ThisIsATestTable,;

7)给列添加注释

例3-8对表S的Sno属性添加'学号’的注释。

SQL>CommentOnColumnS.SnoIs'学号';

3.3删除表

例3-9删除Course表。命令为：SQL>DropTableCourse;

3.5创建和管理视图

1、创建视图

例3T0在S表中创建以学号、姓名、系别的新视图。

SQL>CreateOrReplaceViewV_S(Num,Name,Sdept)AsSelectSno,Sname,

SdeptFromS;

例3-11在SC上定义新视图，当用update修改数据时，必须满足视图score>60的条

件，不满足则不能被改变。

SQL>CreateOrReplaceViewVSCAsSelect*FromSCWhereScore>60WithCheck

Option;

例3T2创建新视图，按照学号分组显示学生的最高、最低分和平均成绩。

SQL>CreateViewV_S_SC(Num,Smin,Smax,Savg)AsSelect

D.Sno,Min(E.Score),Max(E.Score),Avg(E.Score)FromSCE,SDWhereE.Sno=D.Sno

GroupByD.Sno;

2、查询视图

例3-13查询上述建立的视图。命令为:SQDSelect*FromV_S_SC;

3、更新视图

例3-14把所有学号为08开头的学生的相关系别信息改为管理系。

SQL>UpdateVSSetSdept=，Management'WhereNum1ike'08%";

3.6表或视图的导入与导出操作

1、Oracle数据间的导入导出imp/exp

下面是导入导出的实例，导入导出的其它例子或方法请参阅实验13。(1)数据导出

1)将数据库orcl完全导出，用户名system密码orcl,导出到c:\orcl.dmp中。exp

system/orcl@orcl2file=c:\orcl.dmpfull=y

2)将数据库吨jxgl用户与scott用户的表导出。expsystem/orcl@orcl2

file=c:\orcl_jxglscott.dmpowner=(jxgl,scott)

3)将数据库和jxgl用胪的表student,sc导出。expjxgl/jxgl@orcl2

file=c:\orcl_jxgl_studentsc.dmptables=(student,sc)

4)将数据库中jxgl用户的表student中年龄大于等于19的学生记录导出。exp

jxgl/jxgl@orcl2file=c:\orcl_jxgl_student_agegel9.dmptables=(student)

query='〃wheresage>=19\，/

上面是常用的导出，对于压缩导出，只要在上面命令后面加上compress=y就可以了。

(2)数据的导入

1)将c:\orcl.dmp中的獭(据导入orcl数据中。imp

system/orcl@orcl2file=c:\orcl.dmp

上面可能有点问题，因为有的表己经存在，然后它就报错，对该表就不进行导入。在

后面加上ignore=y就可以了。

2)将c:\orcl_jxgl_studentsc.dmp中的表sc导入。impjxgl/jxgl@orcl2file=

c:\orcl_jxgl_studentsc.dmptables=(sc)ignore=y

实验4SQL语言——SELECT查询操作

创建Student、SC^Course三表及添加表记录命令如下：

CreateTableStudent

(SnoCHAR(5)NOTNULL,

SnameVARCHAR(20),

SageSMALLINTCHECK(Sage>=15ANDSage<=45),

SsexCHAR(2)DEFAULT'男'CHECK(Ssex='男'ORSsex='女')，

SdeptCHAR(2),constraintprsnoprimarykey(sno));

CreateTableCourse(CnoCHAR(2)NOTNULL,CnameVARCHAR(20),Cpno

CHAR(2),CcreditSMALLINT,constraintprCnoprimarykey(Cno));

CreateTableSC(SnoCHAR(5)NOTNULL,CnoCHAR(2)NOTNULL,GradeSMALLINT

CHECK((GradeISNULL)OR(GradeBETWEEN0AND100)),PRIMARY

KEY(Sno,Cno),CONSTRAINTCFFOREIGNKEY(Cno)REFERENCESCourse(Cno),CONSTRAINT

S_FFOREIGNKEY(Sno)REFERENCESStudent(Sno));

INSERTINTOStudentVALUES98001','钱横'，18,'男'，'CS，)

INSERTINTOStudentVALUESC98002','王林'，19,'女'，'CS')

INSERTINTOStudentVALUES('98003','李民'，20,'男'，'IS')

INSERTINTOStudentVALUES('98004','赵三'，16,'女'，'MA')

INSERTINTOCourseVALUESCT，'数据库系统‘，'5',4);

INSERTINTOCourseVALUES('2'，'数学分析'，null,2);

INSERTINTOCourseVALUES('3'，'信息系统导论‘，'1',3);

INSERTINTOCourseVALUESC4'，'操作系统原理‘，'6',3);

INSERTINTOCourseVALUES('5'，‘数据结构‘，’7',4);

INSERTINTOCourseVALUESC6'，'数据处理基础'，null,4);

INSERTINTOCourseVALUES('7','C语言‘，’6',3);

INSERTINTOSCVALUESC98001'.'l',87);INSERTINTOSC

VALUES('98001，,，2,,67);INSERTINTOSCVALUES('98001','3',90);INSERTINTOSC

VALUES('98002','2',95);INSERTINTOSCVALUES('98002,,'3',88);

例4-1

SELECTDISTINCTSno

FROMSC

WHEREGrade>=90;

例4-2

SELECTSname,Ssex

FROMStudent

WHERESage>18ANDSdeptNOTIN('IS','MA');

例4-3

SELECT*FROMCourseWHERECnameLIKE导，ESCAPE，#，；

例4-4

SELECTCOUNT(DISTINCTSno)/*加DISTINCT去掉重复值后计数*/

FROMSC;

例4-5

SELECTStudent.SnoFROMStudent,SC

WHERESdept=,CS，ANDStudent.Sno=SC.Sno

GROUPBYStudent.SnoHAVINGCOUNT(*)>=2;

例4-6

SelectStudent.*,SC.*FromStudent,SC;

或SelectStudent.*,SC.*FromStudentCrossJoinSC;

例4-7

Select*FromStudent,SCWHEREStudent.Sno=SC.Sno;

例4-8

SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

FROMStudent,SCWIIEREStudent.Sno=SC.Sno;

或SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

FROMStudentINNERJOINSCONStudent.Sno=SC.Sno;

例4-9

SELECTFIRST.Cno,SECOND.cpnoFKOMCourseFIRST,CourseSECOND

WHEREFIRST.cpno=SEC0ND.Cno;

我们为Course表取两个别名FIRST与SECOND,这样就可以在SELECT子句和WHERE子

句中的属性名前分别用这两个别名加以区分。

例4-10

SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

FROMStudentLeftOuterJOINSCONStudent.Sno=SC.Sno;

例4-11

SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,

Course.Cno,Grade,cname,cpno,ccredit

FROMStudentLeftOuterJOINSCONStudent.Sno=SC.SnoFullOuterjoinCourse

onSC.eno=Course,eno;

例4-12

SELECTStudent.*,Cno,GradeFROMSTUDENTINNERJOINSCONStudent.Sno=SC.Sno

WHERESSEX='男'ANDGRADE>=60

例4-13

SELECT*FROMStudent

WHERESdeptIN(SELECTSdeptFROMStudentWHERESname='钱横')；

或SELECT*FROMStudent

WHERESdept=(SELECTSdeptFROMStudent

WHERESname='钱横')；一当子查询为单列单行值时可以用“二”

或SELECTSL*FROMStudentSI,StudentS2

WHERESl.Sdept=S2.SdeptANDS2.Sname='钱横'；

一般来说，连接查询可以替换大多数的嵌套子查询。

SQL-92支持“多列成员”的属于(IN)条件表达，例：

例4-14

Select*fromStudentT

Where(T.sdept,T.sage,T.ssex)IN(Selectsdept,sage,ssexFromstudentS

WhereS.snoOT.sno);-Oracle支持的

它等价于逐个成员IN的方式表达，如下：

Select*fromStudentTWhereT.sdeptIN

(SelectsdeptFromstudentS

WhereS.snoOT.snoandT.sageIN

(SelectsageFromstudentXWhereS.sno=X.snoandX.snoOT.sno

andT.ssexIN

(SelectssexFromstudentYWhereX.sno=Y.snoandY.snoOT.sno)));

例4-15

SELECTSno,Sname,SdeptFROMStudent--IN嵌套查询方法

WHERESnoIN

(SELECTSnoFROMSC

WHERECnoIN(SELECTCnoFROMCourseWHERECname='数据库系统'))；

或SELECTSno,Sname,SdeptFROMStudent-IN、=嵌套查询方法

WHERESnoIN

(SELECTSnoFROMSC

WHERECno=(SELECTCnoFROMCourseWHERECname='数据库系统'))；

或SELECTStudent.Sno,Sname,Sdept一连接查询方法

FROMStudent,SC,Course

WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname='数据库系统

或SelectSno,Sname,SdeptFromStudent-Exists嵌套查询方法

WhereExists(Select*FromSCWhereSC.Sno=Student.SnoAnd

Exists(Select*FromCourse

WhereSC.Cno=Course.CnoAndCname=，数据库系统'))；

或SelectSno,Sname,SdeptFromStudent-Exists嵌套查询方法

WhereExists(Select*FromcourseWhereCname='数据库系统'and

Exists(Select*FromSCWheresc.sno=student.snoandSC.Cno=

Course.Cno));

例4-16

SELECTSno,SnameFROMStudent

WHERESnoNOTIN(SELECTSnoFROMSCWHERECnoIN('2','4'));

例4-17

SELECTSnameFROMStudent

WHERESage>A11(SELECTSageFROMStudent

WHERESdept='IS')ANDSdept<>'IS'

ORDERBYSname;

本查询实际上也可以用集函数实现：

SELECTSnameFROMStudent

WHERESage>(SELECTMAX(Sage)FROMStudent

WHERESdept=TS')ANDSdeptO'IS"

ORDERBYSname;

例4-18

SELECTDISTINCTCNAMEFROMCOURSEC

WHERE'女'=ALL(SELECTSSEXFROMSC,STUDENT

WHERESC.SNO=STUDENT.SNOANDSC.CNO=C.CNO);

或SELECTDISTINCTCNAMEFROMCOURSEC

WHERENOTEXISTS

(SELECT*FROMSC,STUDENT

WHERESC.SNO=STUDENT.SNOANDSC.CNO=C.CNOANDSTUDENT.SSEX='男')；

例4T9

SELECTSnameFROMStudent

WHERENOTEXISTS

(SELECT*FROMSCWHERESno=Student.SnoANDCno=T');

或SELECTSnameFROMStudent

WHERESnoNOTIN(SELECTSnoFROMSCWHERECno='T);

但如下是错的：

SELECTSnameFROMStudent,SCWHERESC.Sno=Student.SnoANDCnoO'l’;

例4-20

SELECTSnameFROMStudent

WHERENOTEXISTS

(SELECT*FROMCourseWHERENOTEXISTS

(SELECT*FR0MSCWHERESno=SC.SnoANDCno=Course.Cno));

由于没有全称量词，我们将题目的意思转换成等价的存在量词的形式：查询这样的学

生姓名没有一门课程是他不选的。

本题的另一操作方法是：

SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.Sno

GroupbyStudent.Sno,Snamehavingcount(*)>=(SELECTcount(*)FROMCourse);

例4-21

SELECTSnoFROMStudentSX

WHERENOTEXISTS

(SELECT*FROMSCSCY

WHERESCY.Sno=98001'ANDNOTEXISTS

(SELECT*FROMSCSCZWHERESCZ.Sno=SX.SnoAND

SCZ.Cno=SCY.Cno));

例4-22

SELECTSnoFROMSCWHERECno=T'

UNION

SELECTSnoFROMSCWHERECno='2';

SELECTSnoFROMSCWHERECno=T'

INTERSECT

SELECTSnoFROMSCWHERECno='2';"查询既选课程1又选课程2的学生学号集

例4-23

SELECT*FROMStudentWHERESdept=CS，

INTERSECT

SELECT*FROMStudentWHERESage<=19;

本查询等价于“鳖询计算机科学系中年龄不大于19岁的学生J,为此变通法为：

SELECT*FROMStudentWHERESdept=CS,ANDSage<=19;

例4-24

SELECTSnoFROMSCWHERECno='2'

MINUS

SELECTSnoFROMSCWHERECno=T;

本例实际上是查询选修了课程2但没有选修课程1的学生。为此变通法为：

SELECTSnoFROMSC

WHERECno=’2'ANDSnoNOTIN(SELECTSnoFROMSCWHERECno=f;

例4-25

Selectstuno,sname,avgr

FromStudent,(Selectsnostuno,avg(grade)avgrFromSCGroupBysno)SG

WhereStudent.sno=SG.stu_noAndavgr>85;

SQL-92允许在From中使用看询表达式，并必须为查询表达式取名。它等价于如下未

使用查询表达式的形式：

SelectStudent.Sno,Sname,AVG(Grade)

FromStudent,SCWhereStudent.Sno=SC.Sno

GroupByStudent.Sno,SnameHAVINGAVG(Grade)>85;

例4-26

SELECTSNAME,CNAME,GRADE

FROM(SELECTSNAME,CNAME,GRADEFROMSTUDENT,SC,COURSE

WHERESSEX='女'ANDSTUDENT.SNO=SC.SNOANDSC.CNO=COURSE.CNO)

TEMPWHEREGRADE>90;一特意用查询表达式实现，完全可用其它方式实现

但如下使用查询表达式的查询，则不易改写为其它形式。

例4-27

Selectavgr,COUNT(*)

From(Selectsno,avg(grade)avgrFromSCGroupBysno)SG

GroupByavgr;

例4-28

GRANTCREATEVIEWTOJXGL—赋予用户JXGLCREATEVIEW的权力

CREATEVIEWIS_Student

ASSELECTSno,Sname,Sage,Ssex

FROMStudentWHERESdept='IS'WITHCHECKOPTION

GO

SELECT*FROMIS_StudentWHERESage>=18ANDSsex=女'；

实验5SQL语言一一数据更新操作

5.1INSERT命令

例5-1

InsertIntoStudentValues('98011'张静'，27,'女CS');Commit;

Insert语句后可跟returning子句来获取插入记录的某字段值。程序代码如下：

Setserveroutputon

Declare

bndlstudent.sno%TYPE;

bnd2student.sname%TYPE;

Begin

InsertIntoStudent(sno,sname,sage,ssex,sdept)Values('98011'张静

',27,‘女'，'CS')RETURNINGsno,Student,snameINTObndl,bnd2;

dbmsoutput.put_line(bndl||’||bnd2);

End;

例5-2

InsertIntoStudent(Sno,Sname,Sage)Values98012,,'李四16);Commit;

例5-3

CreateSequencettincrementby1minvalue101maxvaluecycle;

Createtabletestable(idint,rqdate);

InsertintotestableValues(tt.nextval,sysdate);

若要删除序列命令为：DropSequencett;若删除测试表testable命令为：Droptable

testable;

例5-4

InsertIntoSC

Selectsno,eno,nullFromStudent,CourseWhereSdept='CS'andeno='5’;

Commit;

例5-5

InsertIntoStudent

Selectcast(cast(snoasinteger)+1as

char(5)),CONCAT(sname,*2*),sage,ssex,sdeptFromStudentWhereSname=，赵三'；

Commit;

SELECT*FROMStudent;一查看结果(执行结果略)

5.2UPDATE命令

例5-6

UPDATEStudentSETSage=23WHERESno='98003,;

例5-7

UPDATEStudentSETStudent,Sage=Student.Sage+1

wheresnoin

(SELECTsnoFROM(SELECT*FROMStudentORDERBYsno)

WHEREROWNUM<=3);

例5-8

UpdateSC

SetGrade=(SelectAVG(Grade)FromSCWhereCno=，3?)WhereSno='98001'and

Cno=,3';

Update语句后也可跟上Returning子句来获取更新后记录的字段值。程序代码如下：

Setserveroutputon

Declare

bndlsc.sno%TYPE;bnd2sc.grade%TYPE;

Begin

UpdateSCSetGrade13(SelectAVG(Grade)FromSCWhereCno=,3')WhereSno

二'98001'andCn。='3'RETURNINGsc.sno,sc.gradeINTObndl,bnd2;

dbmsoutput.putline(bndl||J|tochar(bnd2));

End;

例5-9

UPDATESCSETGRADE=0

WHERECN0='2'AND

‘王林'=(SELECTSNAMEFROMSTUDENTWHERESTUDENT.SNO=SC.SNO);

例5-10

Updatestudent

Setsage=(selectavg(sage)fromstudentwheresdept='CS'),

(ssex,sdept)=(selectssex,sdeptfromstudentwheresno='98003')

Wheresno='98002'

5.3DELETE命令

先备份选修表SC到TSC中,命令为：

CreateTableTSCasSelect*FromSC一备份到表TSC中

例5-11

SELECT*FROMSC一删除前

DELETEFROMSC一删除中

WHERE'CS'=(SELECTSdeptFROMStudent

WHEREStudent.Sno=SC.Sno)

SELECT*FROMSC一删除后

DELETE语句后也可跟上Returning子句来获取刚删除记录相应的字段值。程序代码如

下：

Setserveroutputon

Declare

bndlsc.sno%TYPE;bnd2sc.grade%TYPE;

Begin

DELETEFROMSC—删除中

WHERESNO=，98001'ANDcno='2'RETURNINGsc.sno,sc.gradeINTObndl,bnd2;

dbmsoutput.putline(bndl|,|；tochar(bnd2));

End;

例5-12

DELETEFROMjxgl.SC@remoteWHERESNO="980011;

例5-13DELETEFR0MSC;

如TRUNCATETABLESC,清空SC表。

从表TSC恢复数据到表SC,命令为：

INSERTINTOSCSELECT*FROMTSC―这是一种方便、简易地恢复数据的方法。

实验6嵌入式SQL应用

Pro*C程序举例

例如：“example.pc”程序能完成输入雇员号、雇员名、职务名和薪金等信息，并插

入到雇员表emp(Oracle缺省安装后SCOTT用户连接能存取到该表)中的功能。

#defineUSERNAME"SCOTT"〃连接Oracle的用户名

#definePASSWORD〃scott〃〃连接Oracle的用户口令

#defineSERVER'localhost:1521/orcl"〃连接Oracle的用户口令

tfinclude<stdio.h>

ttinclude<string.h>

#include<stdlib.h>

#include<sqlda.h>

^include<sqlcpr.h>

EXECSQLINCLUDEsqlca;

EXECSQLBEGINDECLARESECTION;

char*username=USERNAME;char*password=PASSWORD;

char*server=SERVER;varcharsqlstmt[80];

intempnum;varcharemp_name[15];

varcharjob[50];floatsalary;

EXECSQLENDDECLARESECTION;

voidsqlerror();

main()

{EXECSQLWHENEVERSQLERRORDOsqlerror();〃错误处理

EXECSQLCONNECT:usernameIDENTIFIEDBY:passwordUSING:server;〃连接

oracle

sqlstmt.len=sprintf(sqlstmt.arr,“INSERTINTOEMP(EMPNO,ENAME,JOB,SAL)

VALUES(:VI,:V2,:V3,:V4)");

EXECSQLPREPARESFROM:sqlstmt;//SQL命令区S动态准备

for(;;)

{printf(^Xnenteremployeenumber:");scanf(〃％d〃，&empnum);

if(empnum二二0)break;

printf(z/\nenteremployeename:");scanf(〃%s〃，emp_name.arr);

emp_name.1en=str1en(emp_name.arr);

printf(，z\nenteremployeejob:");scanf(〃％s〃，job.arr);

job.len=strlen(job.arr);

printf(^Xnenteremployeesalary:，z);scanf&salary);

printf(，，%d--%s--%s--%f〃，empnum,emp_name.arr,job.arr,salary);

//以下通过命令区S参数化动态执行SQL命令

EXECSQLEXECUTESUSING:empnum,:empname,:job,:salary;

}

EXECSQLCOMMITWORKRELEASE;

exit(0);

)

voidsqlerror(){〃错误处理程序

EXECSQLWHENEVERSQLERRORCONTINUE;

printfCAnOracleerrordetected:\n，z);

printf(，z\n%.70s\n〃，sqlca.sqlerrm.sqlerrmc);

EXECSQLROLLBACKWORKRELEASE;//出错回滚,取消操作。

exit(1);

)