<DataBase_231110금> 다중커서

<선생님이 정리해주신>

 

-- ed8

--컬럼명은 변수로 사용할 수 없다!! => select ~ into ***

-- 입력한 사번의 사원명을 출력
accept bun prompt '사번 입력->'
declare
v_name sawon.saname%type;
begin
select saname into v_name from sawon where sabun=&bun;
dbms_output.put_line('이름 : ' || v_name || ', 급여 : ' || v_pay);
end;
/

--*** PL/SQL에서의 변수 선언 방법
--v_name  sawon.saname%type; // sawon.saname과 같은 타입(길이) 선언 속성
--v_pay  constant   sawon.sapay%type := 2000;    // 변경 불가능한 상수형 변수 선언!
--v_bun   sawon.sabun%type := 15 not null; // 변수에 not null 제약설정!
--v_loc dept.loc%type := '서울';
--a sawon%rowtype; // a.sabun, a.saname,... 변수를 한 번에 선언!!

 

-- ed9

alter table sawon add comm number(10);

--사번을 입력받아 사원의 급여를 기준으로 커미션을 변경(update)하는 프로그램
-- 커미션 기준 
-- 급여가 1000미만   -> 급여의 10%
-- 1000~2000 -> 급여의 15%
--  2000초과    -> 급여의 20%
-- null    -> 0


--1. 사원의 급여 추출->변수에 담기
--2. 비교-> 커미션 계산
--3. 변경

accept bun prompt '사번->'
declare
v_bun sawon.sabun%type := &bun;
v_pay sawon.sapay%type;
v_comm sawon.comm%type;
begin 
select sapay into v_pay from sawon where sabun = v_bun;

if  v_pay<1000 then
v_comm :=  v_pay*0.1;
elsif v_pay<=2000 then
v_comm :=  v_pay*0.15;
elsif v_pay>2000 then 
v_comm := v_pay*0.2;
else v_comm := 0;
    end if;

update sawon set comm = v_comm where sabun=v_bun;
commit;

dbms_output.put_line(v_bun || '번 사원의 커미션은 ' || v_comm || '으로 변경!');
end;
/

 

-- ed10

declare
cursor mycur is select saname, sapay from sawon;
v_name sawon.saname%type;
v_pay sawon.sapay%type;
begin
if mycur%isopen then close mycur;
end if;

open mycur;

loop
fetch mycur into v_name, v_pay;
exit when(mycur%notfound);

dbms_output.put_line('이름: ' || v_name || ', 급여: ' || v_pay);
end loop;
close mycur;
end;
/

 

--ed11

-- 모든 사원을 커미션을 변경하는 커서를 정의
-- 커미션 기준 
-- 급여가 1000미만   -> 급여의 10%
-- 1000~2000 -> 급여의 15%
--  2000초과    -> 급여의 20%
-- null    -> 0
declare
cursor mycur is select sabun, sapay from sawon;
v_bun sawon.sabun%type;
v_pay sawon.sapay%type;
v_comm sawon.comm%type;
cnt number(3);
begin
if mycur%isopen then close mycur;
end if;
open mycur;
loop
fetch mycur into v_bun, v_pay;
exit when(mycur%notfound);

if v_pay<1000 then
v_comm:=v_pay*0.1;
elsif v_pay<=2000 then
v_comm:=v_pay*0.15;
elsif v_pay>2000 then
v_comm:=v_pay*0.2;
else  v_comm:=0;
end if;

update sawon set comm=v_comm where sabun=v_bun;
-- dbms_output.put_line(v_bun||'사원의 커미션은 '||v_comm||'으로 변경되었다');
end loop;
cnt := mycur%rowcount;
commit;
dbms_output.put_line('처리한 사원 수는 '|| cnt || '명이다.');
close mycur;
end;
/

--* 커서에 사용하는 속성 종류
--%isopen : 커서가 열려있다
--%notfound : 가져올 행이 없다
--%found : 존재하는 행이 있다
--%rowcount : 처리된 행 수

 

--ed11_1

declare
cursor mycur is select sabun, sapay from sawon;
v_comm sawon.comm%type;
cnt number(3);
begin
for i in mycur loop
if i.sapay<1000 then
v_comm:=i.sapay*0.1;
elsif i.sapay<=2000 then
v_comm:=i.sapay*0.15;
elsif i.sapay>2000 then
v_comm:=i.sapay*0.2;
else  v_comm:=0;
end if;

