오라클 PL/SQL 프로시저(Procedure) 특징 및 예제

프로시저(Procedure)의 특징

개발자가 자주 실행해야하는 특정 작업을 필요할 때 호출하기위해 절차적인 언어를 이용하여 작성한 이름이 있는 프로그램 모듈(Block)을 의미한다.

- 매개 변수를 받을 수 있는 PL/SQL BLOCK 이다. (PL/SQL 블록이란?)

- 프로시저 내의 변수는 Scalar변수라고 해서 임시 데이터 1개만 저장할 수 있는 변수이며, 모든 형태의 데이터 유형 지정 가능.

- PL/SQL의 대입연산자는 ‘ := ’이다. (↔ T-SQL은 일반적인 ‘ = ’이다)

- PL/SQL에서 사용하는 프로시저 내의 SELECT문장은 반드시 결과 값이 있어야 하며, 그 결과는 반드시 1개여야 한다. 조회결과가 없거나 2개 이상인 경우에는 에러가 발생(T-SQL은 결과값없어도 상관없음)한다. 그러나 특정한 로직을 처리하면서 결과는 있어야하지만 그 결과 값을 함수(사용자 정의 함수)처럼 반환(return)하지는 않는다.

프로시저(Procedure) 문법

CREATE [OR REPLACE] Procedure “Procedure_name”( argument1 [MODE] data_type1, argument2 [MODE] data_type2, … … )

IS[AS]

…

BEGIN

…

EXCEPTION

…

END;

/

1) CREATE [OR REPLACE] 구문을 이용하여 생성한다.

2) OR REPLACE : 같은 프로시저가 있을 때, 기존의 프로시저를 무시하고 새로운 내용으로 덮어쓰겠다는 의미이다.

3) MODE : mode는 매개변수의 역할을 결정하는 자리이다. mode자리에 들어갈 수 있는 변수는 3가지로 IN, OUT, INOUT이 있다.

  ① IN: 운영체제에서 프로시저로 전달될 변수의 모드.

  ② OUT: 프로시저에서 처리된 결과라 운영체제로 전달.

  ③ INOUT: IN과 OUT 두 가지 기능 모두 수행.

4) IS : PL/SQL의 Block을 시작한다는 의미이며, 프로시저 내(정확히는, Begin문뒤에 나올 SQL문)에서 사용할 변수를 선언하는 곳이다. LOCAL변수는 IS와 Begin사이에 선언해서 사용한다.

5) EXCEPTION : Begin~end사이에서 실행되는 SQL문 실행 도중 발생한 에러를 처리하는 예외 처리부.

6) END; : 실행문의 종료를 의미한다.

7) / : end; 뒤에 위치하는 슬러시(/)는 데이터베이스에게 프로시저를 컴파일하라는 명령.

프로시저(Procedure) 예제

CREATE OR REPLACE Procedure p_DEPT_insert (

v_DEPTNO in number,

v_dname in varchar2,

v_loc in varchar2,

v_result out varchar2 )

IS cnt number := 0;

BEGIN

SELECT COUNT(*) INTO CNT FROM DEPT WHERE DEPTNO = v_DEPTNO AND ROWNUM = 1;

If cnt > 0 then v_result := '이미 등록된 부서번호이다';

else INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (v_DEPTNO, v_dname, v_loc);

COMMIT;

v_result := '입력 완료!!';

end if;

EXCEPTION

WHEN OTHERS

THEN ROLLBACK; v_result := 'ERROR 발생';

END;

/

프로시저(Procedure) 실행 예제

SQL> EXECUTE p_DEPT_insert(10, 'dev', 'seoul', :rslt);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> print rslt;

1) EXECUTE구문을 이용하여 정의한 프로시저(Procedure)를 실행.

2) 프로시저의 OUT변수 rslt를 print할 때, DEPTNO(10)가 이미 존재한다면 '이미 등록된 부서번호'가 출력될 것이다. 반대로 없다면 '입력 완료!!'라고 출력됩니다.