PL/SQL ; Oracle Subprogram(Procedure, Function)

참고자료 : 서진수강사님 강의자료 PL/SQL 부분
참고pdf :  http://docs.oracle.com/cd/B19306_01/server.102/b14200.pdf  ; Oracle 10g book
참고사이트
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#LNPLS008  ; Oracle Doc(Subprogram)
http://www.oracle.com/pls/db102/search?word=procedure&partno= ; Oracle Doc(procedure)
http://www.oracle.com/pls/db102/search?word=function&partno=  ; Oracle Doc(Function)
http://psoug.org/browse.htm?cid=4 ; Oracle PL/SQL reference site

아래 예시에 쓰인 테이블 export파일(exp, 10.2.0.1, scott/oracle)

example.dmp

Oracle Subprogram
(참고; 생성시나 변경시 컴파일 에러(Warning: Function created with compilation errors.) 가 발생한다면,
show error 명령어로 어떤 에러가 났는지 살펴볼 수 있습니다.)

1. Procedure
특정처리를 실행하는 서브프로그램 중 하나로, 단독으로 실행되거나,
다른프로시저 또는 다른 툴, 또는 다른 환경등에서 호출되어 실행될 수 있습니다.
오브젝트로서 데이터베이스 내에 저장되여, 실행될때는 별도의 컴파일없이 바로 실행됩니다.
DDL문(Data Definition Language)으로 처리됩니다.


1.1 생성방법
CREATE OR REPLACE PROCEDURE procedure_name
[(parameter1 [mode1] datatype,
  parameter2 [mode2] datatype, ...)]
IS | AS
PL/SQL block;

1.1.1 parameter
파라미터를 선언할 때는 데이터형만을 기술한다(데이터의 크기는 기술하지 않음).
선언 시 DEFAULT 키워드를 사용하여 기본값을 사용할 수 있다.

1.1.2 mode
IN ; 호출환경으로부터 값을 전달받는 매개변수로 설정하며, IN모드로 설정된 매개변수는 프로시저 내에서는 읽기전용 지역변수처럼 취급.
   IN은 기본값으로 생략하면 자동으로 IN이 됨.
OUT ; 프로시저에서 호출환경으로 값을 전달하는 매개변수로 설정.
   이 모드로 설정되면 프로시저내에서는 읽을 수 없고, 값을 저장하기만 하는 지역변수처럼 사용됨.
   호출환경에서는 이 매개변수로부터 값을 전달받기 위해 환경변수가 선언되어야 한다.
IN OUT ; 이 모드로 설정되면, 호출환경과 프로시저간에 값을 주고받는 지역변수로 사용됨. 읽기 쓰기 가능

IN : 호출할 때 값을 부여 ---> 프로시저로 전달 (읽기전용변수)
OUT : 프로시저에서 계산된 값 ---> 호출환경으로 전달 (값을 저장만 가능)
IN OUT : 호출환경 <---> 프로시저 (읽기 쓰기가능)

1.3 생성된 procedure보는 법 및 생성된 procedure의 source보는법
; user_procedures, user_source이용

SQL> select object_name from user_procedures;

OBJECT_NAME
------------------------------
EMP_UPDATE_20

SQL> select name, text from user_source
  2  where type='PROCEDURE';

NAME            TEXT
--------------- --------------------------------------------------------------------------------
EMP_UPDATE_20   procedure emp_update_20
EMP_UPDATE_20   is
EMP_UPDATE_20   begin
EMP_UPDATE_20     update emp
EMP_UPDATE_20     set job='CLERK'
EMP_UPDATE_20     where deptno=20;
EMP_UPDATE_20     dbms_output.put_line(SQL%ROWCOUNT||' rows updated');
EMP_UPDATE_20   end;

8 rows selected

실습1. 부서번호가 20 인 사람들의 job을 'CLERK'로 변경하는 프로시저

SQL> create or replace procedure emp_update_20
is
begin
  update emp
  set job='CLERK'
  where deptno=20;
  dbms_output.put_line(SQL%ROWCOUNT||' rows updated');
end;

SQL> exec emp_update_20
5 rows updated

PL/SQL procedure successfully completed.

실습2. 사번을 입력 받아 급여를 10% 인상하는 프로시져(up_salary)

SQL> create or replace procedure up_salary(vempno emp.empno%type)
is
begin
  update emp set sal=sal*1.1
  where empno=vempno;
  dbms_output.put_line(SQL%ROWCOUNT||' updated.');
end;
/
Procedure created.

SQL> exec up_salary(7499);
1 updated.

PL/SQL procedure successfully completed.

SQL> rollback;

Rollback complete.

