울면서개발하기삭제 전에 다른 테이블에서 쓰는 코드인지 확인하는 Oracle 함수
왜 필요했나
SI 프로젝트에서는 업무(모듈)별로 담당자가 나눠 ERD 를 그리다 보니, 자기 업무 안의 테이블끼리는 FK 가 걸려 있어도 다른 업무 테이블과는 FK 가 빠지는 일이 흔하다. 실제로 회계 쪽에서 관리하는 거래처 코드를 급여 쪽에서 쓰고 있었는데, 회계 화면에서 그 거래처를 지워 버린 일이 있었다.
그래서 코드를 지우기 전에 “같은 이름의 컬럼을 가진 다른 테이블에 이 값이 있는가” 를 찾아 막는 함수를 만들었다.
먼저 짚어 둘 점: 이 문제의 정답은 FK 제약이다. 이 함수는 FK 를 걸 수 없는 상황에서 쓰는 보조 수단이다. 이유는 글 끝의 “FK 와의 차이” 에 적었다.
함수
CREATE OR REPLACE FUNCTION sf_data_use_chk (
p_exclude_tables IN VARCHAR2 -- 검사에서 뺄 테이블. 자기 테이블은 꼭 넣는다. 쉼표로 여러 개
, p_col1 IN VARCHAR2 -- 확인할 컬럼 이름 1
, p_val1 IN VARCHAR2 -- 컬럼 값 1
, p_col2 IN VARCHAR2 DEFAULT NULL
, p_val2 IN VARCHAR2 DEFAULT NULL
, p_col3 IN VARCHAR2 DEFAULT NULL
, p_val3 IN VARCHAR2 DEFAULT NULL
, p_col4 IN VARCHAR2 DEFAULT NULL
, p_val4 IN VARCHAR2 DEFAULT NULL
, p_col5 IN VARCHAR2 DEFAULT NULL
, p_val5 IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2
IS
c_max CONSTANT PLS_INTEGER := 5;
v_cols sys.odcivarchar2list := sys.odcivarchar2list();
v_vals sys.odcivarchar2list := sys.odcivarchar2list(NULL, NULL, NULL, NULL, NULL);
v_ncols PLS_INTEGER;
v_excl VARCHAR2(32767) := ',' || UPPER(REPLACE(p_exclude_tables, ' ')) || ',';
v_sql VARCHAR2(32767);
v_cnt PLS_INTEGER;
v_used VARCHAR2(32767);
v_keys VARCHAR2(32767);
PROCEDURE add_key (p_col IN VARCHAR2, p_val IN VARCHAR2) IS
BEGIN
IF p_col IS NOT NULL THEN
v_cols.EXTEND;
v_cols(v_cols.COUNT) := UPPER(TRIM(p_col));
v_vals(v_cols.COUNT) := p_val;
v_keys := v_keys || ' ' || p_val;
END IF;
END add_key;
BEGIN
add_key(p_col1, p_val1);
add_key(p_col2, p_val2);
add_key(p_col3, p_val3);
add_key(p_col4, p_val4);
add_key(p_col5, p_val5);
v_ncols := v_cols.COUNT;
IF v_ncols = 0 THEN
RAISE_APPLICATION_ERROR(-20001, '확인할 컬럼을 하나 이상 넘겨야 합니다.');
END IF;
-- 넘긴 컬럼을 모두 가진 테이블만 고른다 (뷰·휴지통·임시·백업 테이블 제외)
FOR t IN (
SELECT c.table_name
, NVL(MAX(tc.comments), c.table_name) AS label
FROM user_tab_columns c
JOIN user_tables tb
ON tb.table_name = c.table_name
LEFT JOIN user_tab_comments tc
ON tc.table_name = c.table_name
WHERE c.column_name IN (SELECT column_value FROM TABLE(v_cols))
AND tb.dropped = 'NO'
AND tb.temporary = 'N'
AND c.table_name NOT LIKE 'Z\_BACKUP\_%' ESCAPE '\'
AND INSTR(v_excl, ',' || c.table_name || ',') = 0
GROUP BY c.table_name
HAVING COUNT(*) = v_ncols
ORDER BY c.table_name
) LOOP
-- 식별자는 딕셔너리에서 확인한 이름을 ENQUOTE_NAME 으로 감싸고, 값은 모두 바인드 변수로 넘긴다
v_sql := 'SELECT COUNT(*) FROM ' || DBMS_ASSERT.ENQUOTE_NAME(t.table_name, FALSE)
|| ' WHERE ROWNUM = 1';
FOR i IN 1 .. c_max LOOP
IF i <= v_ncols THEN
v_sql := v_sql || ' AND ' || DBMS_ASSERT.ENQUOTE_NAME(v_cols(i), FALSE) || ' = :v' || i;
ELSE
v_sql := v_sql || ' AND :v' || i || ' IS NULL'; -- 바인드 개수를 늘 5개로 맞춘다
END IF;
END LOOP;
EXECUTE IMMEDIATE v_sql INTO v_cnt
USING v_vals(1), v_vals(2), v_vals(3), v_vals(4), v_vals(5);
IF v_cnt > 0 THEN
v_used := v_used || CASE WHEN v_used IS NOT NULL THEN ', ' END || t.label;
END IF;
END LOOP;
IF v_used IS NULL THEN
RETURN NULL;
END IF;
RETURN SUBSTRB('[' || v_used || '] 에서 사용 중인 코드입니다. (' || LTRIM(v_keys) || ')', 1, 4000);
END sf_data_use_chk;
/- 사용 중이 아니면
NULL, 사용 중이면 어느 테이블(테이블 코멘트, 없으면 테이블 이름)에서 쓰는지 메시지를 돌려준다. - 만들어지는 SQL 은 이런 모양이다. 값은 문장에 들어가지 않고 바인드로만 넘어간다.
SELECT COUNT(*) FROM "PAYROLL" WHERE ROWNUM = 1 AND "CUST_CD" = :v1 AND :v2 IS NULL AND :v3 IS NULL AND :v4 IS NULL AND :v5 IS NULL쓰는 법
MyBatis 매퍼에서 삭제 전에 확인하는 예시다(#{} 는 MyBatis 가 바인드 변수로 바꾼다).
-- 1. 키가 1개: 거래처 삭제
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := sf_data_use_chk('CUSTOMER', 'CUST_CD', #{custCd});
IF v_msg IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20001, v_msg || ' 삭제할 수 없습니다.');
END IF;
DELETE FROM customer
WHERE cust_cd = #{custCd};
END;-- 2. 키가 2개 + 제외할 테이블이 2개(자기 테이블과 그 이력 테이블)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := sf_data_use_chk('ACCOUNT_DETAIL,ACCOUNT_DETAIL_HIST',
'ACCOUNT_CD', #{accountCd},
'DETAIL_CD', #{detailCd});
IF v_msg IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20001, v_msg || ' 삭제할 수 없습니다.');
END IF;
DELETE FROM account_detail
WHERE account_cd = #{accountCd}
AND detail_cd = #{detailCd};
END;SQL 에서 바로 확인해 볼 수도 있다.
SELECT sf_data_use_chk('CUSTOMER', 'CUST_CD', 'C0001') AS msg FROM dual;쓸 때 알아 둘 것
- 같은 컬럼 이름 규칙이 전제다. 다른 테이블에서
VENDOR_CD처럼 다른 이름으로 거래처 코드를 쓰고 있으면 찾지 못한다. 반대로 이름만 같고 뜻이 다른 컬럼이 있으면 잘못 막는다. 결과 메시지를 보고 제외 목록을 조정한다. - 성능: 그 컬럼을 가진 테이블마다 한 번씩 조회한다. 해당 컬럼에 인덱스가 없으면 테이블 전체를 읽으므로, 큰 테이블이 걸리면 삭제가 느려진다.
- 값은 문자로 넘긴다. 숫자 컬럼은 Oracle 이 값을 숫자로 바꿔 비교하므로 문제없지만,
DATE컬럼은 세션의 날짜 형식(NLS)에 따라 결과가 달라지므로 이 함수로 비교하지 않는다. 코드 값(문자·숫자) 확인용이다. - 확인 중 오류가 나면 그대로 예외를 던진다. 오류를 메시지로 바꿔 돌려주면 “사용 중” 으로 오해하거나 원인을 놓치기 쉽다.
FK 와의 차이
이 함수로는 막을 수 없는 경우가 있다.
- 동시에 일어나는 작업: 확인과 삭제 사이에 다른 세션이 그 코드를 쓰는 행을 넣고 커밋하면(또는 아직 커밋 전이라 내 확인에 안 보이면) 그대로 지워진다.
- 함수를 거치지 않는 삭제: 다른 화면이나 배치, 직접 실행한 DELETE 는 막지 못한다.
FK 는 둘 다 DB 가 막아 준다. 업무 사이에도 FK 를 거는 것이 가장 확실하다.
ALTER TABLE payroll
ADD CONSTRAINT fk_payroll_customer
FOREIGN KEY (cust_cd) REFERENCES customer (cust_cd);
-- FK 컬럼에는 인덱스를 함께 만든다
CREATE INDEX ix_payroll_cust_cd ON payroll (cust_cd);FK 컬럼에 인덱스가 없으면 부모 행을 지우거나 키를 바꿀 때 자식 테이블 전체를 읽고, 자식 테이블에 테이블 단위 잠금이 걸려 다른 작업이 기다리게 된다. FK 를 걸면 사용 중인 코드를 지울 때 ORA-02292(자식 레코드가 있음) 오류가 나므로, 화면에서는 이 오류를 알아듣기 쉬운 메시지로 바꿔 보여 주면 된다.
다시 쓰며 바로잡은 점
- SQL 인젝션: 원래 함수는 컬럼 값을
' = ''' || 값 || ''''처럼 문자열로 이어 붙여 동적 SQL 을 만들었다. 값에 작은따옴표가 들어가면 SQL 이 깨지고, 악의적인 값이면 문장이 바뀔 수 있었다. 값은 모두 바인드 변수로, 테이블·컬럼 이름은 딕셔너리에서 확인한 뒤DBMS_ASSERT.ENQUOTE_NAME으로 감싸도록 바꿨다. - 길이 초과: 모든 테이블의 조회문을
UNION ALL로 이어LISTAGG한 문장(VARCHAR2 4000)으로 만들었기 때문에, 그 컬럼을 가진 테이블이 많으면ORA-01489로 실패했다. 테이블마다 따로 조회하도록 바꿨다. - 오류 숨김:
WHEN OTHERS에서 오류 메시지를 정상 반환값으로 돌려주던 것을 없앴다. - 외부 함수 의존: 제외 테이블 목록을 나누는 데 별도의 문자열 분리 함수를 썼는데(본문의 설명과 코드의 함수 이름도 서로 달랐다),
INSTR로 바꿔 함수 하나로 동작하게 했다. - 컬럼을
USER_COL_COMMENTS에서 찾던 것을USER_TAB_COLUMNS로 바꾸고, 테이블 코멘트가 없으면 테이블 이름을 보여 주도록 했다. 지원이 끝난/*+ RULE */힌트도 뺐다. - 원래 사용 예시에 있던 문법 오류(인자 끝의 쉼표)를 고치고, 함수는
SELECT ... FROM dual없이 PL/SQL 에서 바로 부르도록 했다. - FK 로 막는 방법과, 이 함수로는 막지 못하는 경우를 덧붙였다.