"오라클 마스킹 함수"의 두 판 사이의 차이
DB CAFE
1번째 줄: | 1번째 줄: | ||
== 오라클 마스킹 처리 == | == 오라클 마스킹 처리 == | ||
+ | === 마스킹 처리 함수 == | ||
+ | <source lang=sql> | ||
+ | CREATE OR REPLACE FUNCTION FN_MASKING(PV_STR VARCHAR, PN_START NUMBER, PN_COUNT NUMBER, PC_MARK CHAR DEFAULT '*', PN_DEBUG NUMBER DEFAULT 0) | ||
+ | RETURN VARCHAR | ||
+ | IS | ||
+ | VN_LEN NUMBER; -- 문자열 길이 | ||
+ | VN_POS1 NUMBER; -- 마스킹 시작 위치 - 1 | ||
+ | VN_POS2 NUMBER; -- 마스킹 종료 위치 + 1 | ||
+ | |||
+ | VV_MASK VARCHAR2(4000); | ||
+ | VV_RETURN VARCHAR2(4000); | ||
+ | -- VV_MSG VARCHAR2(100); -- 디버그 메시지 출력시 사용 -- FOR DEBUG | ||
+ | /* TEST CASE *************************************************************** | ||
+ | SELECT DATA||', '||P1||', '||P2||', '||P3 PARAM, FN_MASKING(DATA, P1, P2, P3, PN_DEBUG=>0) RESULT | ||
+ | FROM (SELECT '1234567890' DATA FROM DUAL UNION ALL SELECT '가나다라마바사아자차' FROM DUAL) A | ||
+ | , (SELECT 0 P1, 0 P2, '*' P3 FROM DUAL | ||
+ | UNION ALL SELECT 1, 1, '*' FROM DUAL | ||
+ | UNION ALL SELECT 1, 5, '*' FROM DUAL | ||
+ | UNION ALL SELECT 1, 100, '*' FROM DUAL | ||
+ | UNION ALL SELECT 2, 100, '*' FROM DUAL | ||
+ | UNION ALL SELECT 5, 1, '*' FROM DUAL | ||
+ | UNION ALL SELECT 5, 2, '*' FROM DUAL | ||
+ | UNION ALL SELECT 5, 10, '*' FROM DUAL | ||
+ | UNION ALL SELECT 5, 0, '*' FROM DUAL | ||
+ | UNION ALL SELECT 5, -1, '*' FROM DUAL | ||
+ | UNION ALL SELECT 5, -5, '*' FROM DUAL | ||
+ | UNION ALL SELECT -5, 1, '*' FROM DUAL | ||
+ | UNION ALL SELECT -5, 5, '*' FROM DUAL | ||
+ | UNION ALL SELECT -5, 0, '*' FROM DUAL | ||
+ | UNION ALL SELECT -5,-10, '*' FROM DUAL | ||
+ | UNION ALL SELECT 10, 1, '*' FROM DUAL | ||
+ | UNION ALL SELECT 10, 2, '*' FROM DUAL | ||
+ | UNION ALL SELECT 10, 0, '*' FROM DUAL | ||
+ | UNION ALL SELECT 10, -1, '*' FROM DUAL | ||
+ | UNION ALL SELECT 10,-10, '*' FROM DUAL | ||
+ | UNION ALL SELECT 11, 11, '*' FROM DUAL ) B | ||
+ | */ | ||
+ | |||
+ | BEGIN | ||
+ | IF PV_STR IS NULL THEN -- 널이면 그대로 리턴 | ||
+ | VV_RETURN := PV_STR; | ||
+ | ELSE | ||
+ | VN_LEN := LENGTH(PV_STR); | ||
+ | IF PN_START >= VN_LEN THEN -- 시작위치가 문자길이 이상이면 마지막 자릿수 - 1; | ||
+ | VN_POS1 := VN_LEN - 1; | ||
+ | ELSIF PN_START < 0 THEN -- 시작위치가 음수일 경우 뒤에서 부터 위치 선정, 최소 시작값 1 | ||
+ | VN_POS1 := GREATEST(VN_LEN + PN_START, 1); | ||
+ | ELSE | ||
+ | VN_POS1 := PN_START - 1; | ||
+ | END IF; | ||
+ | |||
+ | IF PN_COUNT < 1 THEN -- 마스킹 자릿수가 음수이면 뒤에서 -N번째 이전까지 마스킹 | ||
+ | VN_POS2 := GREATEST(VN_LEN + PN_COUNT + 1, VN_POS1 + 2); -- 최소 1개 마스킹 위해 POS1 + 2 | ||
+ | ELSE | ||
+ | VN_POS2 := LEAST(VN_LEN + 1, VN_POS1 + PN_COUNT + 1); | ||
+ | END IF; | ||
+ | |||
+ | -- VV_MSG := '[DEBUG]: ' ||TO_CHAR(VN_POS1)||', '||TO_CHAR(VN_POS2); -- FOR DEBUG | ||
+ | |||
+ | -- FOR IX IN VN_POS1..(VN_POS2-2) -- 각각 -1, +1로 인해 2의 GAP이 있음 | ||
+ | -- LOOP | ||
+ | -- VV_MASK := VV_MASK || PC_MARK; | ||
+ | -- END LOOP; | ||
+ | VV_MASK := RPAD(PC_MARK, VN_POS2 - VN_POS1 -1, PC_MARK); | ||
+ | |||
+ | IF VN_POS1 < 1 THEN | ||
+ | VV_RETURN := VV_MASK || SUBSTR(PV_STR, VN_POS2); | ||
+ | ELSIF VN_POS2 > VN_LEN THEN | ||
+ | VV_RETURN := SUBSTR(PV_STR, 1, VN_POS1) || VV_MASK; | ||
+ | ELSE | ||
+ | VV_RETURN := SUBSTR(PV_STR, 1, VN_POS1) || VV_MASK || SUBSTR(PV_STR, VN_POS2); | ||
+ | END IF; | ||
+ | |||
+ | END IF; | ||
+ | |||
+ | IF PN_DEBUG = 1 THEN | ||
+ | -- RETURN VV_RETURN||VV_MSG; -- FOR DEBUG | ||
+ | RETURN VV_RETURN||'[DEBUG]: ' ||TO_CHAR(VN_POS1)||', '||TO_CHAR(VN_POS2); -- FOR DEBUG | ||
+ | ELSE | ||
+ | RETURN VV_RETURN; | ||
+ | END IF; | ||
+ | --EXCEPTION WHEN OTHERS THEN -- FOR DEBUG | ||
+ | -- RETURN SQLERRM||VV_MSG; -- FOR DEBUG | ||
+ | END; | ||
+ | / | ||
+ | </source> | ||
<source lang=sql> | <source lang=sql> | ||
-------------------------------------------------------------------------------- | -------------------------------------------------------------------------------- |
2020년 5월 18일 (월) 16:15 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 오라클 마스킹 처리[편집]
2 = 마스킹 처리 함수[편집]
CREATE OR REPLACE FUNCTION FN_MASKING(PV_STR VARCHAR, PN_START NUMBER, PN_COUNT NUMBER, PC_MARK CHAR DEFAULT '*', PN_DEBUG NUMBER DEFAULT 0)
RETURN VARCHAR
IS
VN_LEN NUMBER; -- 문자열 길이
VN_POS1 NUMBER; -- 마스킹 시작 위치 - 1
VN_POS2 NUMBER; -- 마스킹 종료 위치 + 1
VV_MASK VARCHAR2(4000);
VV_RETURN VARCHAR2(4000);
-- VV_MSG VARCHAR2(100); -- 디버그 메시지 출력시 사용 -- FOR DEBUG
/* TEST CASE ***************************************************************
SELECT DATA||', '||P1||', '||P2||', '||P3 PARAM, FN_MASKING(DATA, P1, P2, P3, PN_DEBUG=>0) RESULT
FROM (SELECT '1234567890' DATA FROM DUAL UNION ALL SELECT '가나다라마바사아자차' FROM DUAL) A
, (SELECT 0 P1, 0 P2, '*' P3 FROM DUAL
UNION ALL SELECT 1, 1, '*' FROM DUAL
UNION ALL SELECT 1, 5, '*' FROM DUAL
UNION ALL SELECT 1, 100, '*' FROM DUAL
UNION ALL SELECT 2, 100, '*' FROM DUAL
UNION ALL SELECT 5, 1, '*' FROM DUAL
UNION ALL SELECT 5, 2, '*' FROM DUAL
UNION ALL SELECT 5, 10, '*' FROM DUAL
UNION ALL SELECT 5, 0, '*' FROM DUAL
UNION ALL SELECT 5, -1, '*' FROM DUAL
UNION ALL SELECT 5, -5, '*' FROM DUAL
UNION ALL SELECT -5, 1, '*' FROM DUAL
UNION ALL SELECT -5, 5, '*' FROM DUAL
UNION ALL SELECT -5, 0, '*' FROM DUAL
UNION ALL SELECT -5,-10, '*' FROM DUAL
UNION ALL SELECT 10, 1, '*' FROM DUAL
UNION ALL SELECT 10, 2, '*' FROM DUAL
UNION ALL SELECT 10, 0, '*' FROM DUAL
UNION ALL SELECT 10, -1, '*' FROM DUAL
UNION ALL SELECT 10,-10, '*' FROM DUAL
UNION ALL SELECT 11, 11, '*' FROM DUAL ) B
*/
BEGIN
IF PV_STR IS NULL THEN -- 널이면 그대로 리턴
VV_RETURN := PV_STR;
ELSE
VN_LEN := LENGTH(PV_STR);
IF PN_START >= VN_LEN THEN -- 시작위치가 문자길이 이상이면 마지막 자릿수 - 1;
VN_POS1 := VN_LEN - 1;
ELSIF PN_START < 0 THEN -- 시작위치가 음수일 경우 뒤에서 부터 위치 선정, 최소 시작값 1
VN_POS1 := GREATEST(VN_LEN + PN_START, 1);
ELSE
VN_POS1 := PN_START - 1;
END IF;
IF PN_COUNT < 1 THEN -- 마스킹 자릿수가 음수이면 뒤에서 -N번째 이전까지 마스킹
VN_POS2 := GREATEST(VN_LEN + PN_COUNT + 1, VN_POS1 + 2); -- 최소 1개 마스킹 위해 POS1 + 2
ELSE
VN_POS2 := LEAST(VN_LEN + 1, VN_POS1 + PN_COUNT + 1);
END IF;
-- VV_MSG := '[DEBUG]: ' ||TO_CHAR(VN_POS1)||', '||TO_CHAR(VN_POS2); -- FOR DEBUG
-- FOR IX IN VN_POS1..(VN_POS2-2) -- 각각 -1, +1로 인해 2의 GAP이 있음
-- LOOP
-- VV_MASK := VV_MASK || PC_MARK;
-- END LOOP;
VV_MASK := RPAD(PC_MARK, VN_POS2 - VN_POS1 -1, PC_MARK);
IF VN_POS1 < 1 THEN
VV_RETURN := VV_MASK || SUBSTR(PV_STR, VN_POS2);
ELSIF VN_POS2 > VN_LEN THEN
VV_RETURN := SUBSTR(PV_STR, 1, VN_POS1) || VV_MASK;
ELSE
VV_RETURN := SUBSTR(PV_STR, 1, VN_POS1) || VV_MASK || SUBSTR(PV_STR, VN_POS2);
END IF;
END IF;
IF PN_DEBUG = 1 THEN
-- RETURN VV_RETURN||VV_MSG; -- FOR DEBUG
RETURN VV_RETURN||'[DEBUG]: ' ||TO_CHAR(VN_POS1)||', '||TO_CHAR(VN_POS2); -- FOR DEBUG
ELSE
RETURN VV_RETURN;
END IF;
--EXCEPTION WHEN OTHERS THEN -- FOR DEBUG
-- RETURN SQLERRM||VV_MSG; -- FOR DEBUG
END;
/
--------------------------------------------------------------------------------
-- 읍,면,동 마스킹 처리
--------------------------------------------------------------------------------
WITH T
AS(
SELECT '서울시 용산구 논현동 114번지' AS TEST FROM DUAL UNION ALL
SELECT '부천시 소사구 심곡본동 527' AS TEST FROM DUAL UNION ALL
SELECT '강원도 횡성군 청일면 신대리' AS TEST FROM DUAL UNION ALL
SELECT '강원도 횡성군 청일면 소포동길 26' AS TEST FROM DUAL UNION ALL
SELECT '강원도 횡성군 차동면 소포동길 26' AS TEST FROM DUAL UNION ALL
SELECT '강원도 진도군 진도읍 소포리' AS TEST FROM DUAL
)
SELECT
REGEXP_REPLACE(TEST, '[^ ]+([동리가로])', '**\1') AS JUSO
, REGEXP_REPLACE(TEST, '[^ ]+([동리가로])', '**\2') AS JUSO
, REGEXP_REPLACE(TEST, '[^ ]+([동리가로])', '**\1') AS JUSO
, REGEXP_REPLACE(TEST, '[^ ]+([읍면동가로])', '**\1') AS JUSO
FROM T
;
-- 결과
1 서울시 용산구 **동 114번지 서울시 용산구 ** 114번지 서울시 용산구 **동 114번지 서울시 용산구 **동 114번지
2 부천시 소사구 **동 527 부천시 소사구 ** 527 부천시 소사구 **동 527 부천시 소사구 **동 527
3 강원도 횡성군 청일면 **리 강원도 횡성군 청일면 ** 강원도 횡성군 청일면 **리 강원도 횡성군 **면 신대리
4 강원도 횡성군 청일면 **동길 26 강원도 횡성군 청일면 **길 26 강원도 횡성군 청일면 **동길 26 강원도 횡성군 **면 **동길 26
5 강원도 횡성군 **동면 **동길 26 강원도 횡성군 **면 **길 26 강원도 횡성군 **동면 **동길 26 강원도 횡성군 **면 **동길 26
6 강원도 진도군 진도읍 **리 강원도 진도군 진도읍 ** 강원도 진도군 진도읍 **리 강원도 진도군 **읍 소포리
;