[오라클] 합격자에게 학번 부여하기

입시를 치르고 학번을 부여하기 위해 다음과 같은 테이블을 살펴본다.

<<접수테이블>>
수험번호, 이름, 지망학과(3자리, FK), 합격자(합격자 1, 예비 2, 불합격 3)

<<학과코드>>
학과코드(4자리, PK), 학과이름, SORTKEY

<<입시용코드>>
학과코드_입시용(3자리), 학과코드(4자리, FK)

접수테이블에는 CHAR(7)의 학번이라는 필드가 있다.
접수자 중 합격자만 골라, 학과순으로 먼저 소트하고, 다음으로 이름순으로 소트하여 학번을 부여한다. 학번은 앞에 2자리는 06과 같이 년도를 나타내고, 뒤의 5자리는 소트한 순서대로 00001부터 입력한다.

해명은 아직 훈련중….. 이 문제를 해결하라고 하시는데, 사실 까짓거 변수 받아서 돌리면 간단하겠지만. PHP나 뭐나 쓰지 말고 그냥 SQL로만 하자는 것이 오늘의 관건. 대충은 알지만 sql에 아직 익숙하지 못하니 땅 파는 수 밖에;;

일단은 다음과 같이 쿼리하여 임시로 학번을 매겨보았다.

SELECT 접수테이블.수험번호, 접수테이블.이름, 학과코드.학과이름, 학과코드.SORTKEY,
(’06’|| lpad(rank() over (ORDER BY 학과코드.SORTKEY, 접수테이블.이름),5,’0′)) as rank, 접수테이블.학번
FROM 학과코드, 입시용코드, 접수테이블
WHERE (접수테이블.지망학과=입시용코드.학과코드_입시용)
AND (입시용코드.학과코드=학과코드.학과코드)
and (접수테이블.합격자=’1′)
ORDER BY 학과코드.SORTKEY, 접수테이블.이름;

여기서 rank에 들어간 가학번을 실제로 접수테이블에 입력하기 위해, 일단 위의 내용을 뷰로 만들고 다음과 같이 돌려보았다.

create view view_test as
SELECT 접수테이블.수험번호, 접수테이블.이름, 학과코드.학과이름, 학과코드.SORTKEY,
(’06’|| lpad(rank() over (ORDER BY 학과코드.SORTKEY, 접수테이블.이름),5,’0′)) as rank, 접수테이블.학번
FROM 학과코드, 입시용코드, 접수테이블
WHERE (접수테이블.지망학과=입시용코드.학과코드_입시용)
AND (입시용코드.학과코드=학과코드.학과코드)
and (접수테이블.합격자=’1′)
ORDER BY 학과코드.SORTKEY, 접수테이블.이름;

declare
cursor c is select * from view_test;
begin
for rec in c loop
dbms_output.put_line(rec.rank || ‘ ‘ || rec.수험번호);
update 접수테이블 set 학번=rec.rank where 수험번호=rec.수험번호;
end loop;
commit;
end;
/

제대로 입력되었다. 아예 뷰 대신 select문을 그 자리에 집어넣어 한번에 처리할 수도 있지만, 아직 정신없어서 두번에 나누었다.

또다른 방법으로 아예 다음과 같이 할 수 있다고도 한다.

SELECT ‘update sisjups set 학번=”’||
(’06’|| lpad(rank() over (ORDER BY 학과코드.SORTKEY, 접수테이블.이름),5,’0′))||
”’ where 수험번호=”’||접수테이블.수험번호||”’;’
FROM 학과코드, 입시용코드, 접수테이블
WHERE (접수테이블.지망학과=입시용코드.학과코드_입시용)
AND (입시용코드.학과코드=학과코드.학과코드)
and (접수테이블.합격자=’1′)
ORDER BY 학과코드.SORTKEY, 접수테이블.이름
/

이렇게 실행하면 각 수험번호에 대해 학번을 부여하는 update문이 펼쳐진다. 이 내용을 덤프하여 실행해도 같은 결과를 얻을 수 있다.

답글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다.