저장 서브프로그램

 앞선 포스트에서 트리거를 제외한 대부분의 소스는 서버에 저장되지 않는 프로그램들이였습니다. 저장되지 않은 프로그램들은 일회적으로 한번만 수행이 되며, 저장이 되지 않았기  떄문에 다른 사용자와 프로그램을 공유하거나 재사용할 수가 없습니다.  반면 저장 서브프로그램은 데이터베이스 서버에 저장 되며 다른 사용자에 의해 중복 호출하여 사용할 수 있기 때문에 동일 사용자 소스코드를 사용자가 여러번 사용하거나 공유 할수 있습니다.

저장 서브프로그램을 사용하는 이유

1. 재사용성 : 한번 작성되어 서버에 저장하면 소스코드를 재 호출하여 사용할 수 있기 떄문에 생산성을 증대한다.
2. 유지보수성 : 이런 프로그램의 작성은 중앙 서버에서만 이루어 지기 때문에 유지보수가 용이해진다
3. 모듈화 : 덩치 큰 프로그램을 통제 가능한 모듈로 분할하여 관리가 가능하다
4. 표준화 : 프로그래머가 서브프로그램을 작성한 후 해당 프로그램에 대한 명세만 배포하면 어떤 사용자라도 이 해당  서브 프로그램을 실해 시에 같은 결과를 얻을 수 있다.
5. 성능 : 반복적인 디비 호출이 일어날 경우 이를 서브프로그램으로 작성하면 모든 작업이 서버 안에서 이루어지 지기 때문에 더 나은 성능을 얻을 수 있다.

저장함수와 프로시저

 이전 포스트에 다루었던 트리거도 저장 서브 프로그램에 포함이 되며, 그 외에는 저장함수와 , 프로시저 , 패키지 , 객체 타입등이  존재합니다. 패키지는 별도의 포스트로 다룰 예정이며 트리거는 아래를 참고 하시면 됩니다.

dkkim2318.tistory.com/83 

트리거

 여기서 저장함수와 프로시저는 독립형 저장 서브 프로그램이라고 일컸는데. 이 둘의 차이는 반환값에 존재합니다.  저장함수와 같은 경우는 결과값을 반환하며 프로시저는 결과값을 반환하지 않습니다.  예제를 통해 저장함수와 프로시저에 대해 알아보겠습니다.

CREATE OR REPLACE FUNCTION get_wage(a_empno number)
return number    --  저장함수는 리턴 형식 지정 
is 
    v_wage number;
begin
    select sal + nvl (comm, 0) comm
     into v_wage
     from emp
     where empno = a_empno; 
    return v_wage ; /*급여를 계산하여 반환*/
end;

 먼저 저장함수 입니다 해당 예제 에서는 get_wage라는 함수를 만들고 get_wage는 sal 값을 변경하여 그 값을 반환합니다. 이렇게 서버에 저장된 함수는 SQL 문에서 사용하거나  pl/sql 에서 사용할 수 있습니다.

 첫번째 로는 SQL문에서의 사용입니다 해당 SQL 코드에서는 get_wage 함수를 select 문에서 사용하여 함수의  결과를 출력했습니다 우리가 흔히 많이 쓰는 decode문이나 nvl 같은 함수들을 사용하는 것과 같은 원리로 생각할 수 있습니다.

 두번째로는 PL/SQL에서 사용하는 예입니다.  해당 PL/SQL에서는 이전애 서버애 저장했던 get_wage를 활용하는 것을 확인 할 수 있습니다.

 그 다음으로는 저장 프로시저 입니다. 프로시저는 함수와 달리 반환값이 존재하지 않습니다. 프로시저는 저장함수와 빗스하지만 결론적으로는 특정 로직만을 처리하는 서브프로그램입니다. 이러한 특성을 이유로 주로 프로시저는 오라클 스케줄러나 배치 프로그램 같이 데이터를 처리하는 프로그램에 유용하게 사용됩니다

create or replace procedure  raise_salary(a_empno number, a_amt number)
is
begin
    if a_amt is not null
       then
            update emp
                set sal  =sal +a_amt
            where empno = a_empno;
    end if;
end;

프로시저는 호출하는 방법은 PL/SQL에서는 저장함수와 동일하게 이름으로 호출이 가능합니다 그 외 일반 SQL문이나 ibatis/mybatis 와 같은 동적 쿼리 xml 매핑에서는 CALL 또는  EXECUTE 또는 EXEC를 통해 해당 프로시저를 수행 할 수 있습니다.

call RAISE_SALARY(7788,100);
execute RAISE_SALARY(7788,100);
exec RAISE_SALARY(7788,100);