最常見的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;