The most common Oracle database interview questions, Oracle database development face Questions

Multiple-choice questions (20 points, 4 points per question)
1. When using cursors to modify data, use the. For update leverages which features of the transaction. （）
A, Atomic
B, consistency
C, Permanent
D, Isolation

The following statement, the correct statement is ()
A, as long as in the stored procedures have to add a deletion of statements, a certain increase in autonomy services
B. You can modify table data within a function
C, function cannot be called recursively
D, the above is wrong

The following statement is correct, multiple selection ()
A, if you include update, DELETE, INSERT statements in a Plsql custom function, you do not have to give a commit in the function body
B, the custom function can be called in the SQL statement, or it can be called in the Plsql block.
C, custom functions can return a table type
D, the parameters in the custom function can be out types

With regard to triggers, the following statements are correct ()
A, you can create instead OF triggers on a table
B, statement-level triggers cannot use ": old" and ": NEW"
C, row-level triggers cannot be used for audit functions
D, triggers can explicitly call

Pl/sql Define Package Description correct is ()
A. Package declarations must declare content in the order of type, variable, exception, subroutine, cursor definition, and do not allow cursors to be defined in front of type
B. The subroutine defined in the package's declaration must have an implementation in the package body, but the cursor can have no
C. All child program definitions in the package body must not necessarily be defined in the package declaration
D. All content in the package must be defined in the package declaration, otherwise it will be considered invalid content
Basic questions (50 points)

Create package, Package name: Cux_plsql_exam work number _pkg, contains the following functions and procedures:
1. Write a function to return the student's academic achievement according to the school number and the course number.
If not, the return value-1;
If multiple rows of data can be found, return-2;
Returns-3 If there are other exceptions;
(5 points)

Attention:
All of the following methods and procedures are written in the package above. Finally, save the package as a. pck file.
Anonymous blocks are written separately.

Write a stored procedure (autonomous transaction), log information to the table Hand_log, where the parameter code (error code), MSG (Error message) is a required field, KEY1 to KEY5 is a required field, and the default is null. (5 points)

Write a stored procedure, add 10 Student information to table hand_student, data format and logic, learn number: S100 ... 109, Name: Wang 001 ... Wang 010, age: 22, Sex: School Number The last one is odd for "male", even for "female". (5 points)

Write a stored procedure, according to the school number, the course number according to the score of 20% bonus points, if the increase after the score is greater than 100, then cancel the bonus points. Returns the result of an increase in the stored procedure at the same time. (5 points)

Write the scripting code according to the following questions:

Write the anonymous block, call the function in the above package to obtain the students who have selected the "Humingshing" teacher's scores, directly using Dbms_output output "name, study number, curriculum, achievement". If an exception is returned, the above autonomous transaction stored procedure record information is invoked (the Code= function returns the value, msg= the student name). (10 points)

Write an anonymous block that first invokes the 3rd stored procedure above to add student data. Then create a table in the anonymous block that is the same as the Hand_student table structure, named Hand__student_temp. The data in the Hand_student table is then inserted into the Hand__student_temp table. (10 points)

Write an anonymous block that invokes the 4th stored procedure above to add points to the scores of students with an average score below 70. Then directly with dbms_output output "name, study number, curriculum, plus points before the score, plus score." (10 points)

 Advanced questions (30 points)

Create a trigger on the table hand_student, when the table data is new, updated, or deleted, add a record to the table hand_student_his, record last_update_date and status. (10 points)
Description
1 The status value "N" indicates the new, "U" indicates the modification, "D" indicates the deletion.
2 last_update_date time to record the time of data operation.

Write an anonymous block that saves all students ' name, student number, course name, grade, and result information in the collection and uses the number as an index. Then in the program to determine whether the number "s200" exists in the collection, if it does not exist, add a new data in the collection (name = Zhang, school number =s200, course =php, Grade =80).
(10 points)

Write a stored procedure with no parameters. Conduct data analysis in the program.
1 to analyze the highest and lowest points of each student's course in the dimension of students. The result of the need is (name, school number, highest score, highest division course name, lowest score, lowest division course name);
2 The teacher as the maintenance, analysis of each teacher taught the highest and lowest points in the curriculum. The desired result is (teacher name, course name, highest score, highest student name, course minimum, lowest student name);
The result data of the above analysis were written to two files, respectively, Student.txt and Teacher.txt. Note that the data is written to the file and needs to be wrapped. (10 points)

