태그 : MSSQL, FOR XML PATH(), STUFF()
요약 :
여러 행(ROW)으로 조회된 데이터를 하나 스트링으로 묶어
하나의 행에서 보여주고자 할 때
임의의 구분자 ', '를 넣어
N 행의 자료를 FOR XML PATH('')를 통해
한 열의 구문으로 만들고
SUTFF()를 이용해 맨 앞의 구분자 ','를 없앤다.
| [MS SQL] split 분할 반환 함수 (문자형, 정수형) (0) | 2018.11.28 |
|---|---|
| [MS SQL] 과거 5개년 년도 보여주기 (다중테이블반환 함수, 임시테이블 활용) (0) | 2018.11.06 |
| [MS SQL] PRIMARY KEY 컬럼에 널체크시 INDEX 풀림(속도 느려짐) (0) | 2018.10.31 |
| [MySQL] 한글 문자 ???? 깨져서 저장되는 현상 (UTF8) (0) | 2018.10.31 |
화면단에서 멀티 체크박스 선택 등으로 인해 여러 개의 값을 하나의 파라미터로 받아서 XML의 SQL WHERE 조건으로 넘기려 한다. 이때 SQL에서는 받은 여러 개의 값을 각각 분리해 하나씩 반환해주는 함수를 만들려고 한다. 예) WHERE KEY IN (‘A’, ‘B’, ‘C’, ‘D’)
‘A,B,C,D’ 이렇게 화면단에서 , 구분자를 이용해 여러개의 값을 하나의 변수로 받아 DB에 최종전달되면 DB에서는 , 구분자를 분리시켜 A, B , C, D 각각의 값을 반환한다.
함수 설명 : SplitStrings( ‘값’, ‘구분자’)
SELECT * FROM SplitStrings('A,B,C,D', ',')
구분자를 '-' 로 해서 반환
SELECT * FROM SplitStrings('A-B-C-D', '-')
사용예) in 조건
@AAA 테이블에는 아래의 값 등록됨
이중에서 WHERE 절의 IN 조건을 이용해 A, B, C만 조회
SELECT idx, id FROM @AAA
WHERE id in (
select * from SplitStrings('A,B,C',',')
)
위 SplitStrings() 함수 select 절은 아래와 동일한 의미
SELECT idx, id FROM @AAA
WHERE id in (
'A',
'B',
'C',
)
□ CREATE문
CREATE FUNCTION [dbo].[SplitStrings]
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
RETURN (
SELECT Item = CONVERT(varchar, Item)
FROM
(
SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
FROM
(
SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
) AS a
CROSS APPLY [XML].nodes('i') AS x(i)
) AS y
WHERE Item IS NOT NULL
);
@AAA테이블에는 아래와 같이 정수형 값들이 존재함
인수에 주어진 값을 각각 int형으로 변환하여 반환함
SELECT idx, id FROM @AAA
WHERE id in (
select * from SplitInts('1,2,3',',')
)
아래와 동일한 의미
SELECT idx, id FROM @AAA
WHERE id in (1,2, 3)
□ CREATE 생성문
CREATE FUNCTION [dbo].[SplitInts]
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
RETURN (
SELECT Item = CONVERT(int, Item)
FROM
(
SELECT Item = x.i.value('(./text())[1]', 'int')
FROM
(
SELECT [XML] = CONVERT(XML, '<i>'+ REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
) AS a
CROSS APPLY [XML].nodes('i') AS x(i)
) AS y
WHERE Item IS NOT NULL
);
| [MS SQL] FOR XML PATH와 STUFF 활용 (0) | 2018.11.28 |
|---|---|
| [MS SQL] 과거 5개년 년도 보여주기 (다중테이블반환 함수, 임시테이블 활용) (0) | 2018.11.06 |
| [MS SQL] PRIMARY KEY 컬럼에 널체크시 INDEX 풀림(속도 느려짐) (0) | 2018.10.31 |
| [MySQL] 한글 문자 ???? 깨져서 저장되는 현상 (UTF8) (0) | 2018.10.31 |
태그: 임시테이블, 다중테이블반환함수, 과거년도 조회, MSSQL
□ 목적
○ 임시 테이블을 사용하여 귀찮은 일련의 절차를 다중 테이블반환함수로 작성한 뒤
○ DB 단에서 모두 처리한 후 화면단으로 가져올 수 있습니다.
□ 작업
○ 다음처럼 DB상에서 직접 과거 5개년도를 CODE, NAME 으로 가져와야 할 때가 있습니다.
○ JSP나 JAVA에서도 해도 되지만 번거롭기 때문에 간단한 테이블 반환함수를 작성해봅니다.
□ NULL로 할 경우 당해년도 포함 과거 5개년 데이터 조회
SELECT * FROM FN_FIVE_YEARS(NULL)
□ 매개변수 넣어줄 경우 – 해당년도로부터 과거 5개년
SELECT * FROM FN_FIVE_YEARS(2017)
--펑션 생성문
CREATE FUNCTION [dbo].[FN_FIVE_YEARS]
(
--입력변수 지정(기본 널값)
@END_YEAR INT = NULL
)
--반환값 지정
-- 여기에선 테이블 반환
RETURNS
@YEAR_TBL TABLE --임시테이블명
(
CODE CHAR(4) --컬럼
,NAME CHAR(4) --컬럼
)
AS
BEGIN
--변수 선언하기
DECLARE @CODE_YEAR INT
DECLARE @NAME_YEAR INT
DECLARE @IDX INT
--입력변수 널로 들어오면 현재 년도 세팅
IF @END_YEAR IS NULL
BEGIN
SET @CODE_YEAR = YEAR(GETDATE())
SET @NAME_YEAR = YEAR(GETDATE())
END
--아니라면 입력변수로 세팅
ELSE
BEGIN
SET @CODE_YEAR = @END_YEAR
SET @NAME_YEAR = @END_YEAR
END
--WHILE문 돌릴 INDEX 변수에 값 세팅
SET @IDX = 0
--4번 돌림(현대 2018포함 5개년)
WHILE @IDX <= 4
BEGIN
--임시테이블에 값을 넣고 (현재년도)
INSERT INTO @YEAR_TBL (CODE, NAME) VALUES (@CODE_YEAR,@NAME_YEAR )
--변수의 값을 (-1) 차감
SET @CODE_YEAR = @CODE_YEAR - 1
SET @NAME_YEAR = @NAME_YEAR - 1
--반복문 IDX 증가
SET @IDX = @IDX + 1
END
--테이블 리턴 @YEAR_TBL
RETURN
END
GO
| [MS SQL] FOR XML PATH와 STUFF 활용 (0) | 2018.11.28 |
|---|---|
| [MS SQL] split 분할 반환 함수 (문자형, 정수형) (0) | 2018.11.28 |
| [MS SQL] PRIMARY KEY 컬럼에 널체크시 INDEX 풀림(속도 느려짐) (0) | 2018.10.31 |
| [MySQL] 한글 문자 ???? 깨져서 저장되는 현상 (UTF8) (0) | 2018.10.31 |
ㅁ 배경
특정 쿼리문이 속도가 느리다는 문의가 왔다.
분명히 PK에 인덱스에 모든 것이 다 설정되어 있고
이런저런 쿼리 개선을 해도 속도가 크게 나아지지 않는다.
ㅁ 원인 및 조치
아래 CNTRCT_NO 컬럼은 PK열값이다. PK는 기본 NOT NULL인데 조건에서 이 컬럼에 ISNULL() 및 IS NOT NULL 등의 널값 체크를 걸면 INDEX가 풀려 속도가 느려진다.
원래 일반 컬럼으로 사용하다가 중간에 PK로 잡힌 컬럼으로 보인다.
일반컬럼일 때 설정된 IS NOT NULL 조건문이 문제가 되었다.
아래의 조건문을 주석처리하여 속도를 향상한다.
AND CTRTC.CNTRCT_NO IS NOT NULL
또는
AND ISNULL(CTRTC.CNTRCT_NO IS NOT NULL
참고사이트 :
https://support.microsoft.com/ko-kr/help/3051225/a-filtered-index-that-you-create-together-with-the-is-null-predicate-i
| [MS SQL] FOR XML PATH와 STUFF 활용 (0) | 2018.11.28 |
|---|---|
| [MS SQL] split 분할 반환 함수 (문자형, 정수형) (0) | 2018.11.28 |
| [MS SQL] 과거 5개년 년도 보여주기 (다중테이블반환 함수, 임시테이블 활용) (0) | 2018.11.06 |
| [MySQL] 한글 문자 ???? 깨져서 저장되는 현상 (UTF8) (0) | 2018.10.31 |
q 작업환경
¡ SQL 클라이언트 : Toad for MySQL Freeware 7.5
¡ 서버 : 리눅스
¡ SQL 서버 : mysql
TOAD Mysql에서 아무리 한글명으로 update 하고나면 아래처럼 ?????로 수정됨.
우선 아래 명령어로 현재 DB character set 확인
show variables like 'c%'
상당부분이 latin1 로 설정되어 있음을 확인
이를 어떻게 utf8로 변경할 수 있는가?
우선 아래처럼 latin1로 설정된 데이터베이스 character set부터 utf8로 변경하도록 함.
toad sql에서 아래를 수행해본다.
ALTER DATABASE [DB명] DEFAULT CHARACTER SET utf8;
아래처럼 ut8로 변경된 것 확인
그 다음에 다시 한글로 update 했는데 역시나 ???? 로 저장된다. (실패ㅠㅠ)
q 리눅스 콘솔로 직접 접속한다.
mysql의 환견설정파일은 보통 my.cnf이다.
어디에 있는지 find 명령어로 찾아본다.
[root@test etc]# find / -name my.cnf
/etc/my.cnf
/etc/my.cnf 위치를 확인했다.
q 환경설정 파일을 편집한다.
vi my.cnf
vi 편집기로 설정파일은 열고 아래의 내용을 추가해줌
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
이후 변경된 설정파일을 적용하기 위해 mysqld 서비스(데몬)을 재시작해야 함
재시작
[root@test]# ./mysqld restart
mysqld 를 정지 중: [ OK ]
mysqld (을)를 시작 중: [ OK ]
q Toad MySQL에서 다시 한글로 저장해본다
각 항목별 문자열 set이 어떻게 되어 있는지 확인하기 위해 sql 에디터에서 아래의 명령어를 쳐본다.
show variables like 'c%'
모두 utf8로 설정(변경)되어 있음을 확인할 수 있다.
이제 다시 데이터를 한글로 업데이트해보자.
결과는 성공!
위와 같이 정상적으로 한글이 수정되어 저장됐음을 확인할 수 있다.
| [MS SQL] FOR XML PATH와 STUFF 활용 (0) | 2018.11.28 |
|---|---|
| [MS SQL] split 분할 반환 함수 (문자형, 정수형) (0) | 2018.11.28 |
| [MS SQL] 과거 5개년 년도 보여주기 (다중테이블반환 함수, 임시테이블 활용) (0) | 2018.11.06 |
| [MS SQL] PRIMARY KEY 컬럼에 널체크시 INDEX 풀림(속도 느려짐) (0) | 2018.10.31 |
|
