공부하자 - 1000dB

<서론>

프로젝트 중 "java.sql.BatchUpdateException: 업데이트에 대한 결과 집합 이 생성되었습니다." 라는 오류 메시지가 콘솔창에 출력되었다.

처음 사용하는 프레임워크로 프로젝트를 진행하고 있기 때문에 프레임워크 문제인지도 많이 고민했었다.

혼자 고민해보고 이곳저곳 고쳐보다가 제발 있었으면 좋겠다 라는 심정으로 구글링한 결과, 해결책을 찾을 수 있었다.

역시 구글은 모든 걸 알고있다.

<본론>

1. sqljdbc4.jar 파일 문제

sqljdbc의 버전에 의해 문제가 발생할 수 있다는 글을 찾았다. 운영도 되고 있는 서비스라 sqljdbc 파일을 변경 후 검증가지 필요하므로 막무가내로 파일을 변경하기에는 조금 무리가 있어 최후의 수단으로 남겨두고 다른 방법을 찾았다.

2. 프로시저 내의 "NO COUNT ON"

프로시저의 "NO COUNT ON" 구문이 해당 문제를 야기한다는 글을 찾았다. 프로시저 마지막에서 리턴받을 데이터가 없는 처리용일 때는 해당 명령을 빼주면 오류가 발생하지 않는다고 한다.

일단 내 쿼리는 리턴값이 있었지만, 속는 셈 치고 해당 명령을 주석처리한 결과 오류는 해결되었다.

<결론>

엄밀히 말하면 완벽한 해결은 아니다. 위에 언급했듯이 내 프로시저에서는 리턴값이 있는 프로시저였음에도 위와 같은 오류가 발생했다. 정말 sqljdbc4.jar 파일 문제일 수도 있지만 아직 개발 초보라 NO COUNT ON이 정확히 어떤 명령어인지 파악하지 못했다.

NO COUNT ON 을 좀 더 공부해보고, 내 프로시저에 어떤 문제가 있었는지 분석한 다음 NO COUNT ON에 대한 정리글도 포스팅하겠다.

<참고자료>

https://blog.naver.com/PostView.nhn?blogId=lkssky2002&logNo=220477127663&proxyReferer=http%3A%2F%2Fwww.google.com%2Furl%3Fsa%3Dt%26rct%3Dj%26q%3D%26esrc%3Ds%26source%3Dweb%26cd%3D1%26ved%3D2ahUKEwjW0v3B05PnAhXMc3AKHYlyBhwQFjAAegQIAxAB%26url%3Dhttp%253A%252F%252Fm.blog.naver.com%252Flkssky2002%252F220477127663%26usg%3DAOvVaw08rR0Nv06OhNtsgJT1y43I

<서론>

프로젝트에서 사용하던 DB가 MSSQL로 되어 있어서 MSSQL을 사용해볼 수 있었다. 그러다보니 문자열을 자르거나, 문자열을 찾거나 하는 등의 처리를 하는 일이 있었는데, 자주 쓰기보다 어쩌다 한 번 나오는 경우라 기억하지 못하고 계속 구글링으로 찾아봤다. 그렇게 계속 찾기엔 귀찮아서 내 블로그에 정리하려 한다. 앞으로는 내 블로그 봐야지 히힛.

<본론>

1. 문자 길이 확인 (LEN, DATALENGTH)

1-1. 글자 수 확인

LEN( string_expression ) / return int
ex) LEN( 'Hello' ) / return 5

하지만 LEN은 자료형이 CHAR, VARCHAR인 경우에 사용할 수 있다(N이 붙어도 가능). 이 점을 잊고 쿼리를 짜다간 깊은 혼란에 빠질 수 있다.

1-2. byte 수 확인

DATALENGTH( expression ) / return int
ex) DATALENGTH( 'Hello' ) / return 5

입력 변수에 TEXT나 NTEXT 등도 올 수 있으며 심지어 IMAGE도 가능하다고 한다.

Byte를 체크하기 때문에 한글인 경우에는 글자 수보다 크게 나온다.

