[Oracle/PLSQL]3.프로시저 작성

* 2단원은 Procedure Builder 사용에 관한 내용이므로 생략.

 

● 프로시저란?

 

매개변수(인수)를 사용하여 호출할 수 있는 명명된 PL/SQL블록이다.

 

● 프로시저 작성구문

 

CREATE [OR REPLACE] PROCEDURE procedure_name

(parameter1 [mode1] datatype1,

 parameter2 [mode2] datatype2,

 ...)

IS/AS

PL/SQL Block;

 

- 프로시저 매개변수 모드 사용

 

매개변수유형	설명
IN (기본값)	호출 환경에서 프로시저로 상수 을 전달한다.
OUT	프로시저에서 호출 환경으로 값을 전달한다.
IN OUT	동일한 매개변수를 사용하여 호출 환경에서 프로시저로 값을 전달하고 프로시저에서 호출 환경으로  다른 값을 전달받을 수 있다.

 

IN	OUT	IN OUT
기본값	지정해야함	지정해야함
값을 서브 프로그램에 전달함	값을 호출 환경으로 반환함	값을 서브 프로그램에 전달하고 호출 환경  으로 반환함
형식 매개변수가 상수로 작용	초기화되지 않은 변수	초기화된 변수
실제 매개변수는 리터널, 표현식, 상수  또는 초기화된 변수임	변수여야함	변수여야함

 

❶ IN 매개변수는 참조만 가능하며 값을 할당할 수 없다.

❷ OUT 매개변수에 값을 전달할 수는 있지만 의미는 없다.

❸ OUT, IN OUT 매개변수에는 디폴트 값을 설정할 수 없다.

❹ IN 매개변수에는 변수나 상수, 각 데이터 유형에 따른 값을 전달할 수 있지만, OUT, IN OUT 매개변수를 전달할 때는 반드시 변수 형태로 값을 넘겨줘야 한다.

 

- IN : 사용자가 고정 값 넘기면, 가공된 값 받을 수 있음.

- OUT : 사용자가 변수 값 넘기면, 가공되지 않은 값 받음.

- IN OUT : 고정값을 변수에 담아서 넘기고, 가공된 값 받을 수 있다.

 

Ex. IN 매개변수 예제

 

CREATE OR REPLACE PROCEDURE raise_salary
    (v_id IN emp.empno%TYPE)
IS
BEGIN  
    UPDATE emp
    SET sal = sal * 1.10
    WHERE empno = v_id;
END raise_salary;

 

실행 : EXECUTE raise_salary(7369);

 

☞ IN 매개변수값은 호출 환경에서 프로시저에 상수로 전달되므로 IN 매개변수의 값을 변경하면 오류가 발생한다.

 

Ex. OUT 매개변수 예제

 

CREATE OR REPLACE PROCEDURE query_emp
(
    v_id     IN emp.empno%TYPE,
    v_name   OUT emp.ename%TYPE,
    v_salary OUT emp.sal%TYPE,
    v_comm   OUT emp.comm%TYPE
)
IS
BEGIN
    SELECT ename, sal, comm
      INTO v_name, v_salary, v_comm
      FROM emp
     WHERE empno = v_id;
END query_emp;

 

실행확인 : OUT으로 프로시저로부터 출력되는 값을 받을 변수 3개를 선언하고, 바인드변수로 PRINT하여 확인한다.

 

VARIABLE g_name VARCHAR2(15);
VARIABLE g_sal NUMBER;
VARIABLE g_comm NUMBER;

EXECUTE query_emp(7654, :g_name, :g_sal, :g_comm);

PRINT g_name;

 

OUT, IN OUT 매개변수를 전달할 때는 반드시 변수 형태로 값을 넘겨줘야 한다.

 

Ex. IN OUT 매개변수 예제

 

CREATE OR REPLACE PROCEDURE format_phone
(
    v_phone_no IN OUT VARCHAR2
)
IS
BEGIN
    v_phone_no := '(' || SUBSTR(v_phone_no,1,3) ||
                  ')' || SUBSTR(v_phone_no,4,3) ||
                  '-' || SUBSTR(v_phone_no,7);
END format_phone;

 

OUT, IN OUT 매개변수에는 디폴트 값을 설정할 수 없으므로, 위의 예에서 VARCHAR2에 데이터 길이를 설정하지 않았다.

 

실행확인.

 

VARIABLE g_phone_no VARCHAR2(15)

BEGIN
    :g_phone_no := '8006330575';
END;

EXECUTE format_phone(:g_phone_no);

PRINT g_phone_no;

 

OUT, IN OUT 매개변수를 전달할 때는 반드시 변수 형태로 값을 넘겨줘야 한다.

 

● 매개변수 전달 방식

 

방식	설명
위치	매개변수 선언 순서로 값을 나열한다.
명명된 연결	특수 구문(=>)을 사용하여 각 값과 매개변수 이름을 연결시켜 임의 순서로 값을 나열한다.
조합	맨 위에 있는 값을 표시한 다음 명명된 방식의 특수 구문을 사용하여 나머지 값을 나열한다.

 

Ex. 컬럼이 3개인 테이블에 INSERT하는 예제

 

CREATE OR REPLACE PROCEDURE add_dept
(
    v_name IN dept.dname%TYPE DEFAULT 'unknown',
    v_loc  IN dept.loc%TYPE   DEFAULT 'unknown'
)
IS 
BEGIN
    INSERT INTO dept
    VALUES (dept_deptno.NEXTVAL, v_name, v_loc);
END add_dept;

 

에서,

 

1. 시퀀스를 제외한 2개의 컬럼에 DEFAULT 값을 넣는 경우

 

BEGIN

add_dept;

END;

 

2. 시퀀스를 제외한 2개의 컬럼에 모두 원하는 값을 넣는 경우 (위치를 이용한 매개변수 전달 방식)

 

BEGIN

add_dept('TRANING' , 'NEW YORK');

END;

 

3. 시퀀스를 제외한 2개의 컬럼에 모두 원하는 값을 넣는 경우 (명명된 연결을 이용한 매개변수 전달 방식)

 

BEGIN

add_dept (v_loc => 'DALLAS', v_name => 'EDUCATION');

END;

 

4. 시퀀스를 제외한 2개의 컬럼 중 하나에만 원하는 값을 넣고, 나머지 컬럼은 DEFAULT값을 넣는 경우.

 

BEGIN

add_dept(v_lo => 'BOSTON');

END; 

 

● 서브 프로그램 선언

 

CREATE OR REPLACE PROCEDURE LEAVE_EMP2
    (v_id IN emp.empno%TYPE)
IS
    PROCEDURE log_exec
    IS
    BEGIN
        INSERT INTO log_table (user_id, log_date)
        VALUES (user,sysdate);
    END log_exec;
BEGIN
    DELETE FROM emp
    WHERE empno = v_id;
    log_exec;
END leave_emp2;

 

서브 프로그램은 블록의 선언 부분(IS와 BEGIN사이)에서 선언해아한다.

 

● 프로시저 제거

 

DROP PROCEDURE procedure_name

 

Ex.

DROP PROCEDURE raise_salary;