※ loop문의 종류 3가지
1. basic loop
2. while loop
3. for loop

 



- 문제 039
숫자 1~1000까지 출력하는데 for loop 문으로 구현해서 출력하시오.

 

- 답
begin
  for i in 1 .. 1000 loop
    dbms_output.put_line ( ' 출력숫자 ' || i );
  end loop;

end;
/

 



- 문제 040
위의 스크립트를 수정해서 구구단 5단을 출력하시오.

 

- 답
declare
  v_count number(10) := 0;
   
begin
  for i in 1 .. 9 loop
    v_count := 5 * i;
    dbms_output.put_line ( i || ' * ' || '5' || ' = ' || v_count );
  end loop;

end;
/

 



- 문제 042
아래의 주어진 테이블에 for loop 문을 이용해서 data를 입력하시오.

create table emp867
(
  empno number(10),
  ename varchar2(10)
);

 

- 답
declare
  v_empno number(10) := 0;
  v_ename varchar2(40);
   
begin
  for i in 1 .. 1000 loop
    insert into emp867
      values( i , 'scott' || i);
  end loop;

end;
/

 



- 문제 043
for loop문을 중첩해서 구구단 2단 ~ 4단까지 출력하시오.

 

- 답
begin
  for i in 2 .. 4 loop
    for j in 1 .. 9 loop
      dbms_output.put_line( i || '*' || j || '=' || i*j );
    end loop;
  end loop;

end;
/

 


 

※ 프로시저 만드는 방법

create or replace procedure pro1
is

begin
  for i in 2 .. 4 loop
    for j in 1 .. 9 loop
      dbms_output.put_line( i || '*' || j || '=' || i*j );
    end loop;
  end loop;

end;
/

실행하는방법 : exec pro1;

 



- 문제 044
위의 문제 043을 while loop문으로 변경하시오.

 

- 답
declare
  i   number(10) := 1;
  j   number(10) := 0;

begin
  while i < 4 loop
    i := i + 1 ;
    j := 0;
   
    while j < 9 loop
      j := j + 1 ;
      dbms_output.put_line ( i || '*' || j || '=' || i*j);
    end loop;
  end loop;

end;
/

 



- 문제 045
위의소스를 프로시저(pro2) 로 만들어서 디버깅을 하시오.

 

- 답
create or replace procedure pro2
is
       i   number(10) := 1;
       j   number(10) := 0;
 begin
       while   i  <  4   loop
 
           i := i + 1 ;
           j := 0;
 
          while  j  < 9  loop
                
             j := j + 1 ;
      dbms_output.put_line ( i || '*' || j || '=' || i*j);
          end loop;
      
       end loop;
end;
/

 


 

 


- 문제 046
문제45번에 레이블을 붙이시오.
 
- 답
create or replace procedure pro3
is
       i   number(10) := 1;
       j   number(10) := 0;
 begin
 <<Outer_loop>>
       while   i  <  4   loop
 
           i := i + 1 ;
           j := 0;
  <<Inner_loop>>
          while  j  < 9  loop
                
             j := j + 1 ;
      dbms_output.put_line ( i || '*' || j || '=' || i*j);
          end loop Inner_loop;
      
       end loop Outer_loop;
end;
/

 



- 문제 047
부서번호를 물어보게 하고 부서번호 입력하면 해당 부서번호의 토탈월급을 출력되게 하시오.
 
- 답
set serveroutput on
set verify off
accept p_deptno prompt '부서번호를 입력하시오!'
declare
    v_deptno emp.deptno%type := &p_deptno;
    v_sumsal emp.sal%type;
begin
    select sum(sal) into v_sumsal
      from emp
      where deptno = v_deptno;
  dbms_output.put_line ( '토탈월급 : ' || v_sumsal );
end;
/

 



- 문제 048
사원번호를 물어보게 하고 부서번호에 속한 사원들의 사원번호, 이름, 월급을 출력하게 하시오.
 
