230724 PL/SQL 4

권주희·2023년 7월 24일

복습

1장 :
2장 : PL/SQL 변수
3장 : 실행절 작성법
4장 : 오라클 DB와의 상호작용 5가지 -> 자동화 가능
1) select.. into
2) insert
3) update
4) delete
5) merge
5장 : IF문과 loop문

case식

SQL 에서의 case 문 사용 (복습)

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

select ename, sal, deptno,
       case when deptno = 10 then 6000
            when deptno = 20 then 8000
            when deptno in (20,30) then 8000
            else 1000 end as bonus
 from emp ;

✔️ SQL이 비절차적 언어인데 sql로 절차적으로 수행된 결과를 볼 수 있게 지원해주는 유일한 함수가 바로 decode 와 case 문입니다.
✔️ SQL의 한계를 PL/SQL 로 극복할 수 있다.

PL/SQL 내에서 case 문 사용

set serveroutput on
set verify off       -- old 값, new 값 안뜨게 하는 장치
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) ||  -- chr(10) 은 pl/sql 의 enter
                      'Appraisal ' || v_appraisal);
END;
/

✅ set verify on 했을때?

B가 들어가서 실행되었다..! 이런 표시?
굳이 결과창이 지저분한게 별로니까 off 해주는게 좋다?

➡️ set verify off 는 &grade 에 값이 들어가기 전의 코드와 값이 들어간 후의 코드를 화면에 나타내지는 않고 결과값만 나오게하는 sql 명령어입니다.

프롬포트 메세지 변경

문제 50.
위의 코드에 accept 절을 이용해서 grade 에 값을 물어보는 메세지가 다음과 같이 되게 하시오

원래 메세지 : grade 에 대한 값 입력

수정된 메세지 : 등급을 입력하세요! (등급의 종류: A,B,C)
set serveroutput on
set verify off 

accept  grade  prompt '등급을 입력하세요! (등급의 종류: A,B,C)'

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) ||  -- chr(10) 은 pl/sql 의 enter
                      'Appraisal ' || v_appraisal);
END;
/

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

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

조인해서 프롬포트 출력하기

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

이름을 입력하세요!  (KING)

급여등급 : 1
평가 : Excellent
set serveroutput on
set verify off 

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

DECLARE
  v_ename emp.ename%type := upper('&p_ename);
  v_grade salgrade.grade&type;
  v_appraisal VARCHAR2(20);

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_appraisal := CASE
    WHEN v_grade = 5 THEN 'Excellent'
    WHEN v_grade in (4,3) THEN 'Very Good'
    WHEN v_grade in (2,1) THEN 'Good'
    ELSE 'No such grade'
  END;

DBMS_OUTPUT.PUT_LINE ('급여등급: '|| v_grade || chr(10) ||
                      '평가: ' || v_appraisal);
END;
/

✔️ 위와 같이 db 에서 데이터를 select 해서 프로그래밍을 하는 것을
db 프로그래밍이라고 합니다. PL/SQL 은 DB 프로그래밍에 최적화된 프로그램 입니다.
DB 쪽에서 프로그래밍 할 일이 있으면 PL/SQL 을 이용하는게 유익합니다.
파이썬이나 자바로 하려면 오히려 성능이 느리고 보안상으로 번거롭다.

case문 안에 select .. into 절을 입력한 PL/SQL 문


⬇️ emp 테이블로 수정하기
예제1. 이름을 물어보게하고 이름을 입력하면 해당 사원이 근무하는 부서의 인원수가 다음과 같이 출력되게 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 108 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;
/


✔️ 위의 case문을 쓸 때는, case 문 종료할 때 end case 로 사용 (select 문의 case 사용시)

join 쓰지말고 case select .. into 사용하기

❓ 수업노트 다시 보기 (답업데이트안함)

문제 53.
위의 코드를 수정해서 다음과 같이 수행되게 하시오 (join 쓰지말고 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;
 case  v_deptno
  when 10 then
           select  count(*) into v_cnt
             from emp
             where deptno = 10;
           select   dname   into  v_dname 
             from  dept
             where  deptno = v_deptno;
  when 20 then
          select count(*) into v_cnt
             from emp
             where deptno = 20;
           select   dname   into  v_dname 
             from  dept
             where  deptno = v_deptno;
  when 30 then
           select count(*) into v_cnt
             from emp
             where deptno = 30;
          select   dname   into  v_dname 
            from  dept
            where  deptno = v_deptno;
  end case;

dbms_output.put_line(  v_ename ||' 이 근무하는 부서의 인원수는 ' || v_cnt || '명 입니다.' || chr(10) 
|| v_ename || ' 은 ' || v_dname ||' 에서 근무하고 있습니다.' ); 

end;
/

✔️ SQL 로 위의 결과를 보려면 조인을 해야하는데 빅데이터 환경에서는 조인을 하는 SQL 의 성능이 느립니다. 그럴때는 위와 같이 PL/SQL로 작성해서 조인하지 않고 단일 테이블에서 데이터를 각각 가져오게 하는게 바람직한 방법입니다.
✔️ WHEN 쪽에다가 SELECT 를 두개써서 하나는 Count 에 대한것, 하나는 dname 에 대한것 으로 구현되게 한다.

PL/SQL 구문에서의 null 처리

NULL 값 규칙

널 값과 관련하여 다음 규칙을 기억해 두면 일반적으로 발생할수 있는 실수를 피할 수 있습니다.

  • 널을 사용하는 단순 비교는 항상 NULL을 반환합니다.
    • v_deptno 중 선택했는데 null 이면 null 반환
  • 논리 연산자 NOT을 널에 적용하면 NULL이 발생합니다.
  • 조건 제어문에서 조건이 NULL을 반환하면 연관된 명령문 시퀀스가 실행되지 않습니다.
    • if 조건 then ~ 에서 이 조건이 null 이 되면, 실행문이 아예 실행되지 않는다.

✅ IF문에서 조건이 NULL이 되면 PL/SQL 실행 X
ex) if 조건 then
실행문 ;
-> 실행X

논리테이블 (비교연산자와 부울조건)

✅ 논리연산자 : and, or, not

TRUE and NULL 은 null이다
FALSE and NUNLL 은 FALSE 이다
-> FALSE and TRUE 여도 FALSE 고, FALSE and FALSE 도 FALSE 라서!!

반대로, or에서는
트루 or 트루 에서는 트루 한개만 있어도 트루라서 트루
펄스 or null 은 '펄스 or 펄스'는 펄스이기 때문에 펄스가 될수 있어서, 펄스!

문제 54. (점심시간 문제)
문제 53번 코드의 case 문에 select .. into 절을 추가해서 다음의 결과가 출력되게 하시오

이름을 입력하세요!  (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 sept
    where deptno = v_deptno;

 select  grade  into  v_grade
   from  emp
   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;
/
profile
권주희
열씨미하자
이전 포스트

230713 PL/SQL 3

0개의 댓글