16_PROCEDURE_FUNCTION

< PROCEDURE >

PL/SQL문을 저장하는 객체

필요할 때마다 구문을 다시 입력할 필요 없이 간단하게 호출해서 실행 결과를 얻을 수 있음

특정 로직을 처리하기만 하고 '결과값'을 반환하지 않는다.

 

1. 프로시저 생성 방법

 

[표현식]

CREATE OR REPLACE PROCEDURE 프로시저명

　(매개변수명1 [IN | OUT | IN OUT] 데이터타입[:= DEFAULT 값],

　(매개변수명2 [IN | OUT | IN OUT] 데이터타입[:= DEFAULT 값],

　...)

 

IS [AS]

　선언부

BEGIN

　실행부

[EXCEPTION

　예외처리부]

END [프로시저명];

/

 

2. 프로시저 실행 방법

 

[표현식]

EXECUTE(OR EXEC) 프로시저명;

 

#1_ 간단한 테스트

테스트용 테이블 생성

CREATE TABLE EMP_DUP
AS SELECT * FROM EMPLOYEE;

SELECT * FROM EMP_DUP;

 

#2_ 프로시저 생성

호출 시 EMP_DUP 테이블의 행을 모두 삭제하는 프로시저

CREATE OR REPLACE PROCEDURE DEL_ALL_EMP
IS
BEGIN
	DELETE FROM EMP_DUP;
	COMMIT;
END;
/

 

 

#3_ DEL_ALL_EMP 프로시저 호출

EXECUTE DEL_ALL_EMP;

SELECT * FROM EMP_DUP;

#4_ 프로시저를 관리하는 데이터 딕셔너리: 프로시저 작성 구문이 라인별로 구분되어 저장되어 있다.

SELECT * FROM USER_SOURCE;

 

1) 매개변수 있는 프로시저

프로시저 실행 시 매개변수로 인자 값을 전달해야한다.

 

- 매개변수 있는 프로시저 생성

CREATE OR REPLACE PROCEDURE DEL_EMP_ID
	(P_EMP_ID EMPLOYEE.EMP_ID%TYPE)
IS
BEGIN
	DELETE FROM EMPLOYEE
	WHERE EMP_ID = P_EMP_ID;
END;
/

 

- 프로시저 실행(단, 매개변수로 인자 값을 전달해주어야한다.)

EXECUTE DEL_EMP_ID; > 인자 값을 전달하지 않아서 에러가 발생한다.

EXEC DEL_EMP_ID('201');

SELECT * FROM EMPLOYEE;

 

- 사용자가 입력한 값도 전달이 가능하다.

EXEC DEL_EMP_ID('&사번');

SELECT * FROM EMPLOYEE;

 

ROLLBACK;

 

 

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

IN 매개변수: 프로시저 내부에서 사용될 변수

OUT 매개변수: 프로시저 호출부(외부)에서 사용될 변수

 

- IN/OUT 매개변수가 있는 프로시저를 생성

CREATE OR REPLACE PROCEDURE SELECT_EMP_ID
	(P_EMP_ID IN EMPLOYEE.EMP_ID%TYPE, > 매개변수로 인자 값을 전달받을 예정
	P_EMP_NAME OUT EMPLOYEE.EMP_NAME%TYPE,
	P_SALARY OUT EMPLOYEE.SALARY%TYPE,
	P_BONUS OUT EMPLOYEE.BONUS%TYPE)
IS
BEGIN
    SELECT EMP_NAME, SALARY, NVL(BONUS, 0)
    INTO P_EMP_NAME, P_SALARY, P_BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = P_EMP_ID;
END;
/

 

 

여기서 끝이 아니라 바인드 변수를 만들어 주어야 한다.

─────────────────

＊바인드 변수(VARIABLE OR VAR)

SQL 문장을 실행했을 때 SQL에 사용 값을 전달할 수 있는 통로 역할을 하는 변수

프로시저를 실행했을 때 조회 결과가 저장될 바인드 변수를 생성해야 오류가 발생하지 않는다.

─────────────────

VARIABLE VAR_EMP_NAME VARCHAR2(30);
VAR VAR_SALARY NUMBER; < VARIABLE을 VAR로 줄여서 써도 가능
VAR VAR_BONUS NUMBER;

