Oracle 56일차

[PL/SQL]

절차적 프로그래밍(이전 명령어의 결과를 다음 명령어에 전달)을 지원하는 SQL 확장 언어.
변수 사용 가능.
반복문/조건문 정의 가능.
블럭 단위 구조(선언부/실행부/예외처리부로 구성)
블럭 이름 유무에 따라 익명블럭, 저장블럭으로 나뉜다.
– 블럭에 이름 부여 -> 객체 생성

객체 : 프로시저, 함수, 트리거, 패키지

1. 구조

1) 선언부(declare)
– 변수 선언 (default value 지정 가능)
– 데이터 타입 정의
– IN/OUT 정의
– 생략 가능

2) 실행부(begin)
– 프로그램 동작 명령
– 변수의 값 정의
– 조건문/반복문 처리

3) 예외처리부(exception)
– 예외를 발생시키는 로직 정의
– 에러 발생(ORA 코드와 함께 발생시킬 수 있다)
– 생략 가능

2. 변수 선언

var1 := 1 형태로 선언.
그냥 = 은 ‘same’의 의미로 쓰이기 때문에 := 기호를 써야 변수 선언 가능하다.

3. 변수 종류

1) 스칼라 변수
– 값을 직접 상수로 정의
ex) var1 := 1

2) 참조 변수
– 변수의 값을 특정 대상을 참조하면서 정의
ex) var1 emp.empno%type

[변수 선언]

select문의 결과를 변수에 저장, 저장된 변수의 값을 활용한 문장 출력.

** plsql 출력
plsql 블럭에 대한 출력은 기본적으로 생략됨.
방법 :
– sqlplus) set serveroutput on
– orange) result 창의 server output 탭에서 출력 가능

** 사전 설정

create table scott.EMP
as
select * from scott.emp@dblink_orcl;

select * from scott.emp;

create table scott.dept
as
select * from scott.dept@dblink_orcl;

select * from scott.dept;

예제1.

emp 테이블에서 사원번호가 7369직원의 이름, 부서번호, 직업을 아래와 같이 출력.
SMITH-20-CLERK

declare
    vename  varchar2(10);
    vdeptno number;
    vjob    varchar2(10);
begin
    select ename, deptno, job into vename, vdeptno, vjob
    from scott.EMP
    where empno=7369;
    
    dbms_output.put_line(vename||'-'||vdeptno||'-'||vjob);
end;
/

예제2.

위와 비슷한데 empno만 입력 받음.
prompt에서만 수행 가능한 듯?

declare
    vename  varchar2(10);
    vdeptno number;
    vjob    varchar2(10);
begin
    select ename, deptno, job into vename, vdeptno, vjob
    from scott.EMP
    where empno = &vempno;
    
    dbms_output.put_line(vename||'-'||vdeptno||'-'||vjob);
end;
/

-- prompt에서 실행시
Enter value for vempno: 7369
old   8:     where empno = &vempno;
new   8:     where empno = 7369;
SMITH-20-CLERK

PL/SQL procedure successfully completed.
-- 이런 식으로 출력됨.

> set verify off 하면

Enter value for vempno: 7369
SMITH-20-CLERK

PL/SQL procedure successfully completed.
-- 이런 식으로 출력됨.

> set feedback off 하면
Enter value for vempno: 7369
SMITH-20-CLERK

예제3.

위와 비슷한데 empno를 “사원번호를 입력하세요”와 함께 입력 받음.
prompt에서만 가능한 듯?

!vi ex1.sql
-- 아래 plsql을 저장해둠.
declare
    vename  varchar2(10);
    vdeptno number;
    vjob    varchar2(10);
begin
    select ename, deptno, job into vename, vdeptno, vjob
    from scott.EMP
    where empno = &vempno;
    
    dbms_output.put_line(vename||'-'||vdeptno||'-'||vjob);
end;
/

-- vempno 설정하기 전에 sql 실행 시
SQL> @ex1.sql
Enter value for vempno: 7369
SMITH-20-CLERK

SQL> accept vempno prompt '사번을 입력하세요 : '
사번을 입력하세요 : 7369  -- vempno값이 저렇게 설정된 거임.
SQL> @ex1.sql
SMITH-20-CLERK  -- 이미 vempno값이 설정되어서 실행시 자동 출력됨.

** 한글 안 깨지려면
export LANG=ko_KR.UTF-8
export NLS_LANG=KOREAN_KOREA.AL32UTF8