- 답
set serveroutput on
accept p_empno prompt '사원번호를 입력하시오 '
  declare
    v_empno emp.empno%type := &p_empno;
    v_ename emp.ename%type;
    v_sal emp.sal%type;
  begin
    select ename, sal into v_ename, v_sal
      from emp
      where empno = v_empno;
    dbms_output.put_line(' 사원번호 : '|| v_empno);
    dbms_output.put_line(' 사원이름 : '|| v_ename);
    dbms_output.put_line(' 월급 : '|| v_sal);
end;
/

 


 

※ 조합 데이터 타입의 2가지 종류
1. 레코드(record)
2. 컬렉션(collection)

 

 



- 문제 049
문제48번을 레코드 변수를 이용해서 코드를 간단하게 작성하시오.
 
- 답
declare
  v_empno emp.empno%type := &p_empno;
  v_emp emp%rowtype;


  begin
    select ename, sal into v_emp.ename, v_emp.sal
      from emp
      where empno = v_empno;
    bms_output.put_line(' 사원번호 : '|| v_empno);
   bms_output.put_line(' 사원이름 : '|| v_emp.ename);
    bms_output.put_line(' 월급 : '|| v_emp.sal);
end;
/

 


 

 


- 문제 050
emp 테이블과 똑같은 구조를 갖는 emp745 테이블을 생성하시오.
 
- 답1
create table emp745
  as
 select *
   from emp;
truncate table emp745; 

- 답2
create table emp745
  as
 select *
   from emp
   where 1 = 2;
select * from emp745;

  



- 문제 051
사원번호를 물어보게 하고 사원번호를 입력하면 해당 사원의 모든 컬럼의 정보가 emp745 테이블에 입력되게 하는 PLSQL을 작성하시오.

- 답
set serveroutput on
accept p_empno prompt '사원번호를 입력하시오 '
declare
  v_empno emp.empno%type := &p_empno;
  v_emp emp%rowtype;
begin
  select * into v_emp
  from emp
  where empno=v_empno;
  v_emp.sal :=0; -- sal만 0으로 바꿔치기
  insert into emp745
 values v_emp ;
dbms_output.put_line (SQL%rowcount || '건이 입력되었습니다.' );
end;
/

 



- 문제 052
위의 소스를 다시 수정하는데 사원번호를 물어보게 하고 해당 사원의 정보가 emp745 테이블에 입력될 때,
월급은 0으로 입사일은 오늘날짜로 부서번호는 70번으로 직업은 SALESMAN으로 입력되게 하시오.
 
- 답
set serveroutput on
accept p_empno prompt '사원번호를 입력하시오 '
declare
  v_empno emp.empno%type := &p_empno;
  v_emp emp%rowtype;
begin
  select * into v_emp
  from emp
  where empno=v_empno;
  v_emp.sal :=0; -- sal만 0으로 바꿔치기
  v_emp.hiredate := sysdate;
  v_emp.deptno := 70;
  v_emp.job := 'SALESMAN';
  insert into emp745
 values v_emp ;
dbms_output.put_line (SQL%rowcount || '건이 입력되었습니다.' );
end;
/

 



- 문제 053
emp745 테이블의 데이터를 삭제하고 emp테이블의 모든 데이터를 다시 emp745테이블에 입력하시오.
 
- 답
(( 데이터 삭제 ))
truncate table emp745;

(( 데이터 입력 ))
insert into emp745
  select * from emp;

 



- 문제 054
emp745 테이블의 월급을 모두 0으로 변경하고 입사일을 모두 오늘날짜로 변경하고 부서번호를 모두 70번으로 변경하시오.
 
- 답
update emp745
  set sal = 0;
 
update emp745
  set hiredate = sysdate;
 
update emp745
  set deptno = 70;

 



