实验指导书——大型数据库应用

更新时间：2024-05-07 09:24:01 阅读量：综合文库文档下载

说明：文章内容仅供预览，部分内容可能不全。下载后的文档，内容与下面显示的完全一致。下载之前请确认下面内容是否您想要的，是否完整无缺。

大型数据库应用

实验指导

准备工作：

在Oracle程序组中启动Net Manager，如下：

选择左边树形目录中的数据库实例名，将右边窗口中的主机名改为本地机器名，保存后重新启动监听服务。

实验一数据文件和表空间的管理

【开发语言及实现平台或实验环境】

Oracle10g

【实验目的】

（1）熟悉ORACLE的环境，学习使用SQL*Plus与ORACLE进行交互；（2）掌握连接数据库以及断开连接的方法；（3）掌握数据文件和控制文件的管理基本命令（4）掌握表空间管理的基本命令

【实验原理】

1．SQL*Plus命令

SQL*Plus有许多命令，下表只是列举了一部分常用的供读者参考。 SQL*Plus命令 APPEND text CHANGE /old/new/ CHANGE /text/ CLEAR BUFFER DEL INPUT INPUT text LIST LIST n LIST m n RUN SAVE filename GET filename START filename ED filename EXIT RUNFORM filename SPOOL filename SPO[OL] OFF|OUT DESCRIBE tablename 缩写 A text C/old/new/ C/text/ CL BUFF I I text L L n L m n R @ filename DESC tablename 意义把字符串增加到当前行的末尾把当前行的旧字符串替换成新字符串把当前行中字符串删除从SQL缓冲区中删除所有行删除当前行插入许多行插入一个包含text字符串的行显示SQL缓冲区的所有行显示SQL缓冲区中的一行到n行 SQL缓冲区中的从第m行显示到第n行显示并运行在缓冲区中的当前SQL命令把SQL缓冲区中的内容保存到以filename为名字的文件中，默认路径为orawin\\bin 把以filename为名字的文件内容调入SQL缓冲区中运行以前保存的命令文件用默认的编辑器编辑保存的文件内容退出SQL*Plus 从SQL*Plus中运行一个Oracle Forms应用程序写所有的后面的命令或者输出到一个已经命名的文件中。假脱机输入输出文件的后缀为.LIS OFF 关闭假脱机输入输出文件；OUT改变假脱机输入输出，送文件到打印机上显示任何数据库表的数据结构 HELP HOST command 击活Oracle内部的帮助部件在SQL*Plus中击活一个操作系统命令 CONNECT userid/password CONN 在当前的登录下，击活其它的Oracle用户 userid/password PROMPT text 当运行一个命令文件时，显示文本 2.数据文件的管理（1）创建数据文件

数据文件依附于表空间而存在，创建数据文件就是向表空间添加文件

在创建数据文件时应该根据文件数据量的大小确定文件的大小以及文件的增长方式。语法：

ALTER TABLESPACE…ADD DATAFILE ALTER TABLESPACE…ADD TEMPFILE

例：向ORCL数据库的USERS表空间中添加一个大小为10 MB的数据文件。 ALTER TABLESPACE USERS ADD DATAFILE

'C:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\USERS02.DBF' SIZE 10M;

（2）修改数据文件大小

创建后修改数据文件为自动增长：

AUTOEXTEND ON NEXT ?MAXSIZE?|UNLIMITED 手工改变数据文件的大小

ALTER DATABASE DATAFILE?RESIZE?

例：为ORCL数据库的USERS表空间添加一个自动增长的数据文件。 ALTER TABLESPACE USERS ADD DATAFILE

'C:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\USERS03.DBF' SIZE 10M AUTOEXTEND ON NEXT 512K MAXSIZE 50M;

例：修改ORCL数据库USERS表空间的数据文件USERS02.DBF为自动增长方式。 ALTER DATABASE DATAFILE

'C:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\USERS02.DBF ' AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED;

例：取消ORCL数据库USERS表空间的数据文件USERS02.DBF的自动增长方式。 ALTER DATABASE DATAFILE

'C:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\USERS02.DBF ' AUTOEXTEND OFF; 例：将ORCL数据库USERS表空间的数据文件USERS02.DBF大小设置为8 MB 。 ALTER DATABASE DATAFILE

'C:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\USERS02.DBF' RESIZE 8M;

（3）删除数据文件

删除某个表空间中的某个空数据文件

ALTER TABLESPACE?DROP DATAFILE

例：删除USERS表空间中的数据文件USERS003.DBF。 ALTER TABLESPACE USERS DROP DATAFILE

'C:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\USERS03.DBF';

3. 表空间的管理（1）创建表空间

创建表空间时，应该确定表空间的名称、类型、对应的数据文件的名称和位置以及区的分配方式、段的管理方式:

表空间名称以字母开头，可以包含字母、数字以及一些特殊字符等；表空间的类型包括普通表空间、临时表空间和撤销表空间；

表空间中区的分配方式包括两种方式：自动扩展（AUTOALLOCATE）和定制（UNIFORM）；段的管理包括两种方式：自动管理（AUTO）和手动管理（MANUAL）。创建永久表空间：

创建永久表空间使用CREATE TABLESPACE语句来实现，该语句包含以下几个子句： DATAFILE：设定表空间对应的数据文件。

EXTENT MANAGEMENT：指定表空间的管理方式，取值为LOCAL（默认）或DICTIONARY。 AUTOALLOCATE（默认）或UNIFORM：设定区的分配方式。

SEGMENT SPACE MANAGEMENT：设定段的管理方式，其取值为MANUAL或AUTO（默认）。例：为ORCL数据库创建一个永久性的表空间，区自动扩展，段采用自动管理方式。 CREATE TABLESPACE ORCLTBS1 DATAFILE 'D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\ORCLTBS1_1.DBF' SIZE 50M;

例：为ORCL数据库创建一个永久性的表空间，区定制分配，段采用自动管理方式。 CREATE TABLESPACE ORCLTBS2 DATAFILE

'D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\ORCLTBS2_1.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K；

例：为ORCL数据库创建一个永久性的表空间，区自动扩展，段采用手动管理方式。 CREATE TABLESPACE ORCLTBS3 DATAFILE

'D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\ORCLTBS3_1.DBF' SIZE 50M SEGMENT SPACE MANAGEMENT MANUAL;

例：为ORCL数据库创建一个永久性的表空间，区定制分配，段采用手动管理方式。 CREATE TABLESPACE ORCLTBS4 DATAFILE

'D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\ORCLTBS4_1.DBF' SIZE 50M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K SEGMENT SPACE MANAGEMENT MANUAL;

（2）修改表空间 I）扩展表空间：

为表空间添加数据文件：

通过ALTER TABLESPACE?ADD DATAFILE语句为永久表空间添加数据文件。例：为ORCL数据库的ORCLTBS1表空间添加一个大小为10 MB的新数据文件。 ALTER TABLESPACE ORCLTBS1 ADD DATAFILE 'D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\ORCLTBS1_2.DBF' SIZE 10M;

