«Tistory 블로그에서 작성했던 글»
정말 오랜만의 포스팅이다..
이번엔 알아두면 좋고 생각보다 많이 쓰이는 쿼리문이다.
보통 한번에 데이터를 넣거나 변경이 필요한 경우 사용한다.
실제 업무를 하다보면 데이터 마이그레이션이나 특정 데이터의 값을 일괄적으로 바꿔야 하는 경우가 생기는데, 이때 종종 사용한다.
쿼리
DECLARE v_num NUMBER; -- 변수 선언
BEGIN
v_num := 0;
FOR c1 IN (SELECT 컬럼명1
,컬럼명2
FROM 테이블명
WHERE 조건1 = xx
ORDER BY 정렬컬럼 ASC)
LOOP -- 루프 시작
v_num := v_num +1; -- 변수 +1
DBMS_OUTPUT.PUT_LINE(v_num); -- 변수값 확인
INSERT INTO 테이블(
컬럼1
,컬럼2
,컬럼3
,컬럼4
) VALUES (
값1
,값2
,값3
,v_num -- 컬럼4에 변수 값을 대입
);
UPDATE 테이블 A
SET A.컬럼1 = (SELECT REPLACE(B.컬럼1, '_', '.') || LPAD(v_num,5,'0') -- ex) 컬럼1 : No_부서명-33 >> No.부서명-00033
FROM 테이블 B
WHERE A.컬럼2 = B.컬럼2)
WHERE A.컬럼3 = c1.컬럼1
AND A.컬럼4 = c1.컬럼2;
END LOOP; -- 루프 종료
END; -- 쿼리 종료
위 예제는 특정 게시물이 등록 될 때 일련번호 채번 테이블에 데이터가 들어가고 채번된 번호를 이용해서
게시물의 일련번호를 넣어주는 쿼리이다. (소스상의 버그로 인해 일련번호가 정상적으로 들어가지 않아서 짜 본 쿼리..)
대략적인 쿼리 흐름은 아래와 같다.
1. FOR문에서 변경할 데이터의 PK 값을 추출해서 담아둔다.
2. LOOP를 돌면서 위에 선언한 변수를 1씩 증가 시켜준다.
3. 변수가 정상적으로 들어갔는지 콘솔로 확인
4. 일련번호 채번 테이블에 INSERT (이 때, 위에서 선언한 변수를 채번컬럼에 넣음)
5. 일련번호가 잘못 들어간 테이블에 일련번호를 업데이트해준다.
- 예를 들어놨듯 기존 데이터가 No_부서명-1 로 되어 있는것을 No.부서명-00001 로 변경하는 작업이다.
- REPLACE를 이용해서 No_ 를 No. 로 바꿔주고 LPAD를 이용해서 변수값 앞에 0000을 붙여주었다.
..REPLACE(변경대상문자, ‘변경대상문자’, ‘변경될문자’)
..LPAD(변경대상숫자, 자릿수, 앞에 붙일 문자)