- 문제 055
사원번호를 물어보게 하고 사원번호를 입력하면 해당 사원에 대한 정보를 emp테이블에서 읽어서 emp745테이블에 갱신되게 하시오.
 
- 답
set serveroutput on
set verify off
accept p_empno prompt '사원번호를 입력하시오 '
declare
  v_empno emp.empno%type := &p_empno;
  v_emp emp%rowtype;
begin
  select * into v_emp
  from emp
  where empno=v_empno;
  update emp745 set row = v_emp
 where empno = v_empno ;
dbms_output.put_line (SQL%rowcount || '건이 입력되었습니다.' );
end;
/

 



- 중간점검 문제
dept 테이블과 똑같은 dept907 테이블을 생성하고 부서번호를 물어보게 하고 부서번호를 입력하면,
 해당 부서번호에 속하는 부서번호, 부서위치, 부서명이 deptno907 테이블에 갱신되게 하는데,
부서위치를 무조건 WASHINGTON으로 갱신되게 하시오.
 
- 답
set serveroutput on
set verify off
accept p_deptno prompt '부서번호를 입력하시오 '
declare
  v_deptno dept.deptno%type := &p_deptno;
  v_dept dept%rowtype;
begin
  select * into v_dept
  from dept
  where deptno=v_deptno;
  v_dept.loc := 'WASHINGTON';
  update dept907 set row = v_dept
 where deptno = v_deptno ;
dbms_output.put_line (SQL%rowcount || '건이 입력되었습니다.' );
end;
/

 



- 문제 057
중첩 테이블을 사용해서 dept 테이블에서 모든 부서번호와 부서위치를 출력될 수 있도록 하시오.
 
- 답
set serveroutput on
  declare
 type dept_table_type is table of
   dept%rowtype index by pls_integer;
 dept_table dept_table_type;
 v_count number(10) := 10;
  begin
 for i in 1 .. 4 loop
   select * into dept_table(i)
     from dept
     where deptno = v_count ;
   v_count := v_count + 10 ;
 end loop;
 for i in dept_table.first .. dept_table.last loop
  dbms_output.put_line( dept_table(i).deptno || ' 의 부서는 ' ||
   dept_table(i).loc );
 end loop;
end;
/

 


 

 


- 문제 058
아래의 작업을 한 후에 사원테이블에서 사원번호, 이름과 월급을 모두 출력하는 PL/SQL을 작성하시오.
 
(( 수행 작업 ))
create table emp12
as
  select rownum rnk, emp.*
 from emp;
select * from emp12;
drop table emp;
rename emp12 to emp;
select * from emp;
 
 
- 답1
set serveroutput on
  declare
 type emp_table_type is table of
   emp%rowtype index by pls_integer;
 emp_table emp_table_type;
  begin
 for i in 1 .. 14 loop
   select * into emp_table(i)
     from emp
     where rnk = i ;
 end loop;
 for i in emp_table.first .. emp_table.last loop
  dbms_output.put_line( emp_table(i).empno || ' ' || emp_table(i).ename || ' ' ||
   emp_table(i).sal );
 end loop;
end;
/
 

- 답2
SET SERVEROUTPUT ON
DECLARE
    TYPEtbl_emp IS TABLE OF
        emp%ROWTYPE
   INDEX BY PLS_INTEGER;
   v_emp tbl_emp;
BEGIN
   FOR i IN 1..14 LOOP
        SELECT * INTO v_emp(i)
       FROM emp
       WHERE rnk = i;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('-----------------------');
   FOR i IN v_emp.FIRST..v_emp.LAST LOOP
       DBMS_OUTPUT.PUT_LINE('사원번호 : ' || v_emp(i).empno);
       DBMS_OUTPUT.PUT_LINE('사원이름 : ' || v_emp(i).ename);
       DBMS_OUTPUT.PUT_LINE('사원월급 : ' || v_emp(i).sal);
       DBMS_OUTPUT.PUT_LINE('-----------------------');
   END LOOP;