2. 문자열 자르기(LEFT, RIGHT, SUBSTRING)

2-1. 문자 앞(왼쪽)에서부터 자르기

LEFT( string_expression, integer_expression ) / return (varchar or nvarchar)
ex) LEFT( '123456', 3 ) / return '123'

자르기를 원하는 문자열을 첫번째 파라메터로 넣고, 몇글자 자를 건 지 숫자를 두번째 파라메터로 넣는다.

입력하는 문자열은 varchar, nvarchar, char, nchar 만 가능하다. text나 ntext는 사용할 수 없다.

입력하는 문자열이 유니코드인 문자 데이터 형식인 경우 nvarchar를 return

입력하는 문자열이 유니코드가 아닌 문자 데이터 형식인 경우 varchar를 return

2-2. 문자 뒤(오른쪽)에서부터 문자 자르기

RIGHT( string_expression, integer_expression ) / return (varchar or nvarchar)
ex) RIGHT( '123456', 3 ) / return '456'

문자 앞에서부터 자르는 방식과 동일하다.

2-3. 중간부터 자르기

SUSBSTRING( expression, start, length ) / return (varchar or nvarchar or varbinary)
ex) SUBSTRING( '123456', 3, 2 ) / return '34'

입력받은 문자열의 중간을 잘라낼 때 사용. 시작값은 0이 아니라 1이다.

입력할 수 있는 데이터 형식은 char, binary, text, image가 가능하다.

입력값의 데이터 형식에 따른 리턴값의 데이터 형식은 다음과 같다.

char, varchar, text / return varchar

nchar, nvarchar, ntext / return nvarchar

binary, varbinary, image / reutnr varbinary

3. 숫자형인지 확인(ISNUMERIC)

ISNUMERIC( expression ) / return int
ex) ISNUMERIC( '123456' ) / return 1
ex2) ISNUMERIC('123k456') / return 0

입력한 문자열이 숫자형으로 변환이 가능한 지 확인하는 함수.

입력한 문자열이 숫자형으로 변환이 가능한 경우 1을 불가능한 경우에는 0을 반환한다.

4. 문자열 치환(REPLACE)

REPLACE( string_expression, string_pattern, string_replacement ) / return (varchar or nvarchar)
ex) REPLACE( '123456789123', '123', 'test' ) / return 'test456789test'

문자열 치환은 텍스트 에디터에서 쓰는 기능과 동일하다. 다만 하나만 바꾸는 것이 아니라 찾은 패턴은 모두 치환해 버린다.

입력한 문자열이 varchar나 nvarchar의 최댓값을 초과하는 값이라면 반환값을 8,000 byte에서 자른다.

5. 문자열 찾기(CHARINDEX, PATINDEX)

CHARINDEX( expression_to_find, expression_to_search [, start_location] ) / return (varchar or bigint or int)
ex) CHARINDEX( '123', '123456789123' ) / return 1
ex2) CHARINDEX( '123', '123456789123', 2) / return 10

처음 입력값을 두번째 입력한 값 내에서 찾고 첫번째 위치를 반환하는 함수. 세번째 입력 값으로 검색하는 위치를 조정할 수도 있다. 찾는 값이 없을 경우, 0을 반환한다.

5-1. 패턴 처음 위치

PATINEDX( '%pattern%', expression ) / return (varchar or nvarchar or bigint or int)
ex) PATINDEX( '%123%', '123456789123' ) / return 1
ex) PATINDEX( '123', '123456789123' ) / return 0

CHARINDEX와 비슷하지만 패턴을 LIKE 처럼 찾는다. CHARINDEX와 마찬가지로 찾는 패턴이 없을 경우 0을 반환한다.

6. 문자열 끝의 공백 지우기(LTRIM, RTRIM)

6-1. 문자 앞(왼쪽) 공백 지우기

LTRIM( character_expression ) / return (varchar or nvarchar)
ex) LTRIM( '        he llo  ') / return 'he llo  '

