[PLSQL]23.07.24

망구씨·2023년 7월 24일

Basic loop PLSQL 구문에서의 NULL처리 PLSQL반복문 PLSQL에서 CASE문 사용 for loop while loop 논리테이블 반복문

PL/SQL

목록 보기

4/8

PL/SQL 복습

1장 : PL/SQL 정의, 왜 배워야하는지?

2장 : PL/SQL 변수

3장 : 실행절 작성법

4장 : 오라클 DB 와의 상호작용 5가지

SELECT ..into

insert

update

delete

merge

5장 : if 문과 loop문

오늘의 TIL

PL/SQL에서 CASE문 사용하기
PL/SQL 구문에서의 NULL처리
논리 테이블
반복 제어문 (Basic loop, while loop, for loop)

CASE 식

SQL에서의 CASE문과 PL/SQL에서의 CASE문의 차이를 이해하기 위해 먼저 SQL로 CASE문을 작성해봅시다.

예제1. (SQL문) 이름, 월급, 부서번호, 보너스를 출력하는데 보너스가 부서번호가 10번이면 6000이 출력되게 하고, 부서번호가 20번, 30번이면 8000이 출력되게 하고 나머지는 1000이 출력되게 하시오

select case when deptno = 10 then 6000
            when deptno in(20,30) then 8000
            else 1000 end as 보너스
  from emp;

✅SQL이 비절차적 언어인데, SQL로 절차적으로 수행된 결과를 볼 수 있게 지원해주는 유일한 함수가 바로 decode, case입니다.
그래서 sql의 한계를 pl/sql로 극복할 수 있습니다.

PL/SQL에서 CASE문 사용하기

✍🏻 예제

✅ SET VERIFY OFF를 쓰는 이유는 치환변수를 입력했을 때 OLD값, NEW값이 나오는 메세지를 출력되지 않게 하려고!

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE 
  v_grade char(1) := upper('&grade');
  v_appraisal varchar2(20);
BEGIN 
  v_appraisal := case v_grade
       WHEN 'A' THEN 'Excellent'
       WHEN 'B' THEN 'Very Good'
       WHEN 'C' THEN 'Good'
       ELSE 'No such grade'
     END;
dbms_output.put_line ('Grade: ' || v_grade || chr(10) ||'Appraisal: ' || v_appraisal);
END;
/

✅ chr(10) 는 PL/SQL에서의 엔터!

✅ SET VERIFY ON 이라고 하면 아래처럼 &grade에 값이 들어가기 전 코드와 &grade에 값이 들어간 후의 코드가 화면에 나타납니다. SET VERIFY OFF 는 결과값만 나오게하는 SQLplus 명령어입니다!

문제 50. 위 코드에 accept절을 이용해서 grade에 값을 물어보는 메세지가 다음과 같이 되게 하시오
원래 메세지 : grade에 대한 값 입력
수정된 메세지 : 등급을 입력하세요! (등급의 종류: A,B,C)

SET SERVEROUTPUT ON
SET VERIFY OFF
accept   p_grade  prompt '등급을 입력하세요! (등급의 종류: A,B,C)'
DECLARE 
  v_grade char(1) := upper('&p_grade');
  v_appraisal varchar2(20);
BEGIN 
  v_appraisal := case v_grade
       WHEN 'A' THEN 'Excellent'
       WHEN 'B' THEN 'Very Good'
       WHEN 'C' THEN 'Good'
       ELSE 'No such grade'
     END;
dbms_output.put_line ('Grade: ' || v_grade || chr(10) ||'Appraisal: ' || v_appraisal);
END;
/

문제 51. SQL) emp와 salgrade를 조인해서 이름, 월급, 급여등급(grade)을 출력하시오

select e.ename, e.sal, s.grade
  from emp e, salgrade s
  where e.sal between s.losal and s.hisal;

문제 52. 이름을 물어보게하고, 이름을 입력해서 해당 사원의 급여등급이 1등급이면 'Excellent'를 출력, 2등급과 3등급이면' Very Good', 4등급과 5등급이면 이면 'Good', 나머지는 'No such grade'

