33일차: Oracle – PL.SQL제어문/저장 프로시저/저장 함수 2021.07.29

목차

1.     PL/SQL 제어문

2.     저장 프로시저

3.     저장 함수

 

 

1. PL/SQL 제어문 <20장>                 → ★2021_07_29_01 참고

 

1) 조건문(= 선택문)

- 기본적으로 모든 문장들은 나열된 순서대로 순차적으로 수행된다. 하지만 경우에 따라서 문장의 흐름을 변경할 필요가 있고, 이때 사용하는 것이 IF문이다.

- IF문은 조건을 제시해서 만족하느냐 하지 않느냐에 따라 문장을 선택적으로 수행하기 때문에 선택문이라고도 한다.

- 오라클에서는 3가지 형태의 조건문이 있다.

 

① IF ~ THEN ~ END IF

- 형식: IF 조건 THEN

       조건을 만족하면 실행될 처리문

       END IF;

 

- 예제)

Q. SCOTT 사원의 부서 번호를 검색해서 부서명을 출력하는 PL/SQL 작성

SET SERVEROUTPUT ON

declare                             → 선언부

    vempno number(4);

    vename varchar2(20);

    vdeptno emp.deptno%type;

    vdname varchar2(20) := null;

begin                               → 실행부

    select empno, ename, deptno into vempno, vename, vdeptno from emp

        where ename='SCOTT';

       

        if vdeptno = 10 then

           vdname := 'ACCOUNTING';

        end if;

   

        if vdeptno = 20 then

           vdname := 'RESEARCH';

        end if;

 

        if vdeptno = 30 then

           vdname := 'SALES';

        end if;

       

        if vdeptno = 40 then

           vdname := 'OPERATIONS';

        end if;

       

        DBMS_OUTPUT.PUT_LINE('사번 / 이름 / 부서명');

        DBMS_OUTPUT.PUT_LINE(vempno || ' / ' || vename || ' / ' || vdname); 

end;

 

② IF ~ THEN ~ ELSE ~ END IF

- 형식: IF 조건 THEN

         조건을 만족하면 실행될 처리문

         ELSE

         조건을 만족하지 않으면 실행될 처리문

         END IF;

- 예제)

Q. 사원 테이블에서 SCOTT 사원의 연봉을 구하는 PL/SQL 작성

SET SERVEROUTPUT ON

declare

    vemp emp%rowtype;            → 레퍼런스 변수

    annsal number(7,2);              → 스칼라 변수

begin

    select * into vemp from emp where ename='SCOTT';

   

    if vemp.comm is null then

        annsal := vemp.sal * 12;

    else

        annsal := vemp.sal * 12 + vemp.comm;

    end if;

   

    DBMS_OUTPUT.PUT_LINE('사번 / 이름 / 연봉');

    DBMS_OUTPUT.PUT_LINE(vemp.empno || ' / ' || vemp.ename || ' / ' || annsal);

end;

 

③ IF ~ THEN ~ ELSIF ~ ELSE ~ END IF

- 형식: IF 조건 THEN

         조건을 만족하면 실행되는 문장

         ELSIF

         조건을 만족하지 않으면 실행될 처리문 1

         ELSE

         조건을 만족하지 않으면 실행될 처리문 2

         END IF;

- 예제)

SET SERVEROUTPUT ON

declare

    vemp emp%rowtype;

    vdname varchar2(14);

begin

    select * into vemp from emp where ename='SCOTT';

 

    if vemp.deptno = 10 then

       vdname := 'ACCOUNTING';

    elsif vemp.deptno = 20 then

          vdname := 'RESEARCH';

    elsif vemp.deptno = 30 then

          vdname := 'SALES';

    elsif vemp.deptno = 40 then

          vdname := 'OPERATIONS';

    end if;

 

    DBMS_OUTPUT.PUT_LINE('사번 / 이름 / 부서명');

    DBMS_OUTPUT.PUT_LINE(vemp.empno || ' / ' || vemp.ename || ' / ' || vdname);

end;

 

2) 반복문