END;
/
 

- 답3
declare
    type emp_table_type is table of
 emp%rowtype index by pls_integer;
    emp_table emp_table_type;
    v_count number(10);
begin
    select count(*) into v_count from emp;
    for i in 1 .. v_count loop
 select * into emp_table(i) from emp
 where rnk = i;
    end loop;
   
    dbms_output.put_line('empno | ename | sal ');
    for i in emp_table.first .. emp_table.last loop
 dbms_output.put_line(emp_table(i).empno || '    ' || emp_table(i).ename
         || '    ' || emp_table(i).sal);
    end loop;
end;
/

 



- 문제 059
위의 스크립트를 이용해서 사원번호를 물어보게 하고 사원번호를 입력하면 해당 사원의 직업이 출력되게 하는데,
사원번호를 물어보기 전에 아래의 사원번호가 미리 출력되게하시오.
 
- 답
set serveroutput on
declare
  type emp_table_type is table of
  emp%rowtype index by pls_integer;
  emp_table emp_table_type;

begin
 for i in 1 .. 14 loop
  select * into emp_table(i)
   from emp
   where rnk = i;
 end loop;
 for i in emp_table.first .. emp_table.last loop
  dbms_output.put_line('사원번호 : ' || emp_table(i).empno);
 end loop;
end;
/

 


 

※ CURSOR
수행하는 SQL문의 결과를 처리하기 위한 메모리 영역
  1. 암시적 커서 : SQL%rowcount와 같은 커서를 말한다.
  2. 명시적 커서 : 메모리를 올려놓고 빈곳에다가 올리고 싶은 데이터를 올린다.

 

 



- 문제 060
부서번호를 물어보게 하고 부서번호를 입력하면 해당 부서번호에 속한 사원들의 사원번호, 이름, 월급을 출력하시오.
 
- 답
accept p_deptno prompt '부서번호를 입력하시오'
declare
 cursor emp_cursor is
  select empno, ename, sal
   from emp
   where deptno = &p_deptno;
 v_empno emp.empno%type;
 v_ename emp.ename%type;
 v_sal emp.sal%type;
begin
 open emp_cursor;
 loop
   fetch emp_cursor into v_empno, v_ename, v_sal;  
   exit when emp_cursor%notfound;
   dbms_output.put_line(v_empno || '  ' || v_ename || '  ' || v_sal );
 end loop;
 close emp_cursor;
end;
/

 



- 문제 061
직업을 물어보게하고 직업을 입력하면 해당 사원의 사원 이름, 월급과 부서위치가 출력되게 하시오.
 
- 답
accept p_job prompt '직업을 입력하시오'
declare
 cursor emp_cursor is
  select ename, sal, deptno
   from emp
   where job = upper('&p_job');
 v_ename emp.ename%type;
 v_sal emp.sal%type;
 v_loc dept.loc%type;
begin
 open emp_cursor;
 loop
   fetch emp_cursor into v_ename, v_sal, v_loc; 
   exit when emp_cursor%notfound;
   dbms_output.put_line(v_ename || '  ' || v_sal || '  ' || v_loc );
 end loop;
 close emp_cursor;
end;
/

 


 

 


- 문제 062
위의 예제를 basic loop --> while loop로 수행하시오.
 
- 답
accept p_job prompt '직업:'
   declare
      cursor emp_dept_cursor is
         select e.ename, e.sal, d.loc
         from emp e, dept d
         where e.deptno=d.deptno and e.job=upper('&p_job');
      v_ename emp.ename%type;
      v_sal emp.sal%type;
      v_loc dept.loc%type;
   begin
      open emp_dept_cursor;
         fetch emp_dept_cursor into v_ename, v_sal, v_loc;

      while emp_dept_cursor%found loop
         dbms_output.put_line(v_ename||' '||v_sal||' '||v_loc);
         fetch emp_dept_cursor into v_ename, v_sal, v_loc;
      end loop;
      close emp_dept_cursor;
   end;
