《数据库原理》实验教学指导书

目录
第一部分大纲和计划
1.1 实验教学大纲 1 1.2 实验实施计划表 2
第二部分实验说明
2.1 实验一：了解Oracle中的基本知识 3 2.2 实验二：Oracle中的查询 4 2.3 实验三：Oracle中的表 5 2.4 实验四：PL/SQL编程(一) 6 2.5 实验五：PL/SQL编程(二) 7 2.6 实验六：PL/SQL编程(三) 8 2.7 实验七：PL/SQL编程(四) 9 2.8 实验八：触发器设计 10
第三部分实验补充教材
3.1 实验一：了解Oracle中的基本知识 11 3.2 实验二：Oracle中的表 12 3.3 实验三：Oracle中的查询 15 3.4 实验四：PL/SQL编程(一) 15 3.5 实验五：PL/SQL编程(二) 18 3.6 实验六：PL/SQL编程(三) 22 3.7 实验七：PL/SQL编程(四) 26 3.8 实验八：触发器设计 31
第一部分大纲和计划
1.1《数据库原理》实验教学大纲
一、课程名称：数据库原理（Principle of Database System）
二、课程编码：
三、课程总学时： 16 学时 [理论： 0 学时；实验： 16 学时]
四、课程总学分：学分
五、适用专业和开课学期：计算机科学与技术专业，第4学期。

六、实验的目的与任务：
通过ORACLE中用户及表的创建、SQL查询、ORACLE中的表、PL/SQL编程、触发器设计等实验，使学生掌握数据库技术的基本原理在大型数据库中的应用。

七、主要仪器设备及台（套）数：计算机
八、主要实验教材（指导书）及参考用书：
《数据库原理及应用》(Oracle)实用教程刘甫迎、王道学、党晋蓉主编清华大学出版社2004年10月第1版
九、成绩考核方式及评分标准：教师根据学生通过FTP上交的实验报告确定其完成数量和质量，进一步综合学生的上机态度进行评分。

实验成绩占课程总成绩的30%。

十、实验开出率：100%
十一、实验项目与要求：
注：1．项目要求：必修、选修、其他；2．项目类型：演示、操作、模拟；3．项目性质：验证、综合、设计、研究。

1．2 《数据库原理》实验大纲及其实施计划表
第二部分实验说明
《×××》实验说明
2．1 实验一了解Oracle的基本知识
实验目的：
1．熟悉ORACLE的环境，学习使用SQL*Plus与ORACLE进行交互。

2．掌握用户的建立、删除、角色的授权以及用户密码的修改。

3．掌握连接数据库以及断开连接的方法。

4．建立基本的数据库表，学习简单的查询。

5．学会表结构的修改、显示与删除。

6．学习提交与回滚的方法。

实验环境（硬/软件要求）：安装Oracle 9i
实验内容：
1．为自己建立用户帐号，练习如何修改用户参数。

2．以新建的帐号登录数据库，建立教材第75页中的四张表结构并输入相应表内容。

实验主要步骤：
1．建立用户。

2．改变用户参数。

3．建立表结构。

4．修改表结构。

5．显示表结构。

6．记录的输入及修改。

7．提交。

评分标准：实验报告占70%，上机态度占30%
实验报告形式(纸质/电子)：电子
2．2 实验二Oracle中的表
实验目的：
1．熟悉Oracle中的各种表。

2．熟悉并练习临时表的使用方法。

3．学会修改表属性的方法。

4．熟练掌握表结构的修改和删除、索引的创建与删除。

5．熟悉数据词典中的几个视图的查看方法。

实验环境（硬/软件要求）：安装Oracle 9i
实验内容：
1．按补充教材建立临时表，注意？处的返回结果。

2．把当前用户的默认表空间改为USERS，并把四张表移到USERS表空间中。

3．完成以下与表结构相关的操作：
（1）复制表S的结构生成一张新表，取名为SS；
（2）在SS表中加入属性TEL（CHAR型）；
（3）将SS表中的属性STA TUS类型改为SMALLINT型。

4．完成以下与索引相关的操作：
（1）在SS表上建立SNAME的唯一索引；
（2）在SS表上按CITY升序、SNAME降序建立唯一索引；
（3）删除以上索引；
（4）删除SS表。

5．熟悉并使用数据词典中的几个视图，查看自己帐户已创建的所有表的信息。

实验主要步骤：
1．实验前根据实验内容做好书面准备。

2．上机验证准备好的命令正确与否，修改错误直至正确。

评分标准：实验报告占70%，上机态度占30%
实验报告形式(纸质/电子)：电子
2．3 实验三Oracle中的查询
实验目的：
1．熟练掌握各种SQL语句的使用方法。

