最常见的oracle数据库面试题、oracle数据库开发面试题详解数据库

选择题（20分,每题4分）
1. 利用游标来修改数据时，所用的..FOR UPDATE充分利用了事务的哪个特性？（）
A、原子性
B、一致性
C、永久性
D、隔离性

下列说法，正确的说法是（）
A、只要在存储过程中有增删改语句，一定加自治事务
B、在函数内可以修改表数据
C、函数不能递归调用
D、以上说法都不对

下列说法正确的是，多选（）
A、在PLSQL自定义函数中如果包含UPDATE、DELETE、INSERT语句，不必在函数体内给出COMMIT
B、自定义函数可以在SQL语句中调用、也可以在PLSQL块中调用
C、自定义函数可以返回表类型
D、自定义函数中的参数可以是OUT类型

关于触发器，下列说法正确的是（）
A、可以在表上创建INSTEAD OF 触发器
B、语句级触发器不能使用“:OLD”和“:NEW”
C、行级触发器不能用于审计功能
D、触发器可以显式调用

PL/SQL下定义package描述正确的是（）
A. 包的声明中必须按照类型、变量、异常、子程序、游标的定义顺序来声明内容，不允许将游标定义在类型的前面
B. 包的声明中定义的子程序在包体中必须有实现，但游标可以没有
C. 包体中的所有子程序定义不一定都必须是在包声明中定义过的
D. 包体中的所有内容都必须在包声明中定义，否则会被视为无效内容
基础题（50分）

创建程序包，包名：CUX_PLSQL_EXAM工号_PKG，包含以下函数和过程：
1. 编写一个函数， 根据学号、课程号返回学生的学科成绩。
如获取不到，则返回值 -1；
如能找到多行数据，则返回 -2；
如有其他异常，则返回 -3；
（5分）

注意：
以下的所有方法、过程，都写在上面的包里面。最后把包保存成.pck文件。
匿名块单独写。

编写一个存储过程（自治事务），记录日志信息到表HAND_LOG中，其中参数CODE（错误代码）、MSG（错误信息）为必输字段、KEY1到KEY5为非必输字段，默认为空 。（5分）

编写一个存储过程，添加10条学生信息到表HAND_STUDENT中，数据格式及逻辑，学号：s100 … 109、姓名：王001 … 王010、年龄：22、性别：学号最后一位是奇数为“男”，偶数为“女”。（5分）

编写一个存储过程，根据学号、课程号 按成绩的20%进行加分，如果增加后的分数大于100，则取消加分。同时在存储过程中返回增加后的成绩。（5分）

根据如下题目要求，编写脚本代码：

编写匿名块，调用上述程序包中函数获取选修了“胡明星”老师的学生的各科成绩，直接用DBMS_OUTPUT输出“姓名、学号、课程、成绩”。如果返回成绩异常，则调用上述自治事务存储过程记录信息（CODE=函数返回值，MSG=学生姓名）。（10分）

编写匿名块，先调用上述第3个存储过程添加学生数据。再在匿名块中创建一个和HAND_STUDENT表结构一样的表，命名为HAND__STUDENT_TEMP。然后将HAND_STUDENT表中数据全部插入到HAND__STUDENT_TEMP表中。 （10分）

编写匿名块，调用上述第4个存储过程对平均成绩在70以下的学生的各科成绩进行加分。然后直接用DBMS_OUTPUT输出“姓名、学号、课程、加分前成绩、加分后成绩”。（10分）

 进阶题（30分）

在表HAND_STUDENT上创建一个触发器，当表数据新增、更新或删除时，都在表HAND_STUDENT_HIS新增一条记录，记录LAST_UPDATE_DATE及STATUS。（10分）
说明：
1） 状态值“N”表示新增、“U”表示修改、“D”表示删除。
2） LAST_UPDATE_DATE时间记录当时数据操作时间。

编写匿名块，将所有学生的“姓名、学号、课程名、成绩”信息保存到集合中，并使用学号作为索引。然后在程序中判断学号“s200”是否存在集合中，若不存在，则在集合中新增一条数据（姓名=张三丰、学号=s200、课程=PHP，成绩=80） 。
（10分）