예제4.

emp 테이블을 사용하여, 사번을 입력받고, 해당 사원의 퇴직금을 아래와 같이 출력
SMITH의 퇴직금 : 9999
– 퇴직금 = 근속연수 * SAL * 0.1 (정수로 반올림)

declare
    vename  varchar2(10);
    vsal    number;
    vhiredate   date;
    vretire number;
begin
    select ename, sal, hiredate into vename, vsal, vhiredate
    from scott.EMP
    where empno = &vempno;
    vretire := round(sal*0.1*trunc(months_between(sysdate,hiredate)/12));
    dbms_output.put_line(vename||'의 퇴직금 : '||vretire);
end;
/

-- 강사님 방법
!vi ex2.sql
-- 아래 모든 거 입력 후 prompt에서 ex2.sql 실행 --
accept vempno prompt '퇴직금을 조회할 사번을 입력하세요 : '

declare
    vename  varchar2(10);
    vretire number;
    vempno  number := &vempno; 
    -- 입력받은 값을 재사용할 경우 반드시 변수 선언 필요(변수 선언 시 값의 입력 유도 가능)
begin
    select ename, round(sal*0.1*trunc(months_between(sysdate,hiredate)/12)) into vename, vretire
    from scott.EMP
    where empno = vempno;
    
    dbms_output.put_line(vename||'의 퇴직금 : '||vretire);
end;
/

-- 그러면
SQL> @ex2.sql
퇴직금을 조회할 사번을 입력하세요 : 7369
SMITH의 퇴직금 : 3520

[UPDATE]

예제1.

사번을 입력받고 해당 직원의 급여를 10% 증가 급여로 update
출력은 기존 급여와 인상된 급여를 같이 출력

!vi ex3.sql
---------------------------------
accept vempno prompt '당첨된 사원의 사원번호를 입력하세요 : '

declare
    vempno  number := &vempno;
    vsal    number;
begin
    select sal into vsal
    from scott.EMP
    where empno = vempno;
    
    update scott.EMP
    set sal = vsal * 1.1
    where empno = vempno;
    
    commit;
    
    dbms_output.put_line(vempno||'사원의 급여가 '||vsal||'에서 '||vsal*1.1||'로 변경되었습니다.');
end;
/

-- 그러면
SQL> @ex3.sql
당첨된 사원의 사원번호를 입력하세요 : 7369
7369사원의 급여가 800에서 880로 변경되었습니다.

-- 그리고
SQL> select sal from scott.emp where empno=7369;
       SAL
----------
       880

예제2.

이미 입력된 학생의 시험성적을 변경(학번과 변경 후 시험성적 입력받음). 시험성적 변경에 따른 학점 변경.

** 사전 설정

select * from SCOTT.EXAM_01;
select * from SCOTT.HAKJUM;

create table scott.stu_hakjum
as
select e.studno, h.grade
from SCOTT.EXAM_01 e, SCOTT.HAKJUM h
where e.total between h.min_point and h.max_point;

select * from SCOTT.stu_hakjum;

** plsql

> !vi ex4.sql
------------------------------------------------------
accept vstudno prompt '변경을 원하는 학생의 학번을 입력하세요 : '
accept vtotal prompt '해당 학생의 점수를 입력하세요 : '

declare
    vstudno  number := &vstudno;
    vtotal   number := &vtotal;
    vgrade   varchar2(5);
    extotal  number;
    exgrade  varchar2(5);
begin
    select total into extotal
    from SCOTT.EXAM_01
    where studno = vstudno;
    
    select grade into exgrade
    from SCOTT.stu_hakjum
    where studno = vstudno;
    
    dbms_output.put_line(vstudno||' ex total : '||extotal||', grade : '||exgrade);
    
    update scott.EXAM_01
    set total = vtotal
    where studno = vstudno;
    
    select grade into vgrade
    from SCOTT.HAKJUM
    where vtotal between min_point and max_point;
    
    update SCOTT.stu_hakjum
    set grade = vgrade
    where studno = vstudno;
    
    commit;
    
    dbms_output.put_line(vstudno||' new total : '||vtotal||', grade : '||vgrade);
end;
/

-- 그러면
SQL> @ex4.sql
변경을 원하는 학생의 학번을 입력하세요 : 9414
해당 학생의 점수를 입력하세요 : 90
9414 ex total : 90, grade : A0
9414 new total : 90, grade : A0