2．掌握视图的建立与删除。

实验环境（硬/软件要求）：安装Oracle 9i
实验内容：
1．实现第三章作业中的第5题的SQL查询。

2．完成以下查询：
（1）按所驻城市升序、供应商号降序显示所有的供应商信息。

（2）显示各供应商供应的零件种类数，要求按供应种类的多少降序显示，显示的内容包括供应商号，零件种类数（提示：结果中应包括未供应零件的供应商）
（3）显示所有供应了“蓝色”零件数量在200以上（含200）的所有供应商信息，显示内容包括供应商号、供应商名字、所驻城市和供应数量。

（4）找出供应零件总量超过1000（含1000）的所有供应商号，要求按供应总量多少降序排列。

（5）找出零件供应总数量最多的供应商号。

3．实现第三章作业第11题中的视图建立及查询，最后删除新建的视图。

实验主要步骤：
1．实验前根据实验内容做好书面准备。

2．上机验证准备好的命令正确与否，修改错误直至正确。

评分标准：实验报告占70%，上机态度占30%
实验报告形式(纸质/电子)：电子
实验目的：
1．熟悉脚本的编写及执行。

2．了解编程的结构以及变量的声明方法以及记录类型的使用。

实验环境（硬/软件要求）：安装Oracle 9i
实验内容：
1．熟悉脚本的使用方式。

2．调试例１、２、3，了解PL/SQL的编程风格、变量的声明方法以及记录类型的使用方法。

3．查询输出’S1’供应商的Sname及City。

实验主要步骤：
1．输入程序。

2．运行程序并分析结果。

3．修改程序，再运行程序，分析结果。

评分标准：实验报告占70%，上机态度占30%
实验报告形式(纸质/电子)：电子
实验目的：
1．掌握索引表的定义及引用。

2．掌握索引表中的记录引用方法。

3．掌握游标的使用方法。

实验环境（硬/软件要求）：安装Oracle 9i
实验内容：
1．调试例3，记录显示结果。

2．模仿例5，分别显示供应商S2以及S5的供货情况（包括PNO、JNO、QTY）。

3．模仿例6，显示表S中每一个供应商的号码及名称。

实验主要步骤：
1．编程序并上机调试运行。

2．修改程序再运行，检查结果是否正确。

评分标准：实验报告占70%，上机态度占30%
实验报告形式(纸质/电子)：电子
实验目的：
1．掌握游标属性的使用。

2．掌握各种程序控制结构的运用。

实验环境（硬/软件要求）：安装Oracle 9i
实验内容：
1．模仿例1，把其中的EMP表改成P表运行一遍，记录显示结果。

2．编程显示J表的前三条记录。

3．编程统计每一个供应商的供应零件总量，要求显示每一个供应商的名称以及供应总量。

实验主要步骤：
1．实验前按实验内容编制程序。

2．调试程序，上机运行。

评分标准：实验报告占70%，上机态度占30%
实验报告形式(纸质/电子)：电子
实验目的：
1．掌握运用过程、函数的方法。

2．掌握程序包的编程方法。

实验环境（硬/软件要求）：安装Oracle 9i
实验内容：
1．针对SPJ表编一程序包，要求包含以下三个过程实现以下功能：（1）插入一条新记录（传入各字段参数）
（2）删除一条记录（传入SNO、PNO、JNO）
（3）输出某供应商所提供的所有的零件号、工程号及数量（传入SNO）
实验主要步骤：
1．实验前书面编制程序包。

2．调试程序，上机运行直至程序包无语法错误。

3．调用程序包中的过程，验证其正确性并记录结果。

评分标准：实验报告占70%，上机态度占30%
实验报告形式(纸质/电子)：电子
2．8 实验八触发器设计
实验目的：
1．掌握ORACLE中各种触发器的设计方法。

实验环境（硬/软件要求）：安装Oracle 9i
实验内容：
1．实现教材P162中例18 的BEFORE行级触发器的定义，并检测触发器是否执行。

2．模仿教材P162例19，定义AFTER行级触发器，当供应情况表SPJ的供应数量Qty发生变化后就自动在数量变化表Qty_log中增加一条相应记录。

3．在S表上创建一个触发器，要求插入的记录中SNO的内容均为大写字符。

实验主要步骤：
1．按照实验内容设计触发器。

2．调试程序，上机运行并加以验证。

