IN과 OUT 매개변수

#핵심 요약

IN 매개변수는 호출하는 쪽이 값을 넘김
DEFAULT 10처럼 기본값을 주면 생략할 수 있음

p_pct => 5, p_empno => 7782처럼 이름으로 넘기면 순서를 바꿔도 됨
이행 때 애플리케이션이 어떤 방식으로 부르는지 확인해야 하는 이유임
실습 비교: PostgreSQL은 이름 지정 호출을 지원하고 MySQL은 없음

UPDATE가 0건이어도 Oracle은 오류를 내지 않음
SQL%ROWCOUNT로 건수를 확인해 0이면 RAISE_APPLICATION_ERROR로 오류를 냄
사용자 오류 번호는 -20000 ~ -20999 범위를 씀

OUT 매개변수는 프로시저가 호출한 쪽으로 값을 돌려줌
SQL*Plus에서는 VARIABLE로 만든 바인드 변수(:v_cnt)로 받음

실습 p_raise_sal에는 COMMIT이 없어 확정은 부르는 쪽이 함
소스의 add_order는 안에서 COMMIT함
이 차이는 이행 후 트랜잭션 동작을 바꿈

#개념과 동작 원리

설명: COUNT, SUM 같은 집계 함수는 대상 행이 없어도 결과 1행을 돌려주므로, 아래 p_dept_stat의 SELECT ... INTO는 해당 부서가 없어도 NO_DATA_FOUND가 나지 않음(GROUP BY가 없는 경우)
집계 함수의 동작은 그룹 함수에 있음

#필수 명령어 또는 SQL

#IN 매개변수, DML, 사용자 오류

CREATE OR REPLACE PROCEDURE p_raise_sal
( p_empno IN NUMBER,
  p_pct   IN NUMBER DEFAULT 10 )
IS
BEGIN
  UPDATE emp_t
     SET sal = ROUND(sal * (1 + p_pct / 100))
   WHERE empno = p_empno;

  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20601, p_empno || '번 사원이 없습니다.');
  END IF;

  DBMS_OUTPUT.PUT_LINE(p_empno || '번 사원 급여 ' || p_pct || '% 인상');
END;
/

EXEC p_raise_sal(7900)                        -- 결과: 950 → 1045 (기본값 10%)
EXEC p_raise_sal(7499, 20)                    -- 결과: 1600 → 1920
EXEC p_raise_sal(p_pct => 5, p_empno => 7782) -- 결과: 2450 → 2573 (이름 지정)
EXEC p_raise_sal(9999)                        -- 결과: ORA-20601
COMMIT;

결과 해석: 2450 × 1.05 = 2572.5가 ROUND로 2573이 됨(산술 확인)
없는 사원번호는 UPDATE 0건이라 ORA-20601이 남

연습 문제 1의 답은 사원번호 대신 이름(p_ename IN emp_m.ename%type)을 받고 WHERE ename = p_ename으로 바꿔 EXEC p_raise_sal('KING')으로 호출하는 것이었음
실습 답의 오류 메시지는 이름을 받으면서도 "번 사원이 없습니다"를 그대로 써 문장이 맞지 않음

#OUT 매개변수

CREATE OR REPLACE PROCEDURE p_dept_stat
( p_deptno IN  NUMBER,
  p_cnt    OUT NUMBER,
  p_sum    OUT NUMBER )
IS
BEGIN
  SELECT COUNT(*), NVL(SUM(sal), 0)
    INTO p_cnt, p_sum
    FROM emp_t
   WHERE deptno = p_deptno;
END;
/

VARIABLE v_cnt NUMBER
VARIABLE v_sum NUMBER
EXEC p_dept_stat(20, :v_cnt, :v_sum)
PRINT v_cnt v_sum                       -- 결과: 2, 5975

결과 해석: 2와 5975는 실습 테이블 데이터 기준이며 환경마다 다름
연습 문제 2(같은 프로시저를 최소, 최대 급여를 돌려주도록 바꾸기)의 답은 기재 없음
실습에서는 앞선 예제에서 OUT 값을 익명 블록의 변수로 받는 방식도 다뤘다고 적었으나 그 예제는 이 페이지에 없음

#사용 조건과 주의사항

RAISE_APPLICATION_ERROR로 오류가 나면 프로시저 안의 UPDATE 결과가 어떻게 되는지(문장 단위 롤백 범위)는 기재 없음