编写一个存储过程，没有任何参数。在程序中进行数据分析。
1） 以学生为维度，分析每个学生所学课程中的最高分和最低分。需要的结果是（姓名、学号、最高分、最高分课程名、最低分、最低分课程名）；
2） 以教师为维护，分析每个教师所教课程中的最高分和最低分。需要的结果是（教师名、课程名、课程最高分、最高分学生姓名、课程最低分、最低分学生姓名）；
将以上分析的结果数据，分别写入两个文件，文件名分别为 student.txt和teacher.txt。注意写入文件中的数据，需要换行。（10分）

答案：
一、选择题
1、D
2、B
3、ABCD
4、B
5、BC

二、基础题
– 程序包
CREATE OR REPLACE PACKAGE cux_plsql_exam3740_pkg IS

– 基础题
– 1. 编写一个函数，根据学号、课程号返回学生的学科成绩
FUNCTION get_course_core(p_student_no IN VARCHAR2,
p_course_no IN VARCHAR2) RETURN NUMBER;

– 2. 编写一个存储过程（自治事务），记录日志信息到表HAND_LOG中
PROCEDURE log_msg(p_code IN VARCHAR2,
p_msg IN VARCHAR2,
p_key1 IN VARCHAR2 DEFAULT NULL,
p_key2 IN VARCHAR2 DEFAULT NULL,
p_key3 IN VARCHAR2 DEFAULT NULL,
p_key4 IN VARCHAR2 DEFAULT NULL,
p_key5 IN VARCHAR2 DEFAULT NULL);

– 3. 编写一个存储过程，添加10条学生信息到表HAND_STUDENT中
PROCEDURE insert_student;

– 4. 编写一个存储过程，根据学号、课程号 按成绩的20%进行加分…
PROCEDURE process_add_core(p_student_no IN VARCHAR2,
p_course_no IN VARCHAR2,
x_core OUT NUMBER);

END cux_plsql_exam3740_pkg;

CREATE OR REPLACE PACKAGE BODY cux_plsql_exam3740_pkg IS

– 1. 编写一个函数，根据学号、课程号返回学生的学科成绩

FUNCTION get_course_core(p_student_no IN VARCHAR2,  
p_course_no IN VARCHAR2) RETURN NUMBER IS  
l_core NUMBER;  
BEGIN  
SELECT hsc.core  
INTO l_core  
FROM hand_student_core hsc  
WHERE hsc.student_no = p_student_no  
AND hsc.course_no = p_course_no;  
RETURN l_core;  
EXCEPTION  
WHEN no_data_found THEN  
RETURN - 1;  
WHEN too_many_rows THEN  
RETURN - 2;  
WHEN OTHERS THEN  
RETURN - 3;  
END get_course_core;

– 2. 编写一个存储过程（自治事务），记录日志信息到表HAND_LOG中

PROCEDURE log_msg(p_code IN VARCHAR2,  
p_msg IN VARCHAR2,  
p_key1 IN VARCHAR2 DEFAULT NULL,  
p_key2 IN VARCHAR2 DEFAULT NULL,  
p_key3 IN VARCHAR2 DEFAULT NULL,  
p_key4 IN VARCHAR2 DEFAULT NULL,  
p_key5 IN VARCHAR2 DEFAULT NULL) IS  
PRAGMA AUTONOMOUS_TRANSACTION;  
BEGIN  
INSERT INTO hand_log  
(code, msg, key1, key2, key3, key4, key5)  
VALUES  
(p_code, p_msg, p_key1, p_key2, p_key3, p_key4, p_key5);  
COMMIT;  
END log_msg;

– 3. 编写一个存储过程，添加10条学生信息到表HAND_STUDENT中