참조 변수 선언

다른 변수나 컬럼을 참조하여 새로운 변수를 정의.

ex)
declare
    vempno scott.emp.empno&type;

예제) 학번을 입력받고 해당 학생의 시험성적, 학점 출력

> !vi ex5.sql
------------------------------------------------------
accept vstudno prompt '학번을 입력하세요 : '

declare
    vstudno scott.student.studno%type := &vstudno;
    vtotal scott.exam_01.total%type;
    vgrade scott.hakjum.grade%type;
begin
    select e.total, h.grade into vtotal, vgrade
    from scott.exam_01 e, scott.hakjum h
    where e.total between h.min_point and h.max_point
    and studno = vstudno;
    
    dbms_output.put_line(vstudno||' 학생의 시험점수 : '||vtotal||' , 학점 : '||vgrade);
end;
/

-- 그러면
SQL> @ex5.sql
학번을 입력하세요 : 9411
9411 ????????? ???????????? : 97 , ?????? : A+

외부 변수 insert

외부 변수의 값을 특정 테이블에 insert 시 입력 변수 사용
** 문자는 ‘로 감싸줘야 한다, number는 안 그래도 됨.

-- 사전 설정
create table scott.table1

-- plsql
> !vi ex7.sql
accept vno prompt '사원번호를 입력하세요 : '
accept vname prompt '이름을 입력하세요 : '
accept vaddr prompt '주소를 입력하세요 : '

declare
    vno     number := &vno;
    vname   varchar2(10) := '&vname';
    vaddr   varchar2(50) := '&vaddr';
begin
    insert into scott.table1 values(vno, vname, vaddr);
    commit;
    dbms_output.put_line('no : '||vno||', name : '||vname||', addr : '||vaddr||' 값이 입력되었습니다.');
end;
/

[if문]

SQL문에는 사용 불가, PL/SQL문에 가능

** 문법1
begin
    if 조건1 then 리턴1;
    end if;
    if 조건2 then 리턴2;
    end if;
end;
/

** 문법2
begin
    if (조건1)  -- 괄호 생략 가능
        then
            명령어1;
            명령어2;
        else
            명령어1;
            명령어2;
    end if;
end;
/

** 문법3
begin
    if (조건1)  -- 괄호 생략 가능
        then
            명령어1;
            명령어2;
    elsif (조건2)
        then
            명령어1;
            명령어2;
        else
            명령어1;
            명령어2;
    end if;
end;
/

예제1

emp 테이블에서 사번을 입력받고 해당 사원의 부서명을 아래와 같이 출력.
– 7369의 소속 부서명 : 인사부
deptno 10=인사부/20=총무부/30=재무부

> !vi ex8.sql
--------------------------
accept vempno prompt '사번을 입력하세요 : '

declare
    vempno  number := &vempno;
    vdeptno number;
    vdname  varchar2(10);
begin
    select deptno into vdeptno
    from scott.emp
    where empno = vempno;
    
    if (vdeptno = 10) then
        vdname := '인사부';
    end if;
    if (vdeptno = 20) then
        vdname := '총무부';
    end if;
    if (vdeptno = 30) then
        vdname := '재무부';
    end if;
    
    dbms_output.put_line(vempno||'의 소속 부서명 : '||vdname);
end;
/

-- 그러면
SQL> @ex8.sql
사번을 입력하세요 : 7369
7369의 소속 부서명 : 총무부

예제2

student 테이블에서 학번을 입력받고 해당 학생의 키, 몸무게, 비만 여부를 출력.
** 표준체중 = (키 – 100) * 0.9
표준체중 < 체중 : 과체중
표준체중 > 체중 : 저체중
표준체중 = 체중 : 표준

> !vi ex9.sql
----------------------------------------------
accept vstudno prompt '학번을 입력하세요 : '

declare
    vstudno number := &vstudno;
    vhei    number;
    vwei    number;
    vfat    varchar2(10);
begin
    select height, weight into vhei, vwei
    from scott.student
    where studno = vstudno;
    
    if (0.9*(vhei-100) < vwei) then
        vfat := 'over';
    elsif (0.9*(vhei-100) = vwei) then
        vfat := 'avg';
    else
        vfat := 'less';
    end if;
    
    dbms_output.put_line('height : '||vhei||', weight : '||vwei||', fat : '||vfat);