SET SERVEROUTPUT ON
SET VERIFY OFF
accept p_ename  prompt '이름을 입력하세요!'
DECLARE
   v_ename emp.ename%type := upper('&p_ename');
   v_message varchar2(20);
   v_grade salgrade.grade%type;
BEGIN   
   select s.grade into v_grade
   from emp e, salgrade s
   where e.sal between s.losal and s.hisal and e.ename = v_ename;

   v_message := case 
       WHEN v_grade = 5 THEN 'Excellent'
       WHEN v_grade in(3,4)  THEN 'Very Good'
       WHEN v_grade in(1,2) THEN 'Good'
       ELSE 'No such grade'
       END; 
dbms_output.put_line ('Grade: ' || v_grade || chr(10) ||'Appraisal: ' || v_message);
END;            
   /
✅ 위와같이 db에서 데이터를 select 해서 프로그래밍을 하는 것을 db프로그래밍이라고 합니다. PL/SQL 은 DB프로그래밍에서 최적화된 프로그램입니다. DB쪽에서 프로그래밍을 할일이 있으면 PL/SQL을 이용하는게 유익합니다. 파이썬이나 자바로 위의 작업을 하는것보다 PL/SQL이 성능도 빠르고 DB보안상의 이슈에 덜 민감합니다.

CASE문의 PL/SQL DB 프로그래밍 예제

✅ 위 CASE문에서는 CASE문 종료할 때 END CASE;로 사용했다. SELECT ... INTO를 사용하면 꼭 END CASE; 사용하자(안하면 에러)
✅ 위 코드는 108이라면 아래의 SELECT절을 실행한다.

예제1. CASE문 안에 select... into절을 입력한 PL/SQL문
이름을 물어보게 하고, 이름을 입력하면 해당 사원이 근무하는 부서의 인원수가 다음과 같이 출력되게 PL/SQL을 작성합니다.

accept p_ename prompt '이름을 입력하세요!'

declare
  v_ename emp.ename%type := upper('&p_ename');
  v_cnt   number;
  v_deptno emp.deptno%type;
  
begin
  select deptno into v_deptno
    from emp
    where ename = v_ename;
    
  case v_deptno
    when 10 then
              select count(*) into v_cnt
                from emp
                where deptno = 10;
    when 20 then
              select count(*) into v_cnt
                from emp
                where deptno = 20;
    when 30 then
              select count(*) into v_cnt
                from emp
                where deptno = 30;            
   end case;
dbms_output.put_line(v_ename || '이 근무하는 부서의 인원수는 '|| v_cnt|| '명 입니다.');
end;

문제 53. 위의 코드를 수정해서 다음과같이 수행되게 하시오
(CASE문에 SELECT .. INTO절을 하나 더 코딩하기)
이름을 입력하세요! -> SCOTT
SCOTT이 근무하는 부서의 인원수는 6명 입니다.
SCOTT은 RESEARCH(부서명)에서 근무합니다.

accept p_ename prompt '이름을 입력하세요!'

declare
  v_ename emp.ename%type := upper('&p_ename');
  v_cnt   number;
  v_deptno emp.deptno%type;
  v_dname dept.dname%type;

begin
  select deptno into v_deptno
    from emp
    where ename = v_ename;

  select dname into v_dname
    from dept
    where deptno = v_deptno;

  case v_deptno
    when 10 then
              select count(*) into v_cnt
                from emp
                where deptno = 10;
    when 20 then
              select count(*) into v_cnt
                from emp
                where deptno = 20;
    when 30 then
              select count(*) into v_cnt
                from emp
                where deptno = 30;            
   end case;
dbms_output.put_line(v_ename || '이 근무하는 부서의 인원수는 '|| v_cnt|| '명 입니다.');
dbms_output.put_line(v_ename || '은 '|| v_dname|| '에서 근무합니다');
end;