评分标准：实验报告占70%，上机态度占30%
实验报告形式(纸质/电子)：电子
第三部分实验补充教材
3.1 实验一 Oracle中的基本知识
一．大型数据库与单用户数据库的区别
1．安全性
（1）用户权限管理
（2）C/S模式，服务器统一处理客户端的请求
（3）没有DBF文件，一个表可以分跨在多个物理文件上，数据存放格式不公开2．并发性：用进程或线程同时处理多个用户的请求，自动进行并发控制，封锁的粒度可以到行
3．分布式处理：多台服务器同时对外服务，提高处理速度及并发度
4．大容量
5．数据词典：定义了表的逻辑模式和物理模式，用户可以在这里找到了解数据库内容所需的所有对象
二．SQL*Plus
SQL*Plus是一个开发者和管理员用来与Oracle数据库进行交互的客户工具。

它可以让用户发布SQL语句，编译和执行PL/SQL代码，从与Oracle相连接的命令行控制台上管理本地服务器或者网络上任何位置的服务器上的数据库。

三．建立用户
1．如何建立用户？
SQL>connect wzp/wzp@myoracle；
SQL>create user jxxx
identified by jxxx;
SQL>grant connect,resource
to jxxx;
2．如何改变用户参数？
（1）修改密码
SQL>connect jxxx/jxxx@myoracle;
SQL>alter user jxxx
identified by jyyy;
（2）锁定帐号以及解除帐号锁定
SQL>connect wzp/wzp@myoracle;
SQL>alter user jxxx account lock; (锁定帐号)
SQL> alter user jxxx account unlock; (解除帐号锁定)
（3）如何删除用户
SQL>connect wzp/wzp@myoracle;
SQL>drop user jxxx [cascade];
四．建表
（1）char(n)：定长字符串
（2）varchar2(n)：变长字符串
（3）number(m)：整数
（4）number(m,n)：浮点数或实数
（5）date：日期
2．改表
alter table s
add …/modify …
3．显示表结构
SQL>describe s;
4．删除表
drop table s;
五．记录的输入及修改
１．输入
SQL>insert into s values(…);
2．修改
SQL>update s set …
六．简单查询select * from s;
七．提交和回滚commit/rollback
3.2 实验二 Oracle中的表
一．Oracle中的表
Oracle已经发布了新类型的表，来适应各种类型的数据存储、数据访问以及性能要求。

对于所有类型的表，Oracle都允许开发人员和管理人员规定各种表特性。

我们应该理解各种表类型，以及用户为什么要选择其中一种类型，而不是其他的类型。

在用户应用中，可能常规关系表就可以完成工作，然而其他类型的表可能会让工作更快速，使用更少的磁盘空间，并占用更少的处理器资源。

常用的表类型有堆表、外部表、索引组织表和临时表，另外还有其他一些表类型。

二．堆表（heap table）：是最基本的表，采用随机存储的方式，在将行写入数据块的时候不会考虑其他行的存储位置，而是将数据写入第一个具有足够自由空间的段，当更新和删除行的时候，就会为新的插入提供可用空间。

三．外部表（rxternal tables,Oracle 9i中才有）：在数据库以外的文件系统上存储的只读表。

通过使用外部表，就无须将数据复制到数据库中，并且强制更新，我们可以让数据保留在普通文件中，并且允许数据库对其进行实地读取。

在这种方法中，外部应用可以采用它认为合适的方法更新数据，而且也不用调用SQL*Loader执行数据载入操作。

和更新性能为代价提供极好的查询性能。

对于总是要通过特定索引访问的表，使用索引组织表来代替堆组织表可以提高性能。

建立方法：
create table s
(sno
sname
status
city
primary
)
organization index
/
五．临时表：只在事务处理或会话进行期间存在数据的表。

数据会在事务处理或者会话开始以后插入临时表，当事务处理或者会话完成之后就会删除。

Oracle中，临时表只需建立一次，建议在需要临时存储数据的应用中使用临时表。

例子：SQL>create global temporary table s1_tab
on commit preserve rows *规定了一个专用于会话的临时表
as select *
from s;
SQL>select count(* )
From s1_tab;
?
SQL>create global temporary table s2_tab
on commit delete rows *规定了一个专用于事务的临时表
as select *
from s
where 1=0;
SQL>insert into s2_tab
select *
from s;
SQL>select count(* )
from s2_tab;
?
SQL>commit;
SQL>select count(* )
from s1_tab;
?
SQL>select count(* )
from s2_tab;
?
SQL>disconnect
SQL>connect wzp/wzp@myoracle;
SQL>select count(* )
from s1_tab;
SQL>select count(* )
from s2_tab;
?
六．其它表类型
1．分区表：可以将非常大的表分割成较小的片段分区存放
2．簇表：物理上存储在一起的两个或多个表（如SQL中连接的表总是会一起受到查询），以减少磁盘读取量。

3．散列簇表：类似于簇表，只是存取行的方法不同。