문자 앞(왼쪽)에 있는 공백을 제거하는 함수. 문자 끝(오른쪽)이나 중간의 공백은 제거하지 않는다.

6-2. 문자 뒤(오른쪽) 공백 지우기

RTRIM( character_expression ) / return (varchar or nvarchar)
ex) LTRIM( '        he llo  ') / return '        he llo')

문자 앞(왼쪽)에 있는 공백을 제거하는 함수. 문자 끝(오른쪽)이나 중간의 공백은 제거하지 않는다.

번외. 문자열 어디에 있든 공백을 한꺼번에 지울 땐 REPLACE를 사용

7. 공백 넣기(SPACE)

SPACE( integer_expression ) / return varchar
ex) SPACE( 3 ) / return '   '

입력한 숫자만큼 공백을 반환하는 함수. 만약 숫자가 음수라면 Null 을 반환한다.

번외. 유니코드 데이터에 공백을 포함하거나 8,000개가 넘는 공백을 반환하려면 SPACE 대신 REPLICATE를 사용

8. ASCII 코드 변환(ASCII, CHAR)

ASCII( character_expression ) / return int
CHAR ( integer_expression / return char
ex) ASCII( 'A' ) / return 65
ex2) CHAR( 65 ) / return 'A'

ASCII와 문자로 변환하는 함수.

CHAR와 관련해서는 몇 가지 제어 문자를 삽입할 수 있다.

CHAR(9)   : 탭

CHAR(10: : 줄바꿈

CHAR(13) : 캐리지 리턴

9. 대소문자 변환(UPPER, LOWER)

UPPER( character_expression ) / return (varchar or nvarchar)
LOWER( character_expression ) / return (varchar or nvarchar)
ex) UPPER( 'AbcdE' ) / return 'ABCDE'
ex2) LOWER( 'AbcdE' ) / return 'abcde'

입력받은 문자열을 모두 대문자 혹은 소문자로 변경하는 함수. 대소문자가 섞여있는 문자열을 비교할 때, 대문자 혹은 소문자로 맞추기 위해 사용하기도 한다.

10. 문자 반복(REPLICATE)

REPLICATE( string_expression, integer_expression ) / return (string_expression과 같은 형식)
ex) REPLICATE( '0', 4 ) / return '0000'

원하는 문자열을 원하는 횟수만큼 반복하는 함수. 숫자로 이루어진 데이터의 앞에 원하는 만큼 0을 넣어 자리를 맞춰주는 데 쓰일 수 있다.

11. 문자열을 반대로(REVERSE)

REVERSE( string_expression ) / return (varchar or nvarchar)
ex) REVERSE( 'abcdEABcdeFg' ) / return 'gFedcBAEdcba'

입력한 문자열을 거꾸로 반환하는 함수. 대소문자도 당연히 구분한다.

<결론>

문자열과 관련된 내장함수가 적진 않지만 자주 쓴다면 못 외울 것도 없다. 하지만 이렇게 많은 종류를 어쩌다 한 번씩 쓰니 일일히 외우고 다니기엔 여간 귀찮은 일이 아니다. 그리고 이번 프로젝트가 끝난다면 언제 또 MSSQL을 만나게 될지도 알 수 없다. 오라클 열심히 하자.

<참고자료>

1. https://docs.microsoft.com/ko-kr/sql/t-sql/language-reference?view=sql-server-ver15

<서론>

프로젝트 진행 중, 전날까지 잘되던 프로젝트가 서버 구동만 하면 콘솔에 어떤 글자가 써지기도 전에 오류 메시지창이 뜨면서 서버가 시작도 하지 않는 것이다. 잘되다가 갑자기 왜 그럴까 하고 구글링.. 비슷한 에러들은 많이 보이지만 나와 같은 에러는 없었다. 아마 "프롤로그에서는 콘텐츠가 허용되지 않습니다" 라는 말이 한글이라 검색결과가 잘 안나온 것 같아 이 부분을 "Content is not allowed in prolog" 로 바꾸고 검색했더니 스택오버플로우에 나왔다. 스택오버플로우 분들 감사합니다.