还可以通过改变表空间已有数据文件的大小，达到扩展表空间的目的。

例：将ORCL数据库的ORCLTBS1表空间的数据文件ORCLTBS1_2.DBF大小增加到20 MB。 ALTER DATABASE DATAFILE 'D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\ORCLTBS1_2.DBF' RESIZE 20M;

II）设置默认表空间

在Oracle10g数据库中，默认表空间为USERS表空间。设置数据库的默认表空间：

ALTER DATABASE DEFAULT TABLESPACE 例：将默认表空间设为ORCLTBS1

ALTER DATABASE DEFAULT TABLESPACE ORCLTBS1;

（3）删除表空间语法:

DROP TABLESPACE tablespace_name

如果表空间非空，应带有子句INCLUDING CONTENTS

若要删除操作系统下的数据文件，应带有子句AND DATAFILES 删除参照完整性约束，应带有子句CASCADE CONSTRAINTS

【实验内容】

用sys/manager登录到orcl数据库实例，完成以下内容：

1. 为Users表空间添加一个数据文件，文件名为users03.dbf，大小为50M。 2. 修改上述数据文件为自动扩展方式，每次扩展5MB，文件最长100M。 3. 修改上述数据文件为自动扩展方式，每次扩展5MB，文件最长无限制。 4. 创建一个本地管理方式下自动分区管理的表空间USERTBS1，其对应的数据文件名称为

USERTBS1_1.DBF，大小为20M。

5. 为上述表空间USERTBS1添加一个数据文件USERTBS1_2.DBF，大小为20M。 6. 修改表空间USERTBS1的数据文件USERTBS1_2.DBF大小为50M。

7. 创建一个本地管理方式下的表空间USERTBS2，要求每个分区大小为512KB。

8. 为数据库添加一个重做日志文件组，组内包含两个成员文件，分别为redo04a.log和

redo04b.log，大小分别为5M。

9. 为新建的重做日志文件组添加一个成员文件，名称为redo04clog。

实验二模式对象的管理

【开发语言及实现平台或实验环境】

Oracle10g

【实验目的】

（1）学习基本数据库表的创建，表结构的修改与删除等命令；（2）学习索引的管理命令；（3）学习索引化表的管理命令（4）学习分区表的管理命令

（5）学习簇、视图、序列、同义词等的管理命令

【实验原理】 1．基本表的管理（1）表的创建： Create命令：

CREATE TABLE table_name( column_name type(size), column_name type(size), …); 或

CREATE TABLE table_name [(column_name,…)] AS SELECT statement；表名命名规则

所用的表名必须满足下面的条件： a) 名字必须以A-Z 或a-z的字母开始； b) 名字可以包括字母、数字和特殊字母（_）。字符$和#也是合法的，但是这种用法不提倡； c) 名字大小写是一样的；例如EMP、emp和eMp是表示同一个表； d) 名字最长不超过30个字符； e) 表名不能和其它的对象重名； f) 表名不能是SQL保留字。

字段类型：

表5 字段类型数据类型 VARCHAR2（w） CHAR（w） NUMBER NUMBER（w） NUMBER（w,s） DATE LONG RAW 和LONG RAW 描述变长字符长度为w。最长为2000个字符。定长字符长度为w。默认为1个字符；最长为255个字符 38位有效数字的浮点数 W位精确度的整数，范围从1至38 W是精度，或总的数字书，范围从1至38。S是比例，或是小数点右边的数字位。比例的范围从-84至127 日期值，范围从公元前14712年1月到公元314712年12月变长字符串，其最大长度为2G（或231-1个字节）面向字节数据，可存储字符串、浮点数，二进制数据等

完整性约束：

Oracle允许用户为表和列定义完整性约束来增强一定的规则。可分为：表约束和字段约束约束类型如下： 1）NOT NULL约束

NOT NULL约束保证字段值不能为NULL。没有NOT NULL约束的字段，值可以为NULL。

2）UNIQUE约束

指定一个字段或者字段的集合为唯一键。在表中没有两行具有相同的值。如果唯一键是基于单条记录的，NULL是允许的。

表约束命令格式：

，[CONSTRAINT constraint_name] UNIQUE (Column, Column, …) 字段约束命令格式：

[CONSTRAINT constraint_name] UNIQUE 例如：

CREATE TABLE DEPT

（DEPTNO NUMBER， DNAME VARCHAR2（9）， LOC VARCHAR2（10），CONSTRAINT UNQ_DEPT_LOC UNIQUE（DNAME，LOC））； UNQ_DEPT_LOC是一个表约束。 3）主键约束（Primary Key Constraint）

主键约束强制字段和字段集合的唯一性，并且用一个唯一索引来管理它。每个表中只能用一个主键，这样可以通过主键来标识表中的每条记录。NULL值不允许在主键字段出现。

表约束命令格式：

，[CONSTRAINT constraint_name] PRIMARY KEY (Column, Column, …) 字段约束命令格式：

[CONSTRAINT constraint_name] PRIMARY KEY 例如：用字段约束定义DEPTNO为主键 CREATE TABLE DEPT

（DEPTNO NUMBER（2） CONSTRAINT DEPT_PRIM PRIMARY KEY, …）; 4）外键约束

外键提供表内或表间的完整性规则。外键必须依赖于一个primary或unique key。表约束命令格式：

，[CONSTRAINT constraint_name] FOREIGN KEY (Column, Column, …) REFERENCE table (column, column, …)

字段约束命令格式：

[CONSTRAINT constraint_name] FOREIGN KEY table (column) 例如：

CREATE TABLE EMP

（…CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCE DEPT(DEPTNO)); 5）Check约束

CHECK约束定义了每条记录必须满足的条件

语法：

[CONSTRAINT constraint_name] CHECK (condition)

（2）修改表

ALTER TABLE命令可用来修改数据表的定义。命令格式：

ALTER TABLE tablename

[ADD或MODIFY或DROP options ](column_spec [column_constraint]) [ENABLE clause 或DISABLE clause]

ADD关键字可以用来给已存在的数据表增加一个字段或约束。如：给EMP增加一个字段

ALTER TABLE EMP ADD （SPOUSES_NAME CHAR(10)）； MODIFY关键字可以用来修改已存在的数据表定义。如：把EMP中ENAME长度改为25个字符

ALTER TABLE EMP MODIFY （ENAM CHAR（25））；

（3）删除表

用DROP TABLE命令删除Oracle数据表的定义。命令格式：

DROP TABLE table_name [CASCADE CONSTRAINT] 例如：

DROP TABLE EMP；

CASCADE CONSTRAINT选项说明了也把完整性约束一起删除。注意：

? DROP TABLE也把数据表中的数据删除。

? 数据表的VIEWS和SYNOMNYMS保留下来，但它们变成了不合法的。 ? 任何悬而未决的事务将被提交。

? 只有数据表的生成者或DBA才有权删除它。

2.索引的管理（1）创建索引语法

CREATE [UNIQUE]|[BITMAP] INDEX index_name ON table_name([column_name[ASC|DESC],…]| [expression]) [REVERSE]