七．表空间
当使用Oracle管理应用数据的时候，表的特性将会决定怎样建立表，怎样在磁盘上存储表，以及当表生成和可以使用之后，应用最终执行方式。

我们可以使用TableSpace子句来规定表的存储位置。

该子句可选，若建表时不写该子句则把新建表放在默认表空间中，初始为system。

但最好改为users。

１．判定默认表空间
select default_tablespace
from user_users;
２．改变默认表空间
（1）初建时(用DBA帐号登录)
Create user j001
identified by j001
default tablespace users;
（2）初建时未设，后来改(须用DBA帐号登录才可以改)
Alter user j001
default tablespace users;
（3）将表移动到新的表空间
Alter table s move tablespace users;
八．数据词典
每一个数据库都有一个数据词典，它是用户的整个Oracle数据库的编目。

当建立用户、表、约束和其他数据库对象的时候，Oracle都会自动维护一个在数据库中存储的项编目。

用户可以通过以下几个视图得到相关的一些信息：
１．User_tables 视图：展示当前用户所拥有的所有表的信息。

SQL> select table_name,tablespace_name
from user_tables
order by table_name;
２．DBA_tables视图：数据库管理员帐号可以使用它查看数据库中所有用户的表数据。

SQL>select owner,table_name,tablespace_name
from dba_tables
where owner in(‘WZP’,’J101’)
order by owner,tablespace_name,table_name;
３．ALL_tables视图：可以查看有关用户所拥有的表的信息以及用户已经被授予特权的表
SQL>select owner,table_name
from all_tables
order by owner,table_name;
3.3 实验三 ORACLE中的查询
一．复杂查询注意点
a)表别名及列别名的设置
b)连接(＊Oracle8i中无法实现连接，只能用笛卡儿积代替)
i.自然连接
ii.内连接
iii.外连接
二．其他ＤＭＬ语句
a)插入多行
insert into s(sno) select distinct sno from spj;
b)生成一张新表
create table 女生
as select * from student where sex=’F’;
c)复制表结构
create table 女生
as select * from student where １＝０;
d)删除表内容
delete fr om s where …
3.4 实验四 PL/SQL编程(一)
一．脚本的编写及执行
１．在记事本中编写并以***.sql 保存
２．运行：@路径\***.sql;
二．PL/SQL的来源及实质
PL/SQL是Oracle的过程化编程语言，用户可以使用它编写用来在数据库中执行的定制程序以及过程代码。

PL/SQL程序能够存储在数据库中，用于用户应用程序和日常操作。

这可以让用户在编程语言中实现与用户数据密切相关的业务逻辑。

三．PL/SQL的块结构
１．PL/SQL代码使用了程序块（block），利用模块化方式进行构建。

每个程序块都是一组逻辑上的变量、可执行代码以及错误控制代码。

SQL>declare
L_number number:=1;
Begin
L_number:=1+1;
Dbms_output.put_line(‘1+1’||to_char(L_number)||’!’);
Exception
When others then
Dbms_output.put_lin e(‘We encountered an exception!’);
End;
/
２．块嵌套：程序块可以在可执行部分和异常处理部分包含另外的程序块。

例２：declare
l_text varchar2(20);
begin
l_text:=’First Block’;
Dbms_output.put_line(l_text);
declare
l_more_text varchar2(20);
begin
l_more_text:=’Second Block’;
Dbms_output.put_line(l_more_text);
End;
End;
/
四．声明
１．变量和常量的声明
i.变量的声明
declare
l_number_variable number;
begin
l_number_variable:=50;
end;
以上方式等价于：
declare
l_number_variable number:=50;
begin
null;
end;
ii.常量的声明
declare
l_number_constant constant number:=50;
begin
null;
２．变量的作用域：在嵌套程序块中，父块本身和任何嵌套块都是在父块中定义的标识符的作用域，而在子块声明中定义的标识符只有在子块本身中才处于它的作用域３．使用%TYPE和%ROWTYPE
%TYPE：声明类似字段的单独的变量，如declare l_status s.status%type;定义了象s表的status那样的字段变量。

%ROWTYPE：声明表示表、视图或游标的完整行的记录变量,如declare l_s s%rowtype;
定义了象表s那样的记录类型。

好处：
（1）用户不需在声明的时候知道数据类型
（2）如果用户正在引用的变量的数据类型发生变化，用户的%TYPE或者%ROWTYPE也
会在运行时进行改变，而用户不必重写用户的变量声明。

五．PL/SQL集合
在大多数编程语言中，提供这样或者那样的方式来声明对象集合都很有必要。

PL/SQL中也是如此，它具有可以用于这一目的的大量集合类型。

