Oracle数据库技术与实验指导

Oracle数据库技术与实验指导2011.08

第0章有用Oracle数据库技术

Oracle 10g/11g数据库都分为标准版（Standard Edition）、标准版1（S tandard Edition One）以及企业版（Enterprise Edition）。可从如下网址下载、学习或试用Oracle：

SQL*Plus Instant Client（SQL*Plus即时客户端）

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

Oracle Database 11g 第2 版(11.2.0.1.0)的下载地址：

适用于Microsoft Windows（32 位）的Oracle Database 11g 第 2 版(11.2.0.1.0) 的下载地址：

实验2 数据库基础操作

手工建库

手工建库须要通过几个步骤，每一个步骤都专门关键。它包括：

1、创建有关名目（数据文件和跟踪文件）（假设要创建KCGL 数据库，Oracle已安装于“c:\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\orcl\pfile那个名目下的参数文件“init.ora.*”（*为数字扩展名）到C:\app\qxz\product\11.2.0\ dbhome_1\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\ora data\KCGL\control02.ctl")

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

4、创建服务：Oradim -new -sid KCGL

5、创建口令文件

Orapwd file=C:\app\qxz\product\11.2.0\dbhome_1\database\pwd KCGL.ora

Password=12345

6、启动服务器：Sqlplus /nolog

Conn / as sysdba

Startup nomount

7、执行建库脚本：

CREATE DATABASE KCGL

datafile 'c:\app\qxz\oradata\KCGL\system01.dbf' size 300m

autoextend on next 10m extent management local

Sysaux datafile 'c:\app\qxz\oradata\KCGL\sysaux01.dbf' size 120m

undo tablespace undotbs1

datafile 'c:\app\qxz\oradata\KCGL\undotbs01.dbf' size 100m

default temporary tablespace temptbs1

tempfile 'c:\app\qxz\oradata\KCGL\temp01.dbf' size 50m

logfile group 1 ('c:\app\qxz\oradata\KCGL\redo01.log') size 50m,

group 2 ('c:\app\qxz\oradata\KCGL\redo02.log') size 50m,

group 3 ('c:\app\qxz\oradata\KCGL\redo03.log') size 50m;

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

Start C:\CREATEKCGL.sql

不管显现哪种错误，都要删除C:\app\qxz\oradata\KCGL名目下创建的所有文件，改正错误后，重新启动实例，再执行建库脚本。

8、创建数据字典和包

Start C:\app\qxz\product\11.2.0\dbhome_1\RDBMS\ADMIN\catalo g

Start C:\app\qxz\product\11.2.0\dbhome_1\RDBMS\ADMIN\catpro c

9、执行pupbld.sql脚本文件

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

Start C:\app\qxz\product\11.2.0\dbhome_1\sqlplus\admin\pupbld

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

Start C:\app\qxz\product\11.2.0\dbhome_1\RDBMS\ADMIN\scott.s ql

这时需要修改密码：Conn / as sysdba

Alter user scott identified by tiger;

再连接scott：Conn scott/tiger

11、select * from dept;

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

2.2 查看数据库

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

select tablespace_name,min_extents,max_extents,pct_increase,status fro m dba_tablespaces;

select tablespace_name,initial_extent,next_extent,contents,logging,

extent_management,allocation_type from dba_tablespaces order by ta blespace_name;

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size fro m dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tables pace_name

group by t.tablespace_name;

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

column db_block_size new_value blksz noprint

select value db_block_size from v$parameter where name='db_block _size';

column tablespace_name format a16;

column file_name format a60;

set linesize 160; -- 为sqlplus 命令

select file_name,round(bytes/(1024*1024),0) total_space,autoextensibl e,increment_by*&blksz/(1024*1024) as incement,maxbytes/(1024*1024) as maxsize from dba_data_files order by tablespace_name; -- blksz一样为8192

select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space

from dba_data_files order by tablespace_name;

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

select a.owner ||' . '|| a.segment_name roll_name , a.tablespace_name tablespace , to_char(a.initial_extent) || '/' || to_char(a.next_extent) in_exte nts , to_char(a.min_extents) || '/' || to_char(a.max_extents) m_extents , a.s tatus status , b.bytes bytes , b.extents extents , d.shrinks shrinks , d.wra ps wraps , d.optsize opt from dba_rollback_segs a , dba_segments b , v $rollname c , v$rollstat d where a.segment_name = b.segment_name and a.segment_name = https://www.360docs.net/doc/b615637891.html, (+) and https://www.360docs.net/doc/b615637891.html,n = https://www.360docs.net/doc/b615637891.html,n (+) order by a.segme nt_name;

