实验五 存储子程序与触发器的设计
一、实验目的
1． 理解存储子程序与触发器的作用
2． 掌握存储子程序与触发器的创建、调试和使用 二、实验环境
一台计算机，安装了WINDOWS 2000 SERVER和Oracle 9i。Oracle帐户（system/123, sys/123, scott/tiger） 三、预备知识 1、存储子程序
存储子程序是指被命名的PL/SQL块，以编译的形式存储在数据库服务器中，可以在应用程序中进行调用，是PL/SQL程序模块化的一种体现。创建存储子程序要求用户有CREATE PROCEDURE 或CREATE ANY PROCEDURE 权限。 ? 创建存储子程序
创建存储子程序的基本语法为：
CREATE [OR REPLACE] PROCEDURE procedure_name (parameter1_name [mode] datatype [DEFAULT|:=VALUE]
[,parameter2_name [mode] datatype [DEFAULT|:=VALUE],…]) AS|IS
声明部分 BEGIN
执行部分 EXCEPTION 异常部分
END[procedure_name]; ? 存储子程序的调用
? 在SQL*Plus中调用 例：execute show_emp(10) ? 在PL/SQL程序中调用 例：DECLARE
v_avgsal emp.sal%type; v_count NUMBER:
BEGIN
show_emp(20);
return_deptinfo(10,v_avgsal,v_count);
DBMS_OUTPUT.PUT_LINE(v_avgsal||? ?||v_count); END;
使用存储过程大大增强了SQL 语言的功能和灵活性，并可用来保证数据的安全性和完整性：通过存储过程可以使没有权限的用户间接地存取数据库，从而保证数据的安全。 通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。
11
2、触发器
触发器是一种特殊的存储过程，在插入、删除或修改特定表中的数据时触发执行，比数据库本身标准的功能有更精细和更复杂的数据控制能力。可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据；可以基于数据库中的数据限制用户的操作，例如不允许股票 的价格的升幅一次超过10%；审计用户操作数据库的语句，把用户对数据库的更新写入审计表；实现复杂的数据完整性规则；实现非标准的数据完整性检查和约束，例如，规定某一列的取值范围，提供可变的缺省值；实现复杂的非标准的数据库相关完整性规则，对相关的表进行连环更新；另外还具有报警功能，例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。
? 触发器的类型
（1）DML触发器：建立在基本表上的触发器，响应基本表的INSERT、UPDATE、DELETE操作。 创建的语法为：
CREATE [OR REPLACE] TRIGGER trigger_name BEFORE|AFTER triggering_event [OF COLUMN_NAME] ON table_name [FOR EACH ROW]
[WHEN trigger_condition] DECLARE 声明部分 BEGIN 执行部分 EXCEPTION 异常部分 END[trigger_name];
（2）INSTEAD OF触发器：建立在视图上的触发器，响应视图上的INSERT、UPDATE、DELETE操作。
（3）系统触发器：建立在系统或模式上的触发器，响应系统事件的DDL（CREATE、ALTER、DROP）操作。
? 触发器的组成
触发器由触发器头部和触发器体两个部分组成，主要包括以下参数： （1）作用对象：触发器作用的对象包括表、视图、数据库和模式。
（2）触发事件：激发触发器执行的事件。如DML、DDL、数据库系统事件等。 （3）触发时间：用于指定触发器在触发事件完成之前还是之后执行。
（4）触发级别：触发级别用于指定触发器响应触发事件的方式。默认为语句级触发器，即触发事件发生后，触发器只执行一次。如果指定为FOR EACH ROW，即为行级触发器，则触发事件每作用于一个记录，触发器就会执行一次。
（5）触发条件：由WHEN子句指定一个逻辑表达式，当触发事件发生，而且WHEN条件为
12
TRUE时，触发器才会执行。
（6）触发操作：触发器执行时所进行的操作。
四、实验内容：
1、定义存储过程，带一个学号参数，功能为：统计指定学号学生新选的课程数量（新选课程为选课表sc中grade字段为空的记录项）。 2、测试刚建的存储过程的功能
3、定义一个行级触发器，当出现删除student表记录的事件时触发，功能为将删除的学生记录保存到一个表bakstudent（sno,sname,sex,birthday,dno,deletedate），其中deletetime（类型为date）字段保存删除操作发生的日期；表bakstudent不存在任何完整性约束。 4、测试刚建的触发器的功能。
13
实验六 分布式数据库操作
一、实验目的
理解Oracle数据库分布式的特点，掌握分布式数据库的一些操作方法。 二、实验环境
假设这样一种应用，有一个公司，公司下有A和B两个分公司，A所有员工信息保存在一台Oracle数据库服务器上（实验中为本机，以下称为A），B所有员工信息保存在另一台Oracle数据库服务器上（以下称为B，具体IP实验时定），现需要同时对两个地方的信息进行修改（如加工资）。 三、预备知识
Oracle数据库管理系统具有分布式数据库功能，分布式数据库的概念不仅仅是能够访问多个数据库，而且多个数据库能够协同工作，维护全局数据库的一致性。Oracle数据库管理系统的分布式数据库功能具有以下特性：
支持分布式查询； 支持分布式事务管理；
具有硬件独立性：安装Oracle 服务器的硬件没有限制； 具有操作系统独立性：在多种操作系统下都能运行； 具有网络独立性：支持多种网络之间的通信。
要访问远程数据库必须通过数据库链，要建立数据库链，必须先在客户端系统配置文件tnsname.ora中建立一个连接串（连接描述符，网络服务名），连接串代表三方面的内容： 1、网络协议（如TCP/IP）
2 、远程数据库服务器的计算机名或网络地址（如IP地址：202.101.210.3) 3、远程数据库名
利用连接串，通过SQL语句建立数据库链，建立数据库链的语法如下：
CREATE DATABASE LINK 数据库链名
CONNECT TO 用户名 IDENTIFIED BY 口令 USING ?连接串名?；
例如：
create database link sales
connect to scott identified by tiger using ?oracle2?;
可以省略CONNECT子句不指定用户账户，系统会把登录账户作为默认的用户账户。
?访问远程数据库
select * from emp@sales;
select ename,tel from dept,emp@sales where dept.dno=emp.dno; update emp@sales set sal=sal+200 where eno=?e01?; insert into emp@sales
14
select * from emp where dno=?d01?; delete from emp@sales where age>60;
**仅仅在需要提供表名的地方需要加 “@数据库链名”
?使用同义词实现位置透明性，同义词是数据库对象，可以为数据库对象（表、视图、序列或其它
同义词等）定义别名。
CREATE SYSNONYM emp1 FOR emp@sales; 那么
select * from emp1； 相当于
select * from emp@sales;
四、实验内容
1． 在本计算机中添加一个Oracle数据库网络服务名“orcacle2”(该网络服务名对应的HOST为教
师指定，可以有两种方式，第一种为直接修改本计算机上的tnsname.ora文件。如下图
改成本机计算机名 改成教师指定计算机名
另一种方式为使用Oracle Net Configuration Assistant。
2． 启动一个SQL*Plus，使用scott/tiger用户账户和服务名orcacle2登录B计算机上的数据库，在
scott模式下以同上的命令创建员工表emp02 (在实验创建表的时候表名后加上自己的学号)，并插入一些数据；
3． 启动另一个SQL*Plus（以下所有操作步骤4~8都在这个sql*plus中完成），使用scott/tiger用户
账户和服务名orcacle登录本计算机上的Oracle数据库，在scott模式下创建员工表emp01，再向表emp01中插入一些数据；
15
create table scott.emp01(eno varchar2(6) primary key, ename varchar2(6), birthday date, sal number(5),
deptno varchar2(4));
4． 创建数据库链：
create database link b02link --b02link为数据库链名（属于scott的模式对象） connect to scott identified by tiger using ?orcacle2?;
5． 用SQL语句通过数据库链b02link操作计算机B上的表。 Select * from emp02@b02link;
Insert into emp02@b02link values(………); Update emp02@b02link set ………… Delete from emp02@b02link where …………
这里举例为操作scott模式下的emp02表，实际应用中也可以是其它的表，这要看有没有操作权限，而有没有权限是看创建的数据库链中对应的用户账户在目标服务器中是否有权限。 6． 创建同义词
create synonym b02emp --b02emp为同义词名（属于scott的模式对象）
for emp02@b02link --（记得在emp02后加上你的学号，因为前面创建这张表的时候加了学号）
7． 再用SQL语句来操作B计算机上scott模式下的emp表 select * from b02emp; insert into b02emp ……; update b02emp …….; delete from b02emp ……;
8． 按照下面的语句编制存储过程raise_salary，功能为给公司的每个员工加工资，再编制调用
raise_salary的PL/SQL程序，然后再用查询语句验证两台计算机上的数据都做了正确的修改。 Create or replace procedure raise_salary(increase number) as Begin
Update emp01 set sal=sal+increase; Update bo2emp set sal=sal+increase; Commit; End;
16