- SQL 문을 반복적으로 여러 번 실행하고자 할 때 사용

- 반복문에는 basic loop문, for loop문, while loop문이 있고, exit으로 반복문을 종료한다.

 

① BASIC LOOP문

- 조건 없이 반복 작업을 제공

- 형식: loop

         반복 실행할 처리문;

         end loop;

 

- 예제 1)

Q. 1 ~ 5까지 출력

SET SERVEROUTPUT ON

declare

    n number := 1;      → 변수의 초기값 1

begin

    loop

        DBMS_OUTPUT.PUT_LINE (n);

        n := n + 1;

        if n > 5 then

            exit;

        end if;

    end loop;

end;

 

- 예제 2)

Q. 1부터 10까지 합을 구하는 프로그램 작성

SET SERVEROUTPUT ON

declare

    n number := 1;      → 루프를 돌릴 변수

    s number := 0;      → 합이 누적될 변수

begin

    loop

        s := s + n;

        n := n + 1;

       

        if n > 10 then

            exit;

        end if;

    end loop;

    DBMS_OUTPUT.PUT_LINE('1부터 10까지의 합:'||s);

end;

 

② FOR LOOP문

- COUNT를 기본으로 작업의 반복 제어를 제공

- 형식: for 증감변수 in[reverse] 초기값..최종값 loop

          반복 실행될 처리문

          end loop;

- 예제 1)

Q. For Loop문으로 1부터 5까지 출력

SET SERVEROUTPUT ON

begin

    for n in 1..5 loop      → 자동으로 1씩 증가

        DBMS_OUTPUT.PUT_LINE (n);

    end loop;

end;

 

- 예제 2)

Q. For Loop문으로 5부터 1까지 출력

SET SERVEROUTPUT ON

begin

    for n in reverse 1..5 loop      → 자동으로 1씩 감소

        DBMS_OUTPUT.PUT_LINE (n);

    end loop;

end;

 

- 예제 3)

Q. For Loop문을 이용해서 부서 테이블(DEPT)의 모든 정보를 출력하는 PL/SQL문 작성

SET SERVEROUTPUT ON

declare

    vdept dept%rowtype;

begin

    DBMS_OUTPUT.PUT_LINE('부서 번호 / 부서명 / 지역명');

    for cnt in 1..4 loop

        select * into vdept from dept where deptno = 10 * cnt;    

        DBMS_OUTPUT.put_line(vdept.deptno||' / '||vdept.dname||' / '||vdept.loc);

    end loop;

end;

 

③ WHILE LOOP 문

- 조건을 기본으로 작업의 반복 제어를 제공

- 형식: while 조건 loop

       반복 실행할 처리문

       end loop;

- 예제 1)

Q. while loop문으로 1부터 5까지 출력

SET SERVEROUTPUT ON

declare

    n number := 1;

begin

    while n<=5 loop

        DBMS_OUTPUT.PUT_LINE(n);

        n := n + 1;

    end loop;

end;

 

- 예제 2)

Q. while loop문을 별(*)을 삼각형 모양으로 출력

SET SERVEROUTPUT ON

declare

    c number := 1;

    str varchar2(100) := null;

begin

    while c<=5 loop

    str := str||'*';

    DBMS_OUTPUT.PUT_LINE(str);

    c := c + 1;

    end loop;

end;

 

2. 저장 프로시저 <21장>                  → ★2021_07_29_02 참고

 

1) 저장 프로시저

- 사용자가 만든 PL/SQL문을 데이터베이스에 저장해놓고 필요한 경우 호출하여 사용할 수 있는 명령어

- 저장 프로시저를 사용하면 복잡한 DML 문들 필요할 때마다 다시 입력할 필요 없이 간단하게 호출만 해서 복잡한 DML 문의 실행 결과를 얻을 수 있다.

- [mode]는 IN과 OUT, INOUT 세 가지를 기술할 수 있는데 IN 데이터를 전달받을 때 쓰고 OUT은 수행된 결과를 받아갈 때 사용한다. INOUT은 두 가지 목적에 모두 사용되지만 잘 쓰이지 않는다.

 