它们是：
●记录：在称为记录的单独集合中可以存储一对多的标量属性
●PL/SQL表：是可以在用户的PL/SQL代码中使用的“表”，只存在用户应用运行期间，
非常类似于其他语言中的数组，但不能够存储在数据库表中。

●V ARRAY：能够在表列中存储的集合，通常用于存储小集合的固定大小的集合。

●NESTED TABLE：另一种可以在表列中存储的集合，大小可变，适用于用户不知道集
合大小，或者用户知道它要包含大量数据的时候。

e)记录
例３：SQL>set serverout on
SQL>declare
Type location_record_type is record(
Streent_address varchar2(40),
Postal_code varchar2(12),
City varchar2(30),
State_province varchar2(25),
Country_id char(2) not null :=’US’
);
l_my_loc location_record_type;
begin
l_my_loc.street_address:=’1 Oracle Way’;
l_my_loc.postal_code:=’20190’;
l_my_loc.city:=’Reston’;
l_my_loc.state_province:=’V A’;
dbms_output.put_line(‘MY LOCA TION IS:’);
dbms_output.put_line(l_my_loc.street_address);
dbms_output.put_line(l_my_loc.city||’,’||l_my_loc.state_province);
dbms_output.put_line(‘ ‘||l_my_loc.postal_code);
dbms_output.put_line(l_my_loc.country_id);
end;
/
3.5 实验五 PL/SQL编程（二）
一．PL/SQL表：有时也称为索引表，是用户可以在PL/SQL例程中使用，能够模仿数组的非永久表。

用户可以定义一个PL/SQL表类型，然后声明这种类型的变量。

接下来，用户就可以将记录增加到用户的PL/SQL表中，并且采用与引用数组元素大体相同的方式引用它们。

1．定义以及引用
例1：
declare
type my_text_table_type is table of varchar2(200)
index by binary_integer;
l_text_table my_text_table_type;
begin
l_text_table(1):='Some varchar2 value';
l_text_table(3):='Another varchar2 value';
dbms_output.put_line('We have '||l_text_table.count||' varchar2''s');
dbms_output.put_line('-');
dbms_output.put_line('vc2(1)='||l_text_table(1));
dbms_output.put_line('vc2(2)='||l_text_table(3));
end;
/
注意：索引算子是变量名称后面的括号中的数值。

它会通知PL/SQL解析器，我们正在谈论由这个值唯一标识的记录，它必须是合法的Binary_integer，但不一定是连续整数，这一点不同于数组下标。

2．PL/SQL中的记录，具体要用到以下几个方法：
i.l_text_tables.delete(i)：删除索引算子I所标识的表项。

ii.l_text_tables.delete：删除所有表项。

iii.l_text_tables:=l_empty_table：把Null表赋给l_text_table，等价于.delete 例2：
declare
type my_text_table_type is table of varchar2(200)
index by binary_integer;
l_text_table my_text_table_type;
l_empty_table my_text_table_type;
begin
l_text_table(10):='A value';
l_text_table(20):='Another value';
l_text_table(30):='Yet another value';
dbms_output.put_line('We start with '||l_text_table.count||' varchar2''s');
dbms_output.put_line('-');
l_text_table.delete(20);
dbms_output.put_line('After using the DELETE operator on the second record,');
dbms_output.put_line('we have '||l_text_table.count||' varchar2''s');
dbms_output.put_line('-');
l_text_table.delete;
dbms_output.put_line('After using the DELETE operator,');
dbms_output.put_line('we have '||l_text_table.count||' varchar2''s');
dbms_output.put_line('-');
l_text_table(15):='Some text';
l_text_table(25):='Some more text';
dbms_output.put_line('After some assignments,');
dbms_output.put_line('we end up with '||l_text_table.count||' varchar2''s');
dbms_output.put_line('-');
l_text_table:=l_empty_table;
dbms_output.put_line('Once we assign our populated to an empty table,');
dbms_output.put_line('we end up with '||l_text_table.count||' varchar2''s');
end;
/
3.First、next和last
1.First：返回PL/SQL表的“第一个”或者最小的索引
st：返回PL/SQL表的“最后一个”或者最大的索引
3.Next：返回PL/SQL表的下一个索引
例3：（需用scott/tiger@myoracle登录）
declare
type my_text_table_type is table of varchar2(200)
index by binary_integer;
l_text_table my_text_table_type;
l_index number;
begin
for emp_rec in(select * from emp)loop
l_text_table(emp_rec.empno):=emp_rec.ename;
end loop;
l_index:=l_text_table.first;
loop
exit when l_index is null;
dbms_output.put_line(l_index||':'||l_text_table(l_index));
l_index:=l_text_table.next(l_index);
end loop;
/
二．V ARRAYS：是能够在用户的表列中存储的PL/SQL集合。