PROCEDURE insert_student IS  
l_rec hand_student%ROWTYPE;  
BEGIN  
FOR i IN 1 .. 10 LOOP  
l_rec.student_no := ‘s10’ || (i - 1);  
IF i < 10 THEN  
l_rec.student_name := ‘王00’ || i;  
ELSE  
l_rec.student_name := ‘王0’ || i;  
END IF;  
l_rec.student_age := 22;  
IF mod(i, 2) = 0 THEN  
l_rec.student_gender := ‘男’;  
ELSE  
l_rec.student_gender := ‘女’;  
END IF;  
INSERT INTO hand_student VALUES l_rec;  
END LOOP;  
EXCEPTION  
WHEN OTHERS THEN  
ROLLBACK;  
END insert_student;

– 4. 编写一个存储过程，根据学号、课程号 按成绩的20%进行加分…

PROCEDURE process_add_core(p_student_no IN VARCHAR2,  
p_course_no IN VARCHAR2,  
x_core OUT NUMBER) IS  
CURSOR cur_core IS  
SELECT hsc.core  
FROM hand_student_core hsc  
WHERE hsc.student_no = p_student_no  
AND hsc.course_no = p_course_no  
FOR UPDATE OF hsc.core;  
BEGIN  
FOR rec_core IN cur_core LOOP  
IF (rec_core.core + rec_core.core * 0.2) <= 100 THEN  
UPDATE hand_student_core hsc  
SET hsc.core = hsc.core + hsc.core * 0.2  
WHERE CURRENT OF cur_core;  
x_core := rec_core.core + rec_core.core * 0.2;  
ELSE  
x_core := rec_core.core;  
END IF;  
END LOOP;  
EXCEPTION  
WHEN OTHERS THEN  
ROLLBACK;  
END process_add_core; 
 
END cux_plsql_exam3740_pkg;

– 匿名块
– 根据如下题目要求，编写脚本代码
– 1. 编写匿名块，调用上述程序包中函数获取选修了“胡明星”老师的学生的各科成绩

DECLARE  
CURSOR cur_core IS  
SELECT hs.student_name,  
hs.student_no,  
hc.course_name,  
cux_plsql_exam3740_pkg.get_course_core(hsc.student_no,hsc.course_no) core  
FROM hand_student_core hsc,  
hand_course hc,  
hand_student hs  
WHERE hsc.student_no = hs.student_no  
AND hsc.course_no = hc.course_no  
AND EXISTS (SELECT 1  
FROM hand_student_core hs,  
hand_course hc,  
hand_teacher ht  
WHERE hs.course_no = hc.course_no  
AND hc.teacher_no = ht.teacher_no  
AND hs.student_no = hsc.student_no  
AND ht.teacher_name = ‘胡明星’);  
BEGIN  
dbms_output.put_line(‘姓名,学号,课程,成绩’);  
FOR rec_core IN cur_core LOOP  
IF rec_core.core NOT IN (-1,-2,-3) THEN  
dbms_output.put_line(rec_core.student_name ||’,’||rec_core.student_no ||’,’||  
rec_core.course_name ||’,’||rec_core.core);  
ELSE  
cux_plsql_exam3740_pkg.log_msg(p_code => rec_core.core,p_msg => rec_core.student_name);  
END IF;  
END LOOP;  
END;

– 2. 编写匿名块，先调用上述第3个存储过程添加学生数据…

DECLARE  
l_count NUMBER;  
BEGIN  
cux_plsql_exam3740_pkg.insert_student;  
SELECT COUNT(1)  
INTO l_count  
FROM all_tables WHERE TABLE_NAME = ‘HAND_STUDENT_TEMP’;  
IF l_count > 0 THEN  
EXECUTE IMMEDIATE ‘DROP TABLE HAND_STUDENT_TEMP’;  
END IF;  
EXECUTE IMMEDIATE ‘CREATE TABLE HAND_STUDENT_TEMP AS SELECT * FROM HAND_STUDENT’;  
END;

– 3. 编写匿名块，调用上述第4个存储过程对平均成绩在70以下的学生的各科成绩进行加分