select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExte nt From dba_rollback_segs r, v$rollstat v Where r.segment_id = https://www.360docs.net/doc/b615637891.html,n(+) order by segment_name ;

4、查看操纵文件

select name from v$controlfile;

5、查看日志文件

select member from v$logfile;

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

select * from (select sum(bytes)/(1024*1024) as "free_space(m)",tabl espace_name from dba_free_space group by tablespace_name) order by " free_space(m)";

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USE D, C.BYTES FREE,(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*10

0)/A.BYTES "% FREE" FROM SYS.SM$TS_A V AIL A,SYS.SM$TS_USE

D B,SYS.SM$TS_FRE

E C WHERE A.TABLESPACE_NAME=B.TABLES PACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

7、查看数据库库对象

select owner, object_type, status, count(*) count# from all_obje cts group by owner,object_type,status;

8、查看数据库的版本

select * from v$version;

Select version FROM Product_component_version Where SUBSTR(P RODUCT,1,6)='Oracle';

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

select created,log_mode,log_mode from v$database;

10、查看临时数据库文件

select status, enabled, name from v$tempfile;

常用数据库信息查看命令

（1）oracle中如何样查看总共有哪些用户

select * from all_users;

（2）查看oracle当前连接数

如何样查看oracle当前的连接数呢？只需要用下面的SQL语句查询一下就能够了。

select * from v$session where username is not null select usernam e,count(username) from v$session where username is not null group by username #查看不同用户的连接数

select count(*) from v$session #连接数

Select count(*) from v$session where status='ACTIVE'#并发连接数

（3）列出当前数据库建立的会话情形

select sid,serial#,username,program,machine,status from v$session;

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

3.1创建差不多表

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

SQL>Create Table S(Sno Varchar2(10) Primary Key, Sname Va rchar2(10) Not Null,Ssex Char(2),Sage Number,Sdept Varchar2(40));

SQL>Create Table Course(Cno Varchar2(10),Cname Varchar2(5 0),Ccredit Number,Constraint Pk_C Primary Key (Cno));

SQL>Create Table SC(Sno Varchar2(10),Cno Varchar2(10),Sco re Number Default 0 Check (Score Between 0 And 100),Constraint Pk_ S Primary Key (Sno,Cno)) TABLESPACE "Testspace"; --使用Testsp ace表空间

3.2修改表

1、修改表空间的有关操作

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

Alter Tablespace Testspace Add Datafile'c:\app\qxz\file_3.dbf' s ize 100m;

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

Alter Tablespace Testspace Drop Datafile'c:\app\qxz\file_3.dbf';

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

Alter Database Datafile'c:\app\qxz\file_2.dbf'Resize50m;

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

Alter Database Datafile 'c:\app\qxz\file_1.dbf' Autoextend Off; --Off 不能自动增长

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

1）插入属性

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

SQL>Alter Table S Add(Address Varchar(100));

2）修改属性

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

SQL>Alter Table S Modify( Ssex Varchar2(2) Default '男');

3）删除表属性

例3-4 删除上述表中的地址属性。命令为：SQL>Alter Table S Drop(Address);

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

Alter Table S Set Unused Column Address;

系统不忙时再执行删除：Alter Table S Drop Unused Column;

4）表重命名

例3-5 把表SC改名为Learn。命令为：SQL>Rename Sc To Le arn;

5）清空表中的数据

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

6）给表增加注释

例3-7 对表S添加注释为'this Is A Test Table'

SQL>Comment On Table S Is 'This Is A Test Table';

7）给列添加注释

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

SQL>Comment On Column S.Sno Is '学号';

3.3删除表

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

3.5 创建和治理视图

1、创建视图

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

SQL>Create Or Replace View V_S(Num,Name,Sdept) As Select Sn o, Sname, Sdept From S;

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

SQL>Create Or Replace View V_SC As Select * From SC Wh ere Score>60 With Check Option;

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

SQL>Create View V_S_SC (Num,Smin,Smax,Savg) As Select D. Sno,Min(E.Score),Max(E.Score),Avg(E.Score) From SC E,S D Where E.S no=D.Sno Group By D.Sno;

2、查询视图

例3-13 查询上述建立的视图。命令为：SQL>Select * From V_ S_SC;

3、更新视图

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

SQL>Update V_S Set Sdept='Management' Where Num like '0 8%';

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

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

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

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

exp system/orcl@orcl2 file=c:\orcl.dmp full=y

