여행을 개발하다
프로시저 (Procedure) 본문
지난 PL/SQL 입문 포스팅에 이어 Procedure(프로시저)에 대해 알아보고자 한다.
Oracle PL/SQL의 Block 종류에는 크게 3가지로, Anonymous Block(익명 블록), Procedure(프로시저), Function(함수)로 나뉜다. 그 중 Procedure의 사전적인 의미는 '순서'인데, PL/SQL에서도 그 의미를 크게 벗어나지는 않는다.
프로시저란 '일정한 순서에 따라 작업을 진행하는 Block'을의미한다. 각각의 프로시저는 고유한 명칭을 가지는데, 이런 점에서도 '익명 블록'의 특징과 구분된다.
그렇다면 프로시저가 무엇인지 자세히 알아보도록 한다.
프로시저(Procedure)란?
- 특정 작업을 수행하는,이름이 있는 PL/SQL BLOCK이다.
- 매개 변수를 받을 수 있고, 반복적으로 사용할 수 있는 BLOCK이다.
- 보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL BLOCK을 데이터베이스에 저장하기 위해 생성한다.
출처 :꿈꾸는 개발자, DBA 커뮤니티 : http://www.gurubee.net/lecture/1041
프로시저의 구조
프로시저의 구조는 다음과 같다.
* CREATE OR REPLACE 프로시저명
- 말 그대로 프로시저명이 있으면 기존 프로시저를 대체하고, 없으면 새로 생성하라는 의미이다. 프로시저의 이름을 정의하거나 수정하는 부분으로 보면 된다. 즉, 프로시저 선언부.
*변수1 IN 변수 타입, 변수2 OUT 변수 타입, ...
-프로시저에서 사용할 변수들을 선언하는 부분이다. 정확히 말하면, 프로시저에 사용할 매개변수들이 선언되는 곳이다. 이 부분에서 명시하는 변수들은 사용자로부터 입력받을 수도 있고, 사용자에게 리턴해줄 수도 있으며, 입력 및 리턴을 동시에 해줄 수도 있다.
* 변수처리부
- 변수처리부에서도 변수들을 선언한다. 하지만 여기에서의 변수란, 사용자 입력에 관계없이 프로시저 처리 내용 내에서 사용할 '로컬 변수'이다. 즉, 사용자가 프로시저의 처리 내용 전체를 까보지 않는 이상, 사용자에게 직접적으로 표현되지는 않는다. 대표적으로, 프로시저 처리 내용 중 발생한 결괏값을 받아 매개변수에 전달해줄 때가 그 용도가 된다.
* 예외처리부
- 프로시저를 사용하다 발생할 수 있는 예외를 처리하는 부분이다. 예외의 종류는 여러 가지가 있으니 추후 포스팅에서 다룬다.
1. 프로시저의 선언
프로시저의 선언 방법은 다음과 같다.
CREATE OR REPLACE 프로시저이름CREATE OR REPLACE fn_search
CREATE OR REPLACE 구문 뒤에 프로시저 이름을 적어준다. 여담으로,CREATE 대신 DECLARE를 적어놓고 '왜 오류가 나지?'하고 의아해했던 기억이 많다.익명 블록의 선언은 DECLARE었지만, 프로시저의 선언은 CREATE OR REPLACE로 시작한다.
2. 매개 변수의 선언
프로시저를 실행시킬 때 사용할 매개 변수들을 입력받을 수도 있다.
예를 들어, 한 회사의 급여 정보를 저장하고 있는 테이블이 있다고 가정하자. 이 테이블의 Primary Key는 SABUN(사번)이 되는데, 사번을 입력받아 해당하는 사원의 급여를 1.3배로 update 하는 프로시저를 작성하고자 한다. 이때, 정상적인 프로시저 실행을 위해서는 사용자로부터 '사번'을 입력받아야 하는데, 이 '사번'이 매개변수가 된다.
매개변수는 사용자에게 입력을 받을 수도, 프로시저의 결과로 사용자에게 리턴할 수도, 입력 및 리턴을 동시에 할 수도 있다. 이때, 입력받을 매개변수들은 'IN'으로, 리턴해줄 매개변수들은 'OUT'으로 표기한다. 입력받는 동시 사용자에게도 리턴할 매개변수는 'INOUT'이라고 적는다. 일반적으로 'IN'은 생략이 가능하다.
(변수명 IN 타입) OR (변수명 타입) -- 사용자로부터 입력 받을 매개변수 선언
(변수명 OUT 타입) -- 사용자에게 리턴할 매개변수 선언
(변수명 INOUT 타입) -- 사용자로부터 입력 받는 동시 리턴할 매개변수 선언CREATE OR REPLACE PROCEDURE fn_search -- fn_search라는 프로시저 생성
(v_var1 IN VARCHAR2, -- 변수명 : v_var1, 입/출력 : 사용자 입력, 변수타입 : VARCHAR2
v_var2 OUT VARCHAR2, -- 변수명 : v_var2, 입/출력 : 사용자 리턴, 변수타입 : VARCHAR2
v_var3 INOUT VARCHA2) -- 변수명 : v_var3, 입/출력 : 사용자 입력 후 리턴, 변수타입 : VARCHAR2
IS -- 변수 선언 종료 및 지역변수 선언 OR 프로시저 실행부 시작
3. 지역 변수의 선언 (변수처리부)
매개변수 외, 지역변수를 선언하고 사용할 수도 있다. 여기서 지역변수란 PL/SQL 블록, PL/SQL 프로그램 안에서 사용할 임시 변수들을 의미한다. 매개변수들이 사용자와 직접 소통하는데 비해, 지역변수는 프로시저 실행부에서만 사용된다.
지역 변수의 선언 방법은 다음과 같다.
변수명 변수타입;var1 VARCHAR2;
var2 NUMBER;
입력과 동시 값을 초기화할 수도 있다. 단, 초기값은 당연히 변수 타입과 일치시켜야 한다.
var1 VARCHAR2 := 'HI';
var2 NUMBER := 2;CREATE OR REPLACE fn_search
(v_var1 IN VARCHAR2,
v_var2 OUT VARCHAR2,
v_var3 INOUT VARCHA2
)
IS
v_var4 VARCHAR2; -- 변수처리부, 지역변수의 선언
4. BEGIN~END 구문
BEGIN~END 구문에는 실제로 PL/SQL 엔진이 실행할 구문들을 적어준다. 그 실행구문이란, 여러 테이블에 대한 CRUD 작업이 될 수도 있고, DBMS_OUPUT을 통한 특정 변수/결괏값에 대한 출력이 될 수도 있다. BEGIN부터 명시한 일련의 실행 절차들은 END 구문을 만나 실행내용을 종료한다.
CREATE OR REPLACE fn_search
(v_var1 IN VARCHAR2,
v_var2 OUT VARCHAR2,
v_var3 INOUT VARCHA2
)
IS
v_var4 VARCHAR2 := 'HI';
v_var5 NUMBER := 1;
BEGIN
DBMS.OUT_PUT.PUT_LINE('v_var1=' || v_var1);
DBMS.OUT_PUT.PUT_LINE('v_var2=' || v_var2);
DBMS.OUT_PUT.PUT_LINE('v_var3=' || v_var3);
DBMS.OUT_PUT.PUT_LINE('v_var4=' || v_var4);
DBMS.OUT_PUT.PUT_LINE('v_var5=' || v_var5);
END;
5. 예외처리부
프로시저에서도 처리중 예외상황이 발생할 수 있다. 이럴 때는 발생 가능한 예외상황이 어떤 유형인지를 명시하고, 해당 예외가 발생하면 어떤 action을 취할 것인지를 함께 적어준다. 예를 들어, 사번을 입력받아 연봉 정보를 update 하려고 했지만, 입력받는 사번이 테이블에 부재하는 경우 연봉 정보를 update할 수 없다. 이럴 때, rollback을 할지, insert를 하고 update를 할지를 예외부에 명시해준다면 보다 정확한 프로시저를 구현할 수 있을 것이다.
CREATE OR REPLACE fn_search
(v_var1 IN VARCHAR2,
v_var2 OUT VARCHAR2,
v_var3 INOUT VARCHA2
)
IS
v_var4 VARCHAR2 := 'HI';
v_var5 NUMBER := 1;
BEGIN
DBMS.OUT_PUT.PUT_LINE('v_var1=' || v_var1);
DBMS.OUT_PUT.PUT_LINE('v_var2=' || v_var2);
DBMS.OUT_PUT.PUT_LINE('v_var3=' || v_var3);
DBMS.OUT_PUT.PUT_LINE('v_var4=' || v_var4);
DBMS.OUT_PUT.PUT_LINE('v_var5=' || v_var5);
[EXEPTION] -- 예외처리부[OPTIONAL]
END;
6. 프로시저 작성 예제
그렇다면 임의의 프로시저를 작성해서 실행해본다.
* 대상 테이블명 :INSA (SABUN, NAME, SALARY)
CREATE TABLE INSA
( SABUN VARCHAR2(10 BYTE) NOT NULL,
NAME VARCHAR2(100 BYTE),
SALARY NUMBER DEFAULT 0,
CONSTRAINT pk_INSA PRIMARY KEY(SABUN)
)* 프로시저명: update_sal
* 매개변수: sabun(사번), upd_type(인상(UP)/감봉(DOWN)), ratio(인상/감봉 비율)
* 내용
- 인사정보 테이블(INSA)에 사번 정보(SABUN)를 입력받는다.
- 해당 사번을 가진 사원의 연봉 정보를 수정한다.
- 연봉 정보는 인상(UP)/감봉(DOWN) 모두 가능하다.
- 인상/감봉률은 사용자가 입력할 수 있다.
6-1. 프로시저 선언
CREATE OR REPLACE PROCEDURE update_sal
6-2. 변수 선언
(sabun IN VARCHAR2,
upd_type IN VARCHAR2,
ratio IN FLOAT)
변수 타입은 VARCHAR2, NUMBER와 같이 그대로 써줄 수 있다. 하지만, 특정 테이블의 필드 TYPE을 똑같이 사용하고 싶을 경우가 빈번하게 생길 수 있다. 그럴 때는, 다음과 같이 필드 타입 정보를 고스란히 가져올 수도 있다.
변수명 IN/OUT/INOUT 테이블.필드명%TYPE
즉, INSA 테이블의 SABUN 필드 타입을 그대로 가져다 쓰기 위해서는 아래와 같이 매개변수를 선언할 수 있다.
(sabun IN INSA.SABUN%TYPE,
upd_type IN VARCHAR2,
ratio IN FLOAT)
IS
매개변수 선언이 끝나면 IS로 매개변언선 종료 및 지역변수 선언 시작 처리를 한다.
6-3. 지역변수 선언
이제는 사용자 입력/리턴과 별개로 프로시저 수행부에서 사용할 지역변수를 선언한다. 매개변수 선언이 끝난 후에는 IS로 마감처리를 해준다. update_sal이라는 프로시저에서는 v_name, v_sal이라는 지역변수를 사용할 것이며, 사용자가 변수를 제대로 입력했는지 DBMS_OUTPUT으로 출력해보기 위해 선언한다.
v_name INSA.NAME%TYPE;
v_sal INSA.SALARY%TYPE;
6-4. 프로시저 실행부
실행부에서는 말그대로 프로시저가 실제로 실행할 내용을 기술한다.
BEGIN
-- 입력받은 sabun으로 해당 레코드를 찾아, v_name, v_sal에 대입한다.
SELECT NAME,
SALARY
INTO v_name, v_sal
FROM INSA
WHERE SABUN = sabun;
-- 사용자가 인상(UP)을 입력했으면, SALARY 필드를 인상률만큼 가산하여 UPDATE한다.
IF upd_type = 'UP' THEN
UPDATE INSA
SET SALARY = SALARY * (1 + ratio)
WHERE SABUN = sabun;
COMMIT;
-- 사용자가 삭감(DOWN)을 입력했으면, SALARY 필드를 삭감률만큼 삭감하여 UPDATE한다.
ELSIF upd_type = 'DOWN' THEN
UPDATE INSA
SET SALARY = SALARY * (1-ratio)
WHERE SABUN = sabun;
COMMIT;
-- 사용자가 UP 혹은 DOWN을 입력하지 않았으면 다음과 같은 메시지를 출력한다.
ELSE
DBMS_OUTPUT.PUT_LINE('두 번째 인수는 UP 혹은 DOWN만 입력 가능');
END IF;
END update_sal;
이렇게 UPDATE_SAL이라는 프로시저 하나가 완성되었다.
CREATE OR REPLACE PROCEDURE update_sal
(sabun IN INSA.SABUN%TYPE,
upd_type IN VARCHAR2,
ratio IN FLOAT
)
IS
BEGIN
SELECT NAME,
SALARY
INTO v_name, v_sal
FROM INSA
WHERE SABUN = sabun;
IF upd_type = 'UP' THEN
UPDATE INSA
SET SALARY = SALARY * (1 + ratio)
WHERE SABUN = sabun;
COMMIT;
ELSIF upd_type = 'DOWN' THEN
UPDATE INSA
SET SALARY = SALARY * (1-ratio)
WHERE SABUN = sabun;
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE('두 번째 인수는 UP 혹은 DOWN만 입력 가능');
END IF;
END update_sal;
6-5. 프로시저 실행
프로시저는 'EXEC + 프로시저'로 실행시킨다.
EXEC 프로시저(매개변수1, 매개변수2, 매개변수3, ...);EXEC update_sal(sabun, upd_type, ratio);
먼저, 프로시저를 실행시킬 타켓 레코드를 추출한다.
SELECT * FROM INSA;SABUN NAME SALARY
---------- ------------ --------------
2020011901 정영훈 4000
다음 레코드에 대해서 3번의 프로시저를 실행해본다.
- 연봉 10% 인상
EXEC update_sal('2020011901', 'UP', 0.1);SABUN NAME SALARY
---------- ------------ --------------
2020011901 정영훈 4400
- 연봉 10% 삭감 (위 프로시저 결과에 대해서 → 4400에서 10% 감면)
EXEC update_sal('2020011901', 'DOWN', 0.1);SABUN NAME SALARY
---------- ------------ --------------
2020011901 정영훈 3960
- 인수 잘못 입력(upd_type)
EXEC update_sal('2020011901', 'RAISE', 0.1);두 번째 인수는 UP 혹은 DOWN만 입력 가능
지금까지 오라클 PL/SQL 프로시저에 대해 알아보았다.
감사합니다!!
'데이터베이스 > Oracle PL SQL' 카테고리의 다른 글
| 함수 (Function) (0) | 2020.01.28 |
|---|---|
| Oracle PL/SQL 입문 (0) | 2020.01.13 |
