--1) 과목번호, 과목이름, 교수번호, 교수이름을 담을 수 있는 변수들을 선언하고
-- 유기화학 과목의 과목번호, 과목이름, 교수번호, 교수이름을 출력하세요.
--2) 위 데이터들을 레코드로 선언하고 출력하세요.
--3) 과목번호, 과목이름, 과목별 평균 기말고사 성적을 갖는 레코드의 배열을 만들고
-- 기본 LOOP문을 이용해서 모든 과목의 과목번호, 과목이름, 과목별 평균 기말고사 성적을 출력하세요.
--4) 학생번호, 학생이름과 학생별 평균 기말고사 성적을 갖는 테이블 T_STAVGSC를 만들고
-- 커서를 이용하여 학생번호, 학생이름, 학생별 평균 기말고사 성적을 조회하고
-- 조회된 데이터를 생성한 테이블인 T_STAVGSC에 저장하세요.
declare type haha is record
(
cno course.cno%type,
cname course.cname%type,
pno professor.pno%type,
PNAME professor.pname%type
);
ha haha;
begin
select cno, cname, PROFESSOR.pno, PNAME
into ha
from PROFESSOR,
course
where PROFESSOR.PNO = COURSE.PNO
and cname = '유기화학';
DBMS_OUTPUT.PUT_LINE(ha.cno);
DBMS_OUTPUT.PUT_LINE(ha.cname);
DBMS_OUTPUT.PUT_LINE(ha.pno);
DBMS_OUTPUT.PUT_LINE(ha.pname);
end;
create table haha as
select course.cno, cname, avg(result) avg
from course,
score
where course.CNO = score.cno
group by course.cno, cname;
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
create table kilkil
(
sno number,
sname varchar2(10),
avg number
);
select *
from haha;
--
declare
cursor curst is select student.sno, sname, avg(result)
from student,
score
where student.SNO = SCORE.SNO
group by student.sno, student.sname;
strow kilkil%rowtype;
begin
open curst;
loop
fetch curst into strow;
insert into kilkil values strow;
DBMS_OUTPUT.PUT_LINE(strow.sno);
DBMS_OUTPUT.PUT_LINE(strow.sname);
DBMS_OUTPUT.PUT_LINE(strow.avg);
exit when curst%notfound;
end loop;
close curst;
end;
select *
from kilkil;
------------------------
declare type hahak is record (cno course.cno%type, cname course.cname%type, avg number);
type hahaarr is table of hahak
index by pls_integer;
arr hahaarr;
idx number := 1;
begin
for strow in ( select course.cno, cname, avg(result) as avg
from course,
score
where SCORE.CNO = COURSE.cno
group by course.cno, cname)
loop
arr(idx) := strow;
DBMS_OUTPUT.PUT_LINE(arr(idx).cno);
DBMS_OUTPUT.PUT_LINE(arr(idx).cname);
DBMS_OUTPUT.PUT_LINE(arr(idx).avg);
end loop;
end ;
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다
---------------------------다시한번 풀어보자
declare type hahak is record (cno course.cno%type, cname course.cname%type, avg number);
type hahaarr is table of hahak
index by pls_integer;
arr hahaarr;
idx number := 1;
begin
loop
arr(idx) =
end loop;
end ;
declare cno number;
cname varchar2(20);
pno number;
pname varchar2(20);
begin
select cno, cname, course.pno, pname into cno, cname, pno, pname from course, PROFESSOR where COURSE.PNO = PROFESSOR.PNO and cname = '유기화학';
DBMS_OUTPUT.PUT_LINE(cno);
DBMS_OUTPUT.PUT_LINE(cname);
DBMS_OUTPUT.PUT_LINE(pno);
DBMS_OUTPUT.PUT_LINE(pname);
end;
declare type haha is record
(
cno course.cno%type,
cname course.cname%type,
pno professor.pno%type,
pname professor.pname%type
);
h haha;
begin
select cno, cname, PROFESSOR.pno, pname
into h
from PROFESSOR,
COURSE
where PROFESSOR.PNO = COURSE.PNO
and CNAME = '유기화학';
DBMS_OUTPUT.PUT_LINE(h.cno);
DBMS_OUTPUT.PUT_LINE(h.cname);
end;
select *
from student;
create table t_stavgsc as
select student.sno, sname, avg(result) a
from student,
score
where student.SNO = score.SNO
group by student.sno, sname;
select *
from t_stavgsc;
delete
from t_stavgsc;
declare
cursor curst is select student.sno, sname, avg(result)
from student,
score
where student.SNO = SCORE.SNO
group by student.sno, sname;
begin
for strow in curst
loop
DBMS_OUTPUT.PUT_LINE(strow.SNO);
DBMS_OUTPUT.PUT_LINE(strow.sname);
insert into t_stavgsc values strow;
end loop;
end;
declare
cursor curst is select student.sno, sname, avg(result)
from student,
score
where student.SNO = SCORE.SNO
group by student.sno, sname;
type haha is record (sno student.sno%type, sname student.sname%type, avg number);
h haha;
begin
open curst;
loop
fetch curst into h;
insert into t_stavgsc values h;
exit when curst%notfound;
end loop;
close curst;
end;
declare cursor curst is select student.sno, sname, avg(result)
from student,
score
where student.SNO = SCORE.SNO
group by student.sno, sname;
strow t_stavgsc%rowtype;
begin
open curst;
loop
fetch curst into strow;
exit when curst%notfound;
insert into t_stavgsc values strow;
end loop;
end;
select * from t_stavgsc;
댓글 영역