PL/SQL 프로시저(Procedure)

자주 사용하는 PL/SQL 블록을 재사용하기 위해 모듈화한 것을 프로시저라고 한다.

프로시저 생성

--부서 번호를 입력받아 사원 리스트를 반환하는 프로시저 생성
CREATE OR REPLACE PROCEDURE get_list_by_deptno(
			p_deptno IN employees.department_id%TYPE) 
IS 
	CURSOR employee_cursors IS
    	SELECT * FROM employees
        	WHERE department_id = p_deptno;
    employee_record employee_cursors%ROWTYPE;
BEGIN
	dbms_output.put_line('=====사원 리스트=====');
   	FOR employee_record IN employee_cursors LOOP
    	dbms_output.put_line(p_deptno || ' ' || employee_record.employee_id || ' ' || employee_record.last_name);
    END LOOP;
END;

프로시저 생성 시 매개변수명과 데이터 타입만 명시하면 디폴트로 IN 매개변수가 되지만 OUT 매개변수는 반드시 OUT 키워드를 명시해야 한다.

프로시저 실행

EXECUTE get_list_by_deptno(100);
EXEC get_list_by_deptno(100);

프로시저의 매개변수

프로시저의 매개변수에는 IN, OUT 그리고 IN OUT이 있다.
IN은 일반 메서드에서의 매개변수와 같은 역할이고, OUT은 리턴되는 변수, IN OUT은 입력 및 출력용으로 사용하는 변수이다.

--OUT 매개변수 사용 예시
declare
  p_result number;

begin
  11111check_duplicated_pk('11','student', 1000, 10000, p_result);
  dbms_output.put_line(p_result);
end;

다음 예시를 보면 프로시저의 매개변수 역할에 대해서 이해할 수 있다.

CREATE OR REPLACE PROCEDURE my_parameter_test_proc (
               p_var1        VARCHAR2,
               p_var2 OUT    VARCHAR2,
               p_var3 IN OUT VARCHAR2 )
IS
    BEGIN
     DBMS_OUTPUT.PUT_LINE('p_var1 value = ' || p_var1); -- 결과: A
     DBMS_OUTPUT.PUT_LINE('p_var2 value = ' || p_var2); -- 결과: 
     DBMS_OUTPUT.PUT_LINE('p_var3 value = ' || p_var3); -- 결과: C

     p_var2 := 'B2';
     p_var3 := 'C2';
    END;

DECLARE
	v_var1 VARCHAR2(10) := 'A';	
    v_var2 VARCHAR2(10) := 'B';
    v_var3 VARCHAR2(10) := 'C';
BEGIN
	my_parameter_test_proc (v_var1, v_var2, v_var3);
	DBMS_OUTPUT.PUT_LINE('v_var2 value = ' || v_var2); --결과: B2
	DBMS_OUTPUT.PUT_LINE('v_var3 value = ' || v_var3); --결과: C2
END;

한편 위와 같이 익명 블록에서 프로시저를 실행할 때, 혹은 다른 함수나 프로시저, 패키지에서 실행하는 경우에는 EXEC나 EXECUTE를 붙이지 않는다.

익명 블록?
PL/SQL의 가장 작은 프로그램 단위는 블록이다. 이름이 없는 블록을 익명 블록이라 하고, 나머지 이름이 있는 블록에는 함수, 프로시저, 패키지 등이 있다.

매개변수 디폴트 값 지정

CREATE OR REPLACE PROCEDURE my_parameter_test_proc (
               p_var1        VARCHAR2 := 'AAA',
               p_var2 OUT    VARCHAR2,
               p_var3 IN OUT VARCHAR2 )