oracle实验指导书之全本
更新时间：2024-05-26 07:22:01 阅读量： 综合文库 文档下载
Oracle数据库 实验指导
段华斌 编著
计算机与通信工程系
实验一
实验名称：数据库的管理 实验学时：2 实验目的：
1）了解Oracle数据库的逻辑结构和物理结构。 2）熟悉Oracle的命令操作环境SQL*PLUS。
3）了解启动和关闭数据库的不同方式及其优缺点。 4）熟悉掌握各种启动、关闭方式。 实验内容及步骤：
1、 SQL*Plus的启动与关闭
（一）SQL*Plus的启动
当登录到操作系统后，有三种方法启动SQL*Plus。 方法一：
(1) 执行―开始‖→―程序‖→―Oracle – OraDb11g_home1‖→―应用程序开发‖→―SQL Plus‖命令，打开SQL Plus窗口，显示登录界面。
(2) 在登录界面中将提示输入用户名，根据提示输入相应的用户名和口令(例如system和admin)后按Enter键，SQL*Plus将连接到默认数据库。
(3) 连接到数据库之后，显示SQL>提示符，可以输入相应的SQL命令。
方法二：
(1) 执行―开始‖→―程序‖→―附件‖→―命令提示符‖，打开命令提示符窗口。 (2) 输入命令：Sqlplus / nolog
Connect sys/口令 as sysdba
方法三：
(1) 执行―开始‖→―程序‖→―附件‖→―命令提示符‖，打开命令提示符窗口。 (2) 输入命令：sqlplus 用户名/口令 as sysdba。
（二）断开与数据库的连接 SQL>DISCONNECT
（三）退出SQL*Plus SQL>EXIT 或：
SQL>Quit
（四）启动sqlplus,可能的故障ORA-12560
造成ORA-12560: TNS: 协议适配器错误的问题的原因有三个：
（1）监听服务没有起起来。windows平台个一如下操作：开始---程序---管理工具---服务，打开服务面板，启动TNSlistener服务。
（2）database instance没有起起来。windows平台如下操作：开始---程序---管理工具---服务，打开服务面板，启动oracleserviceXXXX,XXXX就是你的database SID.
（3）进入sqlplus前，在command line下输入 set oracle_sid=XXXX,XXXX就是你的database SID。
2、 使用shutdown命令关闭数据库
练习使用shutdown命令关闭数据库实例，分别按以下方式启动数据库实例。
（1）正常关闭。等待当前所有已连接的用户断开与数据库的连接，然后关闭数据库。正常关闭的语句如下：
SHUTDOWN NORMAL
（2）立即关闭。回退活动事务处理并断开所有已连接的用户，然后关闭数据库。 立即关闭语句如下：
SHUTDOWN IMMEDIATE
（3）事务关闭：完成事务处理后断开所有已连接的用户，然后关闭数据库。事务处理关闭语句如下：
SHUTDOWN TRANSACTIONAL
（4）中止关闭：中止数据库实例， 立即关闭数据库。中止关闭的语句如下： SHUTDOWN ABORT
3、 使用startup命令启动数据库
练习使用STARTUP命令启动数据库实例，分别按以下方式启动数据库实例。 （1）启动数据库实例时不装载数据库。执行此操作的命令如下： STARTUP NOMOUNT （2）启动数据库实例，装载数据库，但不打开数据库。通常在数据库维护时执行此操作，对应的命令如下：
STARTUP MOUNT
（3）启动后限制对数据库实例的访问。执行此操作的命令如下：
STARTUP RESTRICT
（4）强制实例启动。在遇到特殊异常的情况时，可以强制启动实例。强制启动实例的语句如下：
STARTUP FORCE
4.改变数据库的状态
（1）装载数据库实例。
在执行某些管理操作时，数据库必须启动、装载一个实例，但此时数据库处于关闭状态。 ALTER DATABASE MOUNT
（2）打开已关闭的数据库。 ALTER DATABASE OPEN
（3）以只读方式打开数据库。
ALTER DATABASE OPEN READ ONLY
思考题：
1、在数据库的nomount、mount、open三种模式下，分别打开数据库的什么文件？
2、当关闭数据库时使用shutdown命令，好久没有关闭，随即将“命令提示符”窗口关闭，之后重新“启动命令提示符”并登录SQL*PLUS，使用shutdown immediate关闭数据库出现如下问题如何解决？
ORA-24324: 未初始化服务句柄 ORA-24323: 不允许此值
ORA-01090: 正在关闭 - 不允许连接
3、如何把数据库从nomount状态改为mount状态？
实验二
实验名称：日志文件的管理 实验学时：2 实验目的：
1）了解日志文件的作用。
2）了解管理重做日志文件的准则。
3）掌握重做日志文件组及成员的添加、清除、删除等操作方法。 实验内容及步骤：
重做日志文件对于Oracle数据库来说是至关重要的，Oracle数据库的变化，都会生成相应的重做日志，这些日志被记录在重做日志文件中。 1、 重做日志文件的查看
（1）查看数据库各组联机重做日志文件的日志序列号： SQL>ARCHIVE LOG LIST;
SQL>SELECT GROUP#,SEQUENCE# FROM V$LOG; （2）查看日志文件组的状态：
SQL>SELECT GROUP#,STATUS FROM V$LOG; （3）查看重做日志组及其成员的基本信息：
SQL>SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE; 2、 添加重做日志文件组
SQL>ALTER DATABASE ADD LOGFILE
2 GROUP 4 ('E:\\REDO04_1.LOG') 3 SIZE 10M;
3、 添加重做日志文件组的成员
方法一：
SQL>ALTER DATABASE ADD LOGFILE MEMBER 2 'E:\\REDO04_2.LOG' 3 TO GROUP 4;
方法二：如果不知道组编号，可以通过下述操作向组中其他成员的名称来添加新的成员：
SQL>ALTER DATABASE ADD LOGFILE MEMBER 2 'E:\\REDO04_3.LOG' 3 TO
4 (?E:\\REDO04_1.log‘,‘ 'E:\\REDO 04_2.log‘); 查询v$log可以看到添加完成后的结果：
SQL>SELECT GROUP#,MEMBERS,STATUS,FIRST_TIME FROM V$LOG; 4、 删除重做日志文件组成员:
SQL>ALTER DATABASE DROP LOGFILE MEMBER ' E:\\REDO04_3.LOG '; 5、 删除重做日志文件组：
SQL>ALTER DATABASE DROP LOGFILE GROUP 4; 可能的错误信息：
Ora-01623:日志10 是实例orcl的当前日志。 解决方法：
SQL> alter system switch logfile;
SQL> select * from v$log; 交替日志文件组，如果多次切换后想要删除的重做日志组状态依旧是active的时候可以重启动一下数据库。
6、 清除重做日志文件组：
SQL>ALTER DATABASE CLEAR LOGFILE GROUP 3; 7、 改变数据库的归档模式：
(1) 修改初始化参数 ，指定归档位置和归档日志文件的命名方法。 SQL> ALTER SYSTEM
2 SET LOG_ARCHIVE_DEST='D:\\ORACLE\\ORADATA\\ARCHIVE'; SQL> ALTER SYSTEM
2 SET LOG_ARCHIVE_DUPLEX_DEST='E:\\ORACLE\\ARCHIVE'; SQL> ALTER SYSTEM
2 SET LOG_ARCHIVE_FORMAT='%R_%T_%S.ARC' SCOPE=SPFILE; (2) 关闭数据库实例SHUTDOWN IMMEDIATE; (3) Startup mount;
(4) Alter database archivelog; (5) Alter database open;
8、 强制进行日志切换，以检查日志文件能否正确归档。
SQL>ALTER SYSTEM SWITCH LOGFILE;
在资源管理器中查看指定归档位置下的归档日志文件。 9、 查看数据库的当前模式：
SQL>SELECT NAME,LOG_MODE FROM V$DATABASE; 10、 查看数据库的归档进程信息：
SQL>SELECT * FROM V$ARCHIVE_PROCESSES; 11、 查询数据库的归档日志文件名称、重做日志序列号以及每次归档的完成时间等。
SQL>SELECT NAME,SEQUENCE#,COMPLETION_TIME FROM V$ARCHIVED_LOG;
思考题：
1、 移动、重命名重做日志文件成员的步骤如下，请完成各步骤的命令代码。
（1）关闭数据库。
（2）将重做日志文件E:\\REDO04_3.LOG移动或复制到D盘，并重命名为REDO04_03.LOG。
（3）以MOUNT状态打开数据库。 （4）重命名数据库的重做日志文件。 （5）打开数据库。
（6）查看重做日志文件信息。
2、在实验内容中添加重做日志文件组group4时，是否可以将group4 改为group1？改为group6呢？请分别说明原因。
3、当重做日志文件组被清除后，该组还可以继续使用吗？为什么？
INSERT INTO Users (UserId, UserName, UserType, UserPwd) VALUES ( 1, 'Admin', 1, 'Admin'); COMMIT; 4、 修改数据
UPDATE语句的基本使用方法如下所示：
UPDATE <表名> SET 列名1 = 值1, 列名2 = 值2, …, 列名n = 值n WHERE <更新条件表达式>
例4、将用户Admin的密码修改为111111： UPDATE Users
SET UserPwd='111111'
WHERE UserName='Admin'; COMMIT WORK; 5、 删除数据
DELETE命令删除表中的数据： DELETE <表名>
WHERE <删除条件表达式>
例5、删除表Users中列UserName等于空（''）的数据： DELETE FROM Users WHERE UserName = ''; COMMIT WORK; 6、 查询数据
（1）SELECT语句的基本语法结构如下： SELECT子句 [ INTO 子句 ] FROM 子句
[ WHERE 子句 ] [ GROUP BY 子句] [ HAVING 子句 ] [ ORDER BY 子句 ]
SELECT语句中各子句的说明： SELECT子句 SELECT子句 INTO 子句 FROM 子句 WHERE 子句 HAVING 子句 UNION运算符 COMPUTE子句 指定由查询返回的列 创建新表并将结果行插入新表中 指定从其中检索行的表 指定查询条件 指定组或聚合的搜索条件 将两个或更多查询的结果组合为单个结果集，该结果集包含联合查询中的所有查询的全部行 生成合计作为附加的汇总列出现在结果集的最后。当与BY一起使用时，COMPUTE子句在结果集内生成控制中断和分类汇总。可在同一查询内指定COMPUTE BY和COMPUTE FOR子句用于指定BROWSE或XML选项 描述 GROUP BY 子句 指定查询结果的分组条件 ORDER BY 子句 指定结果集的排序 FOR子句 OPTION子句 应在整个查询中使用指定的查询提示。每个查询提示只能指定一次，但允许指定多个查询提示。用该语句只可能指定一个OPTION子句。查询提示影响语句中的所有运算符。如果主查询中涉及UNION，则只有涉及UNION运算符的最后一个查询可以有OPTION子句。 例6、在SELECT语句中查询指定的列名 COL EMP_NAME FORMAT A20 COL SEX FORMAT A10 COL TITLE FORMAT A10
SELECT Emp_name, Sex, Title FROM dhb.Employees; （2）使用ROWNUM
（3）使用LIKE关键字实现模糊查询 Oracle的通配符及其含义 % _ ? # [] 包含零个或多个任意字符的字符串 任意单个字符 任意单个字符 表示0——9的数字 指定范围或集合中的任意单个字符 查询所有身份证号中包含ddd的员工记录
例7、SELECT EMP_NAME, TITLE, IDCard FROM DHB.Employees WHERE IDCard LIKE 'Yd%';
（3）在SELECT中使用DECODE函数 DECODE函数语法如下：
DECODE(<输入值>,<值1>,<结果1>,[,<值2>,<结果2>…][,<默认结果>])
如果<输入值>等于<值1>，则DECODE函数返回<结果1>；如果<输入值>等于<值2>，则DECODE函数返回<结果2>，以此类推。如果参数列表中没有与<输入值>相等的值，则DECODE函数返回<默认结果>
例8、SELECT Emp_name, DECODE(Sex, '男', '先生', '女', '女士', '未知') AS Sex FROM DHB.Employees;
（4）在SELECT中使用CASE函数
CASE函数还可以根据不同逻辑表达式是否成立来决定函数的返回值，语法如下： CASE
WHEN <逻辑表达式1> THEN <结果1> [WHEN <逻辑表达式2> THEN <结果2> …] [ELSE <默认结果>] END
例9：在Employees中，将员工工资分3个级别，工资小于等于3000的级别为低，工资大于3000且小于5000的级别为中，工资大于等于5000的级别为高。
SELECT Emp_name, Wage, CASE WHEN Wage<=3000 THEN '低' WHEN Wage>3000 AND Wage<5000 THEN '中' WHEN Wage>=5000 THEN '高' END AS GRADE FROM DHB.Employees;
（5）保存查询结果
在CREATE TABLE语句中使用SELECT子句可以将查询结果集填充到新建的表中。新表的
结构由选择列表中列的特性定义。语法如下： CREATE TABLE <新表名> AS
例10：将办公室的所有员工的姓名和职务信息保存到表Office中： CREATE TABLE DHB.Office AS SELECT e.Emp_Name, e.Title
FROM DHB.Employees e, DHB.Departments d
WHERE e.Dep_id = d.Dep_Id AND d.Dep_Name = '办公室';
思考题：
1、创建用户，指定密码、表空间。用户名命名以本人姓名的缩写+_+学号后两位命名。 2、创建如下表，将表保存到自己的用户方案中。 学生表：student
字段名称 字段类型 约束 sno varchar2(10) 主键 sname varchar2(20) sage number(2) ssex varchar2(5)
CREATE TABLE student
(sno varchar2(10) Primary Key, sname varchar2(20), sage number(2),
ssex varchar2(5));
教师表：teacher
tno varchar2(10) 主键 tname varchar2(20)
CREATE TABLE teacher
(tno varchar2(10) Primary Key, tname varchar2(20));
课程表：course
cno varchar2(10) 与tno联合做主键 cname varchar2(20) tno varchar2(20)
CREATE TABLE course
(cno varchar2(10), cname varchar2(20), tno varchar2(20), Primary Key(cno,tno));
成绩表：sc
sno varchar2(10) 与cno联合做主键 cno varchar2(10) score number(4,2)
create table sc( sno varchar2(10), cno varchar2(10), score number(4,2), primary key (sno,cno) );
3\\为各表输入数据。
insert into student values ('s001','张三',23,'男'); insert into student values ('s002','李四',23,'男'); insert into student values ('s003','吴鹏',25,'男'); insert into student values ('s004','琴沁',20,'女'); insert into student values ('s005','王丽',20,'女'); insert into student values ('s006','李波',21,'男'); insert into student values ('s007','刘玉',21,'男'); insert into student values ('s008','萧蓉',21,'女'); insert into student values ('s009','陈萧晓',23,'女'); insert into student values ('s010','陈美',22,'女'); commit;
insert into teacher values ('t001', '刘阳'); insert into teacher values ('t002', '谌燕'); insert into teacher values ('t003', '胡明星'); commit;
insert into course values ('c001','J2SE','t002'); insert into course values ('c002','Java Web','t002'); insert into course values ('c003','SSH','t001'); insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003'); insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002'); insert into course values ('c008','DIV+CSS','t001'); insert into course values ('c009','PHP','t003'); insert into course values ('c010','EJB3.0','t002'); commit;
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9); insert into sc values ('s003','c001',81.9); insert into sc values ('s004','c001',60.9); insert into sc values ('s001','c002',82.9); insert into sc values ('s002','c002',72.9); insert into sc values ('s003','c002',81.9); insert into sc values ('s001','c003','59'); commit;
4、 查询每门课程被选修的学生数。
SELECT cno,count(sno) FROM sc GROUP BY cno Order by cno;
5、 查询出只选修了一门课程的全部学生的学号和姓名。
SELECT sc.sno,student.sname FROM sc,student
Where sc.sno= student.sno
GROUP BY sc.sno,student.sname HAVING count(cno)=1 ;
6、查询男生、女生人数。 7、查询姓“张”的学生名单。
8、查询同名同姓学生名单，并统计同名人数。 9、1981 年出生的学生名单。
10、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列。
11、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩。 12、统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
13、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分。 14、按各科平均成绩从低到高和及格率的百分数从高到低顺序。
15、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]。
实验五
实验名称：PL/SQL编程 实验学时：4 实验目的:
1) 熟练掌握PL/SQL程序设计的基本知识。
2) 熟练掌握PL/SQL中控制结构的使用。具体包括选择结构语句（IF语句和CASE语句），
循环结构（四种循环结构）。 3) 熟练使用PL/SQL中系统函数。 4) 掌握PL/SQL中异常处理语句的使用
5) 掌握PL/SQL中SELECT语句和DML语句的综合运用。
实验内容及步骤
1、条件语句IF的语法及使用 IF <条件表达式> THEN
<执行语句> …… <执行语句n> [ELSIF <条件表达式> THEN
<执行语句> …… <执行语句n> …… ELSE
<执行语句>] END IF;
例1：声明一个整型变量Num，使用IF语句判断Num变量是正数、负数或0。 SET ServerOutput ON; DECLARE
Num INTEGER := -11; BEGIN
IF Num < 0 THEN
dbms_output.put_line('负数'); ELSIF Num >0 THEN
dbms_output.put_line('正数'); ELSE
dbms_output.put_line('0'); END IF; END;
2、分支语句CASE 语法： CASE <变量>
WHEN <表达式1> THEN 值1 WHEN <表达式2> THEN 值2 ……
WHEN <表达式n> THEN 值n ELSE 值n + 1 END;
例2、使用CASE语句根据给定的整数输出对应的星期值： SET ServerOutput ON; DECLARE
varDAY INTEGER := 3; Result VARCHAR2(20); BEGIN
Result := CASE varDAY WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三' WHEN 4 THEN '星期四' WHEN 5 THEN '星期五' WHEN 6 THEN '星期六' WHEN 7 THEN '星期七' ELSE '数据越界' END;
dbms_output.put_line(Result); END;
3、练习循环结构语句的使用方法。 （1）循环语句LOOP…EXIT…END LOOP
<程序块1>
IF <条件表达式> THEN EXIT END IF
<程序块2> END LOOP;
例1、计算1到3的累加和。 SET ServerOutput ON; DECLARE
v_Num INTEGER := 1; v_Sum INTEGER := 0; BEGIN LOOP
v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); IF v_Num = 3 THEN EXIT; END IF;
dbms_output.put_line(' + '); v_Num := v_Num + 1; END LOOP;
dbms_output.put_line(' = ');
dbms_output.put_line(v_Sum); END;
（2）循环语句LOOP…EXIT WHEN…END LOOP
<程序块1>
EXIT WHEN <条件表达式> <程序块2> END LOOP;
例2、计算1到3的累加和。 SET ServerOutput ON; DECLARE
v_Num INTEGER := 1; v_Sum INTEGER := 0; BEGIN LOOP
v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); EXIT WHEN v_Num = 3; dbms_output.put_line(' + '); v_Num := v_Num + 1; END LOOP;
dbms_output.put_line(' = '); dbms_output.put_line(v_Sum); END;
（3）循环语句WHILE…LOOP…END LOOP WHILE <条件表达式> LOOP
<程序块> END LOOP;
（4）循环语句FOR…IN…LOOP…END LOOP FOR <循环变量> IN <初始值> .．<终止值> LOOP
<程序块> END LOOP;
例3、计算1到3的累加和。 SET ServerOutput ON; DECLARE
v_Num INTEGER; v_Sum INTEGER := 0; BEGIN
FOR v_Num IN 1..3 LOOP
v_Sum := v_Sum + v_Num;
dbms_output.put_line(v_Num); IF v_Num < 3 THEN
dbms_output.put_line(' + '); END IF; END LOOP;
dbms_output.put_line(' = '); dbms_output.put_line(v_Sum); END;
4、练习异常处理语句的使用方法。 EXCEPTION
WHEN <异常情况名> THEN <异常处理代码>
WHEN <异常情况名> THEN <异常处理代码> ……
WHEN OTHERS THEN <异常处理代码> 例1、
SET SERVEROUTPUT ON; DECLARE
x NUMBER; BEGIN
x:= 'a123';--向NUMBER类型的变量X中赋值字符串，导致异常 EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('数据类型错误'); END; 例2、
SET SERVEROUTPUT ON; DECLARE
v_result NUMBER(10):=0; BEGIN
v_result:= 100/0;
DBMS_OUTPUT.PUT_LINE('结果是：'||v_result); EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('除数是零！默认用1替代除数，结果是：'||100/1); END;
思考题： 1、 编写PL/SQL程序，使用LOOP?EXIT?END语句计算1—100之间所有偶数之和。 2、 创建表departments和表employees, 并为两张表输入若干数据。
表departments结构：
字段名称 字段类型 约束 Dep_id number 主键 Dep_name varchar2(20) 表employees结构：
字段名称 字段类型 约束 emp_id number 主键 emp_name varchar2(20) sex varchar2(20) title varchar2(20) wage number idcard varchar2(12)
dep_id number 外键 3、 编写PL/SQL程序，查询5号员工工资，如果工资小于3000，则加200员工资，并
提示信息“5号员工工资已更新。”，如果工资大于3000，则提示信息“5号员工工资为XXX，已达到规定标准。”
4、 编写PL/SQL程序，查询1号员工的工资，使用CASE语句输出其工资等级。工资
小于等于3000，等级为―低‖；工资大于3000，小于5000，等级为‖中‖；工资大于等于5000，等级为高；
5、 编写PL/SQL程序，查询的departments表中是否有部门号为“6”的记录，如果没
有，插入新记录部门号为6，部门名称为“后勤部”。如果有，显示查询结果。
实验六
实验名称：使用游标、存储过程和触发器 实验学时：2 实验目的:
（1） 了解游标的概念和工作原理。 （2） 了解存储过程的分类和使用方法。 （3） 了解触发器的概念。
（4） 学习编写和执行自定义过程。 （5） 学习创建和使用触发器。
实验内容及步骤 1、游标的使用
（一）使用显式游标 （1）说明游标。
DECLARE CURSOR <游标名> [ (<参数列表>) ] IS
OPEN <游标名> [ (<参数列表>) ]; （3）读取数据。
FETCH <游标名> INTO <变量列表>; （4）关闭游标。 CLOSE <游标名>; 例1、使用游标读取1号部门的名称。 DECLARE --开始声明部分
varName VARCHAR2(50); --声明变量,用来保存游标中的部门名称 --定义游标, varId为参数, 指定部门编号 CURSOR MyCur(varId NUMBER) IS
SELECT Dep_Name FROM dhb.Departments WHERE Dep_id = varId; BEGIN --开始程序体
OPEN MyCur(1); --打开游标,参数为1,表示读取部门编号为1的记录 FETCH MyCur INTO varName; --读取当前游标位置的数据 CLOSE MyCur; --关闭游标
dbms_output.put_line('部门名称:' || varName); --显示读取的数据 END; --结束程序体
（二）带子查询的游标for循环的语法如下： FOR <记录名> IN (SELECT 子查询) LOOP 语句1； 语句2； ?
语句n；
END LOOP;
例2、带子查询的游标for循环例子： /* 打开显示模式 */
SET ServerOutput ON; BEGIN --开始程序体
FOR var_DeptRecord IN (SELECT Dep_id, Dep_Name FROM dhb.Departments) LOOP /* 显示保存在记录变量var_DeptRecord中的数据 */
dbms_output.put_line('部门编号:' || var_DeptRecord.Dep_Id ||', 部门名称:' || var_DeptRecord.Dep_Name); END LOOP;
END; --结束程序体 /
2、过程的定义及使用
CREATE PROCEDURE语句来创建过程的语法格式： CREATE [ OR REPLACE ] PROCEDURE<过程名> [ <参数列表> ] IS | AS [ <局部变量声明> ] BEGIN
<过程体>
END [ <过程名> ]; 例3、创建过程GetDepAvgWage ，此过程的功能是根据输入的部门号获取指定部门的平均工资。
CREATE OR REPLACE PROCEDURE DHB.GetDepAvgWage ( v_DepId IN NUMBER, v_AvgWage OUT NUMBER) AS BEGIN
SELECT AVG(Wage) INTO v_AvgWage
FROM DHB.Employees WHERE Dep_Id = v_DepId; END; /
执行过程GetDepAvgWage ，获取1号部门的平均工资。 --执行在使用输出参数时，必须首先定义参数的类型。 SET ServerOutput ON; DECLARE
AvgWage NUMBER := 0; BEGIN
DHB.GetDepAvgWage(1, AvgWage); dbms_output.put_line(AvgWage); END; /
3、触发器的创建及使用。
CREATE TRIGGER语句来创建触发器：
CREATE [ OR REPLACE ] TRIGGER <触发器名> {BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] ON <表名>|<视图名> [ FOR EACH ROW ]
[ WHEN <条件表达式> ]
例4、创建一个语句级DML触发器。
（1）首先创建表Test和表LogTable（记录对表Test的更新操作） CREATE TABLE dhb.Test ( id INTEGER,
name VARCHAR2(50)); CREATE TABLE dhb.LogTable ( log_date DATE,
action VARCHAR2(50));
（2）创建语句触发器LogUpdateTrigger
CREATE OR REPLACE TRIGGER dhb.LogUpdateTrigger AFTER INSERT OR UPDATE OR DELETE ON dhb.Test
DECLARE log_action VARCHAR2(50); BEGIN
IF INSERTING THEN log_action := 'Insert'; ELSIF UPDATING THEN log_action := 'Update'; ELSIF DELETING THEN log_action := 'Delete'; ELSE DBMS_OUTPUT.PUT_LINE(' ..'); END IF;
INSERT INTO dhb.LogTable (log_date, action) VALUES (SYSDATE, log_action); END; /
（3）在表Test上执行插入、修改、删除操作： INSERT INTO dhb.Test VALUES(1, 'Insert'); UPDATE dhb.Test SET name='Update'; DELETE FROM dhb.Test WHERE id=1; COMMIT;
（4）查看表LogTable的内容： SELECT * FROM DHB.LogTable;
例5、创建一个行级触发器MyTrigger，它的作用是当dhb.Departments中Dep_id列的值发生变化时，自动更新表Employees中的Dep_id列的值，从而保证数据完整性。 CREATE OR REPLACE TRIGGER DHB.MyTrigger AFTER UPDATE ON DHB.Departments FOR EACH ROW BEGIN
UPDATE DHB.Employees SET Dep_id = :new.Dep_id WHERE Dep_id = :old.Dep_id; END; /
验证触发器的功能：
UPDATE DHB.Departments SET Dep_id=200 WHERE Dep_id=1; Commit;
SELECT Emp_name, Dep_id FROM DHB.Employees;
思考题：
（1） 创建游标mycur，从表employees中读取1号部门的员工姓名，职务。并输出“员工
姓名：XXX，职务：XXX。”
（2） 创建存储过程GetGrade，查询指定员工的工资，使用CASE语句输出其工资等级。
工资小于等于3000，等级为―低‖；工资大于3000，小于5000，等级为‖中‖；工资大于等于5000，等级为高。并执行该存储过程。
（3） 创建存储过程UpdateWage，查询指定员工的工资，如果工资小于3000，则加200
工资，并提示信息“XX号员工工资已更新。”，如果工资大于3000，则提示信息“XX号员工工资为XXX，已达到规定标准。”
（4） 创建触发器MyTrigger，它的作用是当表departments中的记录被删除后，自动删除
表employees中的对应的员工记录，从而保证数据的完整性。
实 验 报 告
实验名称：表的管理 实验课时：4课时 实验地点：E座305
实验时间：2014年 5月 6日 星期二 第十二周 实验目的及要求：
1) 了解Oracle表和视图的概念。 2) 熟练掌握使用SQL语句创建表。 3) 学习使用SELECT语句查询数据。
实验环境：
1) 硬件设备：PC机一台 2) 操作系统：Windows XP 3) 应用工具：Oracle 10g
实验内容：（算法、程序、步骤和方法）
1) 创建用户，指定密码、表空间。用户名命名以本人姓名的缩写+_+学号后
两位命名。代码如下： create user ty_06 identified by 123
default tablespace users temporary tablespace temp;
2) 创建如下表，将表保存到自己的用户方案中。代码如下： create table student
(sno varchar2(10) Primary Key, sname varchar2(20), sage number(2), ssex varchar2(5));
create table teacher
(tno varchar2(10) Primary Key, tname varchar2(20));
create table course
(cno varchar2(10), cname varchar2(20), tno varchar2(20), Primary Key(cno,tno));
create table sc( sno varchar2(10), cno varchar2(10), score number(4,2), primary key (sno,cno) );
3) 为各表输入数据。代码如下： /*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男'); insert into student values ('s002','李四',23,'男'); insert into student values ('s003','吴鹏',25,'男'); insert into student values ('s004','琴沁',20,'女'); insert into student values ('s005','王丽',20,'女'); insert into student values ('s006','李波',21,'男'); insert into student values ('s007','刘玉',21,'男'); insert into student values ('s008','萧蓉',21,'女'); insert into student values ('s009','陈萧晓',23,'女'); insert into student values ('s010','陈美',22,'女'); commit;
/******************初始化教师表***********************/ insert into teacher values ('t001', '刘阳'); insert into teacher values ('t002', '谌燕'); insert into teacher values ('t003', '胡明星'); commit;
/***************初始化课程表****************************/ insert into course values ('c001','J2SE','t002'); insert into course values ('c002','Java Web','t002'); insert into course values ('c003','SSH','t001'); insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003'); insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002'); insert into course values ('c008','DIV+CSS','t001'); insert into course values ('c009','PHP','t003'); insert into course values ('c010','EJB3.0','t002'); commit;
/***************初始化成绩表***********************/ insert into sc values ('s001','c001',78.9); insert into sc values ('s002','c001',80.9); insert into sc values ('s003','c001',81.9); insert into sc values ('s004','c001',60.9); insert into sc values ('s001','c002',82.9); insert into sc values ('s002','c002',72.9); insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59'); commit;
4) 查询每门课程被选修的学生数。代码如下： SELECT cno,count(sno) FROM sc GROUP BY cno Order by cno;
5) 查询出只选修了一门课程的全部学生的学号和姓名。代码如下： SELECT sc.sno,student.sname FROM sc,student Where sc.sno= student.sno
GROUP BY sc.sno,student.sname HAVING count(cno)=1 ; 6) 查询男生、女生人数。代码如下： SELECT ssex,COUNT(SSEX) FROM STUDENT GROUP BY SSEX;
7) 查询姓“张”的学生名单。代码如下： Select sname From student
Where sname like ’张%’;
8) 查询同名同姓学生名单，并统计同名人数。代码如下：
select sname,count(*) from student
group by sname having count(*)>1;
9) 查询1981 年出生的学生名单。代码如下：
Select sname From student
where to_char(sysdate,'yyyy')-sage =1981;
10) 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，
按课程号降序排列。代码如下： Select cno,cname, (select Avg(Score) From sc
Where cno=course.cno) 平均成绩 From course
Order by 平均成绩，cno Desc;
11) 查询平均成绩大于85 的所有学生的学号、姓名和平均成绩。代码如下：
select st.sno,st.sname,avg(score) from student st left join sc on sc.sno=st.sno
group by st.sno,st.sname having avg(score)>85;
12) 统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课
程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。代码如下：
Select distinct cno, Count sno From sc
Group by cno having count(sno)>10 Order by 2,cno desc;
13) 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最
低分。代码如下： Select cno,
(Select Max(Score) From sc Where sc.cno= Course.cno ) 最高分, (Select Min(Score) From sc Where sc.cno=course.cno ) 最低分 From Course;
14) 按各科平均成绩从低到高和及格率的百分数从高到低顺序。代码如下：
select cno,avg(score),sum(case when score>=60 then 1 else 0 end)/count(*)*100||'%'as 及格率 from sc
group by cno
order by abg(score),及格率 desc; 15) 统计各科分数段人数,请以下列格式显示:
课程ID,课程名称, [100-85],[85-70],[70-60],[ <60]。
代码如下：
Select sc.cno,c.cname,
sum(case when score between 85 and 100 then 1 else 0 end) AS \
sum(case when score between 70 and 85 then 1 else 0 end) AS \
sum(case when score between 60 and 70 then 1 else 0 end) AS \
sum(case when score <60then 1 else 0 end) AS \ from sc,course c where sc.cno=c.cno
group by sc.cno,c.cname;
小结：
通过这次上机实验，我有以下收获：
对表的创建有了更深层次的了解，知道如何创建表，对表的查询
等操作更加熟悉，另外，Oracle的相关查询操作和SQL语句大同小异，很容易理解，对格式的要求更加精简。
但是还上机操作过程中还存在以下几个方面的不足：
语句调试过程中出现了很多类似的错误，select语句没写完就接了
下个语句，符号经常中英文混淆，基本函数不了解，比如调用系统时间年份的sysdate等，以后要加强对细节的注意，细节决定成败。
指导老师评议：
成绩： 指导老师签名：
年 月 日
实 验 报 告
实验名称：PL/SQL编程 实验课时：4课时 实验地点：
实验时间： 年 月 日 星期 第 周 实验目的及要求：
1) 熟练掌握PL/SQL程序设计的基本知识。
2) 熟练掌握PL/SQL中控制结构的使用。具体包括选择结构语句（IF语句和
CASE语句），循环结构（四种循环结构）。 3) 熟练使用PL/SQL中系统函数。 4) 掌握PL/SQL中异常处理语句的使用
5) 掌握PL/SQL中SELECT语句和DML语句的综合运用。
实验环境：
1) 硬件设备：PC机一台 2) 操作系统：Windows XP 3) 应用工具：Oracle 10g
实验内容：（算法、程序、步骤和方法）
1） 2）
以SYS用户登录系统。代码如下：
Sqlplus sys/123 as sysdba
创建用户，指定密码、表空间。用户名命名以本人姓名的缩写+‘_’+学号最后两位。代码如下： Create user ty_06 identified by 123
default tablespace users temporary tablespace temp;
为该用户授予sysdba、connect、resource权限。代码如下：
Grant sysdba to ty_06; Grant connect to ty_06; Grant resource to ty_06;
以自己的用户身份登录。代码如下：
Connect ty_06/123 as sysdba
在本用户方案中创建表departments, 并为该表输入若干数据。
3）
4）
5）
程序代码如下：
create table departments
(dep_id number primary key, dep_name varchar2(20));
INSERT INTO departments VALUES ('1','人事部'); INSERT INTO departments VALUES ('2','办公室'); INSERT INTO departments VALUES ('3','财务部'); INSERT INTO departments VALUES ('4','技术部'); INSERT INTO departments VALUES ('5','服务部'); commit; 执行结果如下：
6） 在本用户方案中创建表employees, 并为该表输入若干数据。 程序代码如下：
create table employees
(emp_id number primary key, emp_name varchar2(20), sex varchar2(2), title varchar2(10), wage number,
idcard varchar2(12), dep_id number,
foreign key(dep_id) references departments(dep_id));
INSERT INTO employees VALUES ('1','张三','男','部门经理',6000, '1101123aadx1','1');
INSERT INTO employees VALUES ('2','李四','男','职员',3000, '1101123dddx2','1'); INSERT INTO employees VALUES ('3','王五','女','职员',3500, '1101123aadx3','1'); INSERT INTO employees VALUES ('4','赵六','男','部门经理',6500, '1101123dddx4','2');
INSERT INTO employees VALUES ('5','高七','男','职员',2500, '1101123aadx5','2');
INSERT INTO employees VALUES ('6','马八','男','职员',3100, '1101123dddx6','2'); INSERT INTO employees VALUES ('7','钱九','女','部门经理',5000, '1101123aadx7','3');
INSERT INTO employees VALUES ('8','孙十','男','职员',2800, '1101123dddx8','3'); commit;
执行结果如下：
7）
编写PL/SQL程序，使用LOOP?EXIT?END语句计算1—100之间所有偶数之和。 程序代码如下：
SET ServerOutput ON; DECLARE
v_Num INTEGER := 2; v_Sum INTEGER := 0; BEGIN LOOP
v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); EXIT WHEN v_Num = 100; dbms_output.put_line(' + '); v_Num := v_Num + 2; END LOOP;
dbms_output.put_line(' = '); dbms_output.put_line(v_Sum); END; 执行结果如下：
8）
编写PL/SQL程序，查询5号员工工资，如果工资小于3000，则加200工资，并提示信息“5号员工工资已更新。”，如果工资大于3000，则提示信息“5号员工工资为XXX，已达到规定标准。” 程序代码如下： DECLARE
ty employees.wage%type; BEGIN
Select wage into ty From employees
Where employees.emp_id =5; If ty<3000 then Update employees
Set employees.wage= employees.wage+200 Where employees.emp_id=5; Commit;
DBMS_OUTPUT.PUT_LINE('5号工资已更新'); Else
DBMS_OUTPUT.PUT_LINE('5号工资为'||ty||'已达到规定标准'); END if; Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE('不存在该员工'); When others then
DBMS_OUTPUT.PUT_LINE('其他异常'); End; /
执行结果如下：
9） 编写PL/SQL程序，查询1号员工的工资，使用CASE语句输出其工资等
级。工资小于等于3000，等级为―低‖；工资大于3000，小于5000，等级为‖中‖；工资大于等于5000，等级为高； 程序代码如下：
declare
v_id number := 1; v_wage number ; result varchar2(20); begin
select wage into v_wage from employees where emp_id=v_id ; result := case
when v_wage < 3000 then '低'
when v_wage > 3000 and v_wage < 5000 then '中' else '高' end;
dbms_output.put_line(result); end; /
执行结果如下：
10） 编写PL/SQL程序，查询的departments表中是否有部门号为“6”的记录，
如果没有，插入新记录部门号为6，部门名称为“后勤部”。如果有，显示查询结果。 程序代码如下： declare
v_depname departments.dep_name%type; v_grade varchar2(2); begin
select departments.dep_name into v_depname from departments
where departments.dep_id=6;
dbms_output.put_line('已存在部门号为6的记录，其部门名称是'||v_depname); exception
when no_data_found then
insert into departments values('6','后勤部'); dbms_output.put_line('插入了一条新记录！'); commit; end;
执行结果如下：
小结：
通过这次上机实验，我有以下收获：
但是还上机操作过程中还存在以下几个方面的不足：
指导老师评议：
成绩： 指导老师签名：
年 月
日
实 验 报 告
实验名称：使用游标、存储过程和触发器 实验课时：2课时 实验地点：E305
实验时间：2014年5月 21日 星期 三 第14 周 实验目的及要求：
（1） 了解游标的概念和工作原理。 （2） 了解存储过程的分类和使用方法。 （3） 了解触发器的概念。
（4） 学习编写和执行自定义过程。 （5） 学习创建和使用触发器。
实验环境：
（1） 硬件设备：PC机一台 （2） 操作系统：Windows XP （3） 应用工具：Oracle 11g
实验内容：（算法、程序、步骤和方法）
（1） 创建游标mycur，从表employees中读取1号部门的员工姓名，职务。并
输出“员工姓名：XXX，职务：XXX。” 程序代码如下： DECLARE
CURSOR mycur IS
SELECT emp_name, title FROM employees where emp_id=1; BEGIN
FOR ty IN mycur LOOP
dbms_output.put_line('员工姓名:'||ty.emp_Name||'职务:'||ty.title); END LOOP; END; /
执行结果如下：
（2） 创建存储过程GetGrade，查询指定员工的工资，使用CASE语句输出其
工资等级。工资小于等于3000，等级为―低‖；工资大于3000，小于5000，等级为‖中‖；工资大于等于5000，等级为高。并执行该存储过程。 创建存储过程代码如下：
create or replace procedure getgrade (ty_depid number) as
ty_wage employees.wage%type; ty_grade varchar2(5); begin
select wage into ty_wage from employees
where emp_id=ty_depid; ty_grade:=case
when ty_wage<=3000 then '低'
when ty_wage>=3000 and ty_wage<5000 then '中' when ty_wage>=5000 then '高' end;
dbms_output.put_line('该员工工资等级为'||ty_grade); end; /
执行存储过程代码如下： execute getgrade(3); call getgrade(3); 执行结果如下：
（3） 创建存储过程UpdateWage，查询指定员工的工资，如果工资小于3000，
则加200工资，并提示信息“XX号员工工资已更新。”，如果工资大于3000，则提示信息“XX号员工工资为XXX，已达到规定标准。” 创建存储过程代码如下：
create or replace procedure updatewage (ty_depid number) as
ty_wage employees.wage%type; BEGIN
Select wage into ty_wage From employees
Where employees.emp_id =ty_depid; If ty_wage <3000 then Update employees
Set employees.wage= employees.wage+200 Where employees.emp_id= ty_depid; Commit;
DBMS_OUTPUT.PUT_LINE('5号工资已更新'); Else
DBMS_OUTPUT.PUT_LINE('5号工资为'|| ty_wage ||'已达到规定标准'); END if; Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE('不存在该员工'); When others then
DBMS_OUTPUT.PUT_LINE('其他异常'); End; /
执行存储过程代码如下： Call updatewage(3); 执行结果如下：
（4） 创建触发器MyTrigger，它的作用是当表departments中的记录被删除后，
自动删除表employees中的对应的员工记录，从而保证数据的完整性。 程序代码如下：
create or replace trigger mytrigger after update on departments for each row begin
update employees set dep_id = :new.dep_id where dep_id = :old.dep_id; end;
执行结果如下：
小结：
通过这次上机实验，我有以下收获：
但是还上机操作过程中还存在以下几个方面的不足：
成绩：指导老师签名：年 月 日
指导老师评议：
正在阅读：
oracle实验指导书之全本05-26
PLC中文手册与结构化文本编程04-23
管道3PE防腐监理基本要求06-08
四川成都高三物理模拟试卷11-29
西安市高三上学期地理第一次月考试卷A卷05-09
P6V7安装程序步骤简介09-30
曾小贤电台经典语录02-06
C语言复习冲刺资料11-05
- 多层物业服务方案
- （审判实务）习惯法与少数民族地区民间纠纷解决问题（孙 潋）
- 人教版新课标六年级下册语文全册教案
- 词语打卡
- photoshop实习报告
- 钢结构设计原理综合测试2
- 2014年期末练习题
- 高中数学中的逆向思维解题方法探讨
- 名师原创 全国通用2014-2015学年高二寒假作业 政治（一）Word版
- 北航《建筑结构检测鉴定与加固》在线作业三
- XX县卫生监督所工程建设项目可行性研究报告
- 小学四年级观察作文经典评语
- 浅谈110KV变电站电气一次设计-程泉焱(1)
- 安全员考试题库
- 国家电网公司变电运维管理规定(试行)
- 义务教育课程标准稿征求意见提纲
- 教学秘书面试技巧
- 钢结构工程施工组织设计
- 水利工程概论论文
- 09届九年级数学第四次模拟试卷
- 指导书
- 全本
- 实验
- oracle
- 语文s版三年级下册1-4单元学案
- 理财产品代销协议
- 合成制动液719型项目可行性研究报告（目录） - 图文
- 微机原理与接口技术期末复习题
- 2017年尔雅科学通史课后题及答案
- 专题11 名句默写和文学常识（第01期）-2017届高三语文百所名校好
- Pt100铂电阻测温电路（稳定版） - 图文
- 幼儿园安全知识判断题
- 汞矿
- 重庆市渝中区巴蜀中学2017-2018学年高考数学三模试卷 Word版含解
- 高二数学选修4-4教案08直线的参数方程
- 职业技能鉴定规范·电气试验工
- 古代自称、妃嫔职位、
- 修改病文练习(5中)
- 高边坡施工方案
- 建筑施工组织课程设计 - 某校区办公楼单位工程施工组织设计
- 2013年浙大远程刑事诉讼法在线作业
- 装饰合同甲种本
- 中国音乐史名词解释2
- 夏、商、西周的政治制度说课稿