《Oracle数据库》实验指导书
更新时间：2024-04-07 13:06:01 阅读量： 综合文库 文档下载
武汉科技大学
计算机科学与技术学院
Oracle数据库实验指导书
2010-2011第二学期
2011年2月
实验一 SQL*PLUS练习 （2学时）
【实验目的】 (1)了解Oracle的工作环境和基本使用方法。
(2) 练习标准SQL的数据操作，查询命令及其查询优化。 (3)学会使用高级SQL命令，排序、分组、自连接查询等。 (5)学会使用SQL*PLUS命令显示报表，存储到文件等。 【实验内容】 一、 准备使用SQL*PLUS 1. 进入SQL*PLUS 2. 退出SQL*PLUS
3. 显示表结构命令DESCRIBE SQL>DESCRIBE emp
使用DESCRIBE（缩写DESC）可以列出指定表的基本结构，包括各字段的字段名以及类型、长度、是否非空等信息。
4. 使用SQL*PLUS显示数据库中EMP表的内容 输入下面的查询语句： SQL>SELECT * FROM emp; 按下回车键执行查询 5. 执行命令文件
START或@命令将指定文件调入SQL缓冲区中，并执行文件内容。 SQL>@ 文件名（文件后缀缺省为.SQL）或 SQL>START 文件名
文件中每条SQL语句顺序装入缓冲区并执行。 二、 数据库命令——有关表、视图等的操作 1. Oracle创建表的命令格式 CREATE TABLE <表名>
(<列名1><数据类型>[DEFAULT 表达式][列约束]
[,<列名2><数据类型>[DEFAULT 表达式][列约束],?] );
“表名”是所要建立的表的名字。作为表名、列名的标识符的长度不能超过30个字符，且必须以字母开头，字母大、小写不分。表名、列名标识符不要与Oracle保留字相同。对同一个用户，表名必须惟一；在同一个表中，列名也必须惟一。
例1 定义一个人事信息管理系统中存放职工基本信息的一张表。可输入如下命令： SQL>CREATE TABLE employee
(empno number(6) PRIMARY KEY, /* 职工编号 name varchar2(10) NOT NULL, /* 姓名 deptno number(2) DEFAULT 10, /* 部门号 salary number(7,2) CHECK(salary<100000), /*工资 birth_date date, /*出生年月 soc_sec_num char(9) UNIQUE, /*内部序列号 foreign key(deptno) references dept(deptno));
按回车键，执行上述语句，即可创建表Employee
2
2. 复制表结构命令
复制表结构命令的一般格式为： CREATE TABLE <复制表名> as
例2 复制一个与emp表的表结构相同的新空表emp2. 可输入如下命令：
SQL>create table emp2 as select * from emp where 1=2;
在命令的where子句中给出1=2，表示条件不可能成立，因而只能复制表结构，而不能复制任何数据到新表中去。另外，还可以复制一个表的部分列定义或部分列定义及其数据。参考课本P62例3. 3. 删除表的命令
DROP TABLE <表名>; /*删除表 三、 Oracle数据库数据查询 查询命令的基本格式是： SELECT <列名表 或*> FROM <表名 或 视图名> [WHERE <条件>]
[GROUP BY <分组内容>] [HAVING <组内条件>] [ORDER BY 排序方式]
1. 单表查询数据操作 1) 无条件查询（在查询命令中缺省WHERE子句） 2) 条件查询 3) 排序显示查询结果（ORDER BY子句） 4) 分组查询
使用SELECT命令，可以方便地对数据库进行分组查询。分组查询就是将数据库表中的数据按一定条件进行分类组合，再根据需要得到统计信息。 下面给出在分组查询中经常用到的几个分组函数的功能说明。
count(<表达式>) 计算一组行中“表达式”的值为非空值的行数。 count(*) 计算表中的全部行数，包括重复行和空行。 avg(n) 计算一组行中n值的平均值 min(<表达式>) 计算一组行中“表达式”值的最小值 max(<表达式>) 计算一组行中“表达式”值的最大值 sum(n) 计算一组行中n值的总和。
例3 计算emp表中的最低工资、最高工资、平均工资和总工资和。 SQL>select min(sal),max(sal),avg(sal),sum(sal) from emp; 例4 计算emp表中公司的总人数及共总数。 SQL>select count(*) ,count(distinct job) from emp; 例5 计算公司支付给每个工种的总工资。 SQL>select job,sum(sal) from emp
3
group by job;
在本例中，先按部门号将职工分组，在同一部门中再按职工的工种进一步分组。但要注意,在包含“group by”子句的查询命令中，select子句后的列明表中，除统计函数外，均应包含在“group by”子句中，否则出错。
5) 带条件的分组查询
利用“group by”子句将选择到的的行进行分组时，还可以使用having子句用于限制选择的组，having子句的作用同where子句相似，都是指定查询条件。不同的是where子句对行进行选择，检查每条记录时候满足条件，而having子句检查分组之后的各组是否满足条件。
带条件的分组查询命令的基本格式为： SELECT <列名表>
FROM <表名 或 视图名> WHERE <条件>
GROUP BY <分组内容> HAVING <组内条件>
例6 查询各工种组的年平均工资，要求每组至少在2人以上。 SQL>select job,count(*),12*avg(sal) avgsal from emp group by job having count(*)>2; 2. 多表查询命令操作 1) 等值连接的查询
例7 查找名字为?ALLEN?的职工所在的部门号、部门名和部门所在地。 SQL>select ename,emp,deptno,dname,loc from emp,dept
where ename=?ALLEN? and emp.deptno=dept.deptno; 2) 自连接的查询
表的连接操作，不仅可以对几个不同的表进行，而且对同一个表也可以进行自身连接，即将同一个表的不同行连接起来。自连接可以看作是一个表的两个副本之间的连接。 例8 查找出职工号与职工的经理编号相同的职工的名字（即每个职工的经理） SQL>select worker.ename employee,manager.ename manager from emp manager,emp worker
where manager.empno= worker.mgr; 四、 SQL*PLUS常用命令
表1 常用报表格式化名命令
命令 Btitle Column Compute Remark Set linesize Set newpage Spool Start Ttitle
定义 为报表的每一页设置底端标题 设置列的标题和格式 让SQL*PLUS计算各种值 将某些字标记为注释 设置报表的行宽字符数 设置报表各页之间的行数 使SQL*PLUS将输出写入文件中 使SQL*PLUS执行一个sql文件 设置报表每页的头标题 4
Break 让SQL*PLUS进行分组操作 例9 建立一个批命令文件对查询到的数据以报表的形式输出并将其保存到指定的文件中。 处理方法：利用SQL*PLUS语言工具（也可以使用其他文本编辑器）建立批命令的.SQL文件。在“SQL>”提示符下，使用EDIT命令在”E:\\”中建立SCGB.SQL文件。 SCGB.SQL文件中的命令组如下： SQL>EDIT E:\\ SCGB.SQL SET echo off SET pagesize 30 SET linesize 75
TTITLE?2008年4月10号?CE?公司职员基本情况登记表?R?Page:? FORMAT 99- >SQL.PNO SKIP 1 CE?===========================? BTITLE COL 60 ?制标单位? TAB 3 ?人事部? COLUMN empno heading ?职工|编号?
COLUMN ename format a10 heading ?姓 名? COLUMN job heading ?工 种?
COLUMN sal format $99,990 heading 工 资? COLUMN comm Like sal heading ?奖 金?
COLUMN deptno format 9999 heading ?部门|编号? COLUMN hiredate heading ?参加工作时间?
SPOOL e:\\sjbb /*在E盘中建立格式报表输出文件，默认属性为LST BREAK on deptno skip 1
COMPUTE sum of sal comm on deptno
SELECT empno,ename,job,hiredate,sal,comm,deptno from emp
ORDER BY deptno,sal;
SPOOL off /*终止SPOOL功能，关闭其文件。注意，此命令不可省，
否则将建立空文件。
五、 实验内容
1、以cs+学号为用户名创建用户，并授予用户创建数据对象的权限。 2、复制emp表，复制表名为emp_学号，然后将emp表中工资低于$2000 的职工插入到复制的表中。
3、对复制的emp表插入一行只包含有职工号,职工名,工资与部门号四个数据 项值的记录。
4、在复制的emp表中将雇员ALLEN提升为经理，工资增至＄2500， 奖（佣 ）金增加40%。
5、删除复制的emp表中工资低于500的记录行。
6、列出10号部门中既不是经理，也不是秘书的职工的所有信息。 7、查找出部门所在地是CHICAGO的部门的职工姓名、工资和工种。 8、统计各部门中各工种的人数、工资总和及奖金总和。
9、查找出工资比其所在部门平均工资高的职工姓名、工种与工资情况。
10、创建一个批文件，制作一张职工情况登记表，并保存到一个磁盘文件中。 要求：①有表头、表尾及制表时间。
②登记表中包括姓名、工种、工资、部门名称和部门所在地。
5
实验二 Oracle数据库开发环境下PL/SQL编程 （2学时）
【实验目的】
(1)掌握 PL/SQL 的基本使用方法。
(2)在SQL*PLUS环境下运行PL/SQL的简单程序。 (3)应用 PL/SQL 解决实际问题 【实验内容与步骤】 一、 Oracle PL/SQL概述
PL/SQL 是ORACLE 的过程化语言，为 SQL 语言提供了过程化运行环境，使对数据库的操作获得了更好的性能。 1、PL/SQL的基本结构
PL/SQL是一种块结构语言，即构成一个 PL/SQL 程序的基本单位(过程、函数和无名块）是逻辑块。程序块可以嵌套。该程序结构支持自上而下逐步求精的方法解决问题。一个块（或子块）将逻辑上相关的说明和语句组合在一起。
一个基本的PL/SQL程序块，由三部分组成：说明部分、可执行部分和异常处理部分。 语法：
DECLARE
． ． ． －－ 说明 BEGIN
． ． ． －－ 语句序列 EXCEPTION
． ． ． －－ 异常处理程序 END； 说明部分：
以DECLARE 关键字开始。在此可对当前块执行部分所用变量、常量、游标，异常处理名等进行说明。还可说明记录和PL/SQL表。PL/SQL 程序要使用的所有定义都必须在声明部分中进行集中定义。 说明部分可选。 执行部分：
以BEGIN开始， 是当前块运行时被执行的代码。 可使用 SQL 的 DML语句， 事务控制语句， PL/SQL的控制结构等。如：条件控制、循环控制及顺序控制构造。PL/SQL 中引入游标结构，利用它命名一专用的 SQL 工作区，可存取它所存储的信息。 异常处理部分：
异常处理部分以 EXCEPTION关键字开始，处理执行过程中发生的异常（正常执行过程中未预料到的事件） 。仅当发生了错误时才执行异常部分的代码。ORACLE 提供了一些系统错误，用户亦可以自己定义异常。对未做处理的异常会导致 PL/SQL 程序块非正常终止。WHEN OTHERS 用来处理所有没有明确列出的异常。若有别的异常处理，这种形式的异常处理须排在最后。
对程序执行中产生的异常进行处理，提高 PL/SQL 程序的健壮性。 异常处理部分可选。
块结束：一个 PL/SQL 程序块以 END语句加分号表示结束。 例1：
DECLARE
X number(3);
6
BEGIN
X : = 3 ; EXCEPTION
WHEN OTHERS THEN NULL; END;
说明语法：
标识符 [CONSTANT]数据类型[NOT NULL] [:=缺省值或 PL／SQL 表达式]
在 PL/SQL 中，用标识符命名程序对象和单元，但不能与保留字同名。标识符是以字母开头的字母、数字串，其长度不超过 30 个字符，大小写均可。对说明的一个常量和变量都有其数据类型。 注意：
（1）定义常量，必须有保留字CONSTANT作标志，先于类型说明符，必须赋初值。 （2）若说明的变量不许为空，必须有 NOT NULL 约束且必须其后跟有初始化子句。 （3）可利用保留字 DEFAULT 代替赋值操作符（：=）来初始化变量或常量。 （4）每行只能定义一个变量，用分号（；）表示结束。
PL/SQL 提供一组预定义的简单数据类型和组合数据类型说明。 （1）简单数据类型
简单类型类似 SQL，常用的有数值、字符、日期和布尔型。 number：数值型
其中：integer或number(n)，整数类型；real 或number(m ,n) 实数类型；
boolean：布尔型。该类型变量可存储 3 种值 true，false，null。仅可将三种值赋给一个布尔变量。但不能将 true，false 布尔常量值插入到数据库表的一列中，也不能从数据库表的一列中选择或获取列值到BOOLEAN变量。
char 字符型； varchar2 可变长字符型； date 日期型 例2：DECLARE
emp_count integer:=0;
acct_id varchar2(5) NOTNULL:=?APOO1? today date:=sysdate;
PI CONSTANT real:=3.14159; redius real:=1;
area real:=PI*redius**2; birthdate date;
valid boolean DEFAULT FALSE; BEGIN
PL/SQL和Oracle都是基于SQL的，且PL/SQL支持SQL全部数据类型。这些共享数据类型与SQL所提供的直接存取相结合，使PL/SQL与ORACLE数据字典结成一体。
说明变量类型时，可利用%TYPE属性提供一个现有变量、常量或数据库表列的数据类型，来定义一个简单变量类型。 语法：变量名基表名.列名%TYPE; 例3：cred number(7,2);
dred cred%TYPE;
用%TYPE属性说明变量在引用数据库表的列值时特别有用。 例4：my_dname [SCOTT.]dept.dname%TYPE; 使用%TYPE定义变量的优点：
7
? 无需知道数据库表列的真正数据类型。
? 当数据库表列定义改变时，在运行时自动地修改变量的数据类型。 使用%TYPE定义变量数据类型后，该变量不能使用NOT NULL约束。 （2）组合数据类型
简单类型提供一组单值的集合，每个值不可再分解。组合型数据则由若干个简单型的元素构成。
PL/SQL中，组合类型有记录和表（自行阅读与理解）。 说明记录组合型变量语法：变量名 表名％ROWTYPE；
功能：用％ROWTYPE属性说明一个记录类型变量，用来表示一个表（或视图）中的一行。该组合记录型变量可存储由表中所选择的一整行或者由一游标所获取的一整行。 例5：emp_rec emp%ROWTYPE;
定义变量中的成员与表记录行中的列有相同的名字和数据类型，但该定义不能包含初始化子句。组合变量可用于存放对表查询的结果。
例6：select * INTO emp_rec from emp where ename=’SMITH’; 组合变量中成员的引用语法：记录变量名.列名 其中，列名同数据库表的列名。
例7：emp_rec.ename:=’JOHNSON’;--赋常量值
emp_rec.sal:=1.15*emp_rec.sal;--赋表达式值 2、变量的简单输入和输出
在SQL*PLUS环境中运行PL/SQL代码时，并不显示变量中存放的任何操作结果，只显示一条错误消息或成功完成的消息。
若PL/SQL块中变量需要通过键盘输入数据，或要在显示器上显示某变量值时，可通过引用SQL*PLUS命令实现。 变量值输入
例8：SQL＞ACCEPT newdept [number|char] PROMPT ?dept:?
定义一个PL/SQL的外部变量（参数）newdept。方括号内限制变量的数据类型，如果回答与该类型不匹配，ACCEPT给出出错信息，并中止执行。（若要想改变输入的值，必须重新执行ACCEPT命令。可将此命令存入一个文件中，使用时用START或@命令启动、执行该文件。）
然后，即可在PL/SQL块内通过该参数给块内的变量赋值。 例9：SQL>SELECT dname FROM dept WHERE deptno=&newdept;
PL/SQL块中，系统通过调用内嵌包DBMS_OUTPUT中的put_Line过程来显示有关文字内容（内嵌包是在服务器上执行的一组相关的过程和函数，DBMS_OUTPUT包是ORACLE提供内装的包）。使用前须用SQL*PLUS的SET命令将系统变量serveroutput的值置为ON。 例10：SQL＞set serveroutput on SQL>BEGIN
DBMS_OUTPUT.PUT_LINE(?output from the put_line function:?); END;
将字符串参数传送到put_line过程中显示。
若是显示其它类型数据，需用TO_CHAR()函数进行转换。 3、PL/SQL中SQL查询语句的使用方法
PL/SQL块中的可执行部分是由一系列语句组成的（包括对数据库进行操作的SQL语句，PL/SQL语言的各种流程控制语句等）。在块中对数据库查询，增、删、改等对数据的操作是由SQL命令完成的。在PL／SQL块中，可以使用SQL的数据查询命令，数据操纵命令
8
和事务控制命令。可使用全部SQL函数。PL／SQL中的SQL语句，可使用SQL的比较操作等运算符。但不能使用数据定义语句。
在PL／SQL块中使用SELECT语句时注意几点： （1）SELECT语句必须含有INTO子句。
（2）INTO子句后的变量个数和位置及数据类型必须和SELECT命令后的字段名表相同。 （3）INTO子句后可以是简单类型变量或组合类型变量。
（4）SELECT语句中的WHERE条件可以包含PL/SQL块中定义的变量及表达式，但变量名不要同数据库表列名相同。
（5）在未使用显式游标的情况下，使用SELECT语句必须保证只有一条记录返回，否则会产生异常情况。
SQL语句，PL/SQL语句在块中可构成顺序结构，或由PL/SQL提供的流程控制语句组成分支或循环结构。对说明部分定义的变量除提供初始值外，在执行部分可以给赋值。但在SQL*PLUS环境中运行PL/SQL程序不能直接交互。 4、运算符：
算术运算符：＋，－，*，／，**
关系运算符：＝，<，>，!=（或<>）,>＝，<＝，IN，IS NULL，LIKE，BETWEEN 字符串运算符：||字串连接符（并置） 赋值运算符：:＝
关系运算符：NOT，AND，OR（含义同SQL） 注释：--用于单行注释，从“--开始，至本行结束。
/*?*/用于多行注释，可跨多行，PL/SQL编译器忽略注释内容。 5、条件控制语句：（IF语句） （1）IF条件THEN
语句序列； ENDIF；
（2）IF条件THEN
语句序列1； ELSE
语句序列2； ENDIF;
（3）IF条件1 THEN
语句序列1；
ELSEIF条件2 THEN 语句序列2； … [ELSE
语句序列n;] ENDIF;
其中，条件可以是逻辑变量或用逻辑运算符AND，OR，NOT连接的逻辑表达式，或关系表达式。其值只能TRUE，FALSE或NULL。语句可为任何合法的PL/SQL语句。 IF语句可以嵌套。 IF语句注意事项：
①每个IF语句都有自己的THEN，以IF开始的语句行THEN后无语句结束符（；）； ②每个IF语句块以相应的ENDIF结束；
9
③每条IF语句有且只有一个ELSE子句； 6、循环控制语句(三种形式) （1）LOOP循环 LOOP
语句序列；
[EXIT [WHEN 条件；] ] END LOOP;
语句中，若无EXIT语句，则进行无限循环；若未选EXIT中的WHEN项，则执行完语句序列后当执行到EXIT时立即退出循环；有WHEN选项，则每执行到EXIT语句，PL/SQL就对条件表达式进行计算，结果是TRUE则退出循环，否则继续执行该循环语句序列。 （2）FOR循环
FOR 计数器 IN [REVERSE] 下界..上界 LOOP 语句序列; END LOOP;
其中，计数器是控制循环次数的变量，不用说明，系统默认为整型。FOR循环语句在一指定整数范围中循环，由下界、上界两整数型表达式值确定循环次数。第一次进入FOR循环时对下界、上界整型表达式求值，以确定循环范围。此后不再重新计算。若选用REVERSE选项，则计数器从上界依次递减到下界，否则由下界递增到上界。 （3）WHILE循环
WHILE 条件 LOOP 语句序列; END LOOP;
由条件控制循环次数，先判断后执行，条件为真时执行一次循环体。然后再判断条件，为假或空时（计算条件表达式的值是FALSE或NULL）结束循环；在循环中引起异常时也结束循环。循环体可能一次也不被执行。 7.综合实例
1）简单的PL/SQL编程
例11：给职工号是7876的职工增加工资1000元，并显示该职工的职工名、工种和修改后的工资。 程序如下：
SQL> DECLARE
x_empno emp.empno%TYPE:=7876; x_sal emp.sal%TYPE; x_job emp.job%TYPE;
x_name emp.ename%TYPE; BEGIN
x_sal:=1000; update emp
set sal=sal+x_sal
where empno= x_empno; COMMIT WORK;
select ename,job,sal INTO x_ name, x_job, x_sal from emp
where empno= x_empno;
10
DBMS_OUTPUT.putline(?职工名是：?| | x_name||?工种是：?| | x_job| |?工资是：?| |to_char(x_sal)); END;
2)IF-THEN-ELSE语句
例12：修改emp表中职工SMITH的工资，若其原工资大于3000元，则加500元，否则加1000元。
PL/SQL程序如下： DECLARE
v_ename emp.ename%TYPE:=? SMITH?; v_addsal emp.sal%TYPE; v_sal emp.sal%TYPE; BEGIN
SELECT sal INTO v_sal FROM EMP WHERE ENAME= v_ename;
IF v_sal>3000 THEN v_addsal:=500; ELSE
v_addsal:=1000; END IF;
UPDATE EMP
SET sal=sal+ v_addsal
WHERE ENAME= v_ename; COMMIT WORK; END; 二、 实验内容：
1、用PL/SQL实现：输入eno的值，显示emp表中对应记录的内容。
2、用PL/SQL完成：读入三个数，计算并输出它们的平均值及三个数的乘积。
3、对职工表emp中的雇员SCOTT提高奖金，若工种为MANAGER,则奖金提高其原来的20%;若工种为SALESMAN,则奖金提高其原来的15%;若工种为ANALYST,则奖金提高其原来的10%，其它都按原来的7%提高。
4、用PL/SQL块实现下列操作
公司为每个职工增加奖金：若职工属于30号部门，则增加＄150；若职工属于20号部门，则增加＄250；若职工属于10号部门，则增加＄350。（提示：游标请自行阅读相关内容）
DECLARE
addcomm emp.comm%type;
CURSOR emp_cursor IS select deptno from emp;
BEGIN
FOR emprec IN emp_cursor LOOP
IF emprec.deptno=30 THEN addcomm:=150;
ELSIF emprec.deptno=20 THEN addcomm:=250;
ELSIF emprec.deptno=10 THEN addcomm:=350;
END IF;
Update emp
11
set comm=comm+ addcomm where deptno= emprec.deptno; END LOOP;
COMMIT WORK; END;
实验三 PL/SQL触发器和存储过程 （2学时）
【实验目的】
(1)了解触发器的类型。
（2）掌握PL/SQL触发器的使用方法。 (3)了解存储过程的使用方法。 (4)掌握存储过程的使用方法。
【实验内容】 一、 触发器概述 1. 触发器的类型
可以创建被如下语句所触发的触发器： （1）DML语句（DELETE，INSERT，UPDATE）； （2）DDL语句（CREATE，ALTER， DROP）；
（3）数据库操作（SERVERERROR，LOGON，LOGOFF，STARTUP，SHUTDOWN）。 2. 触发器的设计规则： （1）作用范围清晰
（2）不要让触发器去完成Oracle后台已经能够完成的功能 （3）限制触发器代码的行数 （4）不要创建递归的触发器
（5）触发器仅在被触发语句触发时进行集中的，全局的操作，同用户和数据库应用无关。 3. 触发器的创建
1）使用CREATE TRIGGER语句创建触发器
使用CREATE TRIGGER语句创建触发器的语句格式如下： CREATE [OR REPLACE] TRIGGER name {BEFORE|AFTER } {event [ OR ... ]} ON table [FOR[EACH ] {ROW|STATEMENT}]
[WHEN(condition)] plsql block|call procedures_statement 2）注意事项 （1）触发器可以声明为在对记录进行操作之前，在之前(检查约束之前和 INSERT，UPDATE 或 DELETE 执行前)或之后(在检查约束之后和完成 INSERT， UPDATE 或 DELETE 操作)触发.。
（2）一个 FOR EACH ROW 执行指定操作的触发器为操作修改的每一行都调用一次。 （3）SELECT 并不更改任何行，因此不能创建 SELECT 触发器。这种场合下规则和视图更适合。
（4）触发器和某一指定的表格有关，当该表格被删除时，任何与该表有关的触发器同样会被删除。
（5）在一个表上的每一个动作只能有一个触发器与之关联。
（6）在一个单独的表上，最多只能创建三个触发器与之关联，一个INSERT触发器，一个DELETE触发器和一个UPDATE触发器。
12
4. 触发器的修改和删除 删除触发器的语句格式为： DROP TRIGGER name ON table;
一个触发器由三部分组成:触发事件或语句、触发限制和触发器动作。触发事件或语句是指引起激发触发器的SQL语句，可为对一指定表的INSERT、UNPDATE或DELETE语句。触发限制是指定一个布尔表达式，当触发器激发时该布尔表达式是必须为真。触发器作为过程，是PL/SQL块，当触发语句发出、触发限制计算为真时该过程被执行。 5. 实例讲解Oracle数据库自带的几个触发器
Oracle数据库自带的几个触发器（最简单触发器格式）示例如下： --
create or replace trigger MDSYS.sdo_drop_user after drop on DATABASE declare
stmt varchar2(200); BEGIN
if dictionary_obj_type = 'USER' THEN
stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' || ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' '; EXECUTE IMMEDIATE stmt; end if; end; ---
create or replace trigger SYS.aurora$server$startup after startup on database call dbms_java.server_startup --
create or replace trigger SYS.JIS$ROLE_TRIGGER$ after drop on database when (ora_dict_obj_type='ROLE') begin
sns_context.role_dropped(ora_dict_obj_name);
http_security_cascade.principal_dropped(ora_dict_obj_name); end;
--删除前备份数据的器
Create Or Replace Trigger YSPJ.T_Bill_reMain_Del Before delete On bill_remain FOR EACH ROW Begin
Insert into BILL_REMAIN_TIGER
Values(:old.BILL_REMAINID,:old.BILL_TYPEID,:old.REMAIN_NUM,:old.ADD_TIME,:old.ORG_ID,:old.STATE,:old.BILL_ID,'删除记录',Sysdate,user); End;
存储过程概述
6. 存储过程的创建
创建存储过程的语句如下：
13
CREATE[OR REPLACE] PROCEDURE＜过程名＞ ＜参数1>，「方式l]＜数据类型1>，
＜参数2>，[ 方式2]＜数据类型2>，??) IS|AS is＿或as完全等价 BEGIN
PL/SQL过程体 END＜过程名＞
过程参数有以下三种类型：
（1）in参数类型：表示输入给过程的参数。
（2）out参数类型：表示参数在过程中将被赋值，可以传给过程体的外部。
（3）in out参数类型：表示该类参数既可以向过程体传值，也可以在过程体中赋值，以便向过程体外传值。 7. 存储过程的调用
存储过程可以直接用EXECUT命令调用或PL/SQL程序块内部调用。 用EXECUT命令调用存储过程的格式如下： SQL>execute proc_name(par1,par2?);
存储过程也可以被另外的PL/SQL块调用，调用的语句是： declare par1,par2; begin
proc_name(par1,par2…); end;
在调用前要声明变量par1，par2 8. 存储过程的释放
当某个存储过程不再需要时，应将其从内存中删除，以释放它占用的内存资源。释放过程的语句格式如下：
SQL>drop procedure proc_name; proc_name为过程名。
1、编写一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员。（要求：emp表、dept表均为复制后的表） CREATE OR REPLACE TRIGGER del_emp_deptno BEFORE DELETE ON dept FOR EACH ROW BEGIN
DELETE FROM emp WHERE deptno=:OLD.deptno; END;
2、创建触发器，当用户对test表执行DML语句时，将相关信息记录到日志表。 --创建测试表
CREATE TABLE test (
t_id NUMBER(4),
t_name VARCHAR2(20), t_age NUMBER(2), t_sex CHAR
14
);
--创建记录测试表
CREATE TABLE test_log (
l_user VARCHAR2(15), l_type VARCHAR2(15), l_date VARCHAR2(30) );
--创建触发器
CREATE OR REPLACE TRIGGER test_trigger
AFTER DELETE OR INSERT OR UPDATE ON test DECLARE
v_type test_log.l_type%TYPE; BEGIN
IF INSERTING THEN --INSERT触发 v_type := 'INSERT';
DBMS_OUTPUT.PUT_LINE('记录已经成功插入，并已记录到日志'); ELSIF UPDATING THEN --UPDATE触发 v_type := 'UPDATE';
DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并已记录到日志'); ELSIF DELETING THEN v_type := 'DELETE';
DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志'); END IF;
INSERT INTO test_log VALUES(user,v_type,
TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss')); END; /
--下面我们来分别执行DML语句
INSERT INTO test VALUES(101,'zhao',22,'M'); UPDATE test SET t_age = 30 WHERE t_id = 101; DELETE test WHERE t_id = 101; --然后查看效果
SELECT * FROM test; SELECT * FROM test_log;
3、创建触发器，它将映射emp表中每个部门的总人数和总工资。 --创建映射表
CREATE TABLE dept_sal AS
SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno;
DESC dept_sal; --创建触发器
CREATE OR REPLACE TRIGGER emp_info
15
AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE
CURSOR cur_emp IS
SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno; BEGIN
DELETE dept_sal; --触发时首先删除映射表信息 FOR v_emp IN cur_emp LOOP
--DBMS_OUTPUT.PUT_LINE(v_emp.deptno || v_emp.total_emp || v_emp.total_sal); --插入数据
INSERT INTO dept_sal
VALUES(v_emp.deptno,v_emp.total_emp,v_emp.total_sal); END LOOP; END; /
--对emp表进行DML操作
INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000); SELECT * FROM dept_sal;
DELETE EMP WHERE empno=123; SELECT * FROM dept_sal;
4、创建触发器，它记录表的删除数据 --创建表
CREATE TABLE employee (
id VARCHAR2(4) NOT NULL, name VARCHAR2(15) NOT NULL, age NUMBER(2) NOT NULL, sex CHAR NOT NULL );
DESC employee; --插入数据
INSERT INTO employee VALUES('e101','zhao',23,'M'); INSERT INTO employee VALUES('e102','jian',21,'F'); --创建记录表
CREATE TABLE old_employee AS SELECT * FROM employee; DESC old_employee; --创建触发器
CREATE OR REPLACE TRIGGER tig_old_emp AFTER DELETE ON employee --
FOR EACH ROW --语句级触发，即每一行触发一次 BEGIN
INSERT INTO old_employee
VALUES(:old.id,:old.name,:old.age,:old.sex); --:old代表旧值
16
END; /
--下面进行测试 DELETE employee;
SELECT * FROM old_employee;
5、创建触发器，比较emp表中更新的工资。 CREATE OR REPLACE TRIGGER sal_emp BEFORE UPDATE ON emp FOR EACH ROW BEGIN
IF :OLD.sal > :NEW.sal THEN
DBMS_OUTPUT.PUT_LINE('工资减少'); ELSIF :OLD.sal < :NEW.sal THEN
DBMS_OUTPUT.PUT_LINE('工资增加'); ELSE
DBMS_OUTPUT.PUT_LINE('工资未作任何变动'); END IF;
DBMS_OUTPUT.PUT_LINE('更新前工资 ：' || :OLD.sal); DBMS_OUTPUT.PUT_LINE('更新后工资 ：' || :NEW.sal); END; /
--执行UPDATE查看效果
UPDATE emp SET sal = 3000 WHERE empno = '7788';
6、需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。Create table foo(a number); Create trigger biud_foo
Before insert or update or delete On foo Begin
If user not in (?DONNY?) then
Raise_application_error(-20001, ?You don?t have access to modify this table.’); End if; End; /
即使SYS，SYSTEM用户也不能修改foo表。
写存储过程，显示所指定雇员名所在的部门名和位置。
CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE, pdname OUT dept.dname%TYPE,ploc OUT dept.loc%TYPE) AS BEGIN
SELECT dname,loc INTO pdname,ploc FROM emp,dept
WHERE emp.deptno=dept.deptno AND emp.ename=pename;
17
END; /
VARIABLE vdname VARCHAR2(14); VARIABLE vloc VARCHAR2(13);
EXECUTE DeptMesg('SMITH',:vdname，:vloc); PRINT vdname vloc;
1、 定义一个为修改职工表（emp）中某职工工资的存储过程子程序，职工名作为形参,若该职工名在职工表中查找不到，就在屏幕上提示“查无此人”然后结束子程序的执行；否则若工种为MANAGER的，则工资加＄1000；工种为SALESMAN，工资加＄500；工种为ANALYST，工资加＄200，否则工资加＄100。
create or replace procedure xggz(name varchar2) is
k_job emp.job%type; addsal emp.sal%type; begin
select job into k_job from emp where ename=name; if k_job=?MANAGER? then
addsal:=1000;
elsif k_job=?SALESMAN? then addsal:=500;
elsif k_job=?ANALYST? then addsal:=200; else
addsal:=100; end if;
update emp set sal=sal+addsal where ename=name; exception
when no_data_found then
dbms_output.put_line(?查无此人”); end; 2、 通过dept表查询出所有部门号,对每个部门雇员的工资进行调整，将工资高于（包含$2000）$2000的雇员每人增加$500,将工资低于$2000的雇员每人增加到$2000。但应注意雇员工资调整后不应大于$10000,否则显示出错信息,并退出程序。并统计显示各部门人数及工资调整后的总和。
实验四 PL/SQL子程序 （2学时）
【实验目的】 (1)掌握创建PL/SQL子程序的方法。
(2) 掌握利用PL/SQL子程序提高代码重用性的方法。 【实验内容】
一、 PL/SQL子程序概述
在PL/SQL中，子程序是命名的PL/SQL块，它可以带参数，能被PL/SQL块等调用。子程序有两种：过程和函数。一般可使用一个过程执行某种特定的操作，使用一个函数完成
18
某种计算获得一个值。
利用过程和函数子程序，用户可以对自己的PL/SQL块命名以创建模块代码，提高对代码的重用。
子程序与未命名的或无名的PL/SQL块一样，也由三部分组成：说明部分、可执行部分和可选的异常处理部分。各部分的内容分别与PL/SQL块相同。在子程序和PL/SQL块的说明部分，除说明类型、变量、常量、游标和异常外，还可以说明子程序，即嵌套子程序。该嵌套子程序的使用范围仅限于说明它的当前模块。 1、定义过程子程序
定义过程的语法格式为：
PROCEDURE 过程名[ (参数表)] IS 说明部分 BEGIN 执行部分 EXCEPTION 异常处理程序 END;
每个参数的说明格式为：
参数名[IN或OUT或IN OUT]数据类型[:=或DEFAULT值] 参数有以下三种类型：
（1）IN参数类型：表示输入给过程的参数。
（2）OUT参数类型：表示参数在过程中将被赋值，可以传给过程体的外部。
（3）IN OUT参数类型：表示该类参数既可以向过程体传值，也可以在过程体中赋值，以便向过程体外传值。 2、定义函数子程序
函数子程序类似于过程子程序，它具有一个函数说明和一个函数体。过程的函数之间的主要差异在于：函数返回一个计算值。 定义函数的语法格式为：
FUNCTION 函数名[ (参数表)] RETURN 返回值类型 IS 说明部分 BEGIN
执行部分（至少有一个RETURN语句返回一个值） EXCEPTION 异常处理程序 END;
二、 实验内容
1、 设计一个过程子程序，根据输入职工所在的部门号和职工名来修改该职工的工资；若该
职工的部门号为10，则工资加$100；若部门号为20，则工资加￥300；否则工资加￥200.（设部门号与职工名作为过程的输入形式参数） 程序的代码如下：
PROCEDURE rais_sal(dept_no integer,v_name varchar2) IS addsal real; v_sal number;
salary_mis EXCEPTION;
19
BEGIN
select sal into v_sal from emp
where ename=v_name and deptno=dept_no; IF v_sal IS NULL THEN RAISE salary_mis; elsif dept_no=10 then addsal:=100; elsif dept_no=20 then addsal:=300; else addsal:=200; END IF; update emp
set sal=sal+addsal
where ename=v_name and deptno=dept_no; COMMIT WORK; EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.putline(?没有找到职工?||v_name); WHEN salary_mis THEN
dbms_output.putline(v_name||?工资是空的，有错误！?); WHEN Others THEN
dbms_output.putline(?发现其他错误！?);
END rais_sal; /*在END后，可以给出本过程名，表示过程到此结束*/ 过程调用为一个PL/SQL语句，其语句调用形式为： rais_sal(10,?SCOTT?);
2、 定义一个函数子程序，根据输入的职工号计算该职工的年收入总额。 程序如下：
FUNCTION compsumal(v_empno number) RETURN real IS /*函数返回值是实型值*/ sum_sal real; BEGIN
select 12*(sal+nvl(comm,0)) INTO sum_sal from emp /* nvl(参数1,参数2) 为空值处理函数参见课本P88* / where emono= v_empno; RETURN(sum_sal); EXCEPTION
WHEN NO_DATA_FOUND THEN dbms_output.putline(?没有找到职工!?); return(-1);
WHEN Others THEN
dbms_output.put_line(?发现其他错误！?); return(-2);
END compsumal; /*在END 后的函数名compsumal 是可选的，可以省略*/ 可用如下PL/SQL赋值语句调用上述函数： y_sum:= compsumal(7788);
在主调函数中可以对y_sum的值进行判断。如果是-1，说明未找到该职工，不能计算；如果是-2，说明有其他错误，否则就是要计算该职工的年收入总额。
20
3、 写过程子程序，以雇员名为参数，显示所指定雇员名所在的部门名和位置，并调用这个
子程序。
4、 写函数子程序，以部门号为参数，计算该部门职工的总人数，并调用这个子程序。
实验五 Oracle综合实例设计—设计分析（2学时）
【实验目的】
(1) 掌握根据系统需求，进行系统设计(包括系统结构设计、数据库设计、表结构设计)
的方法。
(2) 学会利用所学的知识进行数据库的系统开发。 (3) 掌握在Oracle中创建数据库、表的方法。 【实验内容】
某单位拟建立一个人力资源管理系统，系统总体需求：实现人事管理的各项基本功能，如职员基本信息的维护(包括员工信息的添加、修改、删除等等)，职员信息的查询，职员信息的统计和报表。
试在系统需求的基础上，进行系统结构设计、数据库设计和表结构设计。 【实验步骤与要求】
1、 将该系统划分为若干个功能模块，并说明各个模块的功能。 2、 设计数据字典和E-R图。 3、 设计表结构。
4、 根据设计，在Oracle数据库中创建数据库和表。
21
3、 写过程子程序，以雇员名为参数，显示所指定雇员名所在的部门名和位置，并调用这个
子程序。
4、 写函数子程序，以部门号为参数，计算该部门职工的总人数，并调用这个子程序。
实验五 Oracle综合实例设计—设计分析（2学时）
【实验目的】
(1) 掌握根据系统需求，进行系统设计(包括系统结构设计、数据库设计、表结构设计)
的方法。
(2) 学会利用所学的知识进行数据库的系统开发。 (3) 掌握在Oracle中创建数据库、表的方法。 【实验内容】
某单位拟建立一个人力资源管理系统，系统总体需求：实现人事管理的各项基本功能，如职员基本信息的维护(包括员工信息的添加、修改、删除等等)，职员信息的查询，职员信息的统计和报表。
试在系统需求的基础上，进行系统结构设计、数据库设计和表结构设计。 【实验步骤与要求】
1、 将该系统划分为若干个功能模块，并说明各个模块的功能。 2、 设计数据字典和E-R图。 3、 设计表结构。
4、 根据设计，在Oracle数据库中创建数据库和表。
21
正在阅读：
《Oracle数据库》实验指导书04-07
循环水中铁含量检测培训课件 - 图文12-07
送派出所锦旗用语大全03-26
形容克服困难的成语02-13
作文素材精华本：议论文作文素材10-15
女痣的位置与命运图04-12
歌曲火苗,歌词,串词,报幕词02-09
开题报告城市道路交通组织优化分析11-22
农田水利工程施工组织设计(1)02-26
- 2018年临床执业医师泌尿系统：尿路结石
- 金字塔函数整理汇总
- 当代管理理论的各主要流派的特征以及发展
- 初中物理压强分层次基础导练
- 统计部分(ch2-ch6)练习答案
- 2018年中国奶茶市场及分析报告目录
- 2010年电装实验讲义 - 图文
- 装配式叠合板安装施工方案(1) - 图文
- 自考 管理思想 历年试题 答案版
- 药师技能大赛试题及答案
- 各种过电压保护器比较分析
- 集成运放的非线性应用--方波三角波发生器
- 计算机网络抓包实验报告
- 老虎板王相对探索者的优势（个人理解）
- 工程实践报告
- 关于“干一行爱一行”和“爱一行干一行”辩论赛正反方素材
- UC3843开关电源经典讲解
- 高中化学4.2富集在海水中的元素-氯（第1课时）学案新人教版必修1
- 宁夏省2015年上半年初级高低压电器装配工考试题
- 蛋壳钙镁含量的测定