데이터베이스 질문과 답변
채번함수에 select for update 구문 적용 문의
12
by SQL [2023.11.27 10:26:03]
[채번방식 요약]
1. 채번테이블구조는 구분값(y01, x01, x02 등...)/날짜(20230101,20230203, 20231031), 시퀀스(50001~99999)가 저장되어있으며
구분값, 날짜별로 시퀀스를 조회해와서 +1 한 값이 새 시퀀스가 된다
2. 새 시퀀스를 채번테이블에 저장한다
3. 날짜+구분값+새 시퀀스를 조합하여 리턴한다.
[수정하고자 하는 이유]
시퀀스 사용이 아니기 때문에 중복채번의 여지가 있어서 수정을 고려하게 되었습니다.
시퀀스 사용을 고려해봤으나, 채번에 사용되는 날짜가 현재날짜가 아닌 특정일을 넣고 호출하는 방식이라
시퀀스가 모두 차서 초기화되는 경우 특정일을 넣고 채번시 시퀸스가 같은 데이터가 나올 확률이 있어 배제한 상황으로
차선책으로 은행에서 잔액관리시 사용하는 select for update로 변경을 고려하게 되었습니다.
[기존함수 소스]
CREATE OR REPLACE FUNCTION Make_Sequence(
v_Module IN VARCHAR2,
v_Date IN VARCHAR2
)
RETURN VARCHAR2 IS v_issue_tax_id varchar2(16);
v_temp_id number(5);
pragma AUTONOMOUS_TRANSACTION;
BEGIN
SELECT DECODE(NVL(MAX(SERIAL), 0),0,NVL(MAX(SERIAL), 0) + 50001,NVL(MAX(SERIAL), 0) +1)
,v_Date ||v_Module || lpad(to_char(DECODE(NVL(MAX(SERIAL), 0),0,NVL(MAX(SERIAL), 0) + 50001,NVL(MAX(SERIAL), 0) +1)), 5, '00000')
into v_temp_id, v_issue_tax_id
FROM table_a
WHERE TYPE = v_Module
AND DATE = v_Date
;
IF v_temp_id = 50001 THEN
INSERT INTO table_a(TYPE, DATE, SERIAL)
VALUES (v_Module, v_Date, v_temp_id);
ELSE
UPDATE table_a SET
SERIAL = v_temp_id
WHERE TYPE = v_Module
AND DATE = v_Date;
END IF;
COMMIT;
RETURN v_issue_tax_id ;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
v_issue_tax_id := '-1';
DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX ERROR');
WHEN NO_DATA_FOUND THEN
ROLLBACK;
v_issue_tax_id := '-2';
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
ROLLBACK;
v_issue_tax_id := '-3';
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END Make_Sequence;
[수정한 함수]
CREATE OR REPLACE FUNCTION .MAKE_SEQUENCE(
V_MODULE IN VARCHAR2,
V_DATE IN VARCHAR2
)
RETURN VARCHAR2 IS V_ISSUE_TAX_ID VARCHAR2(16);
V_TEMP_ID NUMBER(5);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT coalesce((
SELECT DECODE(NVL(SERIAL, 0),0,NVL(SERIAL, 0) + 50001,NVL(SERIAL, 0) +1)
FROM table_a
WHERE TYPE = V_MODULE
AND DATE = V_DATE
), 50001),
V_DATE ||V_MODULE || LPAD(TO_CHAR(coalesce((
SELECT DECODE(NVL(SERIAL, 0),0,NVL(SERIAL, 0) + 50001,NVL(SERIAL, 0) +1)
FROM table_a
WHERE TYPE = V_MODULE
AND DATE = V_DATE
), 50001)
), 5, '00000')
INTO V_TEMP_ID, V_ISSUE_TAX_ID
FROM DUAL FOR UPDATE WAIT 5
;
DBMS_OUTPUT.PUT_LINE(V_TEMP_ID);
IF V_TEMP_ID = 50001 THEN
INSERT INTO table_a (TYPE, DATE, SERIAL)
VALUES (V_MODULE, V_DATE, V_TEMP_ID);
ELSE
UPDATE table_a SET
SERIAL = V_TEMP_ID
WHERE TYPE = V_MODULE
AND DATE = V_DATE;
END IF;
COMMIT;
RETURN V_ISSUE_TAX_ID ;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
V_ISSUE_TAX_ID := '-1';
DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX ERROR');
WHEN NO_DATA_FOUND THEN
ROLLBACK;
V_ISSUE_TAX_ID := '-2';
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
ROLLBACK;
V_ISSUE_TAX_ID := '-3';
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END MAKE_SEQUENCE;
[결과(고민)]
select for update가 dual에 적용된것같습니다ㅠㅠㅠ
만건중 40건 정도 중복되던게 1200개가 중복이 납니다ㅠㅠㅠㅠ
어떻게사용해야하는걸까요ㅠㅠ
by 마농
[2023.11.28 16:12:36]
table_a 에는 사전에 데이터가 들어 있어야 합니다. (초기값 50000 세팅)
즉, insert 가 발생할 일이 없도록, update 만 발생하도록 사전 세팅.
이 상태에서 for update 거시면 될 것 같습니다.
CREATE OR REPLACE FUNCTION Make_Sequence
( v_Module IN VARCHAR2
, v_Date IN VARCHAR2
)
RETURN VARCHAR2
IS
v_temp_id NUMBER(5);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT serial + 1
INTO v_temp_id
FROM table_a
WHERE type = v_Module
AND date = v_Date
FOR UPDATE
;
UPDATE table_a
SET serial = v_temp_id
WHERE type = v_Module
AND date = v_Date;
;
COMMIT;
RETURN v_Date || v_Module || v_temp_id ;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
RETURN '-3';
END Make_Sequence;
/
by SQL
[2023.11.28 17:52:59]
오...! 감사합니다ㅠSELECT 결과가 없는 경우 함수에서 오류를 뱉다보니 쿼리가 산으로 갔었는데 생각해보니 테이블에 애초에 데이터가 있으면 가능하긴 하겠네요 감사합니다! 그방법을 고민해보겠습니다~!ㅎㅎ
) 버튼을 클릭하여 작성 하시면 됩니다.