2）将数据库中jxgl用户与scott用户的表导出。

exp system/orcl@orcl2 file=c:\orcl_jxglscott.dmp owner=(jxgl,scott) 3）将数据库中jxgl用户的表student,sc导出。

exp jxgl/jxgl@orcl2 file=c:\orcl_jxgl_studentsc.dmp tables=(student,sc) 4）将数据库中jxgl用户的表student中年龄大于等于19的学生记录导出。

exp jxgl/jxgl@orcl2 file=c:\orcl_jxgl_student_agege19.dmp tables=(student) query=\" where sage>=19\"

上面是常用的导出，关于压缩导出，只要在上面命令后面加上comp ress=y就能够了。

（2）数据的导入

1）将c:\orcl.dmp中的数据导入orcl数据库中。

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

上面可能有点咨询题，因为有的表差不多存在，然后它就报错，对该表就不进行导入。在后面加上ignore=y 就能够了。

2）将c:\orcl_jxgl_studentsc.dmp中的表sc 导入。

imp jxgl/jxgl@orcl2 file= c:\orcl_jxgl_studentsc.dmp tables=(sc) ignore= y

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

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

Create Table Student

( Sno CHAR(5) NOT NULL,

Sname V ARCHAR(20),

Sage SMALLINT CHECK(Sage>=15 AND Sage<=45),

Ssex CHAR(2) DEFAULT '男' CHECK (Ssex='男' OR Ssex='女'),

Sdept CHAR(2),constraint pr_sno primary key(sno));

Create Table Course( Cno CHAR(2) NOT NULL,Cname V ARCHAR (20),Cpno CHAR(2),Ccredit SMALLINT,constraint pr_Cno primary key(C no));

Create Table SC( Sno CHAR(5) NOT NULL,Cno CHAR(2) NOT N ULL,Grade SMALLINT CHECK ((Grade IS NULL) OR (Grade BETWE EN 0 AND 100)),PRIMARY KEY(Sno,Cno),CONSTRAINT C_F FOREI GN KEY(Cno) REFERENCES Course(Cno), CONSTRAINT S_F FOREI GN KEY(Sno) REFERENCES Student(Sno));

INSERT INTO Student V ALUES('98001','钞票横',18,'男','CS');

INSERT INTO Student V ALUES('98002','王林',19,'女','CS');

INSERT INTO Student V ALUES('98003','李民',20,'男','IS');

INSERT INTO Student V ALUES('98004','赵三',16,'女','MA');

INSERT INTO Course V ALUES('1','数据库系统', '5',4);

INSERT INTO Course V ALUES('2','数学分析',null ,2);

INSERT INTO Course V ALUES('3','信息系统导论','1',3);

INSERT INTO Course V ALUES('4','操作系统原理','6',3);

INSERT INTO Course V ALUES('5','数据结构','7',4);

INSERT INTO Course V ALUES('6','数据处理基础',null,4);

INSERT INTO Course V ALUES('7','C语言','6',3);

INSERT INTO SC V ALUES('98001','1',87);INSERT INTO SC V ALU ES('98001','2',67);INSERT INTO SC VALUES('98001','3',90); INSERT I NTO SC V ALUES('98002','2',95);INSERT INTO SC VALUES('98002','3',8 8);

例4-1

SELECT DISTINCT Sno

FROM SC

WHERE Grade>=90;

例4-2

SELECT Sname,Ssex

FROM Student

WHERE Sage>18 AND Sdept NOT IN ('IS', 'MA');

例4-3

SELECT * FROM Course WHERE Cname LIKE ’MIS#_%导_’ESCAPE ’#’;

例4-4

SELECT COUNT(DISTINCT Sno) /* 加DISTINCT 去掉重复值后计数*/

FROM SC;

例4-5

SELECT Student.Sno FROM Student,SC

WHERE Sdept='CS' AND Student.Sno=SC.Sno

GROUP BY Student.Sno HA VING COUNT(*)>=2;

例4-6

Select Student.*,SC.* From Student,SC;

或Select Student.*,SC.* From Student Cross Join SC;

例4-7

Select * From Student,SC WHERE Student.Sno=SC.Sno;

例4-8

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade

FROM Student, SC WHERE Student.Sno=SC.Sno;

或SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade

FROM Student INNER JOIN SC ON Student.Sno=SC.Sno;

例4-9

我们为Course表取两个别名FIRST与SECOND，如此就能够在SEL ECT子句和WHERE子句中的属性名前分别用这两个别名加以区分。

例4-10

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade

FROM Student Left Outer JOIN SC ON Student.Sno=SC.Sno;

例4-11

例4-12

SELECT Student.*,Cno,Grade FROM STUDENT INNER JOIN SC ON Student.Sno=SC.Sno

WHERE SSEX=’男’AND GRADE >=60

例4-13

SELECT * FROM Student

WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname ='钞票横');

或SELECT * FROM Student

WHERE Sdept=(SELECT Sdept FROM Student

WHERE Sname='钞票横'); --当子查询为单列单行值时能够用“=”

或SELECT S1.* FROM Student S1,Student S2

WHERE S1.Sdept=S2.Sdept AND S2.Sname='钞票横';

一样来讲，连接查询能够替换大多数的嵌套子查询。

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

例4-14

Select * from Student T

Where (T.sdept,T.sage,T.ssex) IN (Select sdept,sage,ssex From studen t S

Where S.sno<>T.sno); --Oracle支持的

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

Select * from Student T Where T.sdept IN

( Select sdept From student S

Where S.sno<>T.sno and T.sage IN

(Select sage From student X Where S.sno=X.sno and X.sno<>T.sno and T.ssex IN

(Select ssex From student Y Where X.sno=Y.sno and Y.sno<>T.sn o)));

例4-15

SELECT Sno,Sname,Sdept FROM Student -- IN嵌套查询方法

WHERE Sno IN

( SELECT Sno FROM SC

WHERE Cno IN (SELECT Cno FROM Course WHERE Cname='数据库系统'));

或SELECT Sno,Sname,Sdept FROM Student -- IN、= 嵌套查询方法

WHERE Sno IN

( SELECT Sno FROM SC

WHERE Cno=(SELECT Cno FROM Course WHERE Cname='数据库系统'));

或SELECT Student.Sno,Sname,Sdept --连接查询方法

FROM Student,SC,Course