当用户建立V ARRAYS的时候，用户必须为它提供最大的规模。

例4：
create type employee_type as object(
employee_id number,
first_name varchar2(30),
last_name varchar2(30)
);
/
create type employee_list_type as varray(50) of employee_type
/
create table departments(
department_id number,
department_name varchar2(30),
manager employee_type,
employees employee_list_type)
/
insert into departments(department_id,
department_name,
manager,
employees)
values(10,
'Accounting',
employee_type(1,'Danielle','Steeger'),
employee_list_type(
employee_type(2,'Madison','Sis'),
employee_type(3,'Robert','Cabove'),
employee_type(4,'Michelle','Sechrist'))
)
/
insert into departments(department_id,
department_name,
manager,
employees)
values(20,
'Research',
employee_type(11,'Ricky','Lil'),
employee_list_type(
employee_type(12,'Ricky','Ricardo'),
employee_type(13,'Lucy','Ricardo'),
employee_type(14,'Fred','Mertz'),
employee_type(15,'Ethel','Mertz'))
)
/
column department_name format a13
column employees format a63 word_wrapped
select department_name,employees
from departments
/
三．Nested table：与索引表相似，区别是嵌套表可以存储在数据库的列中，而索引表则不行。

四．游标：是构建在PL/SQL中，用来查询数据库，获取记录集合的指针，它可以让开发者一次访问一行结果集，即可以让我们以编程方式访问数据，最常使用的类型是显式游标和隐式游标。

例5：（显式游标）
SQL>connect scott/tiger@myoracle；
declare
cursor emp_cur(p_deptno in number)
is select *
from emp
where deptno=p_deptno;
l_emp emp%rowtype;
begin
dbms_output.put_line('Getting employees for department 30');
open emp_cur(30);
loop
fetch emp_cur into l_emp;
exit when emp_cur%notfound;
dbms_output.put_line('Employee no '||l_emp.empno||' is '||l_emp.ename);
end loop;
close emp_cur;
dbms_output.put_line('Getting employees for department 20');
open emp_cur(20);
loop
fetch emp_cur into l_emp;
exit when emp_cur%notfound;
dbms_output.put_line('Employee no '||l_emp.empno||' is '||l_emp.ename);
end loop;
close emp_cur;
end;
/
例6：（隐式游标）
declare
begin
for my_dept_rec in(select deptno,dname
from dept
order by 1)
loop
dbms_output.put('Department #'||my_dept_rec.deptno);
dbms_output.put_line(' is named '||my_dept_rec.dname);
end loop;
end;
/
3.6 实验六 PL/SQL编程（三）
一．游标属性：可以返回SQL语句执行的元数据
1．%FOUND：指出了当PL/SQL代码最后从游标的结果集中获取记录的时候，找到了记录。

2．%NOTFOUND：指出当PL/SQL代码最后从游标中获取记录的时候，在结果集中没有记录。

3．%ROWCOUNT：返回当前时刻已经从游标中获取的记录数量。

4．%ISOPEN：当游标已经打开，还没有关闭的时候，就会返回TRUE。

该操作符适用于用户代码要基于逻辑，有条件地打开和关闭游标的情况。