[parameter_list]；

创建非惟一性索引

CREATE INDEX employee_ename ON employee(ename)TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75); 创建惟一性索引

CREATE UNIQUE INDEX deptartment_index ON department(dname); 创建位图索引

CREATE BITMAP INDEX student_sex ON student(sex); 创建反序索引

CREATE INDEX player_sage ON player(sage) REVERSE; 创建函数索引

CREATE INDEX idx ON employee(UPPER(ename));

（2）删除索引语法

DROP INDEX index_name;

3.索引化表的管理

在CREATE TABLE语句中指定ORGANIZATION INDEX关键字。 CREATE TABLE new_student(

sno NUMBER PRIMARY KEY , sname CHAR(11), sage NUMBER, sex CHAR(2))

ORGANIZATION INDEX TABLESPACE USERS ;

可以通过子查询创建索引化表 CREATE TABLE new_emp(

empno PRIMARY KEY,ename) ORGANIZATION INDEX

AS SELECT empno,ename FROM emp; 4. 分区表的管理创建分区表（1）范围分区

范围分区是按照分区列值的范围来对表进行分区的。

通过PARTITION BY RANGE子句说明根据范围进行分区，其后括号中列出分区列。每个分区以PARTITION关键字开头，其后是分区名。VALUES LESS THAN子句用于设置分区中分区列值的范围。

示例:

创建一个分区表，将学生信息根据其出生日期进行分区，将1980年1月1日前出生的学生信息保存在ORCLTBS1表空间中，将1980年1月1日到1990年1月1日出生的学生信息保存在ORCLTBS2表空间中，将其他学生信息保存在ORCLTBS3表空间中。 CREATE TABLE student_range(

sno NUMBER(6) PRIMARY KEY, sname VARCHAR2(10), sage int, birthday DATE )

PARTITION BY RANGE(birthday) ( PARTITION p1 VALUES LESS THAN

(TO_DATE('1980-1-1', 'YYYY-MM-DD'))

TABLESPACE ORCLTBS1, PARTITION p2 VALUES LESS THAN

(TO_DATE('1990-1-1', 'YYYY-MM-DD')) TABLESPACE ORCLTBS2,

PARTITION p3 VALUES LESS THAN(MAXVALUE) TABLESPACE ORCLTBS3 STORAGE(INITIAL 10M NEXT 20M))

STORAGE(INITIAL 20M NEXT 10M MAXEXTENTS 10 );

（2）列表分区概述

如果分区列的值并不能划分范围（非数值类型或日期类型），同时分区列的取值范围只是一个包含少数值的集合，则可以对表进行列表分区（LIST），如按地区、性别等分区。

通过PARTITION BY LIST子句说明根据列表进行分区，其后括号中列出分区列。每个分区以PARTITION 关键字开头，其后是分区名。VALUES子句用于设置分区所对应的分区列取值。示例

创建一个分区表，将学生信息按性别不同进行分区，男学生信息保存在表空间ORCLTBS1中，而女学生信息保存在ORCLTBS2中。 CREATE TABLE student_list(

sno NUMBER(6) PRIMARY KEY, sname VARCHAR2(10),

sex CHAR(2) CHECK(sex in ('M', 'F')) )

PARTITION BY LIST(sex)

(PARTITION student_male VALUES('M') TABLESPACE ORCLTBS1, PARTITION student_female VALUES('F') TABLESPACE ORCLTBS2 );

（3）散列分区概述

散列分区（HASH）是在指定数量的分区中均等地分配数据。

为了创建散列分区，需要指定分区列、分区数量或单独的分区描述。

通过PARTITION BY HASH指定分区方法，其后的括号指定分区列。使用PARTITION子句指定每个分区名称和其存储空间。或者使用PARTITIONS子句指定分区数量，用STORE IN子句指定分区存储空间。示例

创建一个分区表，根据学号将学生信息均匀分布到ORCLTBS1和ORCLTBS2两个表空间中。 CREATE TABLE student_hash (

sno NUMBER(6) PRIMARY KEY, sname VARCHAR2(10) )

PARTITION BY HASH(sno)

(PARTITION p1 TABLESPACE ORCLTBS1, PARTITION p2 TABLESPACE ORCLTBS2 );

CREATE TABLE student_hash2 (

sno NUMBER(6) PRIMARY KEY, sname VARCHAR2(10) ) PARTITION BY HASH(sno)

PARTITIONS 2 STORE IN(ORCLTBS1,ORCLTBS2);

（4）复合分区复合分区包括：范围-列表复合分区范围-散列复合分区。

创建复合分区时需要指定：

分区方法（PARTITION BY RANGE）分区列

子分区方法（SUBPARTITION BY HASH，SUBPARTITION BY LIST）子分区列

每个分区中子分区数量或子分区的描述。

范围-列表复合分区

范围-列表复合分区先对表进行范围分区，然后再对每个分区进行列表分区，即在一个范围分区中创建多个列表子分区。示例：

创建一个范围-列表复合分区表，将1980年1月1日前出生的男、女学生信息分别保存在ORCLTBS1和ORCLTBS2表空间中，1980年1月1日到1990年1月1日出生的男、女学生信息分别保存在ORCLTBS3和ORCLTBS4表空间中，其他学生信息保存在ORCLTBS5表空间中。

CREATE TABLE student_range_list( sno NUMBER(6) PRIMARY KEY,

sname VARCHAR2(10), sex CHAR(2) CHECK(sex IN ('M','F')), sage NUMBER(4), birthday DATE )

PARTITION BY RANGE(birthday) SUBPARTITION BY LIST(sex)

(PARTITION p1 VALUES LESS THAN(TO_DATE('1980-1-1', 'YYYY-MM-DD')) (SUBPARTITION p1_sub1 VALUES('M') TABLESPACE ORCLTBS1, SUBPARTITION p1_sub2 VALUES('F') TABLESPACE ORCLTBS2), PARTITION p2 VALUES LESS THAN(TO_DATE('1990-1-1', 'YYYY-MM-DD')) (SUBPARTITION p2_sub1 VALUES('M') TABLESPACE ORCLTBS3, SUBPARTITION p2_sub2 VALUES('F') TABLESPACE ORCLTBS4), PARTITION p3 VALUES LESS THAN(MAXVALUE) TABLESPACE ORCLTBS5

);

范围-散列复合分区

范围-散列复合分区先对表进行范围分区，然后再对每个分区进行散列分区，即在一个范围分区中创建多个散列子分区。示例：

创建一个范围-散列复合分区表，将1980年1月1日前出生的学生信息均匀地保存在ORCLTBS1和ORCLTBS2表空间中，1980年1月1日到1990年1月1日出生的学生信息保存在ORCLTBS3和ORCLTBS4表空间中，其他学生信息保存在ORCLTBS5表空间中。 CREATE TABLE student_range_hash( sno NUMBER(6) PRIMARY KEY, sname VARCHAR2(10), sage NUMBER(4), birthday DATE )

PARTITION BY RANGE(birthday) SUBPARTITION BY HASH(sage)

