[SQL] PL/SQL – PROCEDURE & FUNCTION

o  PROCEDURE & FUNCTION은 PL/SQL 구문을 저장하여 다음에 사용할 수 있도록 한다.

    PROCEDURE : 반환값이 없다. 매개변수가 있다. (in/out)

    FUNCTION  : 반환값이 있다. 매개변수가 있다. (in/out)

 

PROCEDURE

o  PL/SQL문을 저장하는 객체로 필요할 때마다 복잡한 구문을 다시 입력할 필요 없이 간단하게 호출해서 실행 결과를 얻을 수 있다.

CREATE TABLE PRO_TEST AS SELECT * FROM EMPLOYEE;  SELECT * FROM PRO_TEST;   -- 데이터를 삭제하는 프로시져 만들기 CREATE OR REPLACE PROCEDURE DEL_DATA IS BEGIN   -- DECLARE없이 지역변수 선언     DELETE FROM PRO_TEST     COMMIT; END; /   --프로시져 호출하기 (EXEC) EXEC DEL_DATA; SELECT * FROM PRO_TEST;  -- 등록된 프로시져 조회하기 SELECT * FROM USER_PROCEDURES;

 

o  IN/OUT 매개변수 있는 프로시저

    IN : 프로시져 실행시 필요한 값을 받는 변수(일반 매개변수 개념)

    OUT : 프로시져가 실행한 결과를 되돌려주는 변수

CREATE OR REPLACE PROCEDURE PRO_SELECT_EMP(V_EMP IN EMPLOYEE.EMP_ID%TYPE,                             V_EMP_NAME OUT EMPLOYEE.EMP_NAME%TYPE,                             V_SALARY OUT EMPLOYEE.SALARY%TYPE,                             V_BONUS OUT EMPLOYEE.BONUS%TYPE) IS BEGIN     SELECT EMP_NAME,SALARY,BONUS     INTO V_EMP_NAME,V_SALARY,V_BONUS     FROM EMPLOYEE     WHERE EMP_ID=V_EMP; END; /   --OUT매개변수가 있는 프로시져는 선언된 변수를 바인딩 해줘야 한다. -- 바인드 변수선언 VAR EMP_NAME VARCHAR2(20); VAR SALARY NUMBER; VAR BONUS NUMBER;   -- 매개변수 있는 프로시져 호출하기 EXEC PRO_SELECT_EMP(200,:EMP_NAME,:SALARY,:BONUS); PRINT EMP_NAME; PRINT SALARY; PRINT BOUNS;

  

FUNCTION

o  프로시저와 거의 유사한 용도로 사용하지만 실행 결과를 되돌려 받을 수 있다는 점에서 프로시저와 다르다.

CREATE OR REPLACE FUNCTION MYFUNC(STR VARCHAR2) RETURN VARCHAR2   --전달받은 변수 STR에 문자열을 추가하여 반환 IS     RESULT VARCHAR2(10000); BEGIN     RESULT:='^.'||STR||'.^';     RETURN RESULT; END; /   SELECT MYFUNC('KKK')FROM DUAL;

 

EMP_ID를 전달받아서 연봉을 계산해 출력하는 함수   CREATE OR REPLACE FUNCTION CALC_SALARY(V_EMP_ID EMPLOYEE.EMP_ID%TYPE) RETURN NUMBER IS     E EMPLOYEE%ROWTYPE;     RESULT NUMBER; BEGIN     SELECT *     INTO E     FROM EMPLOYEE     WHERE EMP_ID=V_EMP_ID;     RESULT:=(E.SALARY+E.SALARY*NVL(E.BONUS,0))*12;     RETURN RESULT; END; / SELECT EMP_ID, EMP_NAME,SALARY,CALC_SALARY(EMP_ID) FROM EMPLOYEE;