PL/SQL - Function

Host Language: JAVA, C

poset language에서 정의한 변수 -> Host variable,  Bind variable

 

Named Block (=Stored Block)

 

• 이름이 없는 무명의 Block
• 중앙에 저장되는 공통 라이브러리 성격
• 저장 위치: DBMS내 Data Dictionary에 저장
• 실행방식: 실행시점에 해당 Block을 호출(Call)하여 실행
• 용도: 공용 Library Module, 개개의 Application내에 저장되는 것이 아니라 중앙의 DBMS내에 저장되어 1개의 Module을 다수의 Application이 호출하여 재사용하는 방식

 

Parameter mode

 

1. IN: Stored Block 외부에 있는 값을 Stored Block안으로 전달하는 Parameter Mode (ex. 함수 수행시의 입력값)

2. OUT: Stored Block 내부에서 있는 값을 Stored Block 외부에 전달하는 Parameter Mode (ex. 프로시져 수행 결과를 저장하여 전달하기 위한 용도)

3. IN OUT: IN과 OUT 2가지 역할 수행

 

IN	OUT	IN OUT
DEFAULT Mode	명시적으로 Mode 표기	명시적으로 Mode 표기
SUBPROGRAM 안으로 값을 전달할 때 사용	SUBPROGRAM 밖으로 값을 전달할 때 사용	IN과 OUT의 2가지 기능
상수 처럼 참조용으로 사용 (AS A CONSTANT)	값이 할당되어 있지 않는 변수 처럼 사용 (UNINITIALIZED VARIABLE)	값이 할당되어 있는 변수 처럼 사용 (INITIALIZED VARIABLE)

 

Function

 

-- DBMS 중앙에 함수 저장됨
CREATE OR REPLACE  FUNCTION  CALC_BONUS(P_SALARY IN  NUMBER,P_DEPTNO IN  NUMBER)
 RETURN  NUMBER 
 IS
        V_BONUS_RATE NUMBER := 0; 
        V_BONUS NUMBER(7,2) := 0; 
 BEGIN 
        -- Business Logic
        IF P_DEPTNO =  10 THEN
            V_BONUS_RATE :=  0.1;
        ELSIF  P_DEPTNO =  20 THEN
            V_BONUS_RATE :=  0.2;
        ELSIF  P_DEPTNO =  30 THEN
            V_BONUS_RATE :=  0.3;
        ELSE
            V_BONUS_RATE :=  0.05;
        END IF;
        -- V_BONUS  :=  ROUND(NVL(P_SALARY,0) * C_BONUS_RATE,2); 
        -- C_BONUS_RATE라는 변수는 정의되지 않은 변수
        -- 구문상의 오류를 일으켜 함수 생성시 Conpile time error 발생
        -- C_BONUS_RATE -> V_BONUS_RATE로 수정해야 됨
        V_BONUS  :=  ROUND(NVL(P_SALARY,0) *V_BONUS_RATE,2);
        RETURN    V_BONUS;
 END CALC_BONUS;
 /

 

<참고>

Stored Block 생성시 발생 에러는 USER_ERRORS라는 데이터 딕셔너리에 저장

1) SELECT * FROM USER_ERRORS

2) SHOW ERROR

 

*에러 위치는 개발 소스상의 위치가 아니라 SQL*PLUS or SQLDEV의 SQL BUFFER상의 위치임

 

 -- 함수의 구조 파악
 DESC CALC_BONUS
 
 -- 내 소유의 OBJECT 확인
 -- STATUS: VALID(사용 가능) / INVALID(사용 불가능) 
 SELECT OBJECT_NAME, OBJECT_TYPE, STATUS, CREATED FROM USER_OBJECTS
 WHERE OBJECT_NAME = 'CALC_BONUS'; -- 데이터 딕셔너리에 모든 오브젝트 이름은 대문자로 저장되기 때문에 소문자로 하면 결과 안 나옴
 
 -- Sroed Block에 저장된 Source 확인
 SELECT NAME, LINE, TEXT
 FROM USER_SOURCE
 WHERE NAME = 'CALC_BONUS';
 
 -- 함수 실행
 -- 사용자가 정의한 함수 실행
 SELECT EMPNO, ENAME, SAL, DEPTNO, CALC_BONUS(SAL, DEPTNO) FROM EMP;

 

<참고>

보너스 계산을 하는 방법이 부서번호를 기분으로 고정적으로 부여

 

1. 사용자 정의 함수의 남용에 주의

사용자 정의 함수를 남발하면 성능에 문제가 생길 수 있음

현업에서 성능 문제가 생기는 원인을 분석해보면 사용자 정의 함수의 남용으로 인해 발생하는 경우를 간혹 발견 CALC_BONUS 함수는 14번 실행. 만약 조회하는 대상 데이터가140만건이라면 함수 역시140만번 실행 1회 실행0.001 초 ➔ 0.001 * 140만번 ➔ 1400초, 함수 실행에만1400초 소요

 

2. 사용자 정의 함수는 공용 LIBRARY

개발자 개개인이 임의대로 만들어서 사용하는 것이 아니라.분석/설계 단계에서 부터 공통Module에 대한 Application 분석/설계에 고려되어 작성 해야 한다. 무분별한 개개인의 함수 생성은 개발 효율성 및 유지보수에 비효율적인 결과발생