프로시저

함수와 프로시저의 가장 큰 차이는 결과값을 반환하는지 아닌지의 차이다.

함수는 특정 연산을 수행하고 결과값을 반환하지만 프로시저는 특정 로직만 처리한다

(그런데 사실 프로시저도 OUT으로 결과를 반환할 수 있다.. 그러면 차이가 없는 거 아닌가?)

프로시저는 데이터를 추출해서 조작 후 그 결과를 다른 테이블에 저장하거나 갱신하는 것처럼 연속적인 처리를 할 때 자주 사용된다.

CREATE OR REPLACE PROCEDURE 프로시저 이름
	(
    	매개변수명 [IN|OUT|IN OUT] 데이터타입 [:=디폴트값]
    )
IS[AS]
	프로시저 내에서 사용할 변수/상수 선언
BEGIN
	실행할 쿼리
[EXCEPTION
	예외 처리]
END [프로시저 이름];

프로시저의 생성 구문

매개변수를 작성할 때 IN은 생략이 가능하며 생략시 디폴트로 들어간다.

OUT이나 IN OUT은 반드시 작성을 해줘야 한다. 

매개변수의 디폴트값은 IN만 가능하며 해당 값이 없을 때는 디폴트값이 들어간다.

CREATE OR REPLACE PROCEDURE MY_PRO
(p_job_id    IN JOBS.JOB_ID%TYPE,
 p_job_title IN JOBS.JOB_TITLE%TYPE)
IS
  //지역변수가 없더라도 IS부는 생략하면 안 된다.
BEGIN
  INSERT INTO JOBS (job_id, job_title) VALUES (p_job_id, p_job_title);
  COMMIT;
END ;

위의 코드처럼 매개변수의 타입은 테이블 컬럼의 타입으로 가져올 수도 있다.

물론 VARCHAR2로 작성해도 된다.

    CREATE OR REPLACE PROCEDURE my_new_job_proc
    ( p_job_id    IN JOBS.JOB_ID%TYPE,
      p_job_title IN JOBS.JOB_TITLE%TYPE,
      p_min_sal   IN JOBS.MIN_SALARY%TYPE:= 10,   -- 디폴트 값 설정
      p_max_sal   IN JOBS.MAX_SALARY%TYPE:= 100,
      p_upd_date  OUT JOBS.UPDATE_DATE%TYPE)
    IS
      vn_cnt NUMBER := 0;
      vn_cur_datec JOBS.UPDATE_DATE%TYPE := SYSDATE;
    BEGIN
      -- 동일한 job_id가 있는지 체크
      SELECT COUNT(*)
        INTO vn_cnt
        FROM JOBS
       WHERE job_id = p_job_id;

      -- 없으면 INSERT
      IF vn_cnt = 0 THEN
        INSERT INTO JOBS ( job_id, job_title, min_salary, max_salary)
                  VALUES ( p_job_id, p_job_title, p_min_sal, p_max_sal);
      ELSE -- 있으면 UPDATE
        UPDATE JOBS
          SET job_title   = p_job_title,
              min_salary  = p_min_sal,
              max_salary  = p_max_sal
             WHERE job_id = p_job_id;
      END IF;
      
      -- OUT 매개변수에 일자 할당
      p_upd_date : = vn_cur_date;
      COMMIT;
    END ;

위의 코드처럼 디폴트값을 정해주면 min_sal이나 max_sal의 값이 없으면 0을 넣어주게 된다.

지역변수에 값을 선언할 때는 := 를 사용해서 값을 넣어줄 수 있다.

실행부에서 SELECT로 값을 조회해오고 그것에 따라 INSERT를 할지 UPDATE를 할지 진행을 하게 된다.

만약에 이것을 프로시저로 하지 않으면 꽤나 번거로울 것이다...

 

처음에 함수와 프로시저의 큰 차이는 반환하는 값의 여부라고 했는데

프로시저도 OUT매개변수를 통해 값을 반환할 수 있다. (...)

위의 프로시저를 실행하고 그 결과값을 받아보려면 아래 코드처럼 작성한다.

DECLARE
      vd_cur_date JOBS.UPDATE_DATE%TYPE;
    BEGIN
      my_new_job_proc ('SM_JOB1', 'Sample JOB1', 2000, 6000, vd_cur_date);

      DBMS_OUTPUT.PUT_LINE(vd_cur_date);
END;

프로시저의 실행은 EXEC(EXECUTE) 프로시저명(매개변수1 값, 매개변수2 값...); 또는

EXEC(EXECUTE) 프로시저명(매개변수1 => 매개변수1 값, 매개변수2 => 매개변수2 값...);처럼 쓸 수 있다.

다만 위의 코드처럼 익명블록일 때는 EXEC를 작성하지 않는다!

바로 프로시저 명을 작성해 접근할 수 있고 IN 매개변수 4개를 넣으면 프로시저가 실행되고 OUT매개변수에 값이 담아져서 출력이 된다.

 

프로시저에도 RETURN이 있는데 이것은 값을 반환하거나 하는 것이 아닌 해당 프로시저를 종료한다는 뜻이다.

 

커서는 특정 SQL문을 처리한 결과를 담고 있는 영역을 가리키는 일종의 포인터다.

프로시저 등에서 셀렉트한 결과를 커서에 담아 OUT시킬 수 있다.

커서는 묵시적 커서와 명시적 커서가 있다.

묵시적 커서는 INSERT, UPDATE, DELETE 등을 할 때 자동으로 생성이 되고 명시적 커서는 사용자가 직접 정의해서 사용하게 된다.

 

출처 : 오라클 SQL과 PL/SQL을 다루는 기술