DECLARE  
CURSOR cur_core IS  
SELECT hs.student_name,  
hs.student_no,  
hc.course_no,  
hc.course_name,  
hsc.core before_core  
FROM hand_student_core hsc,  
hand_course hc,  
hand_student hs  
WHERE hsc.course_no = hc.course_no  
AND hsc.student_no = hs.student_no  
AND EXISTS (SELECT hs.student_no  
FROM hand_student_core hs  
WHERE hs.student_no = hsc.student_no  
GROUP BY hs.student_no  
HAVING AVG(hs.core) < 70);  
l_after_core NUMBER;  
BEGIN  
dbms_output.put_line(‘姓名,学号,课程,加分前成绩,加分后成绩’);  
FOR rec_core IN cur_core LOOP  
cux_plsql_exam3740_pkg.process_add_core(p_student_no => rec_core.student_no,  
p_course_no => rec_core.course_no,  
x_core => l_after_core); 
 
dbms_output.put_line(rec_core.student_name ||','||rec_core.student_no ||','|| 
                     rec_core.course_name ||','||rec_core.before_core ||','||l_after_core); 
END LOOP;  
END;

三、进阶题
– 1.在表HAND_STUDENT上创建一个触发器，当表数据新增、更新或删除时，都在表

HAND_STUDENT_HIS新增一条记录  
CREATE OR REPLACE TRIGGER hand_student_trg  
AFTER INSERT OR UPDATE OR DELETE ON hand_student  
FOR EACH ROW  
DECLARE  
BEGIN  
IF inserting THEN  
INSERT INTO hand_student_his  
(student_no,  
Student_Name,  
student_age,  
student_gender,  
last_update_date,  
status)  
VALUES  
(:NEW.student_no, :NEW.Student_Name, :NEW.student_age, :NEW.student_gender, SYSDATE, ‘N’);  
ELSIF updating THEN  
INSERT INTO hand_student_his  
(student_no,  
Student_Name,  
student_age,  
student_gender,  
last_update_date,  
status)  
VALUES  
(:NEW.student_no, :NEW.Student_Name, :NEW.student_age, :NEW.student_gender, SYSDATE, ‘U’);  
ELSIF deleting THEN  
INSERT INTO hand_student_his  
(student_no,  
Student_Name,  
student_age,  
student_gender,  
last_update_date,  
status)  
VALUES  
(:OLD.student_no, :OLD.Student_Name, :OLD.student_age, :OLD.student_gender, SYSDATE, ‘D’);  
END IF;  
EXCEPTION  
WHEN OTHERS THEN  
NULL;  
END hand_student_trg;

– 2. 编写匿名块，将所有学生的“姓名、学号、课程名、成绩”信息保存到集合中

DECLARE  
TYPE core_rec IS RECORD (student_name hand_student.student_name%TYPE,  
student_no hand_student.student_no%TYPE,  
course_name hand_course.course_name%TYPE,  
core hand_student_core.core%TYPE);  
TYPE core_tdl_type IS TABLE OF core_rec INDEX BY hand_student.student_no%TYPE;  
core_tdl core_tdl_type;  
CURSOR cur_core IS  
SELECT hs.student_name,  
hs.student_no,  
hc.course_name,  
hsc.core  
FROM hand_student_core hsc,  
hand_course hc,  
hand_student hs  
WHERE hsc.course_no = hc.course_no  
AND hsc.student_no = hs.student_no;  
l_student_no hand_student.student_no%TYPE;  
BEGIN  
FOR rec_core IN cur_core LOOP  
core_tdl(rec_core.student_no).student_name := rec_core.student_name;  
core_tdl(rec_core.student_no).student_no := rec_core.student_no;  
core_tdl(rec_core.student_no).course_name := rec_core.course_name;  
core_tdl(rec_core.student_no).core := rec_core.core;  
END LOOP;  
BEGIN  
l_student_no := core_tdl(‘s200’).student_no;  
EXCEPTION  
WHEN NO_DATA_FOUND THEN  
core_tdl(‘s200’).student_name := ‘张三丰’;  
core_tdl(‘s200’).student_no := ‘s200’;  
core_tdl(‘s200’).course_name := ‘PHP’;  
core_tdl(‘s200’).core := 80;  
WHEN OTHERS THEN  
NULL;  
END;  
END;