/


- 설명
emp_cursor%found : 커서에서 데이터가 발견되면 true
emp_cursor%notfound : 커서에서 데이터가 발견되지 않으면 true

 



- 문제 063
위의 예제를 for loop 문으로 수행하시오
 
- 답
accept p_job prompt '직업:'
 
declare
  cursor emp_dept_cursor is
    select e.ename, e.sal, d.loc
      from emp e, dept d
      where e.deptno=d.deptno and e.job=upper('&p_job');
begin
  for emp_record in emp_dept_cursor loop
    dbms_output.put_line(emp_record.ename ||' '||
    emp_record.sal ||' '||
    emp_record.loc );
  end loop;
end;
/

 



- 문제 064
부서위치를 물어보게 하고 부서위치를 입력하면, 해당 부서위치에 속한 사원들의 이름과 월급과 직업을 출력하는 PL/SQL을 작성하는데 for loop문을 이용한 cursor문으로 생성하시오.
 
- 답
accept p_loc prompt '부서위치를 입력하시오'
declare
  cursor emp_cursor is
    select e.ename, e.sal, e.job
    from emp e, dept d
    where     e.deptno = d.deptno
    and d.loc = upper('&p_loc');
begin
  for emp_record in emp_cursor loop
    dbms_output.put_line( emp_record.ename|| '     ' || emp_record.sal || '     ' || emp_record.job); 
  end loop;

end;
/

 



- 문제 065
위의 예제를 수정해서 아래와 같이 결과가 출력되게 하시오. 만약에 월급이 3000이상이면 고소득자, 월급이 3000보다 작으면 저소득자입니다.
 
SCOTT 은 고소득자입니다.
SMITT 은 저소득자입니다.
ADAMS 은 저소득자입니다.
 
- 답
accept p_loc prompt ' 부서위치를 입력하시오 : '
declare
  v_loc dept.loc%type :=upper('&p_loc');
  cursor emp_dept_cursor is
    select e.ename, e.sal, e.job
      from emp e, dept d
      where d.deptno = e.deptno
      and d.loc = v_loc;
begin
  for emp_record in emp_dept_cursor loop
    if emp_record.sal >=3000 then
      dbms_output.put_line(emp_record.ename || '은  고소득자 입니다.' );
    else
      dbms_output.put_line(emp_record.ename || '은  저소득자 입니다.' );
    end if;
  end loop;
end;
/

 



- 마지막 문제
사원 테이블에 income이란 컬럼을 문자형으로 추가하고 값을 갱신하는 PL/SQL을 작성하는데 자기의 월급이 자기가 속한 부서번호의 평균 월급보다 크면 고소득자로 갱신되게 하고 자기의 월급이 자기가 속한 부서번호의 평균월급보다 작으면 저소득자로 갱신되게 하시오. 커서문을 사용해서 한꺼번에 전부 갱신되게 하시오.
 
- 답
(( income 컬럼 추가 ))
alter table emp
  add income varchar2(30);
 
(( PL/SQL 생성 ))
set verify off
declare
  cursor emp_cursor is
    select ename, sal, deptno
    from emp;
  
  v_avgsal emp.sal%type;
 
begin
  for emp_record in emp_cursor loop
    select avg(sal) into v_avgsal
      from emp
      group by emp_record.deptno;
 
    if emp_record.sal >= v_avgsal then
      update emp set income = '고소득자' where ename = emp_record.ename;
    else
      update emp set income = '저소득자' where ename = emp_record.ename;
    end if;
  end loop;
end;
/

 

 

'Personal > Big Data Course' 카테고리의 다른 글

PL/SQL : FOR문, WHILE문, CURSOR  (0) 2015.04.23
SQL : With절을 사용한 튜닝, 정규식 함수  (0) 2015.04.20
Private comment