(PARTITION p1 VALUES LESS THAN(TO_DATE('1980-1-1', 'YYYY-MM-DD')) (SUBPARTITION p1_sub1 TABLESPACE ORCLTBS1,

SUBPARTITION p1_sub2 TABLESPACE ORCLTBS2), PARTITION p2 VALUES LESS THAN(TO_DATE('1990-1-1', 'YYYY-MM-DD'))

(SUBPARTITION p2_sub1 TABLESPACE ORCLTBS3, SUBPARTITION p2_sub2 TABLESPACE ORCLTBS4), PARTITION p3 VALUES LESS THAN(MAXVALUE) TABLESPACE ORCLTBS5 );

5. 簇的管理（1）创建簇

在数据库中，簇占据实际的存储空间，因此用户必须具有足够的表空间配额。例：

CREATE CLUSTER student_class ( class_id NUMBER(3)) SIZE 600

TABLESPACE users

STORAGE (INITIAL 200K NEXT 300K MINEXTENTS 2 MAXEXTENTS 20); （2）创建聚簇表

在簇中创建的表称为“聚簇表”。通过将两个或多个聚簇表保存在同一个簇中，可以将两个表中具有相同的聚簇字段值的记录集中存放在同一个数据块（或相邻的多个数据块）中。在CREATE TABLE语句中通过CLUSTER子句来指定表所使用的簇和聚簇字段。聚簇表中的聚簇字段必须与创建簇时指定的聚簇字段具有相同的名称和数据类型。 SQL> CREATE TABLE classes(

class_id NUMBER(3) PRIMARY KEY, cname VARCHAR2(10) )

CLUSTER student_class (class_id);

SQL> CREATE TABLE students (

sno NUMBER(5) PRIMARY KEY, sname VARCHAR2(15) NOT NULL,

class_id NUMBER(3) REFERENCES classes) CLUSTER student_class(class_id); （3）创建聚簇索引

可以为簇中的聚簇字段创建索引，这种类型的索引称为“聚簇索引”。例如，为簇student_class创建一个聚簇索引。 CREATE INDEX student_class_index ON CLUSTER student_class TABLESPACE USERS

STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 2 MAXEXTENTS 10) PCTFREE 10; （4）删除簇

删除簇的同时将删除聚簇索引。根据簇中是否包含表，簇删除可以分为下列3种情况。使用DROP CLUSTER删除不包含聚簇表的簇及簇索引。 DROP CLUSTER student_class;

使用DROP CLUSTER...INCLUDING TABLES语句删除包含聚簇表的簇。 DROP CLUSTER student_class INCLUDING TABLES;

如果聚簇表中包含其他表外键参考的主键约束列或唯一性约束列，则需要使用CASCADE CONSTRAINTS子句删除约束，同时删除簇。

DROP CLUSTER student_class INCLUDING TABLES CASCADE CONSTRAINTS;

6.视图的管理（1）创建视图语法

CREATE [OR REPLACE] [FORCE| NOFORCE] VIEW [schema.]view_name[(column1,column2,…)] AS subquery

[WITH READ ONLY]|[WITH CHECK OPTION] [CONSTRIANT constraint]; （2）删除视图

使用DROP VIEW语句删除视图。

7.序列的管理

序列用于产生惟一序号的数据库对象，用于为多个数据库用户依次生成不重复的连续整数。通常使用序列自动生成表中的主键值。（1）创建序列语法：

CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n]

[MAXVALUE n | NOMAXVALUE]

[MINVALUE n | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE n | NOCACHE]; 参数说明：

INCREMENT BY：设置相邻两个元素之间的差值，即步长，默认值为1； START WITH：设置序列初始值，默认值为1； MAXVALUE：设置序列最大值

NO MAXVALUE：设置默认情况下，递增序列的最大值为1027，递减序列的最大值为-1； MINVALUE：设置序列最小值；

NOMINVALUE：设置默认情况下，递增序列的最小值为1，递减序列的最小值为-1026； CYCLE|NOCYCLE：指定当序列达到其最大值或最小值后，是否循环生成值，NOCYCLE 是默认选项；

CACHE|NOCACHE：设置是否在缓存中预先分配一定数量的数据值，以提高获取序列值的速度，默认为缓存20个值。例：

CREATE SEQUENCE stud_sequence INCREMENT BY 1 START WITH 100 MAXVALUE 1000;

（2）修改序列说明

除了不能修改序列起始值外，可以对序列其他任何子句和参数进行修改。例如，修改序列stud_sequence的设置。 ALTER SEQUENCE stud_sequence INCREMENT BY 10

MAXVALUE 10000 CYCLE CACHE 20;

（3）删除序列

可以使用DROP SEQUENCE语句删除序列。例如，删除序列stud_sequence。 DROP SEQUENCE stud_sequence;

8. 同义词管理

同义词是数据库中表、索引、视图或其他模式对象的一个别名。（1）创建同义词语法：

CREATE [PUBLIC] SYNONYM synonym_name FOR object_name; 例如：

CREATE PUBLIC SYNONYM scottemp FOR scott.emp; （2）删除同义词语法：

DROP [PUBLIC] SYNONYM synonym_name; 例如：

DROP PUBLIC SYNONYM scottemp;

【实验内容】

Class表的结构：列名数据类型 CNO NUMBER（2） CNAME VARCHAR2(20) NUM NUMBER(3) Student表的结构：列名数据类型 SNO NUMBER（4） SNAME VARCHAR2(10) SAGE NUMBER SEX CHAR(2) CNO NUMBER(2) 约束主键备注班号班名人数约束主键唯一备注学号姓名年龄性别班级号要求：

1. 创建Class、Student两个表；

2. 为Student表添加一个外键约束，其CNO列参照class表的CNO列； 3. 为Student表的sex列添加一个检查约束，保证该列取值为“M”或“F”，且默认值为“M”； 4. 在class表的CNAME列上创建一个唯一性索引； 5.创建一个索引化表，其表结构与Student表相同；

6.创建一个Student_range表（列、类型与student表的列、类型相同），按学生年龄分为3个区，低于20岁的学生信息放入part1区，存储在stbs1表空间中；20-30岁的学生信息放在part2区，存储在stbs2表空间中；其他数据放在part2区，存储在stbs3表空间中。 7. 创建一个Student_list表（列、类型与student表的列、类型相同），按学生性别分为两个区。

8.创建一个class_number簇，聚簇字段名为CNO，类型为NUMBER(2)。然后利用该簇，创建students和classes两个聚簇表。

9.创建一个起始值为10000的序列，步长为2，最大值为1000000，不可循环。

实验三安全管理

【开发语言及实现平台或实验环境】

Oracle10g

【实验目的】

练习创建用户、用户的授权、创建角色、将角色授权给用户等安全管理命令

【实验原理】一、用户管理 1创建用户基本语法：

CREATE USER user_name IDENTIFIED [BY password|EXTERNALLY|GLOBALLY AS 'external_name']

[DEFAULT TABLESPACE tablespace_name]