Answer:
One, the choice question
1, D
2, B
3, ABCD
4, B
5, BC

Second, the basic question
– Package
CREATE OR REPLACE PACKAGE cux_plsql_exam3740_pkg is

-Basic Questions
–1. Write a function to return the student's academic achievement according to the school number and the course number
FUNCTION Get_course_core (p_student_no in VARCHAR2,
P_course_no in VARCHAR2) return number;

–2. Write a stored procedure (autonomous transaction), log information to the table 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. Write a stored procedure that adds 10 student information to the table hand_student
PROCEDURE insert_student;

–4. Write a stored procedure, according to the school number, course number according to the score of 20% bonus points ...
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. Write a function to return the student's academic achievement according to the school number and the course number

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. Write a stored procedure (autonomous transaction), log information to the table Hand_log

PROCEDURE log_msg (P_code in VARCHAR2, 
p_msg in VARCHAR2, p_key1 in VARCHAR2 
DEFAULT NULL, 
P_key2 in Varcha R2 default NULL, 
P_key3 in VARCHAR2 default NULL, 
P_key4 in VARCHAR2 default NULL, 
P_key5 in VARCHAR2 Defau LT 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. Write a stored procedure that adds 10 student information to the table hand_student

PROCEDURE insert_student is 
l_rec hand_student%rowtype; 
BEGIN for 
i in 1 loop 
l_rec.student_no: = ' S10 ' | | (i-1); 
IF i < THEN 
l_rec.student_name: = ' King ' | | i; 
ELSE 
l_rec.student_name: = ' King 0 ' | | 
End IF; 
L_rec.student_age: =; 
IF MoD (i, 2) = 0 THEN 
l_rec.student_gender: = ' male '; 
ELSE 
L_rec.student_gender: = ' female '; 
End IF; 
INSERT into Hand_student VALUES L_rec; 
End LOOP; 
EXCEPTION when 
others THEN 
ROLLBACK; 
End Insert_student;

–4. Write a stored procedure, according to the school number, course number according to the score of 20% bonus points ...

PROCEDURE Process_add_core (p_student_no in VARCHAR2, 
p_course_no-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) <= THEN 
UPDATE Hand_stud Ent_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;

– Anonymous block
– Write scripting code based on the following topic requirements
–1. Write an anonymous block that calls the function in the above package to get the grades of the students who took the "Humingshing" teacher

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 = ' Humingshing '); C18/>begin 
dbms_output.put_line (' name, study number, course, Grade '); 
For Rec_core in Cur_core LOOP 
IF rec_core.core ( -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. To write an anonymous block, first call the 3rd stored procedure above to add student data ...

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. Write an anonymous block that invokes the 4th stored procedure above to add points to the scores of students with an average score below 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 have 
AVG (hs.core) <); 
L_after_core number; 
BEGIN 
dbms_output.put_line (' name, number, course, score before, plus score '); 
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;

Third, the advanced question
–1. Create a trigger on the table hand_student, when the table data is new, updated, or deleted, in the table

hand_student_his add a record for CREATE or REPLACE TRIGGER after INSERT or UPDATE or DEL Ete on hand_student to each ROW DECLARE the 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. Write an anonymous block that saves all students ' name, student number, course name, and result information to the collection

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: = ' Zhang San Feng '; 
Core_tdl (' s200 '). Student_no: = ' s200 '; 
Core_tdl (' s200 '). Course_name: = ' PHP '; 
Core_tdl (' s200 '). Core: = 80; 
When others THEN NULL; 
End; End;

–3. Write a stored procedure with no parameters. Data analysis in a program

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.co Re Max_core, Hc1.course_name max_course_name, Hsc2.core Min_core, Hc2.course_name min_course_name from Hand_student_co Re 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 HS C1.core = (SELECT MAX (Hsc.core) from Hand_student_core hsc WHERE hsc.student_no = hsc1.student_no) and Hsc2.core = (SEL 
ECT 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.c Ourse_no = hc2.course_no and hc1.teacher_no = hc2.teacher_no and hsc2.student_no = hs2.student_no and Hsc1.core = (SELE CT 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 (' name, school number, highest score, highest class name, lowest score, lowest division course name '); 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 (' Teacher name, course name, highest score, highest student name, minimum course score, lowest student name '); 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;