或Select Sno,Sname,Sdept From Student -- Exists嵌套查询方法Where Exists( Select * From SC Where SC.Sno=Student.Sno And Exists( Select * From Course

或Select Sno,Sname,Sdept From Student -- Exists嵌套查询方法Where Exists( Select * From course Where Cname='数据库系统' an d

例4-16

SELECT Sno,Sname FROM Student

WHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno IN ('2','4'));

例4-17

SELECT Sname FROM Student

WHERE Sage>All(SELECT Sage FROM Student

WHERE Sdept='IS') AND Sdept <> 'IS'

ORDER BY Sname;

本查询实际上也能够用集函数实现：

SELECT Sname FROM Student

WHERE Sage>(SELECT MAX(Sage) FROM Student

WHERE Sdept='IS') AND Sdept<>'IS'

ORDER BY Sname;

例4-18

SELECT DISTINCT CNAME FROM COURSE C

WHERE '女'=ALL( SELECT SSEX FROM SC,STUDENT

或SELECT DISTINCT CNAME FROM COURSE C

WHERE NOT EXISTS

( SELECT * FROM SC,STUDENT

例4-19

SELECT Sname FROM Student

WHERE NOT EXISTS

( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');

或SELECT Sname FROM Student

WHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno='1 ');

但如下是错的：

SELECT Sname FROM Student,SC WHERE SC.Sno=Student.Sno A ND Cno<>'1';

例4-20

SELECT Sname FROM Student

WHERE NOT EXISTS

( SELECT * FROM Course WHERE NOT EXISTS

由于没有全称量词，我们将题目的意思转换成等价的存在量词的形式：查询如此的学生姓名没有一门课程是他不选的。

本题的另一操作方法是：

SELECT Sname FROM Student,SC WHERE Student.Sno=SC.Sno

Group by Student.Sno,Sname having count(*)>=(SELECT count(*) F ROM Course);

例4-21

SELECT Sno FROM Student SX

WHERE NOT EXISTS

( SELECT * FROM SC SCY

WHERE SCY.Sno='98001' AND NOT EXISTS

例4-22

SELECT Sno FROM SC WHERE Cno='1'

UNION

SELECT Sno FROM SC WHERE Cno='2';

SELECT Sno FROM SC WHERE Cno='1'

INTERSECT

SELECT Sno FROM SC WHERE Cno='2'; -- 查询既选课程1又选课程2的学生学号集

例4-23

SELECT * FROM Student WHERE Sdept='CS'

INTERSECT

SELECT * FROM Student WHERE Sage<=19;

本查询等价于“查询运算机科学系中年龄不大于19岁的学生。”，为此变通法为：

SELECT * FROM Student WHERE Sdept='CS' AND Sage<=19;

例4-24

SELECT Sno FROM SC WHERE Cno='2'

MINUS

SELECT Sno FROM SC WHERE Cno='1';

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

SELECT Sno FROM SC

WHERE Cno='2' AND Sno NOT IN (SELECT Sno FROM SC WH ERE Cno='1');

例4-25

Select stu_no,sname,avgr

From Student,( Select sno stu_no,avg(grade) avgr From SC Group By sno) SG

Where Student.sno=SG.stu_no And avgr>85;

SQL-92承诺在From中使用查询表达式，并必须为查询表达式取名。它等价于如下未使用查询表达式的形式：

Select Student.Sno,Sname,AVG(Grade)

From Student,SC Where Student.Sno = SC.Sno

Group By Student.Sno,Sname HA VING A VG(Grade)>85;

例4-26

SELECT SNAME,CNAME,GRADE

FROM (SELECT SNAME,CNAME,GRADE FROM STUDENT,SC,C OURSE

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

例4-27

Select avgr,COUNT(*)

From (Select sno,avg(grade) avgr From SC Group By sno) SG

Group By avgr;

例4-28

GRANT CREATE VIEW TO JXGL -- 给予用户JXGL CREATE V IEW 的权力

CREATE VIEW IS_Student

AS SELECT Sno,Sname,Sage,Ssex

FROM Student WHERE Sdept='IS' WITH CHECK OPTION

GO

SELECT * FROM IS_Student WHERE Sage>=18 AND Ssex='女';

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

5.1 INSERT命令

例5-1

Insert Into Student Values('98011','张静',27,'女','CS'); Commit;

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

Set serveroutput on

Declare

bnd1 student.sno%TYPE;

bnd2 student.sname%TYPE;

Begin

Insert Into Student(sno,sname,sage, ssex, sdept) Values('98011','张静',27,'女','CS') RETURNING sno,Student.sname INTO bnd1,bnd2;

dbms_output.put_line(bnd1||' '||bnd2);

End;

例5-2

Insert Into Student(Sno,Sname, Sage) Values('98012', '李四',16); Co mmit;

例5-3

Create Sequence tt increment by 1 minvalue 101 maxvalue 9999999 cycle;

Create table testable(id int,rq date);

Insert into testable Values(tt.nextval,sysdate);

若要删除序列命令为：Drop Sequence tt; 若删除测试表testable命令为：Drop table testable;

例5-4

Insert Into SC

Select sno,cno,null From Student,Course Where Sdept='CS' and cno ='5';

Commit;

例5-5

Insert Into Student

Select cast(cast(sno as integer)+1 as char(5)),CONCAT(sname,'2'),sag e,ssex,sdept From Student Where Sname='赵三';

Commit;

SELECT * FROM Student; --查看结果（执行结果略）

5.2 UPDATE命令

例5-6

UPDATE Student SET Sage=23 WHERE Sno='98003';

例5-7

UPDATE Student SET Student.Sage= Student.Sage+1

where sno in

(SELECT sno FROM (SELECT * FROM Student ORDER BY sno) WHERE ROWNUM <=3);

例5-8

Update SC

Set Grade=(Select A VG(Grade) From SC Where Cno='3') Where Sn o ='98001' and Cno='3';

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

Set serveroutput on

Declare

bnd1 sc.sno%TYPE;bnd2 sc.grade%TYPE;

Begin

Update SC Set Grade=(Select A VG(Grade) From SC Where Cno ='3') Where Sno ='98001' and Cno='3' RETURNING sc.sno,sc.grade INT O bnd1, bnd2;

dbms_output.put_line(bnd1||' '||to_char(bnd2));

End;

例5-9

UPDATE SC SET GRADE＝0

WHERE CNO＝'2' AND

'王林'＝(SELECT SNAME FROM STUDENT WHERE STUDENT.S NO=SC.SNO);

例5-10

Update student

Set sage=(select avg(sage) from student where sdept='CS'),

(ssex,sdept)=(select ssex,sdept from student where sno='98003')

Where sno='98002'

5.3 DELETE命令

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

Create Table TSC as Select * From SC --备份到表TSC中

例5-11

SELECT * FROM SC --删除前

DELETE FROM SC --删除中

WHERE 'CS'=(SELECT Sdept FROM Student

WHERE Student.Sno=SC.Sno)

SELECT * FROM SC --删除后

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

Set serveroutput on

Declare

bnd1 sc.sno%TYPE; bnd2 sc.grade%TYPE;