SQL> exec up_salary(8000);
0 updated.

PL/SQL procedure successfully completed.

실습 3
emp 테이블을 사용하여 사원번호와, 부서번호를 입력 받아서 보너스(comm)를
아래의 조건으로 계산하는 프로시저를 작성하세요
* 조건
1. Deptno = 10 이면 급여의 20%
2. Deptno = 20 이면 급여의 30%
3. Deptno = 30 이면 급여의 10%
4. 나머지는 0%

SQL> create or replace procedure cal_comm
(vempno emp.empno%type)
is
vdeptno emp.deptno%type;
begin
  select deptno into vdeptno from emp
  where empno=vempno;
  
  dbms_output.put_line('Calculating commission');
  update emp
  set comm=sal * decode(vdeptno, 10, 0.2,
                                 20, 0.3,
                                 30, 0.1,
                                 0)
  where empno=vempno and deptno=vdeptno;
  dbms_output.put_line(SQL%ROWCOUNT||' rows updated.');
end;
/
Procedure created.

실습3 결과확인

SQL> select empno, sal, comm, deptno from emp
  2  where empno in(7369, 7499, 7934
  3  );

     EMPNO        SAL       COMM     DEPTNO
---------- ---------- ---------- ----------
      7369        800                    20
      7499       1600        300         30
      7934       1300                    10

SQL> exec cal_comm(7369);
Calculating commission
1 rows updated.

PL/SQL procedure successfully completed.

SQL> exec cal_comm(7499);
Calculating commission
1 rows updated.

PL/SQL procedure successfully completed.

SQL> exec cal_comm(7934);
Calculating commission
1 rows updated.

PL/SQL procedure successfully completed.

SQL> select empno, sal, comm, deptno from emp
  2  where empno in(7369, 7499, 7934);

     EMPNO        SAL       COMM     DEPTNO
---------- ---------- ---------- ----------
      7369        800        240         20
      7499       1600        160         30
      7934       1300        260         10

2. Function
서브프로그램 중의 하나로, 데이터베이스내에 오브젝트로서 저장됩니다.
함수는 반드시 하나의 값을 리턴해야 합니다..
즉 함수는 실행 후 리턴데이터 유형에 맞는 값으로 대치되기 때문에, 
호출하고자 한다면 반드시 수식의 일부로서 실행됩니다.
다른 프로시저나 함수, 다른 툴, 다른 환경 등에서 호출되어 실행될 수 있습니다.

2.1 생성방법
CREATE OR REPLACE FUNCTION function_name
[(parameter1 [mode] datatype,
  parameter2 [mode] datatype, ...)]
RETURN datatype
IS | AS
PL/SQL block;

2.1.1 parameter ; 1.1.1 참조, DEFAULT도 사용가능

2.1.2 mode ; 1.1.2 참조
   주의해야 할 사항은, OUT, IN OUT 모드는 지양하는게 바람직합니다.(함수는 하나의 값만을 리턴하는 것이기 때문에)
   Function 에서 의 OUT/IN OUT 모드에 관한 설명
          http://bytes.com/topic/oracle/answers/510138-pl-sql-can-function-have-out-out-parameter < 참조하세요.

2.1.3 return
함수명으로 리턴할 데이터(값)의 형을 선언하는 절. 반드시 포함되어야 하며, 리턴되는 데이터는 return에서 선언한 datatype과 일치해야만 합니다.


실습1. 부서번호를 입력 받은 후 해당 부서의 인원수를 구해주는 함수 member_dept

SQL> create or replace function function member_dept
(f_deptno emp.deptno%type)
return number
is
  cnt number;
begin
  select count(*) into cnt from emp
  where deptno=f_deptno;
  return cnt;
end;
/

결과확인

SQL> select distinct deptno, member_dept(deptno) from emp;

    DEPTNO MEMBER_DEPT(DEPTNO)
---------- -------------------
        30                   6
        20                   5
        10                   3

2. 해당 사원의 급여와 세율을 입력 받아 세금을 구하는 함수 tax

SQL> create or replace function tax
(f_sal emp.sal%type, f_rate number)
return number
is
begin
  return f_sal * f_rate;
end;
/

결과확인

SQL> select empno, sal, tax(sal,0.25) from emp;

     EMPNO        SAL TAX(SAL,0.25)
---------- ---------- -------------
      7369        800           200
      7499       1600           400
      7521       1250         312.5
      7566       2975        743.75
      7654       1250         312.5
      7698       2850         712.5
      7782       2450         612.5
      7788       3000           750
      7839       5000          1250
      7844       1500           375
      7876       1100           275
      7900        950         237.5
      7902       3000           750
      7934       1300           325

14 rows selected.