180328 반복문 IF WHILE FOR CONTINUES, 5장 연습문제

180328 SPOOL.txt

<나이대에 따라 다른 문구 출력하기>

  1  Declare

  2      v_myage number :=&emp;

  3  Begin

  4      if v_myage < 11 then

  5         dbms_output.put_line ('I am a child');

  6      elsif v_myage < 20 then

  7          dbms_output.put_line ('I am young');

  8      elsif v_myage < 30 then

  9         dbms_output.put_line ('I am in my twenties');

 10      elsif v_myage < 40 then

 11         dbms_output.put_line ('I am in my thirties');

 12      else

 13         dbms_output.put_line ('I am always young');

 14      end if;

 15  End;

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

<사원번호 입력하면 상여금 얼마인지 출력>

declare 

v_eid employees.employee_id%type := &eid;

v_sal employees.salary%type;

v_bper number(2,2);

v_bonus number(7,2);

begin

select salary

into v_sal

from employees

where employee_id = v_eid;

if v_sal < 5000 then v_bper := 0.1;

elsif v_sal between 5000 and 10000 then v_bper := 0.15;

elsif v_sal > 10000 then v_bper := 0.2;

else v_bper := 0;

end if;

b_bonus := v_sal * v_bper;

dbms_output.put_line(v_eid || '  ' || v_sal || '  '|| v_bper|| '   ' || v_bonus);

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

BASIC LOOP문 (최소 한 번 실행하고 사용)

declare

v_counter number := 0;

begin

        loop

                v_counter := v_counter + 1;

                dbms_output.put_line(v_counter);

                exit when v_counter > 9;

        end loop;

end;

/

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

FOR LOOP문 (반복된 횟수가 정해진 경우 사용)

<1-10 까지 출력>

begin

        For i in reverse 1..10 LOOP

                dbms_output.put_line(i);

        end loop;

end;

/

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

CONTINUE문

<3으로 나눠서 0이면 Outer Loop 로 빠져나가기>

  1  begin

  2     <<OuterLoop>>

  3     For outer in 1..10 loop

  4             dbms_output.put_line(' -> outer = ' || outer);

  5             For inner in 1..10 loop

  6                     continue OuterLoop when mod(inner,3)= 0;

  7                     dbms_output.put_line('..-> inner = '|| inner)'

  8             end loop;

  9     end loop;

 10* end;

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

연습문제 <5-2>

begin

        for i in 1..10 loop

                continue when i in (6,8);

                insert into messages values(i);

        end loop;

        commit;

end;

/

**if문으로도 바꿔볼 것**

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

연습문제 <5장 mission>

1. 구구단 출력 (3단)begin

begin

        for samdan in 1..9 loope

                dbms_output.put_line('3*'||samdan||'='||3*samdan);

        end loop;

end;

/a

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

2. 입력받은 수의 구구단 출력

declare

        num number(1) := &num;

begin

        for gugudan in 1..9 loop

                dbms_output.put_line(num||'*'||gugudan||'='||num*gugudan);

        end loop;

end;

/

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

3. 별 LPAD

begin

        for i in 1..9 loop

                dbms_output.put_line(lpad('*',i,'*'));

        end loop;

end;

/

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

4. 별 RPAD

begin

        for i in reverse 1..9 loop

                dbms_output.put_line(lpad('*',i,'*'));

        end loop;

end;

/

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

5. -------********

 

begin

        for i in 1..9 loop

        dbms_output.put_line ((lpad ('-',10-i,'-'))||(lpad ('*',i,'*')));

        end loop;

end;

/

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

6. 짝수 단 출력

begin

     for i in 2..9 loop

     continue when mod(i, 2)!=0;

             dbms_output.put_line(i||'*'|| j || '=' ||i*j);

             end loop;

     end loop;

end;

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

7. employees 테이블을 기반으로 emp 테이블을 생성 후 샐러리 1000불당 *로 표시하기

DECLARE

v_empno emp.empno%type := &eid;

v_sal emp.sal%type;

v_star_num number;

v_star emp.start%type;

BEGIN

SELECT sal

INTO v_sal

FROM emp

WHERE empno = v_empno;

v_star_num := trunc(v_sal/1000);

for i in 1..v_star_num loop

v_star := v_star||'*';

end loop;

dbms_output..put_line (v_star);

END;