> 스크립트 출력 없이 작업이 완료된다.

 

바인드 변수는 ':변수명' 형태로 참조 가능

EXEC SELECT_EMP_ID('&사번: ', :VAR_EMP_NAME, :VAR_SALARY, :VAR_BONUS);

< 실행은 잘 되었지만 조회된 결과가 제대로 변수에 담겼는지는 확인할 수 없다.

< PRINT를 사용해서 결과를 확인할 수 있다.

 

＊PRINT: 해당 변수의 내용을 출력해주는 명령어

PRINT VAR_EMP_NAME;
PRINT VAR_SALARY;
PRIN VAR_BONUS;

 

＊SET AUTOPRINT ON -> 기본 값은 OFF

성공적인 PL/SQL 블럭에서 사용되는 바인드 변수의 값을 자동으로 출력한다.

별도의 DBMS_OUTPUT.PUT_LINE() 없이 프로시저 호출문을 실행할 때 바로 PRINT한다.

SET AUTOPRINT ON;

EXEC SELECT_EMP_ID(&'사번: ', :VAR_EMP_NAME, :VAR_SALARY, :VAR_BONUS);

 

 

 

 

 

< FUNCTION >

프로시저와 사용 용도가 거의 비슷하지만 프로시저와 다르게 OUT 매개변수를 사용하지 않아도 실행 결과를 되돌려받을 수 있다.(RETURN)

 

[표현식]

CREATE OR REPLACE FUNCTION 함수명

　(매개변수1 타입,

　매개변수2 타입,

　...

　)

RETURN 데이터타입 << 프로시저와 다른 부분

IS [AS]

　선언부

BEGIN

　실행부

RETURN 반환값; << 프로시저와 다르게 RETURN 구문이 추가됨

[EXCEPTION

　예외처리부]

END [함수명];

/

 

 

- 함수 생성

#1_ 사번을 입력받아 해당 사원의 연봉을 계산하고 리턴하는 함수 생성

CREATE OR REPLACE FUNCTION BONUS_CALC
	(V_EMP_ID EMPLOYEE.EMP_ID%TYPE)
RETURN NUMBER < 연봉 계산이니까 NUMBER 타입 사용
IS < 프로시저의 DECLARE 부분이 여기서는 IS에 들어간다.
	V_SAL EMPLOYEE.SALARY%TYPE;
    V_BONUS EMPLOYEE.BONUS%TYPE;
    CALC_SAL NUMBER;
BEGIN
	SELECT SALARY ,NVL(BONUS, 0)
    INTO V_SAL, V_BONUS < 변수에 값을 담는다
    FROM EMPLOYEE
    WHERE EMP_ID = V_EMP_ID;
    
    CALC_SAL := (V_SAL + (V_SAL * V_BONUS) * 12;
    
    RETURN CALC_SAL;
END;
/

- 함수 결과를 반환받아 저장할 바인드 변수 선언

VAR VAR_CALC NUMBER; < 받아주기 위한 값(실행했을 때 출력 없음)

EXEC :VAR_CALC := BONUS_CALC('&사번');

 

- 함수는 RETURN 값이 있어서 SELECT 문에서도 사용이 가능하다. (EXEC 생략 가능)

SELECT EMP_ID, EMP_NAME, BONUS_CALC(EMP_ID)
FROM EMPLOYEE
WHERE BONUS_CALC(EMP_ID) > 3000000;

 

 

 

 

 

< PL / SQL 블록 (Anonymaous Block) >

이름없는 블록이라 불리며 간단한 Block 수행 시 사용된다.

객체 저장X, 값 반환X, 이름 지정X, 파라미터(매개변수) 사용X, 타 응용 프로그래밍 호출X

 

< 프로시저 (Prodedure >

지정된 특정 처리를 실행하는 서브 프로그램의 한 유형으로 단독으로 실행되거나 다른 프로시저나 툴 등에 호출되어 실행된다.

객체 저장O, 값 반환X, 이름 지정O, 파라미터(매개변수) 사용O, 타 응용 프로그래밍 호출O

 

< 함수 (Function) >

 프로시저와 수행되는 결과가 유사하지만 값 반환 여부의 차이가 있다.

객체 저장O, 값 반환O, 이름 지정O, 파라미터(매개변수) 사용O, 타 응용 프로그래밍 호출O