[TEMPORARY TABLESPACE temp_tablesapce_name] [QUOTA n K|M|UNLIMITED ON tablespace_name] [PROFILE profile_name] [PASSWORD EXPIRE]

[ACCOUNT LOCK|UNLOCK];

注意：在创建新用户后，必须为用户授予适当的权限。例如，授予用户CREATE SESSION权限后，用户才可以连接到数据库。参数说明：

user_name：用于设置新建用户名，在数据库中用户名必须是唯一的； IDENTIFIED：用于指明用户身份认证方式；

BY password：用于设置用户的数据库身份认证，其中password为用户口令； EXTERNALLY：用于设置用户的外部身份认证； GLOBALLY AS'external_name'：用于设置用户的全局身份认证，其中external_name为Oracle的安全管理服务器相关信息；

DEFAULT TABLESPACE：用于设置用户的默认表空间，如果没有指定，Oracle将数据库默认表空间作为用户的默认表空间；

TEMPORARY TABLESPACE：用于设置用户的临时表空间；

QUOTA：用于指定用户在特定表空间上的配额，即用户在该表空间中可以分配的最大空间； PROFILE：用于为用户指定概要文件，默认值为DEFAULT，采用系统默认的概要文件； PASSWORD EXPIRE：用于设置用户口令的初始状态为过期，用户在首次登录数据库时必须修改口令；

ACCOUNT LOCK：用于设置用户初始状态为锁定，默认为不锁定；

ACCOUNT UNLOCK：用于设置用户初始状态为不锁定或解除用户的锁定状态。

实例：

创建一个用户user3，口令为user3，默认表空间为USERS，在该表空间的配额为10 MB，

初始状态为锁定。

CREATE USER user3 IDENTIFIED BY user3

DEFAULT TABLESPACE USERS QUOTA 10M ON USERS ACCOUNT LOCK;

创建一个用户user4，口令为user4，默认表空间为USERS，在该表空间的配额为10 MB。口令设置为过期状态，即首次连接数据库时需要修改口令。概要文件为example_profile（假设该概要文件已经创建）。

CREATE USER user4 IDENTIFIED BY user4 DEFAULT TABLESPACE USERS QUOTA 10M ON USERS PROFILE example_profile PASSWORD EXPIRE;

2. 修改用户基本语法：

ALTER USER user_name [IDENTIFIED] [BY password|EXTERNALLY|GLOBALLY AS 'external_name']

[DEFAULT TABLESPACE tablespace_name]

[TEMPORARY TABLESPACE temp_tablesapce_name] [QUOTA n K|M|UNLIMITED ON tablespace_name] [PROFILE profile_name]

[DEFAULT ROLE role_list|ALL [EXCEPT role_list] |NONE]

[PASSWORD EXPIRE]

[ACCOUNT LOCK|UNLOCK]; 参数说明：

role_list：角色列表； ALL ：表示所有角色；

EXCEPT role_list：表示除了role_list列表中的角色之外的其他角色； NONE：表示没有默认角色。

注意，指定的角色必须是使用GRANT命令直接授予该用户的角色。实例：

将用户user3的口令修改为newuser3，同时将该用户解锁。 ALTER USER user3

IDENTIFIED BY newuser3 ACCOUNT UNLOCK;

3. 删除用户基本语法

DROP USER user_name [ CASCADE ]; 步骤

先删除用户所拥有的对象再删除用户

将参照该用户对象的其他数据库对象标志为INVALID

实例：

DROP USER users4;

二、权限管理 1. 系统权限管理 (1) 系统权限的授权语法：

GRANT sys_priv_list TO user_list|role_list|PUBLIC [WITH ADMIN OPTION]; 参数说明：

sys_priv_list：表示系统权限列表，以逗号分隔； user_list：表示用户列表，以逗号分隔； role_list：表示角色列表，以逗号分隔； PUBLIC：表示对系统中所有用户授权；

WITH ADMIN OPTION：表示允许系统权限接收者再把此权限授予其他用户。例：

为用户user1授予CREATE SESSION，CREATE TABLE，CREATE INDEX系统权限。 CONNECT SYSTEM/MANAGER@ORCL;

GRANT CREATE SESSION,CREATE TABLE,CREATE VIEW TO user1;

为用户user2授予CREATE SESSION，CREATE TABLE ，CREATE INDEX系统权限。user2获得权限后，为用户user3授予CREATE TABLE权限。 CONNECT SYSTEM/MANAGER@ORCL; GRANT CREATE SESSION,CREATE TABLE, CREATE VIEW TO user2 WITH ADMIN OPTION; CONNECT user2/user2 @ORCL GRANT CREATE TABLE TO user3;

（2）系统权限的回收语法：

REVOKE sys_priv_list

FROM user_list|role_list|PUBLIC;

例：回收user1的CREATE TABLE，CREATE VIEW 权限，语句为： CONNECT SYSTEM/MANAGER@ORCL;

REVOKE CREATE TABLE，CREATE VIEW FROM user1;

2. 对象权限的管理 (1) 对象权限的授权语法:

GRANT obj_priv_list|ALL ON [schema.]object TO user_list|role_list [WITH GRANT OPTION]; 参数说明:

obj_priv_list：表示对象权限列表，以逗号分隔；

[schema.]object：表示指定的模式对象，默认为当前模式中的对象； user_list：表示用户列表，以逗号分隔； role_list：表示角色列表，以逗号分隔；

WITH GRANT OPTION：表示允许对象权限接收者把此对象权限授予其他用户。

例：将scott模式下的emp表的SELECT，UPDATE，INSERT权限授予user1用户。 CONNECT SYSTEM/MANAGER@ORCL;

GRANT SELECT,INSERT,UPDATE ON scott.emp TO user1;

例：将scott模式下的emp表的SELECT，UPDATE，INSERT权限授予user2用户。user2用户再将emp表的SELECT，UPDATE权限授予user3用户。 CONNECT SYSTEM/MANAGER@ORCL;

GRANT SELECT,INSERT,UPDATE ON scott.emp TO user2 WITH GRANT OPTION; CONNECT user2/user2@ORCL

GRANT SELECT,UPDATE ON scott.emp TO user3;

(2) 对象权限的回收语法：

REVOKE obj_priv_list | ALL ON [schema.]object FROM user_list|role_list;

三、角色管理 1、创建角色语法为

CREATE ROLE role_name [NOT IDENTIFIED][IDENTIFIED BY password]; 参数说明

role_name：用于指定自定义角色名称，该名称不能与任何用户名或其他角色相同； NOT IDENTIFIED：用于指定该角色由数据库授权，使该角色生效时不需要口令； IDENTIFIED BY password：用于设置角色生效时的认证口令。

例如，创建不同类型的角色。 CREATE ROLE high_manager_role;

CREATE ROLE middle_manager_role IDENTIFIED BY middlerole; CREATE ROLE low_manager_role IDENTIFIED BY lowrole;

2、角色权限的授予与回收实例：