例1：
declare
cursor emps
is select *
from emp
where rownum<6
order by 1;
employee emp%rowtype;
row number:=1;
begin
open emps;
fetch emps into employee;
loop
if emps%FOUND then
dbms_output.put_line('Looping over record '||row||' of'||emps%ROWCOUNT);
fetch emps into employee;
row:=row+1;
elsif emps%NOTFOUND then
exit;
end if;
end loop;
if emps%ISOPEN then
close emps;
end if;
end;
二．控制语句
1．IF条件语句：有以下三种不同的用法：
(1) IF expression1 THEN
plsql_statement1;
END IF;
(2) IF expression1 THEN
plsql_statement1;
ELSE
plsql_statement2;
END IF;
(3) IF expression1 THEN
plsql_statement1;
ELSIF expression2 THEN
plsql_statement2;
ELSIF expression3 THEN
plsql_statement3;
…
ELSE
plsql_statement2;
END IF;
例2：
set echo off
set define '&'
set verify off
set serverout on size 10000
prompt
accept NUM prompt 'Enter a single digit number:' 5
prompt
declare
l_num number:=&NUM;
begin
if l_num=1 then
dbms_output.put_line('You selected one');
elsif l_num=2 then
dbms_output.put_line('You selected two');
elsif l_num=3 then
dbms_output.put_line('You selected three');
elsif l_num=4 then
dbms_output.put_line('You selected four');
elsif l_num=5 then
dbms_output.put_line('You selected five');
elsif l_num=6 then
dbms_output.put_line('You selected six');
elsif l_num=7 then
dbms_output.put_line('You selected seven');
elsif l_num=8 then
dbms_output.put_line('You selected eight');
elsif l_num=9 then
dbms_output.put_line('You selected nine');
elsif l_num=0 then
dbms_output.put_line('You selected zero');
else
dbms_output.put_line('You selected more than one digit...');
end if;
end;
2．CASE语句（Oracle 9i 中才有）
例3：
set echo off
set define '&'
set verify off
set serverout on size 10000
prompt
accept NUM prompt 'Enter a single digit number:'
5
prompt
declare
l_num number:=&NUM;
begin
case l_num
when 1 then dbms_output.put_line('You selected one');
when 2 then dbms_output.put_line('You selected two');
when 3 then dbms_output.put_line('You selected three');
when 4 then dbms_output.put_line('You selected four');
when 5 then dbms_output.put_line('You selected five');
when 6 then dbms_output.put_line('You selected six');
when 7 then dbms_output.put_line('You selected seven');
when 8 then dbms_output.put_line('You selected eight');
when 9 then dbms_output.put_line('You selected nine');
when 0 then dbms_output.put_line('You selected zero');
else dbms_output.put_line('You selected more than one digit...');
end case;
end;
3．循环语句
（1）无条件循环：要有EXIT语句才能退出循环
例4：（利用条件语句退出循环）
declare
l_loops number:=1;
begin
dbms_output.put_line('Before my loop');
loop
if l_loops>4 then
exit;
end if;
dbms_output.put_line('Looped '||l_loops||' times');
l_loops:=l_loops+1;
end loop;
dbms_output.put_line('After my loop');
end;
/
例5：（基于评估来退出循环）
declare
l_loops number:=1;
begin
dbms_output.put_line('Before my loop');
loop
exit when l_loops>4;
dbms_output.put_line('Looped '||l_loops||' times');
l_loops:=l_loops+1;
end loop;
dbms_output.put_line('After my loop');
end;
/
（2）FOR循环：循环计数器是局域变量，它的作用域是循环处理的期间。

例6：
declare
l_names dbms_sql.varchar2_table;
begin
l_names(1):='Whitney';
l_names(2):='Jordan';
l_names(3):='Cameron';
for idx in 1..l_names.COUNT loop
dbms_output.put_line('Names('||idx||') is '||l_names(idx));
end loop;
end;
/
（3）WHILE循环
例7：
declare
l_loops number:=0;
begin
dbms_output.put_line('Before my loop');
while l_loops<5 loop
dbms_output.put_line('Looped '||l_loops||' times');
l_loops:=l_loops+1;
end loop;
dbms_output.put_line('After my loop');
end;
/
*循环语句注意点：
（1）FOR循环至少要执行一次循环语句内部的代码，而WHILE循环可能根本不会执行循环语句的内容。

（2）当用户需要的循环预先知道，或者不会在循环语句执行期间发生改变时，FOR要比条件循环和WHILE循环更有效率。

3.7 实验七 PL/SQL编程（四）
一．概念和优势
１．什么是过程、函数和程序包？
开发者能够命名他们的PL/SQL程序块，为它们确定参数，将它们存储在数据库中，并且从任何数据库客户或者实用工具中引用或者运行它们。

这些命名的PL/SQL程序称为存储过程和函数。

它们的集合称为程序包。

２．优势和利益
（1）可扩展性：编写用户自己的例程可以让用户灵活地扩展数据库的核心能力。

（2）模块化：用户可以通过编写小型、可管理地过程在用户应用中定义单独的处理模块，它们组织在一起形成更复杂的应用。

小型的分离的过程要比复杂的过程更容易修改，并且
也可以更加有效地共享。

（3）可重用性：由于这些例程命名后保存在数据库中，所以任何应用都能够执行它们（只要它们具有合适的权限完成这项工作）。

这种重用代码的能力在开发过程中非常关键。

（4）可维护性。

（5）抽象和数据隐藏：过程和函数对用户来说是个黑箱，用户所需知道的就是它能够完成的工作。

（6）安全性：用户可以设置用户应用，使得访问数据的唯一方式就是通过用户提供的过程和函数。

二．过程
存储过程从根本上讲就是命名的ＰＬ／ＳＱＬ程序块，它可以被赋予参数，存储在数据库中，然后由另一个应用或者ＰＬ／ＳＱＬ例程激活（或者调用）。