– 3. 编写一个存储过程，没有任何参数。在程序中进行数据分析

GRANT CREATE ANY DIRECTORY TO hand_student; 
 
CREATE OR REPLACE DIRECTORY FILENAME AS ‘D:/EXAM’; 
 
CREATE OR REPLACE PROCEDURE process_core_info IS  
CURSOR cur_stu_core IS  
SELECT hs.student_name,  
hs.student_no,  
hsc1.core max_core,  
hc1.course_name max_course_name,  
hsc2.core min_core,  
hc2.course_name min_course_name  
FROM hand_student_core hsc1,  
hand_course hc1,  
hand_student hs,  
hand_student_core hsc2,  
hand_course hc2  
WHERE hsc1.course_no = hc1.course_no  
AND hsc1.student_no = hs.student_no  
AND hsc1.student_no = hsc2.student_no  
AND hsc2.course_no = hc2.course_no  
AND hsc1.core = (SELECT MAX(hsc.core)  
FROM hand_student_core hsc  
WHERE hsc.student_no = hsc1.student_no)  
AND hsc2.core = (SELECT MIN(hsc.core)  
FROM hand_student_core hsc  
WHERE hsc.student_no = hsc1.student_no);  
CURSOR cur_teh_core IS  
SELECT ht.teacher_name,  
hc1.course_name,  
hsc1.core max_core,  
hs1.student_name max_student_name,  
hsc2.core min_core,  
hs2.student_name min_student_name  
FROM hand_student_core hsc1,  
hand_course hc1,  
hand_teacher ht,  
hand_student hs1,  
hand_student_core hsc2,  
hand_course hc2,  
hand_student hs2  
WHERE hsc1.course_no = hc1.course_no  
AND hc1.teacher_no = ht.teacher_no  
AND hsc1.student_no = hs1.student_no  
AND hsc2.course_no = hc2.course_no  
AND hc1.teacher_no = hc2.teacher_no  
AND hsc2.student_no = hs2.student_no  
AND hsc1.core = (SELECT MAX(hsc.core)  
FROM hand_student_core hsc,  
hand_course hc  
WHERE hsc.course_no = hc.course_no  
AND hc.teacher_no = hc1.teacher_no)  
AND hsc2.core = (SELECT MIN(hsc.core)  
FROM hand_student_core hsc,  
hand_course hc  
WHERE hsc.course_no = hc.course_no  
AND hc.teacher_no = hc2.teacher_no);  
FILEHANDLE UTL_FILE.FILE_TYPE;  
BEGIN  
FILEHANDLE := UTL_FILE.FOPEN(‘FILENAME’,’student.txt’,’W’);  
UTL_FILE.PUT_LINE(‘姓名,学号,最高分,最高分课程名,最低分,最低分课程名’);  
FOR rec_stu_core IN cur_stu_core LOOP  
UTL_FILE.PUT_LINE(FILEHANDLE,rec_stu_core.student_name||’,’||rec_stu_core.student_no||’,’||  
rec_stu_core.max_core||’,’||rec_stu_core.max_course_name||’,’||  
rec_stu_core.min_core||’,’||rec_stu_core.min_course_name);  
END LOOP;  
UTL_FILE.FCLOSE(FILEHANDLE); 
 
FILEHANDLE := UTL_FILE.FOPEN(‘FILENAME’,’teacher.txt’,’W’);  
UTL_FILE.PUT_LINE(‘教师名,课程名,课程最高分,最高分学生姓名,课程最低分,最低分学生姓名’);  
FOR rec_teh_core IN cur_teh_core LOOP  
UTL_FILE.PUT_LINE(FILEHANDLE,rec_teh_core.teacher_name||’,’||rec_teh_core.course_name||’,’||  
rec_teh_core.max_core||’,’||rec_teh_core.max_student_name||’,’||  
rec_teh_core.min_core||’,’||rec_teh_core.min_student_name);  
END LOOP;  
UTL_FILE.FCLOSE(FILEHANDLE); 
 
END process_core_info;