end;
/

-- 그러면
SQL> @ex9.sql
학번을 입력하세요 : 9411
height : 180, weight : 72, fat : avg
SQL> @ex9.sql
학번을 입력하세요 : 9412
height : 172, weight : 64, fat : less

[case문]

SQL, PL/SQL 모두에서 사용 가능.

예제1)

바로 위 1번에서 한 예제 case문으로 변경

accept vempno prompt '사번을 입력하세요 : '

declare
    vempno  number := &vempno;
    vdeptno number;
    vdname  varchar2(10);
begin
    select deptno into vdeptno
    from scott.emp
    where empno = vempno;
    
    vdname := case when vdeptno = 10 then '인사부'
                          when vdeptno = 20 then '총무부'
                          else '재무부'
                   end;
    
    dbms_output.put_line(vempno||'의 소속 부서명 : '||vdname);
end;
/

예제2)

바로 위 2번에서 한 예제 case문으로 변경

accept vstudno prompt '학번을 입력하세요 : '

declare
    vstudno number := &vstudno;
    vhei    number;
    vwei    number;
    vfat    varchar2(10);
begin
    select height, weight into vhei, vwei
    from scott.student
    where studno = vstudno;
    
    vfat := case when (0.9*(vhei-100) < vwei) then 'over'
                    when (0.9*(vhei-100) = vwei) then 'avg'
                    else 'less'
              end;
    
    dbms_output.put_line('height : '||vhei||', weight : '||vwei||', fat : '||vfat);
end;
/

[실습]

delivery 테이블에서 일자와 업종을 입력 시 해당 업종의 전이랒 대비 통화량 증가율 출력

** 사전 설정

insert into scott.DELIVERY
select * from scott.DELIVERY@dblink_orcl;
commit;

select * from SCOTT.DELIVERY;

> !vi ex12.sql
-----------------------------------------------------
accept vdate prompt '일자를 입력하세요 : '
accept vshop prompt '업종을 입력하세요 : '

declare
    vdate       varchar2(8) := '&vdate';
    vshop       varchar2(100) := '&vshop';
    vyesterday  number;
    vtoday      number;
begin
    select sum(통화건수) into vtoday 
    from SCOTT.DELIVERY
    group by 일자, 업종
    having 일자 = vdate
    and 업종 = vshop;
    
    select sum(통화건수) into vyesterday 
    from SCOTT.DELIVERY
    group by 일자, 업종
    having 일자 = vdate-1
    and 업종 = vshop;   
    
    dbms_output.put_line('yesterday : '||vyesterday||', today : '||vtoday||', increase% : '||(vyesterday-vtoday)/vyesterday*100||'%');
end;
/

-- 그러면
SQL> @ex12.sql
일자를 입력하세요 : 20180202
업종을 입력하세요 : 피자
yesterday : 5733, today : 6411, increase% :
-11.82626896912611198325484039769754055468%

-- 강사님 코드 증가율을 lag과 윈도우 함수를 이용해서 한 쿼리로 구해버림.
-- 한 쿼리로 하는게 데이터베이스에 한 번만 접근하면 돼서 성능이 더 좋음.
select 증가율
from (select 일자, round((sum(통화건수) - lag(sum(통화건수)) over(order by 일자)) / lag(sum(통화건수)) over(order by 일자) * 100, 2) as 증가율
      from SCOTT.DELIVERY
      where 업종 = '음식점-중국음식'
      group by 일자)
where 일자 = '20180205';

accept vdate prompt '일자를 입력하세요 : '
accept vname prompt '업종을 입력하세요 : '

declare
    -- 여기서 ' 안 썼는데 그러면 실행할 때 accept 부분에서 직접 ' 넣어서 입력시켜야됨.
    -- ex) '피자', '20180205'
    vdate   varchar2(8) := &vdate;
    vname   varchar2(100) := &vname;
    vrate   number;
begin
    select 증가율 into vrate
    from (select 일자, round((sum(통화건수) - lag(sum(통화건수)) over(order by 일자)) / lag(sum(통화건수)) over(order by 일자) * 100, 2) as 증가율
          from SCOTT.DELIVERY
          where 업종 = vname
          group by 일자)
    where 일자 = vdate;
    
    dbms_output.put_line(vname||'의 '||vdate||'날의 이전일자 대비 통화건수 증가율 : '||vrate);
end;
/