Oracle PL/SQL 프로시저 실행방법

BEGIN
    <proc_name>
    end;
    
    exec <proc_name>;

방법	설명
Positional	선언한 매개변수 순으로 차례로 변수 값 입력
Named Association	'=>'구문과 함께 매개변수 이름을 사용하여 변수 값 입력
Combination	앞의 값은 Positional 방법으로, 나머지 값은 Named Association 방법으로 혼합하여 입력

create or replace PROCEDURE EMP_COUNT 
(P_DEPTNO IN NUMBER DEFAULT 20, -- 입력변수
 O_EMP_COUNT OUT NUMBER) --프로시저 출력 변수
IS
    --커서 선언 
    CURSOR GET_EMP_DATA IS
    SELECT COUNT(*)
    FROM EMP
    WHERE DEPTNO = P_DEPTNO;
    
    I_COUNT NUMBER;
    
BEGIN
    --커서오픈
  OPEN GET_EMP_DATA;
  --커서 FETCH
  FETCH GET_EMP_DATA INTO I_COUNT;
  --출력 변수에 값 입력
  o_emp_count := i_count;
  --커서 close
  CLOSE GET_EMP_DATA;
END EMP_COUNT;

이전에 만들어뒀던 프로시저를 잠시 보고 가자

부서 번호를 전달받고 몇개의 부서가 있는지 확인하는 프로시저 이다 

기본값이 20 이고 IN 으로 선언되어 있다 즉 값을 저달받을수 있다는말이다. OUT도 값을 전달받을수 있지만 의미가 없다

커서를 선언함으로서 결과값이 커서 GET_EMP_DATA 이곳에 저장될것이고 그값을 INTO 절안에 다시 전달한다

이후에 o_emp_count 값에 다시 전달하고 커서를 닫아준다

 

그러면 실행해보자 

   DECLARE
--프로시저 OUT값을 저장할 출력 변수 선언
O_CNT NUMBER;
BEGIN
    --순차적으로 해당 위치에 값 전달
    emp_count(10,o_cnt);
    DBMS_OUTPUT.PUT_LINE('EMP COUNT:' || o_cnt);
    --매개변수 생략 시 매개변수 이름을 이용하여 값 전달
    emp_count(o_emp_count => o_cnt);
    DBMS_OUTPUT.PUT_LINE('EMP COUNT:' || o_cnt);
    --위치와 변수의 이름을 이용하는 방법을 조합하여 전달
    emp_count(30,o_emp_count => o_cnt);
    DBMS_OUTPUT.PUT_LINE('EMP COUNT:' || o_cnt);
    
    end;

첫번째 와 세번째의 경우는 IN 매개변수를 통해 선언된 P_DEPTNO 변수에 10 또는 30이 전달되고 값이 없는 두번째 경우에는 기본값인 20이 입력 값으로 사용된다. OUT 매개변수를 통해 o_cont 라는 변수에 저장된다.

 

SQL 플러스를 이용할시 실행구문

VAR o_cnt VARCHAR2(1000);
    EXEC EMP_COUNT(10,:o_cnt);
    PRINT o_cnt;

 

 

프로시저 Invalid시 재컴파일(Re-compile)방법

프로시저에서 차조하고 있는 테이블의 구조가 변경되었거나, 프로시저가 변경되었을 경우 변경된 사항을 반영하기 위해 프로시저의 상태(Status)가 INVALID로 변경된다. 이러한 경우 INVALID 상태가 되었더라도 대부분의 경우 다음 번 실행 시에 오라클이 자동으로 컴파일을 시도하기 때문에 문제가 되지는 않는다

그러나 오브젝트 변경 후 관련된 패키지 ,프로시저, 함수에 대해 일괄적으로 컴파일을  수행하거나 어떤 이유에서 자동으로 컴파일이 완료되지 못했을 경우에는 아래 명령어를 이용하여 컴파일을 메뉴얼하게 수행할 수 있습니다.

ALTER PROCEDURE PROC_NAME COMPILE;