1. PL/SQL 코드
DECLARE
x_id VARCHAR(5);
x_name VARCHAR(20);
x_dept_name VARCHAR(20);
x_course_id VARCHAR(8);
x_semester VARCHAR(8);
x_year NUMERIC(4,0);
x_grade VARCHAR(2);
x_credits NUMERIC(2,0);
x_total_credits NUMERIC(3,0) :=0;
x_total_points NUMERIC(4,1) :=0;
x_gpa NUMERIC(3,1) :=0;
x_prev_deptname VARCHAR(20) :=NULL;
CURSOR c1 IS
SELECT ID, name, dept_name
FROM student
ORDER BY dept_name ASC, name ASC;
CURSOR c2 IS
SELECT course_id, semester, year, grade
FROM takes
WHERE ID = x_id
ORDER BY year DESC,
CASE
WHEN semester ='Spring' THEN 1
WHEN semester ='Summer' THEN 2
WHEN semester ='Fall' THEN 3
ELSE 4
END ASC;
CURSOR c3 IS
SELECT credits
FROM course
WHERE course_id = x_course_id;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO x_id, x_name, x_dept_name;
EXIT WHEN c1%NOTFOUND;
IF x_prev_deptname IS NULL OR x_dept_name != x_prev_deptname THEN
x_prev_deptname := x_dept_name;
dbms_output.put_line(x_dept_name);
END IF;
dbms_output.put_line (' '|| x_name ||':');
x_total_credits :=0;
x_total_points :=0;
OPEN c2;
LOOP
FETCH c2 INTO x_course_id, x_semester, x_year, x_grade;
EXIT WHEN c2%NOTFOUND;
OPEN c3;
FETCH c3 INTO x_credits;
CLOSE c3;
dbms_output.put_line (
lpad(NVL(x_course_id, ''), 15) ||' '||
lpad(NVL(x_credits, 0), 8) ||' '||
lpad(NVL(x_semester, ''), 10) ||' '||
lpad(NVL(x_year, 0), 6) ||' '||
lpad(NVL(x_grade, ''), 6)
);
IF x_grade IS NOT NULL THEN
CASE x_grade
WHEN 'A+' THEN x_total_points := x_total_points + (4.3 * x_credits);
WHEN 'A' THEN x_total_points := x_total_points + (4.0 * x_credits);
WHEN 'A-' THEN x_total_points := x_total_points + (3.7 * x_credits);
WHEN 'B+' THEN x_total_points := x_total_points + (3.3 * x_credits);
WHEN 'B' THEN x_total_points := x_total_points + (3.0 * x_credits);
WHEN 'B-' THEN x_total_points := x_total_points + (2.7 * x_credits);
WHEN 'C+' THEN x_total_points := x_total_points + (2.3 * x_credits);
WHEN 'C' THEN x_total_points := x_total_points + (2.0 * x_credits);
WHEN 'C-' THEN x_total_points := x_total_points + (1.7 * x_credits);
WHEN 'D+' THEN x_total_points := x_total_points + (1.3 * x_credits);
WHEN 'D' THEN x_total_points := x_total_points + (1.0 * x_credits);
WHEN 'D-' THEN x_total_points := x_total_points + (0.7 * x_credits);
ELSE x_total_points := x_total_points +0;
END CASE;
x_total_credits := x_total_credits + x_credits;
END IF;
END LOOP;
close c2;
if x_total_credits >0 then
x_gpa := x_total_points / x_total_credits;
else
x_gpa :=0;
end if;
dbms_output.put_line (' '||'Total Credits: '|| x_total_credits ||
' GPA: '|| x_gpa);
dbms_output.put_line (' '||'--------------------------------------------');
END LOOP;
CLOSE c1;
END;2. 추가설명
-
학생별 성적 정보를 출력하기 위해 총 이수 학점(x_total_credits), 총 평점(x_total_points), 평균 학점(x_gpa) 등을 저장할 변수들을 선언한다.
-
커서(c1)는 student 테이블에서 학생ID, 학생 이름, 학과 이름을 가져온
다. 커서(c2)는 takes 테이블에서 수강과목 ID, 학기, 년도, 성적을 가져온다. 커서(c3)는 course 테이블에서 수강과목 ID에 해당하는 학점을 가져온다. -
첫 번째 커서(c1)를 열어 학과 이름, 학생 이름을 출력하고, 두 번째 커서(c2)를 열어 각 학생이 수강한 과목의 성적 정보를 출력한다. 세 번째 커서(c3)를 열어서 해당 과목의 학점을 가져온다.
-
학생의 성적 정보에 대해서, 해당 학점에 따른 총 이수 학점과 총 평점을 계산한다. 그리고, 총 이수 학점과 총 평점을 사용하여, 해당 학생의 평균 학점을 계산한다. 학점은 만점을 4.3으로 하여 계산하고, 학점이 NULL인 경우 총 이수학점과 평균 학점 계산에 넣지 않도록 한다.
3. 출력내용
Biology
Tanaka:
BIO-301 4 Summer 2018
BIO-101 4 Summer 2017 A
Total Credits: 4 GPA: 4
Comp. Sci.
Brown:
CS-319 3 Spring 2018 A
CS-101 4 Fall 2017 A
Total Credits: 7 GPA: 4
Shankar:
CS-315 3 Spring 2018 A
CS-190 4 Spring 2017 A
CS-101 4 Fall 2017 C
CS-347 3 Fall 2017 A
Total Credits: 14 GPA: 3.4
Williams:
CS-190 4 Spring 2017 B+
CS-101 4 Fall 2017 A-
Total Credits: 8 GPA: 3.5
Zhang:
CS-347 3 Fall 2017 A-
CS-101 4 Fall 2017 A
Total Credits: 7 GPA: 3.9
Elec. Eng.
Aoi:
EE-181 3 Spring 2017 C
Total Credits: 3 GPA: 2
Bourikas:
CS-315 3 Spring 2018 B
CS-101 4 Fall 2017 C-
Total Credits: 7 GPA: 2.3
Finance
Chavez:
FIN-201 3 Spring 2018 C+
Total Credits: 3 GPA: 2.3
History
Brandt:
HIS-351 3 Spring 2018 B
Total Credits: 3 GPA: 3
Music
Sanchez:
MU-199 3 Spring 2018 A-
Total Credits: 3 GPA: 3.7
Physics
Levy:
CS-101 4 Spring 2018 B+
CS-319 3 Spring 2018 B
CS-101 4 Fall 2017 F
Total Credits: 11 GPA: 2
Peltier:
PHY-101 4 Fall 2017 B-
Total Credits: 4 GPA: 2.7
Snow:
Total Credits: 0 GPA: 0