例１：
create or replace procedure my_proc as
begin
dbms_output.put_line('Hello World');
end my_proc;
/
set serverout on
begin
my_proc;
end;
/
１．语法：
[create [or replace]]
PROCEDURE procedure_name[(parameter[,parameter]…)]
[AUTHID {DEFINER|CURRENT_USER}]{IS|AS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
[local declarations]
BEGIN
Executable statements
[EXCEPTION
exception handlers]
END [name];
２．执行存储过程
（１）从ＰＬ／ＳＱＬ匿名程序块中调用
（２）execute my_proc;
３．安全：存储过程存储在数据库中，属于数据库对象，具有Execute特权。

将过程上的Execute 特权赋予用户或者角色，所有这些实体有能力运行它；将它赋予角色Public可以让所有用户都可以使用这个过程。

４．参数：过程可以进行参数化处理，即过程的调用者传递一个值即可使用它。

参数可以是任何合法的ＰＬ／ＳＱＬ类型，可采用以下三种模式：
（１）ＩＮ参数：要通过调用者传入，只能够由过程读取，是只读值，不能由过程所
改变，是参数行为中最常用的模式，也是没有给出参数模式时的默认模式。

ＩＮ参数可以有默认值。

例２：
create table t(
n number
)
/
create or replace procedure insert_into_t(
p_parm1 in number,
p_parm2 in number)is
begin
insert into t values(p_parm1);
insert into t values(p_parm2);
end insert_into_t;
/
exec insert_into_t(p_parm1=>101,p_parm2=>102);
select * from t
/
a)参数传递：
（a）使用名称传递法：如例２
(b)使用位置表示法：基于参数在过程中定义的次序进行传递
(c)使用混合表示法：将名称表示法和位置表示法相混合
（２）ＯＵＴ参数：能够由过程写入，适用于过程需要向调用者返回多条信息的时候。

ＯＵＴ参数不能是具有默认值的变量，也不能是常量或表达式，必须向ＯＵＴ参数传递返回值。

例３：
connect scott/tiger
create or replace procedure emp_lookup(
p_empno in number,
o_ename out emp.ename%type,
o_sal out emp.sal%type ) as
begin
select ename,sal
into o_ename,o_sal
from emp
where empno=p_empno;
exception
when NO_DA TA_FOUND then
o_ename:='NULL';
o_sal:=-1;
end emp_lookup;
/
set serverout on
declare
l_ename emp.ename%type;
l_sal emp.sal%type;
begin
emp_lookup(7782,l_ename,l_sal);
dbms_output.put_line('Ename='||l_ename);
dbms_output.put_line('Sal='||l_sal);
end;
/
（３）ＩＮＯＵＴ参数：同时具有ＩＮ参数和ＯＵＴ参数的特性。

例４：
create or replace procedure swap(
p_parm1 in out number,
p_parm2 in out number) as
l_temp number;
begin
l_temp:=p_parm1;
p_parm1:=p_parm2;
p_parm2:=l_temp;
end swap;
/
declare
l_num1 number:=100;
l_num2 number:=101;
begin
swap(l_num1,l_num2);
dbms_output.put_line('l_num1='||l_num1);
dbms_output.put_line('l_num2='||l_num2);
end;
/
三．函数：与过程相似，也是数据库中存储的命名的ＰＬ／ＳＱＬ程序块，主要特性是它必须返回一个值。

例５：
create or replace function ite(
p_expression boolean,
p_true varchar2,
p_false varchar2) return varchar2 as
begin
if p_expression then
return p_true;
end if;
return p_false;
end ite;
/
exec dbms_output.put_line(ite(1=2,'Equal','Not Equal'));
exec dbms_output.put_line(ite(2>3,'True','False'));
四．程序包：这种结构可以让用户从逻辑上组织过程、函数、对象类型以及放入单独的数据库对象中的各种内容。

程序包通常由两部分组成：规范和主体。

规范是程序包的公共接口，主体包含了规范的实现以及所有私有例程、数据和变量。

例６：
create or replace
package employee_pkg as
procedure print_ename(p_empno number);
procedure print_sal(p_empno number);
end employee_pkg;
/
create or replace
package body employee_pkg as
procedure print_ename(p_empno number)is
l_ename emp.ename%type;
begin
select ename
into l_ename
from emp
where empno=p_empno;
dbms_output.put_line(l_ename);
exception
when NO_DA TA_FOUND then
dbms_output.put_line('Invalid employee number');
end print_ename;
procedure print_sal(p_empno number)is
l_sal emp.sal%type;
begin
select sal
into l_sal
from emp
where empno=p_empno;
dbms_output.put_line(l_sal);
exception
when NO_DA TA_FOUND then
dbms_output.put_line('Invalid employee number');。