SQL - PROCEDURE / FUNCTION

PROCEDURE

• PL/SQL문을 저장하는 객체
• 필요할 때마다 호출하여 사용
• 결과값을 반환하지 않고 로직 처리만 가능

CREATE  OR REPLACE PROCEDURE 프로시저명
(
    매개변수명1 [IN | OUT | INOUT] 데이터 타입 [:= DEFAULT 값],
    매개변수명1 [IN | OUT | INOUT] 데이터 타입 [:= DEFAULT 값],
    ...
)
IS(선언부) 
    ...
BEGIN(실행부)
    ...
EXCEPTION(예외처리부)
    ...
END [프로시저명];
/

실행

EXECUTE 프로시저명(매개값1, 매개값2, ...);
EXEC 프로시저명(매개값1, 매개값2, ...);

---

수업 시간에 했던 예제.

-- 테스트용 테이블 생성
CREATE TABLE EMP_DUP
AS SELECT * FROM EMPLOYEE;

SELECT * FROM EMP_DUP;

-- 테이블의 데이터를 모두 삭제하는 프로시저 작성
CREATE OR REPLACE PROCEDURE DEL_ALL_EMP
IS    -- 선언부
BEGIN -- 실행부
    DELETE FROM EMP_DUP;
    
    COMMIT;
END;
/

-- 실행 필수
EXECUTE DEL_ALL_EMP;

-- 프로시저 작성 구문 라인 별로 저장된 것 확인
SELECT * FROM USER_SOURCE;


DROP TABLE EMP_DUP;
DROP PROCEDURE DEL_ALL_EMP;

테스트 용으로 테이블을 만들었다.
오라클 나름 쉬운 줄 알았는데 뒤로 갈수록 개념이 점점 복잡한 것들이 등장…
수업 땐 이해했다고 생각했지만.. 혼자 하려면 절대 안 되는 것…

매개변수가 있는 프로시저

• 프로시저 실행 시 매개변수로 값 전달

-- 
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;
    
    -- COMMIT 생략
END;
/

-- 실행 필수
EXECUTE DEL_EMP_ID('&사번');

이번엔 사번을 매개변수로 받아서 그 사번의 데이터들만 출력했다.
P_EMP_ID EMPLOYEE.EMP_ID%TYPE 이 부분 확인.

---

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

• IN : 프로시저 내부에서 사용
• OUT : 프로시저 호출 시 외부에서 사용

---

FUNCTION

• 프로시저와 유사한 용도
• 실행결과를 되돌려 받을 수 있다는 점이 프로시저와 상이
  • RETURN 키워드 사용

CREATE OR REPLACE FUNCTION 함수명
(
    매개변수1 타입,
    매개변수2 타입,
    ...
)
RETURN 데이터타입
IS
    ...
BEGIN
    ...
    RETURN 반환값;
EXCEPTION
    ...
END [함수명];
/

CREATE OR REPLACE FUNCTION BONUS_CALC
(
    V_EMP_ID EMPLOYEE.EMP_ID%TYPE -- 매개변수
)

RETURN NUMBER

IS
    V_SAL EMPLOYEE.SALARY%TYPE;
    V_BONUS EMPLOYEE.BONUS%TYPE;
    
BEGIN
    SELECT SALARY, NVL(BONUS, 0)
    INTO V_SAL, V_BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = V_EMP_ID;
    
    RETURN (V_SAL + (V_SAL * V_BONUS)) * 12 ;
END;
/

이번에는 사번을 입력 받아 해당 사원의
연봉(보너스 포함)을 계산하고 리턴하는 함수 생성했다.
작성한 걸 보면 맨 처음 오라클 배웠을 떄랑은 정말 많이 다르다.
있는 데이터를 조회만 했었는데
이제 원하는 정보를 입력하고 해당 데이터만을 가져올 수 있다.

---

cursor

• 다중 행의 조회 결과를 담고 있는 객체(단일 행일 때는 INTO)
• 조회 결과에 순차적으로 접근 가능
• 묵시적 / 명시적
• 속성
  • NOTFOUND : 커서 영역에 남아있는 행이 없을 경우 TRUE, 아니면 FALSE
  • FOUND : 커서 영역에 남아있는 행이 한 개 이상일 경우 TRUE, 아니면 FALSE
  • ISOPEN : 커서가 OPEN 상태인 경우 TRUE(묵시적 커서는 항상 FALSE)
  • ROWCOUNT : 조회된 행의 개수
  • 커서명%NOTFOUND
  • SQL%NOTFOUND(묵시적 커서)

---