목차
소개
이번엔 Tibero, Oracle Database의 Stored Procedure, Function에 대해 알아보고자 합니다.
기본적인 Stored Procedure, Function 에 대한 기본 개념과 정의, 활용에 대한 내용을 포함하고 있습니다.
Stored Procedure 를 설명하기 이전 PL/SQL 에 대한 설명을 먼저 진행하고자 합니다.
PL/SQL
PL/SQL은 Oracle's Procedural Language extension to SQL의 약자로 SQL의 확장된 개념으로 ORACLE에서 제공하는 프로그래밍 언어의 특성을 수용한 SQL 확장 문법입니다. PL/SQL Block 내에서 SQL의 DML(데이터 조작어)문과 Query(검색어) 문, 절차형 언어(if, loop) 등을 사용하여 절차적 프로그래밍을 가능하게 한 강력한 트랜잭션 언어입니다.
이러한 PL/SQL 의 종류는 다음과 같습니다.
- 익명 procedure
- 이름 없이 사용되는 PL/SQL 블록
- DB에 저장되지 않고 사용자가 필요할 때마다 반복적으로 작성, 실행
- Stored Procedure
- 생성 이후 DB에 정보가 저장됨
- 실행하려는 로직을 처리하고 PL/SQL 블록의 흐름 제어
- 인자를 받아서 호출되고 실행
- Stored Function
- 프로시저와 동일한 개념, 기능이나 처리 결과를 사용자에게 리턴
- Package
- 특정 업무에 사용되는 프로시저 또는 함수를 묶어 생성하여 관리
- Trigger
- 테이블 생성 시 지정하며, 지정된 이벤트 발생 시 자동적으로 호출되어 실행되는 특수한 형태의 프로시저
- DB의 감시, 보안, 연속적 오퍼레이션의 자동처리 구현
이후 다룰 주제인 Stored Procedure도 PL/SQL의 한 종류 입니다.
이어서 PL/SQL의 특징을 확인해 보겠습니다.
PL/SQL의 기본 구조
Oracle의 PL/SQL은 기본적으로 블록(BLOCK) 구조로 되어 있으며, 블록의 기본적인 구성은 선언부(DECLARE), 실행부(BEGIN), 예외 처리부(EXCEPTION)로 구성되어 있습니다.
DECLARE -- 선언부
v_param NUMBER;
v_param2 VARCHAR(30);
BEGIN -- 실행부
-- Business Logic
..
EXCEPTION -- 예외 처리부
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR');
END;
-- DECLARE(선언부) 는 블록에서 사용될 변수, 상수, 커서, 예외를 선언하는 섹션으로 필요하지 않으면 생략할 수 있습니다.
-- 1. 변수 선언 : identifier [constant] datatype [not null] [:= | Default expr];
-- 2. 변수에 값 지정하기 : idenfier := expr;
-- 3. %TYPE을 이용한 변수 선언 : idenfier tablename.columnname%Type;
-- 4. %ROWTYPE을 이용한 변수 선언 : idenfier tablename%ROWTYPE;
-- 5. 바인드 변수 : VAR[IABLE] identifier datatype
-- DECLARE 사용은 아래와 같이 나타낼 수 있습니다.
-- DECLARE --
DECLARE
v_age number(3); -- number(3)형 변수 선언
v_name varchar2(30); -- varchar2(30)형 변수 선언
v_member_row member_tbl%ROWTYPE; -- member_tbl 테이블에 컬럼 속성들을 그대로 가진 변수 선언
v_member_name member_tbl.name%Type; -- member_tbl 테이블에 name 컬럼과 동일한 데이터형 변수 선언
v_member_age member_tbl.age%Type; -- member_tbl 테이블에 age 컬럼과 동일한 데이터형 변수 선언
BEGIN
-- 변수에 값 할당
v_age := 20;
v_name := '로그';
-- MEMBER_TBL 테이블에 ID가 '1'인 구성원의 모든 정보를 v_member_row 변수에 할당
SELECT *
INTO v_member_row
FROM MEMBER_TBL
WHERE ID = '1';
-- MEMBER_TBL 테이블에 ID가 '2'인 구성원의 이름과 나이를 변수에 할당
SELECT NAME, AGE
INTO v_member_name, v_member_age
FROM MEMBER_TBL
WHERE ID = '2';
END;
-- BEGIN(실행부) 는 Begin 문으로 시작하여 End; 문으로 종료하며 수행될 작업의 몸체입니다. SQL문, 제어문, 반복문, 커서 속성 등을 이용하여 블록에서 실행할 몸체를 구성할 수 있으며 생략할 수 없는 필수적 섹션입니다.
-- CRUD, IF문, Loop문, 커서 속성
-- 제어문, 반복문, 함수 정의 등의 로직을 기술함
-- 해당 실행부 내용 중 커서관련 내용은 제외하였습니다. 더 자세한 내용이 궁금하시면 가이드 문서 하단 참조 부분을 참고해주세요.
1. 제어문(IF, CASE)
DECLARE
V_SCORE NUMBER := 80;
V_GRADES CHAR(1);
BEGIN
IF (V_SCORE >= 90) THEN
V_GRADES := 'A';
ELSIF (V_SCORE >= 80) THEN
V_GRADES := 'B';
ELSIF (V_SCORE >= 70) THEN
V_GRADES := 'C';
ELSIF (V_SCORE >= 60) THEN
V_GRADES := 'D';
ELSE
V_GRADES := 'F';
END IF;
DBMS_OUTPUT.PUT_LINE('학점은 ' || V_GRADES || '입니다.');
END;
2. 반복문(LOOP, WHILE, FOR)
2-1. LOOP 문 : 0 ~ 5 까지 출력
DECLARE
V_NUM NUMBER := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(V_NUM);
V_NUM := V_NUM + 1;
EXIT WHEN V_NUM > 5;
END;
2-2. WHILE 문 : 0 ~ 5 까지 출력
DECLARE
V_NUM NUMBER := 0;
BEGIN
WHILE V_NUM < 6 LOOP
DBMS_OUTPUT.PUT_LINE(V_NUM);
V_NUM := V_NUM + 1;
END LOOP;
END;
2-3. FOR 문 : 0 ~ 5 까지 출력
DECLARE
FOR i in 0..5 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
-- EXCEPTION(예외처리부)는 End; 문 바로 앞에 위치하며 미리 정의된 예외를 추적하고 명시된 조건이 발생할 경우에 취할 작업을 정의하고 선택적 섹션입니다.
-- 미리정의된 예외, 사용자정의 예외, Exception 함수(sqlcode, sqlerrm)
-- 실행 도중 에러 발생 시 해결하는 문장들을 기술함
-- 해당 예외처리부는 Oracle 과 Tibero 에서 제공하는 시스템예외가 달라보였기 때문에 하단에 tbPSM의 시스템예외사항을 나타냈습니다. 이후 예제를 확인해보겠습니다.
DECLARE
employee_num NUMBER := 980180;
employee_grade VARCHAR2(10) := 'S';
BEGIN
CASE employee_grade
WHEN 'A' THEN pay_bonus_a(employee_num);
WHEN 'B' THEN pay_bonus_b(employee_num);
WHEN 'C' THEN pay_bonus_c(employee_num);
WHEN 'D' THEN pay_bonus_d(employee_num);
END CASE;
EXCEPTION
WHEN case_not_found THEN
pay_bonus_special(employee_num);
END;
tbPSM(Tibero7)에서 제공하는 시스템 정의 예외 종류
Oracle과 Tibero에서 제공하는 PL/SQL, tbPSM에 대한 공식문서는 다음과 같습니다. 참고해 보시면 좋을 것 같습니다.
설명
Stored Procedure란?
DB 내부에 저장된 일련의 SQL 명령문들을 하나의 함수처럼 실행하기 위한 쿼리의 집합. 즉, DB에 대한 작업을 정리한 절차를 RDBMS(관계형 데이터 베이스 관리 시스템)에 저장한 쿼리의 집합이다. 영구저장모듈이라고도 불린다. 여러 쿼리를 하나의 함수로 묶은 것이다.
- SQL Server에서 제공되는 프로그래밍 기능. 쿼리문의 집합
- 어떠한 동작을 일괄 처리하기 위한 용도로 사용.
- 자주 사용되는 일반적인 쿼리를 모듈화 시켜서 필요할 때마다 호출
- 테이블처럼 각 데이터베이스 내부에 저장
이러한 Procedure는 Tibero7 가이드문서상에는 다음과 같은 형태로 안내되어 있습니다.
[CREATE [OR REPLACE]] PROCEDURE 프러시저_이름 [(파라미터[, 파라미터])]
[AUTHID {DEFINER | CURRENT_USER}] {AS | IS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
[선언부]
BEGIN
[실행부]
[예외 처리부]
END;
예를 들어 Tibero7에서 Procedure를 작성한다면 다음과 같습니다.
CREATE OR REPLACE PROCEDURE NEW_EMP (ename VARCHAR2, deptno NUMBER)
IS
salary NUMBER;
deptno_not_found EXCEPTION;
BEGIN
IF deptno = 5 THEN
salary := 30000;
ELSIF deptno = 6 THEN
salary := 35000;
ELSE
RAISE deptno_not_found;
END IF;
INSERT INTO EMP (ENAME, SALARY, DEPTNO)
VALUES (ename, salary, deptno);
EXCEPTION
WHEN deptno_not_found THEN
-- Report Unknown Deptno
ROLLBACK;
END NEW_EMP;여기서 OR REPLACE문을 붙일 경우엔 해당 프로시저의 이름이 이미 정의되어 있음에도 프로시저를 업데이트한다는 의미로 사용됩니다.
그렇다면 구체적으로 어떤 차이가 있을지 확인해 보겠습니다.
일반 쿼리문 vs Stored Procedure의 차이점
쉽게 결론부터 말하자면 실행 순서와 속도의 차이가 존재한다
일반 쿼리문의 경우는 다음 흐름으로 실행되며 여러 번 실행되는 것도 매번 같은 순서로 실행된다.
일반 쿼리문
Stored Procedure
[1] Stored Procedure 정의 단계
- 구문분석 : 구문의 오류 파악
- 지연된 이름 확인 : 저장 프로시저를 정하는 시점에서 해당 개체(ex. 테이블)가 존재하지 않아도 상관없다. 프로시저 실행 당시에 테이블 존재 여부 확인함(개체이름 확인).
- 생성권한 확인 : 현재 사용자가 저장 프로시저를 생성할 권한이 있는지 확인
- 시스템 테이블 등록 : 저장 프로시저의 이름 및 코드가 시스템 테이블에 등록.
[2] 처음 Stored Procedure 실행 단계
구문분석 단계가 빠지는 것만 빼면 일반적인 쿼리문 수행단계와 동일하다. 저장프로시저 정의 단계의 지연된 이름확인에서 미루어두었던 해당 개체 존재 유무를 개체 이름 확인을 통해 수행한다.
[3] 이후 Stored Procedure 실행 이후에 두 번째 실행부터는 메모리(캐시)에 있는 것을 그대로 가져와 재사용하게 되어 수행시간을 많이 단축한다.
Stored Procedure의 장/단점
- 장점
- SQL Server의 성능을 향상할 수 있다 -> 저장프로시저의 두 번째 실행부터는 캐시(메모리)에 있는 것을 가져와서 사용하므로 속도가 빨라진다. 또한, 여러 개의 쿼리를 한 번에 실행할 수 있다.
- 유지보수 및 재활용 측면에서 좋다 -> 한번 저장 프로시저를 생성해 놓으면, 언제든 실행이 가능하기 때문에 재활용 측면에서 매우 좋다.
- 보안을 강화할 수 있다(권한체계) -> 저장 프로시저는 사용자들에게 데이터에 대한 제한적인 접근을 허용케 하는 전통적인 수단이다. 사용자별로 테이블에 권한을 주는 게 아닌 저장 프로시저에만 접근 권한을 줌으로써 테이블의 모든 정보를 사용자에게 노출하지 않고 프로시저에서 선택한 정보만 사용자에게 보여줄 수 있다.
- 네트워크부하를 줄일 수 있다 -> 클라이언트에서 서버로 쿼리의 모든 텍스트가 전송될 경우 네트워크에는 큰 부하가 발생하게 된다. 하지만 저장 프로시저를 이용한다면 저장 프로시저의 이름, 매개변수 등 몇 글자만 전송하면 되기 때문에 부하를 크게 줄일 수 있다. (저장 프로시저를 사용하면, 서버내부에서 이동하는 모든 데이터를 임시 테이블 혹은 변수에 저장할 수 있게 된다.)
- 단점
- DB 확장 어려움 -> 서비스 사용자가 많아져 서버의 수를 늘려야 할 때, DB의 수를 늘리는 것이 더 어렵다. 또한, DB 교체는 거의 불가능하다.
- 데이터 분석의 어려움 -> APP에서 SP를 호출하여 사용하는 경우 문제가 생겨도 해당 이슈에 대한 추적이 힘들다(별도의 에러 테이블 사용),
개발된 프로시저가 여러 곳에서 사용될 경우 수정했을 때 영향의 분석이 어렵다(별도의 Description 사용),
배포, 버전 관리 등에 대한 이력 관리가 힘들다. - 낮은 처리 성능 -> 문자, 숫자열 연산에 SP를 사용하면 오히려 c, java보다 느린 성능을 보일 수 있다
Function
Function을 설명하기 이전 Procedure와 Function의 차이를 한번 짚고 넘어가려고 합니다.
| 프로시저(Procedure) | 함수(Procedure) |
|---|---|
| 특정 작업을 수행 | 특정 계산을 수행 |
| 리턴값을 가질수도 안가질수도 있음 | 리턴값을 반드시 가져야 함 |
| 리턴값을 여러개 가질 수 있음 | 리턴값을 오직 하나만 가질 수 있음 |
| 서버(DB)단에서 기술 | 화면(Client)단에서 기술 |
| 수식내에서 사용 불가 | 수식내에서만 사용 가능 |
| 단독으로 문장 구성 가능 | 단독으로 문장 구성 불가 |
다음과 같은 차이점이 존재하는데 여기서 개인적으로 중요하게 생각했던 부분은 단독으로 구성이 불가능하다는 점이었습니다.
이어서 Function에 대하여 설명하자면 기본적인 구조는 Stored Procedure와 큰 차이는 존재하지 않습니다.
구조의 경우는 다음과 같습니다
[CREATE [OR REPLACE]] FUNCTION 함수_이름 [(파라미터[, 파라미터])]
RETURN 반환_타입 [AUTHID {DEFINER | CURRENT_USER}]
[DETERMINISTIC]
[PARALLEL_ENABLE]
[RESULT_CACHE [RELIES_ON (데이터소스이름[,데이터소스이름])]]
[PIPELINED]
{AS | IS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
[선언부]
BEGIN
[실행부]
RETURN 반환값;
[예외 처리부]
END;그리고 해당 구조의 예시를 보면 다음과 같습니다.
CREATE OR REPLACE FUNCTION NEW_EMP (ename VARCHAR, deptno INT)
RETURN NUMBER IS
...
BEGIN
...
RETURN salary;
EXCEPTION
...
END NEW_EMP;(Option) 캐싱 가능 함수
현재 tbPSM의 공식문서상 서브프로그램파트를 확인해 볼 때 위에서 설명한 함수 자체도 캐싱되어 작동될 수 있도록 따로 선언할 수 있게 설명되어 있습니다.
공식문서상 캐싱함수에 대한 설명은 다음과 같습니다.
- 함수 내에서 패키지 변수를 사용하지 않을 때
- 함수내에서 시퀀스를 사용하지 않을 때
- 함수 내의 모든 SQL이 캐싱 가능할 때
- SQL이 캐싱 가능할 조건 : INSERT, UPDATE, DELETE, MERGE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP, USERENV, SYS_CONTEXT, SYS_CONTEXT, SYS_GUID, CURRENT_USER, ROWNUM, USER, UID, SYSDATE 등이 들어가지 않을 때
캐싱 가능 함수는 SQL에서 실행 시 결괏값을 캐싱하여 실행한다.
함수 선언 시 DETERMINISTIC 절을 명시하면 강제로 캐싱 가능 함수로 정의할 수 있다.
구조는 다음과 같습니다.
CREATE OR REPLACE FUNCTION NEW_EMP (ename VARCHAR, deptno INT)
RETURN NUMBER
[DETERMINISTIC] IS
....
BEGIN
....
END;