GRANT CONNECT,CREATE TABLE,CREATE VIEW TO low_manager_role; GRANT CONNECT,CREATE TABLE,CREATE VIEW TO middle_manager_role; GRANT CONNECT,RESOURCE,DBA TO high_manager_role;

GRANT SELECT,UPDATE,INSERT,DELETE ON scott.emp TO high_manager_role; REVOKE CONNECT FROM low_manager_role;

REVOKE CREATE TABLE,CREATE VIEW FROM middle_manager_role;

REVOKE UPDATE,DELETE ,INSERT ON scott.emp FROM high_manager_role;

3、修改角色语法：

ALTER ROLE role_name

[NOT IDENTIFIED]|[IDENTIFIED BY password]；实例：

ALTER ROLE high_manager_role IDENTIFIED BY highrole; ALTER ROLE middle_manager_role NOT IDENTIFIED;

4、角色的生效与失效语法：

SET ROLE [role_name[IDENTIFIED BY password ]]|[ALL [EXCEPT role_name]]|[NONE]; 参数说明：

role_name：表示进行生效或失效设置的角色名称；

IDENTIFIED BY password：用于设置角色生效或失效时的认证口令； ALL：表示使当前用户所有角色生效；

EXCEPT role_name：表示除了特定角色外，其余所有角色生效； NONE：表示使当前用户所有角色失效。

实例：

SET ROLE NONE;

SET ROLE high_manager_role IDENTIFIED BY highrole;

SET ROLE middle_manager_role,low_manager_low IDENTIFIED BY lowrole; SET ROLE ALL EXCEPT low_manager_role,middle_manager_role;

5、删除角色语法结构

DROP ROLE role_name;

6、利用角色进行权限管理 (1) 给用户或角色授予角色语法:

GRANT role_list TO user_list|role_list；

例如，将CONNECT，high_manager_role角色授予用户user1，将RESOURCE，CONNECT角色授予角色middle_manager_role。

GRANT CONNECT,high_manager_role TO user1;

GRANT RESOURCE,CONNECT TO middle_manager_role;

（2）从用户或角色回收角色语法:

REVOKE role_list FROM user_list|role_list；

例如，回收角色middle_manager_role的RESOURCE，CONNECT角色。 SQL>REVOKE RESOURCE,CONNECT FROM middle_manager_role;

（3）用户角色的激活或屏蔽语法:

ALTER USER user_name DEFAULT ROLE [role_name]|[ALL [EXCEPT role_name]]| [NONE]; 例：

ALTER USER user1 DEFAULT ROLE NONE;

ALTER USER user1 DEFAULT ROLE CONNECT,DBA; ALTER USER user1 DEFAULT ROLE ALL;

ALTER USER user1 DEFAULT ROLE ALL EXCEPT DBA;

【实验内容】

1. 创建一个用户口令认证的数据库用户usera_exer，口令为usera，默认表空间为USERS，

配额为10MB，初始账户为锁定状态。

2. 创建一个口令认证的数据库用户userb_exer，口令为userb。

3. 为usera_exer用户授权CREATE SESSION权限、scott.emp的SELECT权限和UPDATE权限，同时允许该用户将获得的权限授予其他用户。 4. 将用户usera_exer的账户解锁。 5. 用usera_exer登录数据库，查询和更新scott.emp中的数据。同时将scott.emp的SELECT权限和UPDATE权限授予用户userb_exer。

6. 禁止用户usera_exer将获得的CREATE SESSION权限再授予其他用户。

7. 禁止用户usera_exer将获得的scott.emp的SELECT权限和UPDATE权限再授予其他用户。 8. 创建角色rolea和roleb，将CREATE TABLE权限，scott.emp的INSERT权限和DELETE权限授予rolea；将CONNECT，RESOURCE角色授予roleb。 9. 将角色rolea,roleb授予用户usera_exer。

实验四数据操纵和查询SQL语句

【开发语言及实现平台或实验环境】

Oracle10g

【实验目的】

（1）练习插入、更新、删除等基本的数据操纵语句；（2）练习SQL查询语句；

【实验原理】 1．数据操纵（1）插入数据

用Insert在基本表customer中插入数据：

SQL>insert into customer values (?Nicholson?,?CA?,6989.99); SQL>insert into customer values (?Martin?,?CA?,2345.45); SQL>insert into customer values (?Laursen?,?CA?,34.34); SQL>insert into customer values (?Bambi?,?CA?,1234.55); SQL>insert into customer values (?McGraw?,?NJ?,123.45);

在表STATE中插入指定的字段：

SQL>insert into state (state_name,state_cd) 2 values ('Massachusetttes', 'M');

SQL>insert into state (state_name,state_cd) 2 values ('California', 'CA');

SQL>insert into state (state_name,state_cd) 2 values ('NewJersey', 'NJ');

SQL>insert into state (state_name,state_cd) 2 values ('NewYork', 'NY');

（2）修改数据

把state表中NewYork改为Florida，NY改为FD： UPDATE state SET state_name = 'Florida', state_cd = 'FD' where state_name = 'NewYork' and state_cd = 'NY';

（3）删除数据

从STATE表删除state_name为Florida和state_cd为FD的记录： DELETE FROM STATE WHERE state_name = 'Florida' AND state_cd = 'FD';

3. 查询

SQL查询包括简单查询、多表连接查询和子查询，例如：（1）显示EMP表中所有的部门号、职工名称和管理者号码：

SELECT DEPTNO,ENAME,MGR FROM EMP;

（2）算术运算符的使用

SELECT ENAME, SAL+250*12 FROM EMP;

（3）连字符的使用

把职工号和职工名字连接起来，如下：

SELECT EMPNO||ENAME EMPLOYEE FROM EMP;

把职工号和职工名字中间用‘-’连接起来，并输出‘WORKS IN DEPARTMENT’，如下： SELECT EMPNO||'-'||ENAME EMPLOYEE, 'WORKS IN DEPARTMENT', DEPTNO FROM EMP;

（4）禁止重复

如果列举出EMP表中所有部门号： Select deptno from emp;

（5）排序

按单个字段排序，如按照ENAME排序，

SELECT ENAME,JOB,SAL*12,DEPTNO FROM EMP ORDER BY ENAME;

（6）带条件的查询

1）查询工作是CLERK的所有职工的姓名，职工号和部门号

SELECT ENAME, EMPNO, JOB, DEPTNO FROM EMP WHERE JOB = 'CLERK';

（6）操作符的应用 1）BETWEEN的应用

查询工资在1000到2000之间的职工名字和工资信息。

SELECT ENAME,SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000; 2）IN

查询有7902，7566，7788三个MGR号之一的所有职工： SELECT EMPNO,ENAME, SAL,MGR FROM EMP WHERE MGR IN (7902,7566,7788);

3）LIKE

通配符%代表任意0或多个字符。通配符_代表任意单个字符。

查询名字以“S”开始的所有职工：

SELECT ENAME FROM EMP WHERE ENAME LIKE 'S%'; 4）IS NULL

查询没有管理者的所有职工：

SELECT ENAME,MGR FROM EMP WHERE MGR IS NULL;