SQL로 위의 결과를 보려면 조인을 해야하는데, 빅데이터 환경에서는 조인을 하는 SQL의 성능이 느립니다. 그럴때는 위와같이 PL/SQL로 작성해서 조인하지 않고 단일테이블에서 데이터를 각각 가져오게 하는게 바람직한 방법입니다.

PL/SQL 구문에서의 NULL처리

널 값과 관련하여 다음 규칙을 기억해 두면 일반적으로 발생할 수 있는 실수를 피할 수 있습니다.
✅ 널을 사용하는 단순 비교는 항상 NULL을 반환합니다.

예: (v_deptno = 10 에서 v_deptno가 null이라면 null반환)

✅ 논리 연산자 NOT을 널에 적용하면 NULL이 발생합니다.
✅ 조건 제어문에서 조건이 NULL을 반환하면 연관된 명령문
시퀀스가 실행되지 않습니다

예: if 조건 then // 조건이 null
     실행문;

💡 혹시 에러는 안나는데 결과가 나오지않는다면? NULL 의심해보자!

논리 테이블

논리연산자: and, or, not
비교 연산자를 사용하여 단순 부울 조건을 작성합니다.

true and null은 null
false and null은 왜 false? (false and false는 false / false and true는 false니까)
true or null 은 true. (true or false 도 true니까)
false or null null이다 (false or true는 true, false or false는 false라 뭔지 모른다.)

[점심시간문제] 문제 53번 코드의 case문에 select into절을 하나 더 추가해서 다음의 결과가 출력되게 하세요. (성능의 느린 sql을 pl/sql로 튜닝하는 방법)

이름을 입력하세요 -> scott
SCOTT이 근무하는 부서의 인원수는 6명 입니다.
SCOTT은 RESEARCH(부서명)에서 근무합니다.
SCOTT의 급여등급은 4등급입니다.

accept p_ename prompt '이름을 입력하세요!'

declare
  v_ename   emp.ename%type := upper('&p_ename');
  v_cnt     number;
  v_deptno  emp.deptno%type;
  v_dname   dept.dname%type;
  v_grade   salgrade.grade%type;
  v_sal     emp.sal%type;
  
begin
  select deptno, sal into v_deptno, v_sal
    from emp
    where ename = v_ename;
    
  select dname into v_dname
    from dept
    where deptno = v_deptno;
    
  select grade into v_grade
    from salgrade
    where v_sal between losal and hisal;
    
  case v_deptno
    when 10 then
              select count(*) into v_cnt
                from emp
                where deptno = 10;          
    when 20 then
              select count(*) into v_cnt
                from emp
                where deptno = 20;
    when 30 then
              select count(*) into v_cnt
                from emp
                where deptno = 30;            
   end case;
dbms_output.put_line(v_ename || '이 근무하는 부서의 인원수는 '|| v_cnt|| '명 입니다.');
dbms_output.put_line(v_ename || '은 '|| v_dname|| '에서 근무합니다');
dbms_output.put_line(v_ename || '의 급여등급은 '|| v_grade|| '입니다');
end;

✅ 조인을 해서 결과를 출력하는 무거운 SLOW SQL -> PL/SQL + 조인하지 않는 SQL

🤔 퀴즈!

반복 제어문 (LOOP문)

✅ 특정 실행문을 반복시키고 싶을 때 반복문을 사용합니다.

반복문의 종류 3가지

basic loop : 조건없이 그냥 반복시키고 싶을 때

for loop : 조건을 주고 반복시키고 싶을 때

while loop

Basic loop문

LOOP + 반복하고싶은 실행문
EXIT [WHEN 반복문을 끝낼 조건] ;
END LOOP ;
🚨 LOOP, END LOOP ; 사이에 실행문을 넣고 해당 실행문이 EXIT WHEN절에 작성한 조건에 해당되지 않는 동안 반복된다. 중요한것은, EXIT WHEN절 없이 수행하게되면 무한루프가 발생하면서 서버의 CPU를 상당히 차지하게 되고, 서버의 성능을 떨어트리는 원인이 됩니다.

