개발/Database
-
[PostgreSQL] 커서를 활용하는 프로시저 만들기개발/Database 2020. 2. 16. 12:24
차세대 프로젝트에서 데이터 이관 작업을 하다 보니 타겟 목록을 SELECT 하여 커서에 담은 뒤, 커서의 크기만큼 반복문을 돌며 원하는 처리를 해주는 패턴이 굉장히 유용했다. 이번에도 우연한 기회에 PostgreSQL에서 비슷한 작업을 했는데, 내가 까먹을 것 같아서 기록해둔다. 오라클 프로시저와의 차이점은 약간의 문법 차이 정도만 있고, 거의 유사한 것 같다. (유의사항 : PostgreSQL 11에서부터 프로시저를 지원) 아래 예제는 실제 사용했던 프로시저를 포스팅용으로 많이 요약한 버전으로, student 테이블에서 grade가 1인 데이터를 추출하여 커서에 담은 뒤 커서를 반복문으로 돌리면서 student_2020 테이블에서 커서에 담은 데이터의 id와 동일한 로우의 grade를 2로 업데이트해준..
-
[Oracle] 계정 잠금 해체개발/Database 2019. 7. 20. 10:20
JDBC 드라이버 통해서 쿼리를 날렸는데 갑자기 "the account is locked" 라는 메시지가 뜨면서 프로그램이 안 돌아간다는 제보를 받았다. 원인은 패스워드 유효기간 만료일수도 있고, 관리자가 직접 계정 lock을 걸어서 계정이 잠겼을 수도 있다는데 나도 DB 무식자다보니 정확한 원인은 모르겠다. 아마도 다시 DBA를 만나게 되면 여쭤볼 수는 있겠다만 언제 오실지 모르니... 우선 아래 쿼리를 날려서 잠긴 계정을 확인 1 2 3 4 5 6 SELECT * FROM DBA_USERS WHERE ACCOUNT_STATUS LIKE 'LOCKED%'; cs 전달받은 대로 계정 하나가 잠겨있는 상태였다. 잠긴 계정을 푸는 쿼리도 날려본다. 1 2 3 ALTER USER [USER_NAME] ACCO..
-
[Oracle] 시퀀스의 현재값 변경하기개발/Database 2019. 5. 8. 22:17
운영 서버에 있던 DB 오브젝트들을 개발 서버에 옮겨서 테스트하던 중 에러가 났다. 에러 로그를 보니 대체 왜 이렇게 된건지 정확히 파악은 안되지만 시퀀스의 현재 값이 실제 데이터의 Max 값보다 한참 작은 상태라 인서트 쿼리를 실행하면 중복 값이 들어가면서 에러가 나는 상황..... 무식하게 계속 .nextval 로 맞춰주기엔 값이 좀 많이 차이나서 부득이하게 머리를 써서 시퀀스의 현재값을 변경해줘야 했다. 가장 편리한건 역시 그냥 시퀀스 자체를 드롭한 후에 실제 데이터에 맞춰서 다시 생성해주는 것이겠지만 이미 운영 중인 서비스라면 그냥 드롭시키기 무서울 수도 있기에..... 아래처럼 약간의 꼼수(?)를 써서 바꿔주면 해결 1 2 3 4 5 6 7 8 9 --예) 현재 시퀀스값은 20인데 데이터값을 9..
-
[MySQL] MySQL에서 ROW_NUMBER 사용하기개발/Database 2019. 2. 19. 17:43
오라클, 티베로..등에서 쿼리를 짤 때 툭하면 ROW_NUMBER() OVER 를 줘서 뽑다가 MySQL로 오니 당연히 신택스 에러가 떨어진다. MySQL에서는 특이하게도 SELECT 절에서 function 을 활용한다거나 이런 방식이 아니라변수를 선언하고 이 변수에 1씩 더해서 뽑아내는 방식으로 Row num을 줄 수 있다. 알고보니 MySQL에서는 쿼리에서도 변수를 선언해서 사용할 수 있고이걸 응용해서 뽑아내는 것 같다. 일단 쿼리를 보자면 요렇게 123456789101112SELECT @ROWNUM := @ROWNUM + 1 AS ROWNUM ,TB.* FROM ( SELECT NOTE_NO ,NOTE_TITLE ,NOTE_CONTENTS FROM EXAMPLE.NOTE ORDER BY NOTE_N..
-
[postgreSQL] 멀쩡한 컬럼인데 column does not exist 오류가 발생할 때개발/Database 2019. 1. 17. 09:37
이번에 시작한 토이 플젝에는 한번도 안 써본 디비를 써보자! 해서 야심차게 postgreSQL을 도입하지만 처음 짜넣은 아주 간단한 select 문 실행에서부터 막혀버렸다ㅠ.ㅠ SELECT COUNT(*) FROM V WHERE VOCA = #{voca} 라는 아주 아주 간단한 쿼리 문인데도 에러가 뜨면서 Column 'voca' does not exist 라고 찍힌다. 대체 왜??? 혹시나 오타가 났나 싶어서 다시 들여다봤지만 오타도 아니었다. 검색해보니 내가 지금까지 써본 DB는 대문자가 기본이고, 컬럼이나 테이블명에서는 대소문자 구별이 없어서 쿼리 짤 때 대문자로 쓰든 소문자로 쓰든 상관이 없었으나postgreSQL은 소문자가 기본인게 모든 것의 원인이었다..... (출처 : https://post..
-
SYS_CONTEXT 함수로 세션정보 추출하기개발/Database 2019. 1. 14. 13:19
세션에 담긴 접속IP주소를 추출하려면 당연히 자바 컨트롤러에서 뽑아내는 것이라고 생각했는데이번 프로젝트를 하면서 DB에서 쿼리로 뽑아낼 수도 있다는 것을 알았다. 지금까지 확인한 바로는 오라클과 티베로에서 SYS_CONTEXT 라는 함수를 사용하여 추출이 가능하다. (출처 : https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm) 사용법- SYS_CONTEXT('namespace' , 'parameter') - namespace 로 'userenv' 를 입력하면 현재 세션의 환경정보 추출 가능- 예 : IP 주소 추출 시SELECT SYS_CONTEXT('userenv', 'IP_ADDRESS') FROM DUAL;
-
TABLE Function 이라는 것도 있다개발/Database 2019. 1. 9. 14:20
DB 담당 수석님께서 짜서 던져주신 쿼리를 보다가 난생 처음 보는 신택스를 만나서 검색오늘도 출처는 구루비 (http://www.gurubee.net/lecture/2238) 아직 내용을 완벽하게 이해한 것이 아니라 간단하게 요약만 남기고 끝낸다...흐흑 Table Function - Result Set 전체를 인자 값으로 받아서 결과를 Return 하고자 할 경우 유용하게 사용 가능 - Table Function / Pipelined Table Function - SELECT * FROM TABLE( Row 출력 Function ) 형태로 사용 가능