<본론>

헛소리는 이쯤하고 해결법부터 적어본다.

1. Eclipse 종료
2. [workspace]/.metadata/.plugins/org.eclipse.wst.server.core 폴더로 이동
3. tmp# 폴더를 삭제(#에는 번호가 들어간다. 예. tmp0, tmp1)
4. Eclipse 실행
5. tomcat, project clean

이렇게 했더니 처음처럼 아주 잘 돌아간다.

<결론>

tmp 폴더 내의 xml 파일이 꼬여서 그런 것 같기때문에 tmp 폴더를 그냥 다 날림으로써 해결했다. 매우매우 간단한 문제였는데, 한글로 검색하니 안나오고, 영어로 검색하니 나와서 다행이었다. 순간적으로 '영어로 검색해보자!' 라는 생각을 안했다면 아마 하루종일 고생했겠지..

다시한번 스택오버플로우 회원들에게 감사한다.

<참조>

https://stackoverflow.com/questions/36196734/removing-obsolete-files-from-server-could-not-clean-server-of-obsolete-files

ISNULL( SUM( column ), 0 ) - 권장

SUM( ISNULL( column, 0 ) ) - 이건 성능 저하. 무의미한 함수 발생

Null값은 연산을 하지 않으니 굳이 NVL 혹은 ISNULL 필요 없음

Oracle에서는 공백('')은 null 취급하지만

MSSQL에서는 공백과 null이 다르다... 이거 주의하자!

2020.01.31 추가

Null 값은 비교할 수 없다.

예)CASE WHEN A.col = '0' THEN ...

예2) CASE WHEN ISNULL(A.col, '0') = '0' THEN -- orcale : NVL(A.col,'0')

예)와 같은 쿼리에서 많약 A.col에 Null 값이 있다면 해당 조건은 무조건 false를 반환.

예2)와 같이 Null을 처리해줘야 CASE 문이 원하는데로 작동한다.

A.col = Null - 잘못된 방식

A.col IS NULL - 올바른 방식

A.col <> Null - 잘못된 방식

A.col IS NOT NULL - 올바른 방식

위에도 가끔 실수한다. 잘 고치자.

MAX( ISNULL( A.col, 0 ))

ISNULL( MAX( A.col ), 0)

큰 차이는 없지만 가끔 값이 다를 수 있다. Null 처리할 땐 조심하자

해당 내용은 내가 사용하려고 백업 용도로 올리는 글이다.

설명이나 이런 건 없다...

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM SYS.TABLES
WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM SYS.COLUMNS
WHERE NAME = 'APPL_TOT_DLV_AM')

SET

1.     Select 쿼리 결과갑을 입력하지 않고 직접 변수에 값을 입력하는 경우

2.     Null 결과 값을 입력하는 경우

3.     여러 변수 타입의 결과값을 입력하고 사용하는 경우

SELECT

1.     한 번에 여러 변수값을 할당하는 경우

2.     쿼리 결과 값 즉, 하나 이상의 값을 할당하는 경우

3.     @@ROWCOUNT, @ERROR를 사용하는 경우

1. 변수 사용

구글링 결과 제일 많이 나온 방법.. 잠깐 쓰고 버릴 변수를 만들고 그 변수로 ROW_NUMBER() 뒤에 오는 OVER() 조건에 넣어줬다.

이것이 그 쿼리...

DECLARE 부터 통째로 MyBatis 쿼리 xml 파일에 넣었다.

그랬더니 깔끔하게 동작!

2. 서브 쿼리 사용

OVER()의 조건에 상수가 들어가지 않는다는 것이 문제였는데.. 생각하다보니 서브쿼리를 넣으면 어떻게 될까 궁금했다.

결과는 성공. 결국 상수 직접은 아니면서 1이기만 하면 다 되는 것 같다.

깨알팁.. 잊어버리지 않기 위해 블로그에 정리해서 올린다. 다음에도 MSSQL 쓸일이 있겠지.....