예제. LOCATIONS의 테이블의 데이터를 불러와서 반복하여 다시 데이터를 LOCATIONS에 INSERT하는 중.

✅ MAX값은, 나라마다 location_id중 가장 높은것을 뽑은것이다. 왜냐면 1900이라면 1900 다음값인 1901가 되게 하려고.

LOCATIONS 테이블

DECLARE
    v_countryid   locations.country_id%TYPE := 'CA';
    v_loc_id      locations.location_id%TYPE;
    v_counter     NUMBER(2) := 1;
    v_new_city    locations.city%TYPE := 'Montreal';
BEGIN
    SELECT MAX(location_id) INTO v_loc_id FROM locations
    WHERE country_id = v_countryid;
    LOOP
        INSERT INTO locations(location_id, city, country_id) 
        VALUES((v_loc_id + v_counter), v_new_city, v_countryid);
        v_counter := v_counter + 1;
    EXIT WHEN v_counter > 3;
    END LOOP;
END;
/

v_counter := v_counter + 1;
EXIT WHEN v_counter > 3; 절대 빠지면 안된다!!!

문제 55. 베이직 루프문으로 숫자 1부터 10까지 출력하시오

DECLARE
    v_counter number(20) := 1;
BEGIN
    LOOP
        dbms_output.put_line(v_counter);
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 10;
    END LOOP;
END;
/

문제 56. 위 숫자를 가로로 출력하시오

SET SERVEROUTPUT ON
DECLARE
    v_counter number(20) := 1;
BEGIN
    LOOP
        dbms_output.put_line(v_counter);
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 10;
    END LOOP;
        dbms_output.new_line;
END;
/

참고! dbms_output.put_line에서 _line을 빼면 가로로 출력된다. 공백을 조금 주려면 연결연산자 사용

문제 57. 숫자를 물어보게하고, 숫자를 입력하면 1번부터 해당 숫자까지 출력되게 하시오 (100 입력하면 1~ 100까지 출력)
⭕ 정답 : 초기값에 1 설정하고, 반복문의 조건(마지막 숫자)를 내가 입력한 숫자로 설정했다.

accept  p_num prompt '숫자를 입력하세요 ~ ' 
declare
      v_counter   number :=  1 ; 
begin
    loop
          dbms_output.put( v_counter ||'  ');
          v_counter := v_counter + 1 ;
          exit   when   v_counter > &p_num;
   end  loop;
         dbms_output.new_line; 
end;
/

❌ 내 오답 : 초기값에 내가 입력한 숫자를 받았고, 반복문의 조건(마지막 숫자)도 내가 입력한 숫자를 설정해서, 내가 입력한 숫자 1개만 나왔다.

accept p_num prompt '숫자를 입력하세요!'
DECLARE
    v_counter number(20) := &p_num; // 초기값 내가 입력한값
BEGIN
    LOOP
        dbms_output.put(v_counter); 
        v_counter := v_counter + 1;
        EXIT WHEN v_counter >= &p_num; // 마지막도 내가 입력한 값
    END LOOP;
        dbms_output.new_line; 
END;
/

문제 58. dept테이블을 select 했을 때 아래와 같이 결과가 출력되게 basic loop문으로 PL/SQL 프로그래밍 작성하기
SELECT * FROM DEPT;

10            ACCOUNTING            NEWYORK
20                .                    .
30                .                    .
40                .                    .
50            RESEARCH1              SEOUL
60            RESEARCH2              SEOUL
70            RESEARCH3              SEOUL
80            RESEARCH4              SEOUL

declare
      v_cnt1     number :=  50 ;
      v_cnt2     number :=  1;
begin
    loop
          insert  into  dept  values( v_cnt1,  'RESEARCH' || v_cnt2, 'SEOUL' );
          v_cnt1 := v_cnt1 + 10;
          v_cnt2 := v_cnt2 + 1 ;
          exit   when    v_cnt2 > 4;
   end  loop;
end;
/

✅ 특정 실행문을 간단하게 반복시키고 싶을 때 basic loop를 사용하면 됩니다. exit when절을 생략하지 않도록 주의하기!

