oracle实验教案10.docx
《oracle实验教案10.docx》由会员分享，可在线阅读，更多相关《oracle实验教案10.docx（26页珍藏版）》请在冰豆网上搜索。
oracle实验教案10
《oracle数据库》教案
单位：
计算机学院
教师：
汤海蓉
时间：
2012.9
课程名称oracle数据库
使用教材oracle10g数据库管理应用与开发标准教程清华大学出版社
专业班级计算机学院信管10级
授课时数共48课时（理论：
32课时，实验：
16课时）
授课教师汤海蓉
授课时间2012学年下学期
主要参考文献
《oracle教程与实验》胡明庆等编著清华大学出版社
教学进度表
课程名称＿Oracle数据库实验＿＿总学时数＿＿＿42＿
其中:
理论课学时＿32＿＿__实验（实习）学时＿＿10＿
使用教材＿Oracle10g数据库管理应用与开发标准教程
周
次
教学内容（含理论和实践）
学时
作业
布置
6
SQL*PLUS练习
2
完成实验报告
8
Oracle数据库开发环境下PL/SQL编程
2
完成实验报告
10
PL/SQL触发器与存储过程
2
完成实验报告
12
PL/SQL子程序与Oracle安全管理
2
完成实验报告
14
Oracle综合实例设计—设计分析
2
完成实验报告
实验一SQL*PLUS练习（2学时）
【实验目的】
（1）了解Oracle的工作环境和基本使用方法。
（2）练习标准SQL的数据操作，查询命令及其查询优化。
（3）学会使用高级SQL命令，排序、分组、自连接查询等。
（4）学会使用SQL*PLUS命令显示报表，存储到文件等。
【实验内容】
一、准备使用SQL*PLUS
1.进入SQL*PLUS
2.退出SQL*PLUS
3.显示表结构命令DESCRIBE
SQL>DESCRIBEemp
使用DESCRIBE（缩写DESC）可以列出指定表的基本结构，包括各字段的字段名以及类型、长度、是否非空等信息。
4.使用SQL*PLUS显示数据库中EMP表的内容
输入下面的查询语句：
SQL>SELECT*FROMemp;
按下回车键执行查询
5.执行命令文件
START或@命令将指定文件调入SQL缓冲区中，并执行文件内容。
SQL>@文件名（文件后缀缺省为.SQL）或
SQL>START文件名
文件中每条SQL语句顺序装入缓冲区并执行。
二、数据库命令——有关表、视图等的操作
1.创建表employee
例1定义一个人事信息管理系统中存放职工基本信息的一张表。
可输入如下命令：
SQL>CREATETABLEemployee
（empnonumber（6）PRIMARYKEY,/*职工编号
namevarchar2（10）NOTNULL,/*姓名
deptnonumber
（2）DEFAULT10,/*部门号
salarynumber（7,2）CHECK（salary<100000）,/*工资
birth_datedate,/*出生年月
soc_sec_numchar（9）UNIQUE,/*内部序列号
foreignkey（deptno）referencesdept（deptno））;
例2复制一个与emp表的表结构相同的新空表emp2.
可输入如下命令：
SQL>createtableemp2as
select*fromemp
where1=2;
在命令的where子句中给出1=2，表示条件不可能成立，因而只能复制表结构，而不能复制任何数据到新表中去。
另外，还可以复制一个表的部分列定义或部分列定义及其数据。
三、Oracle数据库数据查询
1、单表查询
2、多表查询
四、SQL*PLUS常用命令
表1常用报表格式化名命令
命令
定义
Btitle
为报表的每一页设置底端标题
Column
设置列的标题和格式
Compute
让SQL*PLUS计算各种值
Remark
将某些字标记为注释
Setlinesize
设置报表的行宽字符数
Setnewpage
设置报表各页之间的行数
Spool
使SQL*PLUS将输出写入文件中
Start
使SQL*PLUS执行一个sql文件
Ttitle
设置报表每页的头标题
Break
让SQL*PLUS进行分组操作
例3建立一个批命令文件对查询到的数据以报表的形式输出并将其保存到指定的文件中。
处理方法：
利用SQL*PLUS语言工具（也可以使用其他文本编辑器）建立批命令的.SQL文件。
在“SQL>”提示符下，使用EDIT命令在”E:
\”中建立SCGB.SQL文件。
SCGB.SQL文件中的命令组如下：
SQL>EDITE:
\SCGB.SQL
SETechooff
SETpagesize30
SETlinesize75
TTITLE’2008年4月10号’CE’公司职员基本情况登记表’R’Page:
’FORMAT99-
>SQL.PNOSKIP1CE’===========================’
BTITLECOL60’制标单位’TAB3‘人事部’
COLUMNempnoheading‘职工|编号’
COLUMNenameformata10heading‘姓名’
COLUMNjobheading‘工种’
COLUMNsalformat$99,990heading工资’
COLUMNcommLikesalheading‘奖金’
COLUMNdeptnoformat9999heading‘部门|编号’
COLUMNhiredateheading‘参加工作时间’
SPOOLe:
\sjbb/*在E盘中建立格式报表输出文件，默认属性为LSTBREAKondeptnoskip1
COMPUTEsumofsalcommondeptno
SELECTempno,ename,job,hiredate,sal,comm,deptnofromemp
ORDERBYdeptno,sal;
SPOOLoff/*终止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、创建一个批文件，制作一张职工情况登记表，并保存到一个磁盘文件中。
要求：
①有表头、表尾及制表时间。
②登记表中包括姓名、工种、工资、部门名称和部门所在地。
实验二Oracle数据库开发环境下PL/SQL编程（2学时）
【实验目的】
（1）掌握PL/SQL的基本使用方法。
（2）在SQL*PLUS环境下运行PL/SQL的简单程序。
（3）应用PL/SQL解决实际问题
【实验内容与步骤】
PL/SQL块中的可执行部分是由一系列语句组成的（包括对数据库进行操作的SQL语句，PL/SQL语言的各种流程控制语句等）。
在块中对数据库查询，增、删、改等对数据的操作是由SQL命令完成的。
在PL／SQL块中，可以使用SQL的数据查询命令，数据操纵命令和事务控制命令。
可使用全部SQL函数。
PL／SQL中的SQL语句，可使用SQL的比较操作等运算符。
但不能使用数据定义语句。
在PL／SQL块中使用SELECT语句时注意几点：
（1）SELECT语句必须含有INTO子句。
（2）INTO子句后的变量个数和位置及数据类型必须和SELECT命令后的字段名表相同。
（3）INTO子句后可以是简单类型变量或组合类型变量。
（4）SELECT语句中的WHERE条件可以包含PL/SQL块中定义的变量及表达式，但变量名不要同数据库表列名相同。
（5）在未使用显式游标的情况下，使用SELECT语句必须保证只有一条记录返回，否则会产生异常情况。
实验内容：
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。
实验三PL/SQL触发器（1学时）
【实验目的】
（1）了解触发器的类型。
（2）掌握PL/SQL触发器的使用方法。
【实验内容】
触发器注意事项
（1）触发器可以声明为在对记录进行操作之前，在之前（检查约束之前和INSERT，UPDATE或DELETE执行前）或之后（在检查约束之后和完成INSERT，UPDATE或DELETE操作）触发.。
（2）一个FOREACHROW执行指定操作的触发器为操作修改的每一行都调用一次。
（3）SELECT并不更改任何行，因此不能创建SELECT触发器。
这种场合下规则和视图更适合。
（4）触发器和某一指定的表格有关，当该表格被删除时，任何与该表有关的触发器同样会被删除。
（5）在一个表上的每一个动作只能有一个触发器与之关联。
（6）在一个单独的表上，最多只能创建三个触发器与之关联，一个INSERT触发器，一个DELETE触发器和一个UPDATE触发器。
1.实例讲解Oracle数据库自带的几个触发器
Oracle数据库自带的几个触发器（最简单触发器格式）示例如下：
--
createorreplacetriggerMDSYS.sdo_drop_user
afterdroponDATABASE
declare
stmtvarchar2（200）;
BEGIN
ifdictionary_obj_type='USER'THEN
stmt:
='DELETEFROMSDO_GEOM_METADATA_TABLE'||
'WHERESDO_OWNER='''||dictionary_obj_name||'''';
EXECUTEIMMEDIATEstmt;
endif;
end;
---
createorreplacetriggerSYS.aurora$server$startupafterstartupondatabase
calldbms_java.server_startup
--
createorreplacetriggerSYS.JIS$ROLE_TRIGGER$afterdropondatabase
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;
--删除前备份数据的器
CreateOrReplaceTriggerYSPJ.T_Bill_reMain_Del
BeforedeleteOnbill_remain
FOREACHROW
Begin
InsertintoBILL_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;
二、实验内容：
1、编写一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员。
（要求：
emp表、dept表均为复制后的表）
2、创建触发器，当用户对test表执行DML语句时，将相关信息记录到日志表。
--创建测试表
CREATETABLEtest
（
t_id NUMBER（4）,
t_nameVARCHAR2（20）,
t_ageNUMBER
（2）,
t_sexCHAR
）;
--创建记录测试表
CREATETABLEtest_log
（
l_user VARCHAR2（15）,
l_type VARCHAR2（15）,
l_date VARCHAR2（30）
）;
--创建触发器
CREATEORREPLACETRIGGERtest_trigger
AFTERDELETEORINSERTORUPDATEONtest
DECLARE
v_typetest_log.l_type%TYPE;
BEGIN
IFINSERTINGTHEN--INSERT触发
v_type:
='INSERT';
DBMS_OUTPUT.PUT_LINE（'记录已经成功插入，并已记录到日志'）;
ELSIFUPDATINGTHEN--UPDATE触发
v_type:
='UPDATE';
DBMS_OUTPUT.PUT_LINE（'记录已经成功更新，并已记录到日志'）;
ELSIFDELETINGTHEN
v_type:
='DELETE';
DBMS_OUTPUT.PUT_LINE（'记录已经成功删除，并已记录到日志'）;
ENDIF;
INSERTINTOtest_logVALUES（user,v_type,
TO_CHAR（sysdate,'yyyy-mm-ddhh24:
mi:
ss'））;
END;
/
--下面分别执行DML语句
INSERTINTOtestVALUES（101,'zhao',22,'M'）;
UPDATEtestSETt_age=30WHEREt_id=101;
DELETEtestWHEREt_id=101;
--然后查看效果
SELECT*FROMtest;
SELECT*FROMtest_log;
3、创建触发器，它将映射emp表中每个部门的总人数和总工资。
--创建映射表
CREATETABLEdept_sal
AS
SELECTdeptno,COUNT（empno）AStotal_emp,SUM（sal）AStotal_salFROMempGROUPBYdeptno;
DESCdept_sal;
--创建触发器
CREATEORREPLACETRIGGERemp_info
AFTERINSERTORUPDATEORDELETEONemp
DECLARE
CURSORcur_empIS
SELECTdeptno,COUNT（empno）AStotal_emp,SUM（sal）AStotal_salFROMempGROUPBYdeptno;
BEGIN
DELETEdept_sal;--触发时首先删除映射表信息
FORv_empINcur_empLOOP
--DBMS_OUTPUT.PUT_LINE（v_emp.deptno||v_emp.total_emp||v_emp.total_sal）;
--插入数据
INSERTINTOdept_sal
VALUES（v_emp.deptno,v_emp.total_emp,v_emp.total_sal）;
ENDLOOP;
END;
/
--对emp表进行DML操作
INSERTINTOemp（empno,deptno,sal）VALUES（'123','10',10000）;
SELECT*FROMdept_sal;
DELETEEMPWHEREempno=123;
SELECT*FROMdept_sal;
4、创建触发器，它记录表的删除数据
--创建表
CREATETABLEemployee
（
id VARCHAR2（4）NOTNULL,
nameVARCHAR2（15）NOTNULL,
ageNUMBER
（2） NOTNULL,
sexCHAR NOTNULL
）;
DESCemployee;
--插入数据
INSERTINTOemployeeVALUES（'e101','zhao',23,'M'）;
INSERTINTOemployeeVALUES（'e102','jian',21,'F'）;
--创建记录表
CREATETABLEold_employeeAS
SELECT*FROMemployee;
DESCold_employee;
--创建触发器
CREATEORREPLACETRIGGERtig_old_emp
AFTERDELETEONemployee--
FOREACHROW--语句级触发，即每一行触发一次
BEGIN
INSERTINTOold_employee
VALUES（:
old.id,:
old.name,:
old.age,:
old.sex）;--:
old代表旧值
END;
/
--进行测试
DELETEemployee;
SELECT*FROMold_employee;
5、创建触发器，比较emp表中更新的工资。
CREATEORREPLACETRIGGERsal_emp
BEFOREUPDATEONemp
FOREACHROW
BEGIN
IF:
OLD.sal>:
NEW.salTHEN
DBMS_OUTPUT.PUT_LINE（'工资减少'）;
ELSIF:
OLD.sal<:
NEW.salTHEN
DBMS_OUTPUT.PUT_LINE（'工资增加'）;
ELSE
DBMS_OUTPUT.PUT_LINE（'工资未作任何变动'）;
ENDIF;
DBMS_OUTPUT.PUT_LINE（'更新前工资：
'||:
OLD.sal）;
DBMS_OUTPUT.PUT_LINE（'更新后工资：
'||:
NEW.sal）;
END;
/
--执行UPDATE查看效果
UPDATEempSETsal=3000WHEREempno='7788';
6、需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。
Createtablefoo（anumber）;
Createtriggerbiud_foo
Beforeinsertorupdateordelete
Onfoo
Begin
Ifusernotin（‘DONNY’）then
Raise_application_error（-20001,‘Youdon’thaveaccesstomodifythistable.’）;
Endif;
End;
/
即使SYS，SYSTEM用户也不能修改foo表。
实验四PL/SQL存储过程（1学时）
【实验目的】
（1）了解存储过程的使用方法。
（2）掌握存储过程的使用方法。
【实验内容】
1、写存储过程，显示所指定雇员名所在的部门名和位置。
CREATEORREPLACEPROCEDUREDeptMesg（penameemp.ename%TYPE,
pdnameOUTdept.dname%TYPE,plocOUTdept.loc%TYPE）AS
BEGIN
SELECTdname,locINTOpdname,ploc
FROMemp,dept
WHEREemp.deptno=dept.deptnoANDemp.ename=pename;
END;
/
VARIABLEvdnameVARCHAR2（14）;
VARIABLEvlocVARCHAR2（13）;
EXECUTEDeptMesg（'SMITH',:
vdname，:
vloc）;
PRINTvdnamevloc;
2、定义一个为修改职工表（emp）中某职工工资的存储过程子程序，职工名作为形参,若该职工名在职工表中查找不到，就在屏幕上提示“查无此人”然后结束子程序的执行；否则若工种为MANAGER的，则工资加＄1000；工种为SALESMAN，工资加＄500；工种为ANALYST，工资加＄200，否则工资加＄100。
createorreplaceprocedurexggz（namevarchar2）is
k_jobemp.job%type;
addsalemp.sal%type;
begin
selectjobintok_jobfromempwhereename=name;
ifk_job=’MANAGER’then
addsal:
=1000;
elsifk_job=’SALESMAN’then
addsal:
=500;
elsifk_job=’ANALYST’then
addsal:
=200;
else
addsal:
=100;
endif;
updateempsetsal=sal+addsalwhereename=name;
exception
whenno_data_foundthen
dbms_output.put_line（‘查无此人”）;
end;
3、通过dept表查询出所有部门号,对每个部门雇员的工资进行调整，将工资高于（包含$2000）$2000的雇员每人增加$500,将工资低于$
升级会员 