Oracle Update관련 채번 방법을 알고싶어요 0 7 4,631

by stevekim [PL/SQL] 채번 Update 재귀 [2024.01.16 15:50:09]


UPDATE wu_hak.th_gyoj_compl_prearnge
SET CRQFC_NO = (select max(to_number(a.crqfc_no))+1 from wu_hak.th_gyoj_compl_prearnge a)
  , UPD_ID   = UPPER('steve')
  , UPD_DT   = sysdate
WHERE 1=1
  AND YEAR='2001'
  and reqst_odr   = '2180100'
  and crqfc_no    is NULL;
--#######################################

자기 자신(table)을 참조로하여 max+1 해서 Update하는데 안되는군요. 물론 시쿼스 테이블 사용하면 됩니다만 그것을 사용하지 않고 자기 자신을 참조했으면 좋겠습니다. commit이 없어서 그런지 max카운트가 똑 같네요. ㅠㅠ
단순한것 같은데 잘모르겠어요. 도움을 주시면 감사하겠습니다.

by 마농 [2024.01.16 16:17:37]
UPDATE wu_hak.th_gyoj_compl_prearnge
   SET crqfc_no = (SELECT MAX(TO_NUMBER(a.crqfc_no)) FROM wu_hak.th_gyoj_compl_prearnge) + ROWNUM
     , upd_id   = UPPER('steve')
     , upd_dt   = sysdate
 WHERE 1=1
   AND year = '2001'
   AND reqst_odr = '2180100'
   AND crqfc_no IS NULL
;

 


by stevekim [2024.01.16 16:20:13]

헉!!!! 
마농님 정말 감사드립니다 ^^
이런수가 있었네요.
마농님은 제 SQL의 정신적 지주님!!!!^^


by stevekim [2024.01.17 10:51:57]

마농님 질문있습니다.

상기 (SELECT MAX(TO_NUMBER(a.crqfc_no)) FROM wu_hak.th_gyoj_compl_prearnge) 스칼라 쿼리문을 

GET_SEQNO_TEACHING_CERTIFICATE() 란 함수로 만들어서 update문을 수정.대체하면 에러가 뜨는데 이유가 무엇일까요?
물론, Select문등 조회에서는 전혀 상관없이 잘 나옵니다.

함수는 다음과 같습니다.

create or replace function WU_HAK.GET_SEQNO_TEACHING_CERTIFICATE
return varchar2 is
      i_cnt        integer := 0;
      v_crqfc_no   varchar2(6) := '';
--#############################################################
--교원자격증 번호 부여
--#############################################################
BEGIN
      select max(to_number(crqfc_no)) + 1
      into   i_cnt
      from   wu_hak.th_gyoj_compl_prearnge;

      IF (i_cnt > 9388) THEN
         v_crqfc_no := lpad(TO_CHAR(i_cnt),6,'0');
      ELSE
         v_crqfc_no := '009388';
      END IF;

      return v_crqfc_no; 

    EXCEPTION
    when NO_DATA_FOUND THEN RETURN 'NoData';
    when OTHERS        then  return sqlerrm; --여기 에러를 뱉어내는군요.
END;

by 마농 [2024.01.17 11:19:12]

대상 테이블에 대한 업데이트 구문에서
대상 테이블을 참조하는 함수를 사용하면 에러가 발생하게 됩니다.
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it


by stevekim [2024.01.17 21:18:13]

감사합니다. 그래서  스칼라 쿼리를 사용하는군요. 크게 한 수 배웠습니다


by 마농 [2024.01.18 10:06:23]

함수 쿼리를 보면
해당 순번이 가변자리수 문자가 아닌 고정자리수 문자인 것으로 보입니다.
이 경우 굳이 숫자로 치환하여 MAX 하는 것은 비효율입니다.
그냥 MAX 하는 것이 효율적입니다.
가변자리수라면 정확한 자료 추출을 위해 TO_NUMBER 해야 하는것이 맞지만
고정자리수라면 TO_NUMBER 는 필요 없기도 하고, 비효율이기도 합니다.


by stevekim [2024.01.22 10:06:25]

가르침 감사드립니다

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입