update sawon set comm=v_comm where sabun=i.sabun;
-- dbms_output.put_line(v_bun||'사원의 커미션은 '||v_comm||'으로 변경되었다');
cnt := mycur%rowcount;
end loop;
dbms_output.put_line('처리한 사원 수는 '|| cnt || '명이다.');
end;
/

 

--ed12

-- 사번, 사원명, 급여, 입사일을 읽어들여서 근무기간, 급여순위를 구한 후, 
-- 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;
/

 

----------------------------------------------

 

<내가 풀이한>

// 숙제

 

-- 학생 성적 테이블
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점 이상이면 [합격] 미만, [불합격]

 

 

<숙제1>

-- 1) student 테이블의 학생 정보를 record 테이블에 삽입. 
커서를 사용하여 student 테이블의 학생 정보를 한 행씩 처리하고, 
각 학생의 총점, 평균, 학점, 평가, 석차를 계산하여 record 테이블에 삽입.

-- 2) record 테이블의 학생 석차를 계산. 
다중커서를 사용하여 record 테이블의 학생 정보를 한 행씩 처리하고, 
각 학생의 총점보다 높은 총점을 가진 학생의 수를 계산하여 석차를 계산.

desc student;

desc student2;

select * from student2;

desc record;

select * from record;

set linesize 120/
select * from student2;

-- 학생 성적 테이블
create table student2(hakbun number(3), name varchar2(10), kor number(3), mat number(3), eng number(3));
insert into student2 values(101,'홍동우',90,80,60);
insert into student2 values(102,'차승원',80,75,60); 
insert into student2 values(103,'현빈',90,60,60); 
insert into student2 values(104,'강동원',55,40,60); 
insert into student2 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));

-- 학점 : 평균이 수~가 (90-80-70-60-50)
-- 평가 : 평균이 70점 이상이면 [합격] 미만이면 [불합격]

declare
   cursor mycur is select hakbun, name, kor, mat, eng from student2;
   v_t record.total%type;
   v_a record.ave%type;
   v_h hakjum%type;
   v_g grade%type;

begin
   for i in mycur loop
      v_t:=i.kor+i.mat+i.eng;
      v_a:=v_t/3;

      if v_a>=90 then v_h:='수';
      elsif v_a>=80 then v_h:='우';
      elsif v_a>=70 then v_h:='미';
      elsif v_a>=60 then v_h:='양';
      else v_h := '가';
      end if;

      if v_a>=70 then v_g:='합격';
      else v_g:='불합격';
      end if;

      insert into record(hakbun, name, kor, mat, eng, total, ave, hakjum, grade) values(i.hakbun, i.name, i.kor, i.mat, i.eng, v_t, v_a, v_h, v_g);
  end loop;

declare  
     cursor mycur is select name, total, s_rank from record;

begin
     for i in mycur loop
        i.s_rank:=0;
        select count(*) into i.s_rank from record where total>i.total;
        i.s_rank:=i.s_rank+1;
        update record set s_rank=i.s_rank
        where hakbun=i.hakbun;
     end loop;
  end;
end;
/

--------------------------------------------------------------------

--<숙제2> --쌤이 준걸로 고칠것
--근무지, 사원정보 테이블 생성 (사번, 사원명, 업무, 부서명, 급여등급)
(seoul_emp : eno, ename, job, dname, salgrade)

create table seoul_emp(eno number(3), ename varchar2(10), job varchar2(10), dname varchar2(10), salgrade varchar(10));

--<숙제3>
-- emp 테이블에서 사번, 이름, 업무를 읽어들여 부서명과 급여등급과 함께 seoul_emp table에 결과를 입력하는 커서를 정의하시오.

declare
cursor mycur is select empno, ename, job, sal, deptno from emp;
v_dn seoul_emp.dname%type;
v_gr seoul_emp.salgrade%type;

begin
for i in mycur loop

select dname into v_dn from dept
where deptno=i.deptno;

select grade into v_gr from salgrade
where i.sal>=losal and i.sal<=hisal;

insert into seoul_emp(eno, ename, job, dname, salgrade) values(i.empno, i.ename, i.job, v_dn, v_gr);

end loop;

end;
/

 

<쌤이 작성한거>