【实验内容】有4个表：

学生表：S(Snum,Sname,sex,birthday,dept)

课程表：C(Cnum,Cname,Credit,Tnum) 选修表：SC(Snum,Cnum,grade)

教师表：T(Tnum,Tname,sex,birthday,dept,title) 请完成以下操作：

1. 建立表，实现完整性约束；

2. 查询学生表中不姓“王”的学生的情况；

3. 查询所有有成绩的学生的学号、姓名和课程名； 4. 查询所有女教师及其所上的课程； 5. 查询各个课程号及其相应的选课人数；

6. 查询每个学生已选修课程的门数及平均成绩； 7. 将学生刘磊新选修的数据库原理插入选修表中； 8. 将学生张红选修的C3课程的成绩加10分； 9. 将学生张红选修的“数据库原理”删除。

实验五 PL/SQL编程语言的使用与程序设计

【开发语言及实现平台或实验环境】

Oracle10g

【实验目的】

（1）了解PL/SQL在Oracle中的基本概念；（2）掌握PL/SQL的各组成部分；（3）PL/SQL的运用。

【实验原理】 1．PL/SQL字符集

和所有其他程序设计语言一样，PL/SQL也有一字符集。读者能从键盘上输入的字符集是PL/SQL的字符。此外，在某些场合，还有使用某些字符的规定。我们将要详细介绍：

? 用PL/SQL编程时可能使用的字符 ? 算术运算符 ? 关系运算符 ? 杂符号 1）合法字符

用PL/SQL程序时，允许使用下列字符： ? 所有大、小写字母 ? 数字0到9 ? 符号：（）+-*/〈〉=！~；：.‘@ %，“# $ ^ & _ | { } ？ [ ] 2）算术运算符

下面列出了PL/SQL中常用的算术运算符。如果读者使用过其他高级程序设计语言，想必不会陌生：

表6 算术运算符运算符意义 + 加法 - 减法 * 乘法 / 除法 ** 幂

关系运算符

下面列出了PL/SQL中常用的关系运算符。如果读者有使用其他程序设计语言的经验，一定见过这些符号：

表7 关系运算符运算符意义 <> 不等于 != 不等于 ^= 不等于 < 小于

> = 大于等于

3）杂符号

PL/SQL为支持编程，还使用下述符号。下面列出了部分符号，它们是最常用的，也是使用PL/SQL的所有读者都必须了解的。

表8 杂符号符号 ( ) ; . , := || -- /*与*/ 列表分隔语句结束项分隔（在例子中，用分隔account与table_name）字符串界定符赋值并置注释符注释定界符意义样例 (?Jones?,?Roy?,?Abramson?) Procedure_name(arg1,arg2) Select * from account.table_name; if var1 = ?SANDRA? Rec_read := rec_read+1 Full_name:=?Nahtan?||??||?Yebba? --This is a comment /*This too is a comment */ 4）变量变量是PL/SQL中用来处理数据项所用的名字。读者根据下列规则选择变量名称： ? 变量必须以字母（A~Z）开头。

? 其后跟可选的一个或多个字母，数字（0~9）或特殊字符$、# 或_。 ? 变量长度不超过30个字符。 ? 变量名中不能有空格。 5）保留字

declare employee varchar2(30); loop number;

保留字不能用作变量名。尽管我们不推荐，但如果读者坚持，也可以连接两个保留字形成变量名（如loop_varchar2）。完整的PL/SQL保留字表可以在Oracle8文档中找到。

2、常用数据类型

到目前为止，我们讨论了在PL/SQL中编程时允许使用的字符，变量的命名和保留字。下面着重讨论数据类型。PL/SQL程序用于处理和显示多种类型的数据。和所有计算机软件一样，Oracle也将数据类型分成大量的子类。例如：数可分为整型（不允许有小数）和小数（有一位小数或多位小数）类。PL/SQL支持多种数据类型，此处讨论代码中最常用并且最实用的数据类型。 ? Varchar2 ? Number ? Date ? Boolean

3．PL/SQL的组件

1）块结构(Block structure)

PL/SQL程序是由独立的变量声明、执行代码和异常处理等部分代码块写成的。PL/SQL可以作为一个命名的子程序存放在数据库中，或直接在SQL*Plus窗口中作为一个匿名的块编码。当在数据库中存储PL/SQL时，子程序包括存储单元命名的头部分，程序类型的声明；以及可选的in, out 和in out参数的定义。只是可执行部分定义为begin 和end语句是固定的。Declare 和exception部分是可选的。

下面在一个无名块上和一个存储过程的例子。 --无名块 declare … begin … end;

--存储过程

create or replace precodure_name as --声明部分自动跟着语句而不需要编码。 … begin … exception … end;

2）声明部分（Declare section）

此PL/SQL块用于定义变量。在declare段中，我们可找到前面讲过的常用数据类型，以及下一节要介绍的cursor（光标）变量类型。下面的程序是一个过程的declare段例子。当存储对象（命名块）产生时，declare 段自动跟着as关键字。在SQL*Plus编写一个PL/SQL代码块（匿名块）时，用户必须指定DECLARE。 Create or replace procedure samp(I_salary in number, I_city in number) as

--这是DECLARE段；因为我们正在进行命名存储对象的编码，declare是隐含的，不需写---出。

Accum1 number; Accum2 number;

H_date date := sysdate; -变量能在此初始化 Status_flag varchar2(1); Mess_text varchar2(80); Temp_buffer varchar2(1); 3）控制结构 A）if逻辑结构

在编写计算机程序时，有各种各样的情况需要处理。这时必须测试条件，如果测试值为TRUE，做某事，为FALSE，做另一件不同的事。PL/SQL提供三种if逻辑结构供用户测试TRUE/FALSE以完成相应的工作。

b1）if-then

这个结构用于测试一个简单条件。如果该条件为TRUE，则执行一行或多行代码；如果条件测试为FALSE，则程序控制转到后面的代码。在PL/SQL中实现if逻辑有两条规则：规则1

每个if语句都有自己的then，以if开始的语句行不跟语句结束符（；）。规则2

每个if语句块以相应的end if结束。 b2）if-then-else

这种结构与if语句非常相似，唯一不同的是在条件为FALSE时，执行跟在else后的一条或多条语句。

下面是PL/SQL中if逻辑的另外两条规则：规则3

每个if语句有且只有一个else。规则4

else语句行不跟语句结束符。

if-then-elsif这种结构用于替代嵌套if-then-else结构。 PL/SQL中有关if逻辑的最后一条规则：规则5

elsif无匹配的end if。

实际上，该end if术语本语句块开始的if，而不属于elsif关键字。注意上列各清单的代码缩进部分是如何表示它们属于那一条件的。

前些例子只说明了一个elsif的语句；但是，在任何if语句中可以有许多elsif语句。值得注意的是else语句是不需要的。

关于前面举过的执照管理部门(DMV)的例子，用PL/SQL编程表示其逻辑如下。根据“the_act”的值，过程 12a,12b,12g将被调用：