while loop문

✅ 베이직루프는 내가 조건을 모르고, 특정 실행문을 반복하고 싶을 때 사용하지만 반복해야하는 조건을 미리 알고있다면, while loop를 사용하면 유용하다. while과 loop 사이에 조건이 true인 동안에만 반복문을 실행한다!

예제 while loop문을 이용해서 숫자 1부터 10까지 출력하시오

declare
  v_cnt  number := 1;
  
begin 
  while  v_cnt < 11 loop
    dbms_output.put_line(v_cnt);
    v_cnt := v_cnt + 1;
  end loop;
end;
/

문제 59. 1~10까지의 숫자중에 짝수만 출력하시오!

declare
  v_cnt  number := 1;
begin 
  while  v_cnt < 11 loop

   if mod(v_cnt,2) = 0 then
    dbms_output.put_line(v_cnt);
   end if;

    v_cnt := v_cnt + 1;
  end loop;
end;
/

✅ if로 시작했다면 end if;로 끝나야하고, v_cnt를 2로 나는 값이 0이면 (짝수) 출력하기.

예제. while loop 문을 db 프로그래밍 하는 예제

🤔 두 반복문의 차이점

exit when절의 유무

반복할 조건을 미리 알고있는지

basic loop 는 exit when 무조건 써야하고,
while loop 는 exit when절이 없다. 조건을 while과 loop사이에 주고있다.
basic loop는 조건을 그냥 끝내고싶은것을 설정해주는거라면
while loop는 3번만 증가하면 된다 라는것을 분명하게 알고 조건을 준 것

DECLARE
    v_countryid locations.country_id%TYPE := 'CA';
    v_loc_id locations.location_id%TYPE;
    v_new_city locations.city%TYPE := 'Montreal';
    v_counter NUMBER := 1;
BEGIN
    SELECT MAX(location_id) INTO v_loc_id FROM locations
    WHERE country_id = v_countryid;
WHILE v_counter <= 3 LOOP

    INSERT INTO locations(location_id, city, country_id) 
    VALUES((v_loc_id + v_counter), v_new_city, v_countryid);
    
    v_counter := v_counter + 1;
    
END LOOP;
END;
/

✅ 위 베이직 루프와 같은결과를 가져오는 while loop!

문제 60. while loop문을 이용해서 dept테이블에서 dname만 다음과 같이 출력하시오

ACCOUNTING
RESEARCH
SALES
OPERATIONS

DECLARE
    v_deptno number := 10;
    v_dname dept.dname%type;
BEGIN

    WHILE v_deptno <= 40 LOOP
  
      SELECT dname INTO v_dname 
        FROM dept
        WHERE deptno = v_deptno;

    dbms_output.put_line(v_dname); 
    v_deptno := v_deptno + 10;
END LOOP;
END;
/

✅ deptno가 40까지라는것을 우리는 너무 잘 안다!

문제 61. (SQL문제) 사원 테이블에 rowid, empno, ename 출력

select rowid, empno, ename
  from emp;

아까는 deptno가 증가되는 패턴이 동일했는데 지금 empno는 동일하게 증가하지 않는다. 그렇다면 rowid를 사용해보자. 뒤에 A, B, C, D...로 증가중!

select ename, sal
  from emp
  where rowid = 'AAASVTAAHAAAaoeAAA';

rowid는 데이터베이스에서 데이터를 검색하는 가장 빠른 방법!

문제 62. (SQL문제) 아래의 결과를 PL/SQL로 출력하기 위해서 숫자 65가 문자로 무엇인지 출력하시오

AAASVTAAHAAAaoeAAA
AAASVTAAHAAAaoeAAB
AAASVTAAHAAAaoeAAC
AAASVTAAHAAAaoeAAD
AAASVTAAHAAAaoeAAE
AAASVTAAHAAAaoeAAF
AAASVTAAHAAAaoeAAG
AAASVTAAHAAAaoeAAH
AAASVTAAHAAAaoeAAI
AAASVTAAHAAAaoeAAJ
AAASVTAAHAAAaoeAAK
AAASVTAAHAAAaoeAAL
AAASVTAAHAAAaoeAAM
AAASVTAAHAAAaoeAAN

