[Oracle] PL/SQL Class 3 - 커서 2, 다중커서 과제,

2023.11.10

 

##커서(cursor)

SQL문을 처리결과 집합을 가리키는 포인터.
SQL 처리결과를 오라클 서버 프로세스 내부의 Private SQL Area(메모리)에 저장하는 프로세스, 포인터(커서).
질의의 결과로 얻어진 여러 행이 저장된 메모리상의 위치.
커서는 SELECT 문의 결과 집합을 처리하는데 사용된다.

• 컬럼명%FOUND: 반환된 총행의 갯수가 1개 이상인 경우 True or False (boolean 사용)
• 컬럼명%NOTFOUND : 반환되는 총행의 갯수가 없는 경우 True o False(boolean 사용)
• 컬럼명%ISOPEN  : 항상 FALSE. 암시적 커서가 열려 있는지의 여부검색( PL/SQL은 실행 후 바로 묵시적 커서를 닫기 때문에 항상 상 false)
• 컬럼명%ROWCOUNT : 반환된 총 행수를 의미. 가장 최근 수행된 SQL문에 의해 영향을 받은 행의 갯수(정수)

 

(연습1)

-- 사번, 사원명, 급여, 입사일을 읽어들여서 근무기간, 급여순위를 구한 후, 
-- sawon_result 테이블(결과) 에 사원정보와 처리된 결과를 저장하는 커서
create table sawon_result(
                                     bun number(10) primary key,
                                      name varchar2(10), 
                                      pay number(10), 
                                      hiredate date, 
                                      hire_bet varchar2(20), 
                                      pay_rank number(3)
);

--1. 근무기간 구하기
--2. 급여석차 구하기
--3. sawon_result 테이블에 입력

declare 
            cursor mycur is select sabun, saname, sapay, sahire from sawon;
            v_rank sawon_result.pay_rank%type;
            v_work sawon_result.hire_bet%type;
            cnt number(3) := 0;
begin 
            for i in mycur loop
            v_work := floor(months_between(sysdate, i.sahire)/12) || '년 ' ||
                              floor(mod(months_between(sysdate, i.sahire), 12)) || '개월';
            v_rank := 0;
                         select count(*)+1 into v_rank from sawon 
            where sapay>i.sapay;
                        insert into sawon_result(bun, name, pay, hiredate, pay_rank, hire_bet)
                                  values (i.sabun, i.saname, i.sapay, i.sahire, v_rank, v_work);
-- 반복문 내에 사용(%rowcount; 속성)
            cnt := mycur%rowcount;
            end loop;  
                         dbms_output.put_line('처리된 사원의 수는 ' || cnt || '명 입니다');
end;
/

 

(과제1)
-- 학생 성적 테이블
create table student( hakbun number(3), name varchar2(10), kor number(3), mat number(3), eng number(3) );
                                 insert into student values(101,'홍동우',90,80,60);
                                 insert into student values(102,'차승원',80,75,60);
                                 insert into student values(103,'현빈',90,60,60);
                                 insert into student values(104,'강동원',55,40,60);
                                 insert into student values(105,'주원',70,65,100);

-- 성적 결과 테이블
create table record( hakbun number(3), name varchar2(10), kor number(3), mat number(3), eng number(3),
                                  total number(3), ave number(5,2), hakjum char(2), grade char(10), s_rank number(3) );

--학점 : 평균이 수~가
--평가 : 평균이 70점 이상이면 [합격] 미만, [불합격]

declare
              cursor sungjeok is select name, kor, mat, eng from student; 
              chong number(5);
              pguen number(10,2);
              hakjeom varchar2(10);
              pga varchar2(10);
begin
              for q in sungjeok loop
              chong := q.kor + q.mat + q. eng;
              pguen := chong/3;
 
             if pguen >= 90 then hakjeom := '수';
              elsif pguen >= 80 then hakjeom := '우';
              elsif pguen >= 70 then hakjeom := '미';
              elsif pguen >= 60 then hakjeom := '양';
              else hakjeom := '가';

                        end if
              if pguen >= 70 then pga := '합격';
              else pga := '불합격';
                         end if
                         insert into record values(q.hakbun, q.name, q.kor, q.mat, q.eng, chong, pguen, hakjeom, pga, null);
               end loop;
             

             declare                                                                                    // 석차계산
                            cursor sungjeokss is select hakbun, total from record;
                            tt number(5);
               begin
                           for aaa in select sungjeokss loop
                            tt  := 0;
                           select count(*) into tt  from record where total >  aaa.total;    // 석차계산 count(*) 필요 *는 전체 의미.
                            tt := tt +1;
                           update record set sukcha = tt  where name = tt .name;
               end loop;
        end;
end;
/