create or replace procedure license_transaction(the_act in varchar2 as begin if the_act = ?DCT? then 12a;

elseif the_act = ?DT? then 12b; else 12g; end if; end; B）循环

下面几节介绍PL/SQL中使用的几种循环形式。 LOOP-EXIT-END循环：

此循环结构由三部分组成，其用法参见下面示例中的注释： cnt:=1; --在循环开始前，初始化循环计数器

loop --第一部分：以循环关键字loop开始循环 cnt:=cnt+1; --第二部分：增加循环计数器的值 if cnt>=100 then -- 测试cnt是否符合退出条件 exit; -- 满足退出条件，退出循环

end if; -- End if与前面的if匹配 …

End loop; -- 第三部分：关键字End loop结束循环 …

LOOP-EXIT WHEN-END循环：

除退出条件检测有所区别外，此结构与前一个循环结构类似。 cnt:=1; --在循环开始前，初始化循环计数器

loop --第一部分：以循环关键字loop开始循环 cnt:=cnt+1; --第二部分：增加循环计数器的值 exit when cnt>=100 -- 测试cnt是否符合退出条件 …

End loop; -- 第三部分：关键字End loop结束循环 …

WHILE-LOOP-END循环：

此结构在循环的while部分测试退出条件。 cnt:=1; --在循环开始前，初始化循环计数器

while cnt < 100 loop --第一部分：在每次执行循环前，while都要检查退出条件 … --第二部分：循环体内部的可执行代码 cnt:=cnt+1; --增加循环计数器的值以满足退出条件 …

End loop; --第三部分：关键字End loop结束循环 …

FOR-IN-LOOP-END循环：

最后介绍的这种循环结构重复执行预定义次数的循环。该循环结构也由三部分组成： for in部分定义跟踪循环的变量；

执行循环体中的一条或多条语句，直至控制循环的变量满足退出条件为止； end loop部分结束循环。

下面是一个说明如何使用这种循环机制的例子： for cnt in 1..3 loop insert into tabl values(?Still in loop?,cnt); end loop; 例：

1．在SQL*Plus中使用PL/SQL块处理

EMP表中职工号7788的职工，如果工资小于3000那么把工资更改为3000： SQL>DECLARE x NUMBER(7,2); BEGIN

SELECT sal INTO x FROM emp WHERE empno = 7788;

IF x < 3000 THEN

UPDATE emp SET sal = 3000 WHERE empno = 7788; END IF; END;

注：PL/SQL块在SQL*Plus中以点号（.）结束。

如果想运行缓冲区的内容，那么可以用RUN命令或者/命令。 2．无参数的存储过程首先创建表：

SQL> create table log_table( 2 user_id varchar2(10), 3 log_date varchar2(12));

CREATE OR REPLACE PROCEDURE log_execution IS

BEGIN

INSERT INTO log_table (user_id,log_date) VALUES (user,sysdate); END; /

存储过程的在SQL*Plus中运行 SQL>EXECUTE log_execution;

3．带输入参数的存储过程

解雇给定职工号的职工，并调用log_execution： SQL> CREATE OR REPLACE PROCEDURE fire_emp

2 (v_emp_no IN emp.empno%type) 3 IS

4 BEGIN

5 Log_execution;

6 DELETE FROM EMP WHERE empno = v_emp_no; 7 END; 8 /

SQL>EXECUTE fire_emp(7654);

存储过程删除了职工号7654的职工。

4．带输入输出的存储过程

查询EMP中给定职工号的姓名、工资和佣金。

SQL> CREATE OR REPLACE PROCEDURE query_emp 2 (v_emp_no IN emp.empno%type, 3 v_emp_name OUT emp.ename%type, 4 v_emp_sal OUT emp.sal%type, 5 v_emp_comm OUT emp.comm%type) 6 IS

7 BEGIN

8 SELECT ename,sal,comm

9 INTO v_emp_name,v_emp_sal,v_emp_comm 10 FROM EMP WHERE empno = v_emp_no;

11 END; 12 /

调用：

SQL>VAR emp_name varchar2(15); SQL>VAR emp_sal number; SQL>VAR emp_comm number;

SQL>EXECUTE query_emp(7566,:emp_name, :emp_sal, :emp_comm); PL/SQL procedure successfully completed. SQL>PRINT emp_name EMP_NAME ------------------- JONES

或者用以下语句调用： DECLARE

emp_name varchar2(15); emp_sal number; emp_comm number; BEGIN

query_emp(7566,emp_name, emp_sal, emp_comm);

DBMS_OUTPUT.PUT_LINE(emp_name||' '|| emp_sal||' '||emp_comm); END;

5．用Function查询出EMP中给定职工号的工资 SQL> CREATE OR REPLACE FUNCTION get_sal

2 (v_emp_no IN emp.empno%type) 3 RETURN number 4 AS

5 V_emp_sal emp.sal%type:= 0; 6 BEGIN

7 SELECT sal INTO v_emp_sal

8 FROM EMP WHERE empno = v_emp_no; 9 RETURN (v_emp_sal); 10 END; 11 /

SQL>VARIABLE emp_sal number;

SQL>EXECUTE :emp_sal := get_sal(7566); PL/SQL procedure successfully completed. SQL>PRINT emp_sal; EMP_SAL ------------------- 2975

【实验内容】

1、查询名为“SMITH”的员工信息，输出其员工号、工资、部门号。如果该员工不存在，则插入一条新记录，员工号为2007，员工名为“SMITH”，工资为1500，部门号为10. 2、创建一个存储过程，以员工号为参数，输出该员工的工资。

3、创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门，则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；若属于其他部门，则工资增加300.

4、创建一个存储过程，以员工号为参数，返回该员工的工作年限（以参数形式返回）。提示：可以采用日期函数months_between求工作的月份

5、创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。

6、在emp表上创建一个触发器，当插入、删除或修改员工信息时，统计各个部门的人数及平均工资，并输出。

7、在dept表上创建触发器，保证该表的记录的删除可以成功完成。

8、在dept表上创建触发器，使得当DEPT表的deptno（主键）发生变化时，EMP表的相关行也要跟着进行适当的修改。

9、创建一个存储过程，以一个整数为参数，输出工资最高的前几个（参数值）员工的信息。 10、创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工信息。 11、创建一个包，包中包含一个函数和一个过程。函数以部门号为参数，返回该部门员工的最高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工号。

实验六简单的选课系统的设计与实现

【开发语言及实现平台或实验环境】

Oracle10g

【实验目的】

（1）进一步熟悉和掌握以上所有实验中的各种操作。（2）能够在实际的事例中灵活运用学到的有关知识。（3）能够编写满足某种要求的功能。

【实验原理】

参考以上几个实验。

【实验内容】

（1）为自己建立用户帐号（以学号为帐号）。

（2）以新建的帐号登录数据库，创建如下三个基表结构并输入相应表内容：

S (S#，SNAME，AGE，SEX) 对应的中文为： [学生 (学号，姓名，年龄，性别)]

SC (S#，C#，GRADE) 对应的中文为： [选课（学号，课程号，成绩）]