select chr(65) 
  from dual;

문제 63. 알파벳 A ~ N까지를 다음과 같이 출력하시오

A
B
C
.
.
.
N

declare
  v_num  number := 65;
  v_chr  VARCHAR2(10) := 'chr(65)';

begin 
  while  v_num < 78 loop
  select chr(v_num) into v_chr
    from dual;
    dbms_output.put_line(v_chr);
    v_num := v_num + 1;
  end loop;
end;
/

문제 64. 위 코드를 가지고 아래의 rowid를 출력하기

declare
  v_num  number := 65;
  v_chr  VARCHAR2(10) := 'chr(65)';

begin 
  while  v_num < 78 loop
  select chr(v_num) into v_chr
    from dual;
    dbms_output.put_line( 'AAASVTAAHAAAaoeAA' || v_chr);
    v_num := v_num + 1;
  end loop;
end;
/

문제 65. 위 코드를 이용해서 사원테이블의 이름을 전부 출력하시오

declare
  v_num  number := 65;
  v_chr  VARCHAR2(10) := 'chr(65)';
  v_ename emp.ename%type;

begin 
  while  v_num < 78 loop     
    select chr(v_num) into v_chr
    from dual;

    select ename into v_ename
     from emp
     where rowid = 'AAASVTAAHAAAaoeAA' || v_chr;

    dbms_output.put_line( v_ename);
    v_num := v_num + 1;
  end loop;
end;
/

for loop문

예제 숫자 1 ~ 10까지 출력하시오

begin
  for i in 1 .. 10 loop
    dbms_output.put_line(i);
  end loop;
end;

✅ 1이 i로 들어간다. i가 1일때, i가 2일때, i가 3일때......
3가지 loop문 중에 코드가 가장 심플한 반복문이다!

문제 65. 1 ~ 50까지의 숫자를 출력하는데 홀수만 출력하기

begin
  for i in 1 .. 50 loop
   if mod(i,2) = 1 then
    dbms_output.put_line(i);
   end if; 
  end loop;
end;

문제 66. (오늘의 마지막 문제) for loop 문으로 사원테이블의 이름, 월급을 다음과 같이 출력

KING    5000
BLAKE   2850
CLARK   2450
      .
      .
      .

내 답

declare
  v_chr  VARCHAR2(10) := 'chr(65)';
  v_num  number := 65; // 지우기
  v_ename emp.ename%type;
  v_sal emp.sal%type;

begin 
  for i in 65.. 78 loop     

      select chr(i) into v_chr
        from dual;  

      select ename , sal into v_ename, v_sal
         from emp
         where rowid = 'AAASVTAAHAAAaoeAA' || v_chr;

    dbms_output.put_line( v_ename ||' ' || v_sal );
  end loop;
end;
/

수정
✅ 지금 선언한 v_num은 i 로 대체되어 있어서 사용하지 않고있음. 지워도 잘 수행이 된다.
✅ select chr(i) into v_chr <- 이거 지우고 chr(i)자체를 emp테이블 where절에 사용해도 된다. 어차피 chr(65) 이거를 뽑고싶은거니까

declare
  v_ename emp.ename%type;
  v_sal emp.sal%type; // 선언에 얘들만 남기고

begin 
  for i in 65.. 78 loop     

      select ename , sal into v_ename, v_sal
         from emp
         where rowid = 'AAASVTAAHAAAaoeAA' || chr(i);
        // select절도 하나만 둔다. where에 v_chr가 아닌
        // chr(i) -> chr(65), chr(66), chr(67)...로 뽑기
    dbms_output.put_line( v_ename || ' ' || v_sal );
  end loop;
end;
/

망구씨

Slow and steady wins the race.

이전 포스트

[PLSQL]23.07.21

다음 포스트