- 형식: CREATE [OR REPLACE ] PROCEDURE 프로시저명 (

매개변수1 [mode] 자료형,   매개변수2 [mode] 자료형 . . . )

IS

지역변수 선언

BEGIN

실행문1;

실행문2;

. . .

END;

 

① 매개변수가 없는 프로시저

1. 저장 프로시저 생성

create or replace procedure del_all

is

begin

    delete from emp01;

end;

 

2. 프로시저 목록 확인

select * from user_source;

 

3. 프로시저 실행

execute del_all;      

 

4. 프로시저 실행 결과 확인

select * from emp01;                    → 프로시저에 의해서 데이터가 모두 삭제됨

 

② 매가변수가 있는 프로시저

- IN: 데이터를 전달받을 때 사용

- OUT: 수행된 결과를 받아갈 때 사용

- INOUT: 두 가지 목적에 모두 사용

 

• 매개변수가 IN인 프로시저

1. 매개변수가 있는 프로시저 생성

create or replace procedure del_ename(vename in emp01.ename%type)

                                                               → 기본값이 in이기에 생략 가능

is

begin

    delete from emp01 where ename=vename;

end;

 

2. 프로시저 목록 확인

select * from user_source;

 

3. 프로시저 실행

select * from emp01;                             → 삭제 전 데이터 확인

execute del_ename('SCOTT');                   → 매개변수 전달하여 프로시저 실행

execute del_ename('KING');

execute del_ename('SMITH');

 

• 매개변수가 IN과 OUT인 프로시저

- in : 매개변수로 값을 받는 역할

- out : 매개변수로 값을 돌려주는 역할

 

1. 프로시저 생성

- 사원 번호(empno)를 프로시저의 매개변수로 전달받아서, 그 사원의 사원명, 직책, 급여를 구하는 프로시저 생성

create or replace procedure sal_empno(

        vempno in emp.empno%type,

        vename out emp.ename%type,

        vjob out emp.job%type,

        vsal out emp.sal%type)

is

begin

    select ename, sal, job into vename, vsal, vjob from emp where empno = vempno;

end;

 

2. 프로시저 목록 확인

select * from user_source;

 

3. 바인드 변수 : 프로시저를 실행했을 때 결과를 돌려받는 변수

variable var_ename varchar2(12);

variable var_job varchar2(10);

variable var_sal number;

 

4. 프로시저 실행

execute sal_empno(7788, :var_ename, :var_job, :var_sal);

execute sal_empno(7839, :var_ename, :var_job, :var_sal);

 

5. 바인드 변수로 받은 값 출력

print var_ename;

print var_job;

print var_sal;

 

3. 저장 함수 <21장>              → ★2021_07_29_02 참고

 

- 저장 프로시저와 유사한 기능을 수행하지만, 실행 결과를 돌려주는 역할을 한다.

- 형식: CREATE [OR REPLACE ] FUNCTION 함수명

         ( 매개변수1 [mode] 자료형,

           매개변수2 [mode] 자료형. . .

         )

                RETURN 자료형

        IS

               반환변수명 자료형;

       BEGIN

               실행문1;

               실행문2;

               RETURN 반환변수명;

       END;

 

- 예제)

Q1. 사원 테이블에서 특정 사원의 급여를 200% 인상한 결과를 돌려주는 저장 함수 생성

1. 저장 함수 생성

create or replace function cal_bonus(vempno in emp.empno%type)

    return number

is

    vsal number(7,2);

begin

    select sal into vsal from emp where empno = vempno;

    return vsal * 2;

end;

 

2. 저장 함수 목록 확인

select * from user_source;

 

3. 바인드 변수 생성

variable var_res number;

 

4. 저장 함수 실행

execute :var_res := cal_bonus(7788);

execute :var_res := cal_bonus(7900);

 

5. 출력

print var_res;

 

- 저장 함수를 SQL문에 포함해서 실행

select sal, cal_bonus(7788) from emp where empno = 7788;

select sal, cal_bonus(7900) from emp where empno = 7900;