프로그래밍/MS-SQL: 8개의 글

MSSQL PIVOT 여러컬럼 동적으로 수행하기

Posted by 고 고특파원
2019. 8. 28. 10:07 프로그래밍/MS-SQL
MSSQL PIVOT

이번 피드에서는 이전에 다루었던 피벗테이블의 응용인 여러컬럼을 피벗처리하는 방법에 대해 소개하도록 하겠다.

 

이전 피벗에 대한 내용을 보지 않고 왔다면 아래 링크를 따라 피벗하는 법을 확인 후 이번 피드를 보시길 추천한다.

 

2019/08/21 - [프로그래밍/MS-SQL] - MSSQL PIVOT 동적으로 수행하기

 

MSSQL PIVOT 동적으로 수행하기

프로그램을 개발하다보면 행(세로) 데이터를 열(가로)로 나열시켜 보여야하는 경우가 있다. 이때 PIVOT을 사용하면 행데이터를 열로 나열시켜 보일 수 있다. 우리는 품목/매출월/매출액 형식으로 되어있는 테이..

maelife.tistory.com

 

여러컬럼 피봇수행

이전 피드에서는  '품목/매출월/매출액' 에 대해 피봇을 수행하였다면, 이번에는 '품목/매출월/매출액/매출수량' 을 피봇처리하는 과정을 설명할 것이다.

 

 

PIVOT 형태로 변경

1. 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT.. INTO하여 저장

2. PIVOT처리할 항목들을 나열 (행에서 열로 변경할 항목들 나열)

3. 문자열의 마지막 index에 있는 , 제거

4. 동적으로 PIVOT처리할 쿼리문 생성

5. PIVOT쿼리 수행

 

 

1. 임시테이블에 SELECT.. INTO하여 저장

	-- 1. 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT ... INTO 하여 저장한다.
	SELECT
		 PRD_NAME
		,YYYYMM
		,QTY
		,AMT
		,COL = YYYYMM + '_' + COL
		,VALUE
	INTO #TEMP_TABLE
	FROM
	(
		SELECT
			 PRD_NAME		-- 품목
			,YYYYMM			-- 년월
			,SUM(QTY)	QTY	-- 수량
			,SUM(AMT)	AMT	-- 금액
		FROM SALE
		GROUP BY PRD_NAME, YYYYMM
	) RES
	CROSS APPLY
	(
		SELECT '매출수량', QTY UNION ALL
		SELECT '매출액', AMT 
	) C (COL,VALUE);

임시테이블에 아래와 같은 결과를 가진 테이블을 저장한다.

 

여기서 우리가 행데이터를 열로 변환해야할 항목은 [COL] 컬럼이다.

 

CROSS APPLY를 이용하여 매출액,매출수량별 값을 갖도록 테이블을 변환시켰다.

 

 

 

4. 동적으로 PIVOT처리할 쿼리문 생성

	-- 2. 피봇처리할 항목들을 나열한다. (행에서 열로 변경할 항목들 나열)
	DECLARE @PIVOT_COLS	VARCHAR(MAX)	-- 피봇대상 컬럼항목 생성
	DECLARE @EXEC_QUERY	VARCHAR(MAX)	-- 피봇대상 쿼리

	SET @PIVOT_COLS = ''
	SET @EXEC_QUERY =''

	SELECT @PIVOT_COLS = @PIVOT_COLS + '['+ COL +'],' 	  
	FROM (
			SELECT DISTINCT 
				COL
			FROM #TEMP_TABLE
		 ) AS A

	-- 3. 문자열 맨뒤에 ,항목 삭제 처리
	SET @PIVOT_COLS = LEFT(@PIVOT_COLS, LEN(@PIVOT_COLS) - 1)

	PRINT(@PIVOT_COLS)
	
	-- 4. 동적으로 피봇처리할 쿼리를 만든다.
	SET @EXEC_QUERY = 'SELECT 
						 Z.PRD_NAME		품목
						,'+@PIVOT_COLS+'
					   FROM
					   (
							SELECT 
								 PRD_NAME
								,COL
								,VALUE
							FROM #TEMP_TABLE
							) X
							PIVOT (SUM(VALUE) 
							FOR COL IN ('+ @PIVOT_COLS +')
					   ) AS Z'

이전 피드에서 설명한 내용은 생략하고 넘어가도록 한다. ( 2번,3번은 동일한 과정임)

@EXEC_QUERY : 동적으로 피봇처리하기 위해 스트링쿼리를 만든다.

 

피봇을 수행할 컬럼은 [COL]이고, 피봇된 결과에 대한 집계는 [VALUE]이다.

 

 

전체스크립트

/*------------------------------
	-- PIVOT 형태로 변경
	-- 1. 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT ... INTO 하여 저장한다.
	-- 2. 피봇처리할 항목들을 나열한다. (행에서 열로 변경할 항목들 나열)
	-- 3. 문자열 맨뒤에 ,항목 삭제 처리
	-- 4. 동적으로 피봇처리할 쿼리를 만든다.
	-- 5. 피봇쿼리 수행
	------------------------------*/

	-- 1. 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT ... INTO 하여 저장한다.
	SELECT
		 PRD_NAME
		,YYYYMM
		,QTY
		,AMT
		,COL = YYYYMM + '_' + COL
		,VALUE
	--INTO #TEMP_TABLE
	FROM
	(
		SELECT
			 PRD_NAME		-- 품목
			,YYYYMM			-- 년월
			,SUM(QTY)	QTY	-- 수량
			,SUM(AMT)	AMT	-- 금액
		FROM SALE
		GROUP BY PRD_NAME, YYYYMM
	) RES
	CROSS APPLY
	(
		SELECT '매출수량', QTY UNION ALL
		SELECT '매출액', AMT 
	) C (COL,VALUE);


	-- 2. 피봇처리할 항목들을 나열한다. (행에서 열로 변경할 항목들 나열)
	DECLARE @PIVOT_COLS	VARCHAR(MAX)	-- 피봇대상 컬럼항목 생성
	DECLARE @EXEC_QUERY	VARCHAR(MAX)	-- 피봇대상 쿼리

	SET @PIVOT_COLS = ''
	SET @EXEC_QUERY =''

	SELECT @PIVOT_COLS = @PIVOT_COLS + '['+ COL +'],' 	  
	FROM (
			SELECT DISTINCT 
				COL
			FROM #TEMP_TABLE
		 ) AS A

	-- 3. 문자열 맨뒤에 ,항목 삭제 처리
	SET @PIVOT_COLS = LEFT(@PIVOT_COLS, LEN(@PIVOT_COLS) - 1)

	PRINT(@PIVOT_COLS)
	
	-- 4. 동적으로 피봇처리할 쿼리를 만든다.
	SET @EXEC_QUERY = 'SELECT 
						 Z.PRD_NAME		품목
						,'+@PIVOT_COLS+'
					   FROM
					   (
							SELECT 
								 PRD_NAME
								,COL
								,VALUE
							FROM #TEMP_TABLE
							) X
							PIVOT (SUM(VALUE) 
							FOR COL IN ('+ @PIVOT_COLS +')
					   ) AS Z'
					  

	-- 5. 피봇쿼리 수행	
	EXEC (@EXEC_QUERY)
	PRINT @EXEC_QUERY
	DROP TABLE #TEMP_TABLE

 

 

 
제 블로그의 내용이 도움이 되셨나요?

여러분의 공감댓글이 큰 힘이됩니다 :)

▼▼▼▼▼▼▼▼▼▼▼

 

이 댓글을 비밀 댓글로

MSSQL PIVOT 동적으로 수행하기

Posted by 고 고특파원
2019. 8. 21. 09:39 프로그래밍/MS-SQL

 

프로그램을 개발하다보면 행(세로) 데이터를 열(가로)로 나열시켜 보여야하는 경우가 있다.

 

이때 PIVOT을 사용하면 행데이터를 열로 나열시켜 보일 수 있다.

 

PIVOT(피벗) 예시

우리는 품목/매출월/매출액 형식으로 되어있는 테이블을 품목 / 201901 / 201902 / ... / 201904 의 형태로 PIVOT처리하여 테이블을 보이도록 할 것이다.

 

아래에 설명할 내용을 천천히 따라가면서 PIVOT을 어떻게 처리하는지 확인해 보도록 한다.

 

PIVOT하는 과정을 간단한 매출(SALE) 테이블을 통해 설명하도록 하겠고, 순서는 아래와 같다.

 

 

PIVOT 순서

1. 매출 테이블생성

2. 매출 데이터 삽입

3. PIVOT 형태로 변경

  3-1) 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT.. INTO하여 저장.

  3-2) PIVOT처리할 항목들을 나열 (행에서 열로 변경할 항목들 나열)

  3-3) 문자열의 마지막 index에 있는 , 제거

  3-4) 동적으로 PIVOT처리할 쿼리문 생성

  3-5) PIVOT 쿼리 수행

 

 

1. 매출(SALE) 테이블 생성

CREATE TABLE SALE
(
	
	 YYYYMM		VARCHAR(6)			-- 년월
	,PRD_NAME	VARCHAR(200)		-- 품목
	,QTY		NUMERIC(18,3)		-- 수량
	,AMT		MONEY				-- 금액
)

매출(SALE) 테이블을 생성하는 쿼리이다.

 

매출 테이블에는 품목, 매출월, 매출액 컬럼이 있다.

 

 

2. 매출(SALE) 데이터 삽입

-- 2. 매출 데이터 입력
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201901', 'A', 5, 5000)			-- 품목 A 19년 1월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201902', 'A', 10, 10000)		-- 품목 A 19년 2월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201903', 'A', 5, 10000)		-- 품목 A 19년 3월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201904', 'A', 10, 10000)		-- 품목 A 19년 4월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201905', 'A', 10, 10000)		-- 품목 A 19년 5월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201901', 'B', 20, 40000)		-- 품목 B 19년 1월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201902', 'B', 10, 20000)		-- 품목 B 19년 2월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201903', 'B', 5, 10000)		-- 품목 B 19년 3월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201904', 'B', 10, 20000)		-- 품목 B 19년 4월 매출

매출(SALE) 테이블에 임의로 데이터를 생성하여 INSERT하고 있는 부분이다.

 

 

3. PIVOT 형태로 변경

3-1) 임시테이블(#TEMP_TABLE)에 변경 전 테이블을  SELECT .. INTO 하여 저장

-- 1. 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT ... INTO 하여 저장
SELECT
	 PRD_NAME		-- 품목
	,YYYYMM			-- 년월
	,SUM(AMT)	AMT	-- 금액
INTO #TEMP_TABLE
FROM SALE
GROUP BY PRD_NAME, YYYYMM

품목별 월별로 GROUP BY한 결과테이블을 임시테이블(#TEMP_TABLE)에 저장한다.

 

 

3-2) PIVOT처리할 항목들을 나열 (행에서 열로 변경할 항목들 나열)

-- 2. 피봇처리할 항목들을 나열한다. (행에서 열로 변경할 항목들 나열)
DECLARE @PIVOT_COLS	VARCHAR(MAX)	-- 피봇대상 컬럼항목 생성
DECLARE @TOTAL_SUM	VARCHAR(MAX)	-- 피봇대상 컬럼항목 생성
DECLARE @EXEC_QUERY	VARCHAR(MAX)	-- 피봇대상 쿼리

SET @PIVOT_COLS = ''
SET @TOTAL_SUM = ''
SET @EXEC_QUERY =''
SELECT @PIVOT_COLS = @PIVOT_COLS + '['+ YYYYMM +'],' 
	  ,@TOTAL_SUM = @TOTAL_SUM + 'ISNULL([' + YYYYMM + '],0) +'
FROM (
			SELECT DISTINCT 
				YYYYMM
			FROM #TEMP_TABLE
	 ) AS A

@PIVOT_COLS : 피봇을 수행하여 생성될 컬럼들을 나열하는 변수 

 

결과형태

[201901],[201902],[201903],[201904],[201905]

 

 

@TOTAL_SUM : 피봇을 수행하여 생성된 컬럼들의 총합계를 계산하기 위한 변수 

 

결과형태

ISNULL([201901],0) +ISNULL([201902],0) +ISNULL([201903],0) +ISNULL([201904],0) +ISNULL([201905],0)

 

 

3-3) 문자열의 마지막 index에 있는 , 제거

-- 3. 문자열 맨뒤에 ,항목 삭제 처리
SET @PIVOT_COLS = LEFT(@PIVOT_COLS, LEN(@PIVOT_COLS) - 1)
SET @TOTAL_SUM = LEFT(@TOTAL_SUM, LEN(@TOTAL_SUM) - 1)

[201901],[201902],[201903],[201904],[201905],

문자열이 이런식으로 만들어진것인데 맨 마지막의 [ , ] 를 제거해주는 과정이다.

 

 

3-4) 동적으로 PIVOT처리할 쿼리문 생성

-- 4. 동적으로 피봇처리할 쿼리를 만든다.
SET @EXEC_QUERY = 'SELECT 
						 Z.PRD_NAME		품목
					   ,'+@PIVOT_COLS+'
					   ,'+@TOTAL_SUM+'	AS 총매출액
				   FROM
				   (
							SELECT 
								 PRD_NAME
								,YYYYMM
								,AMT
							FROM #TEMP_TABLE
							) X
							PIVOT (SUM(AMT) 
							FOR YYYYMM IN ('+ @PIVOT_COLS +')
					) AS Z'

@EXEC_QUERY : 동적으로 피봇처리하기 위해 스트링쿼리를 만든다.

결과형태

SELECT 
	 Z.PRD_NAME		품목
	,[201901],[201902],[201903],[201904],[201905]
	,ISNULL([201901],0) +ISNULL([201902],0) +ISNULL([201903],0) +ISNULL([201904],0) +ISNULL([201905],0) 	AS 총매출액
FROM
(
	SELECT 
		 PRD_NAME
		,YYYYMM
		,AMT
	FROM #TEMP_TABLE
) X
PIVOT (SUM(AMT) 
FOR YYYYMM IN ([201901],[201902],[201903],[201904],[201905]) 
	  ) AS Z

 

3-5) PIVOT 쿼리 수행

-- 5. 피봇쿼리 수행	
EXEC (@EXEC_QUERY)
PRINT @EXEC_QUERY
DROP TABLE #TEMP_TABLE

스트링쿼리를 EXEC 구문을 이용하여 수행하여 결과 테이블을 생성한다.

 

 

전체 스크립트

	-- 1. 매출 테이블 생성
	CREATE TABLE SALE
	(
	
		 YYYYMM		VARCHAR(6)			-- 년월
		,PRD_NAME	VARCHAR(200)		-- 품목
		,QTY		NUMERIC(18,3)		-- 수량
		,AMT		MONEY				-- 금액

	)

	-- 2. 매출 데이터 입력
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201901', 'A', 5, 5000)		-- 품목 A 19년 1월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201902', 'A', 10, 10000)		-- 품목 A 19년 2월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201903', 'A', 5, 10000)		-- 품목 A 19년 3월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201904', 'A', 10, 10000)		-- 품목 A 19년 4월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201905', 'A', 10, 10000)		-- 품목 A 19년 5월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201901', 'B', 20, 40000)		-- 품목 B 19년 1월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201902', 'B', 10, 20000)		-- 품목 B 19년 2월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201903', 'B', 5, 10000)		-- 품목 B 19년 3월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201904', 'B', 10, 20000)		-- 품목 B 19년 4월 매출

	-- 변경전 테이블
	SELECT
		 PRD_NAME	품목			-- 품목
		,YYYYMM		매출월			-- 년월
		,SUM(AMT)	매출액			-- 금액
	FROM SALE
	GROUP BY PRD_NAME, YYYYMM

	/*------------------------------
	-- PIVOT 형태로 변경
	-- 1. 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT ... INTO 하여 저장한다.
	-- 2. 피봇처리할 항목들을 나열한다. (행에서 열로 변경할 항목들 나열)
	-- 3. 문자열 맨뒤에 ,항목 삭제 처리
	-- 4. 동적으로 피봇처리할 쿼리를 만든다.
	-- 5. 피봇쿼리 수행
	------------------------------*/

	-- 1. 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT ... INTO 하여 저장한다.
	SELECT
		 PRD_NAME		-- 품목
		,YYYYMM			-- 년월
		,SUM(AMT)	AMT	-- 금액
	INTO #TEMP_TABLE
	FROM SALE
	GROUP BY PRD_NAME, YYYYMM


	-- 2. 피봇처리할 항목들을 나열한다. (행에서 열로 변경할 항목들 나열)
	DECLARE @PIVOT_COLS	VARCHAR(MAX)	-- 피봇대상 컬럼항목 생성
	DECLARE @TOTAL_SUM	VARCHAR(MAX)	-- 피봇대상 컬럼항목 생성
	DECLARE @EXEC_QUERY	VARCHAR(MAX)	-- 피봇대상 쿼리

	SET @PIVOT_COLS = ''
	SET @TOTAL_SUM = ''
	SET @EXEC_QUERY =''
	SELECT @PIVOT_COLS = @PIVOT_COLS + '['+ YYYYMM +'],' 
		  ,@TOTAL_SUM = @TOTAL_SUM + 'ISNULL([' + YYYYMM + '],0) +'
	FROM (
			SELECT DISTINCT 
				YYYYMM
			FROM #TEMP_TABLE
		 ) AS A

	-- 3. 문자열 맨뒤에 ,항목 삭제 처리
	SET @PIVOT_COLS = LEFT(@PIVOT_COLS, LEN(@PIVOT_COLS) - 1)
	SET @TOTAL_SUM = LEFT(@TOTAL_SUM, LEN(@TOTAL_SUM) - 1)

	PRINT(@PIVOT_COLS)
	PRINT(@TOTAL_SUM)
	
	-- 4. 동적으로 피봇처리할 쿼리를 만든다.
	SET @EXEC_QUERY = 'SELECT 
						 Z.PRD_NAME		품목
						,'+@PIVOT_COLS+'
						,'+@TOTAL_SUM+'	AS 총매출액
					   FROM
					   (
							SELECT 
								 PRD_NAME
								,YYYYMM
								,AMT
							FROM #TEMP_TABLE
							) X
							PIVOT (SUM(AMT) 
							FOR YYYYMM IN ('+ @PIVOT_COLS +')
					   ) AS Z'
					  

	-- 5. 피봇쿼리 수행	
	EXEC (@EXEC_QUERY)
	PRINT @EXEC_QUERY
	DROP TABLE #TEMP_TABLE

 

품목에 대한 월별 자료를 PIVOT하여 보여주는 과정에 대해 설명을 해보았다.

 

이번 내용에서는 매출액을 월별로 PIVOT하여 보여주는 과정을 설명했는데, 이것을 조금 더 응용하면 2개이상(매출액, 매출수량 등)의 컬럼을 PIVOT하여 처리할 수도 있다.

 

여러컬럼 PIVOT하기 응용링크 ▼

2019/08/28 - [프로그래밍/MS-SQL] - MSSQL PIVOT 여러컬럼 동적으로 수행하기

 

MSSQL PIVOT 여러컬럼 동적으로 수행하기

이번 피드에서는 이전에 다루었던 피벗테이블의 응용인 여러컬럼을 피벗처리하는 방법에 대해 소개하도록 하겠다. 이전 피벗에 대한 내용을 보지 않고 왔다면 아래 링크를 따라 피벗하는 법을 확인 후 이번 피드를..

maelife.tistory.com

 
제 블로그의 내용이 도움이 되셨나요?

여러분의 공감댓글이 큰 힘이됩니다 :)

▼▼▼▼▼▼▼▼▼▼▼

 

이 댓글을 비밀 댓글로

MSSQL 함수 작성법

Posted by 고 고특파원
2019. 8. 6. 13:02 프로그래밍/MS-SQL

MSSQL에서는 함수를 사용할 수 있는 기능을 제공하고 있다.

함수의 종류는 시스템함수와 사용자 정의함수로 나뉘어 진다.

 

정확하게 말하자면, 우리는 여기에서 사용자 정의함수를 작성하는 방법에 대해 설명할것인데, 그 전에 시스템 함수는 무엇인지 설명하고 넘어가도록 하겠다.

 

 

 

시스템함수는?

 

Sql Server Management Studio 개체 탐색기 내 시스템 함수

시스템함수는 MSSQL에서 제공하여 바로 사용할 수 있는 함수이다. (개념은 사용자정의함수와 마찬가지인데, 개발자가 직접 정의하여 만들어 사용하는 함수가 사용자 정의 함수라고 보면 이해하기 쉽다.)

시스템함수종류로는 집계함수, 구성함수, 커서함수, 날짜 및 시간함수 등 다양하게 제공되어지고 있고 대표적으로Avg(), Max() 등 다양한부분으로 존재하니, 사용자 정의 함수를 만들기 전 검색을 통해 비슷한 기능을 하는 함수를 제공하는지 먼저 참고해보면 좋을것 같다.

 

 

 

사용자정의 함수는?

사용자정의함수는 개발자가 직접 정의하여 사용할 수 있는 함수를 말한다.

쿼리를 작성하면서 자주 사용되는 기능을 함수로 정의하여 재사용 할 수 있으므로, SQL문이 더욱 간결하고 가독성이 증가하게 된다.

 

내가 프로그램을 개발하면서 자주 사용하는 함수는 스칼라반환함수와 테이블반환함수이다.

(스칼라반환함수를 대체적으로 많이쓴다.)

 

 

 

스칼라반환함수 작성예시

CREATE FUNCTION [dbo].[TEST_FUNCTION]
(
	 --@P_PRAMETER1		VARCHAR(100) 사용할경우 주석 해제
)
RETURNS VARCHAR(100)
AS
BEGIN

	RETURN 'HELLOW FUNCTION'

END

 

스칼라반환함수는 한개의 값을 반환하는 함수이다. 여기에서 반환하는 값의자료형식(INT, VARCHAR, NUMERIC 등)을 지정하여 반환 할 수 있다.


함수를 수정해야 할경우 CREATE를 ALTER로 변경하면 된다.

함수를 삭제해야 할 경우 DROP 을 사용하면 된다.


SELECT dbo.[TEST_FUNCTION]() 를 이용하여 함수의 결과값을 확인한다.

 

 

 

테이블반환함수 작성예시

CREATE FUNCTION [dbo].[TEST_TABLE_FUNCTION]
(
	 
)
RETURNS @RETURN_TB TABLE
( 
	 NAME	VARCHAR(10)
	,AGE	INT
)
AS
BEGIN

	INSERT INTO @RETURN_TB VALUES('박지성', 30)
	INSERT INTO @RETURN_TB VALUES('이영표', 35)
	
	
	RETURN

END

 

테이블반환함수는 DB테이블을 반환하는 함수이다.

 

테이블 반환함수는 반환할 테이블의 형태를 지정하고, 데이터를 담아서 리턴해주면 된다.

 

함수를 수정해야 할경우 CREATE를 ALTER로 변경하면 된다.

함수를 삭제해야 할 경우 DROP 을 사용하면 된다.

 

SELECT * FROM [dbo].[TEST_TABLE_FUNCTION]() 를 이용하여 함수의 결과값을 확인한다.

 
제 블로그의 내용이 도움이 되셨나요?

여러분의 공감댓글이 큰 힘이됩니다 :)

▼▼▼▼▼▼▼▼▼▼▼
이 댓글을 비밀 댓글로

MSSQL 저장프로시저(StoredProcedure) 작성법

Posted by 고 고특파원
2019. 7. 30. 19:09 프로그래밍/MS-SQL

MSSQL을 사용하다 보면 흔히 접할 수 있는것 중의 하나가 바로 저장프로시저(Stored Procedure)이다.

 

저장프로시저는 개발자가 자주 쓰는 쿼리문을 하나의 집합단위로 관리하는것을 말한다.

 

프로그램과 DB를 연결하여 데이터를 관리(데이터조회, 삽입, 수정, 삭제) 하는 수단은 반복되는 작업이므로

DB테이블 1개당 저장프로시저를 보통 (C,R,U,D) 4개의 프로시저를 생성하여 관리한다.

 

본론으로 돌아와서, 저장프로시저를 작성하는 방법에 대해 설명하도록 하겠다.

 

 

저장프로시저(StoredProcedure) 작성 예시

USE [DataBase이름]
GO

CREATE PROCEDURE [dbo].[프로지서이름]
	 
	--	@P_PARAMETER1		VARCHAR(10)		-- 파라미터가 필요한경우, 추가해야한다.

AS  
BEGIN  
	
	-- 자주사용하는 SQL 쿼리문
	SELECT
		NULL

END

USE[DataBase이름] => 개발중인 데이터베이스 이름을 입력해서 넣어준다.

GO

 

CREATE PROCCEDURE [dbo].[프로시저이름] => 프로시저이름은 본인이 설정하고 싶은대로 입력하여 넣어준다.

 

파라미터가 필요한 경우,

 

@파라미터변수명    자료형

 

저장프로시저를 통해서 SELECT문, INSERT문, UPDATE문, DELETE문 등 데이터를 조작하는 쿼리를 입력하여 관리할 수 있다.

 

 

저장프로시저(StoredProcedure) 수정 예시

저장프로시저를 생성하고 난 후, 수정을 해야하는 경우는 생성된 저장프로시저를 찾고 간단히 수정을 해주면 된다.

 

CREATE PROCCEDURE [dbo].[프로시저이름] >> ALTER PROCCEDURE [dbo].[프로시저이름]

 

생성예시문과 전부다 동일하고 윗부분만 다르다.

 

저장프로시저 수정 예시

 

아래에는 저장프로시저를 쉽게 만들수 있도록, 자동으로 생성해주는 스크립트 파일 링크를 추가해 놓도록 하겠다.

필요한 경우 가져가서 마음껏 사용 하도록 바란다.

(가져가실때 공감과 감사의표시로 댓글 하나씩만 부탁드립니다.)

 

자세한 내용과 설명은 아래링크에서 확인하시면 됩니다.

 

[프로그래밍/MS-SQL] - [MS-SQL] CRUD 자동 스크립트 작성 내용 공유

 

[MS-SQL] CRUD 자동 스크립트 작성 내용 공유

해당 SQL스크립트는 MSSQL 저장프로시저를 자동으로 생성하는데 도움을 주는 프로시저이다. 프로그램을 개발하면서 가장 기본이되는 작업은 데이터를 읽고, 입력하고, 수정하고, 삭제하는 작업이다. 프로그램을 개..

maelife.tistory.com

 
제 블로그의 내용이 도움이 되셨나요?

여러분의 공감댓글이 큰 힘이됩니다 :)

▼▼▼▼▼▼▼▼▼▼▼
이 댓글을 비밀 댓글로

MSSQL 커서(CURSOR) 사용법

Posted by 고 고특파원
2019. 7. 30. 09:14 프로그래밍/MS-SQL

MSSQL에서는 커서라는것이 존재하는데, 테이블의 데이터를 커서에 담고, 한개의 행단위로 읽어주는 기능을 한다.

이름 (NAME) 나이 (AGE) 성별 (SEX)
박지성 36 남자
이영표 38 남자
손흥민 28 남자
기성용 32 남자

위와 같은 데이터 테이블이 존재 한다면, 정방향으로 읽었을 때 박지성->이영표->손흥민->기성용 순서로 데이터테이블의 행 정보를 읽어낼 수 있다. 

 

독자의 경우에는, 반복적인 배치작업을 할때 주로 사용한다.

 

그렇다면 사용방법에 대해 설명하도록 하겠다.

 

1. 대상 테이블생성 및 데이터삽입 (이 부분은 대상 테이블이 없을때만 해당)

2. 커서에 DB테이블 담기 및 반복작업 수행

 

1. 대상 테이블 생성 및 데이터삽입

DB테이블 생성

CREATE TABLE SOCCER_PLAYER
(
    "ID"    int             NOT NULL    IDENTITY, 
    "NAME"  nvarchar(50)    NULL, 
    "AGE"   int             NULL, 
    "SEX"   nvarchar(10)    NULL, 
    CONSTRAINT PK_SOCCER_PLAYER PRIMARY KEY (ID)
)

MSSQL 구문을 이용하여 'SOCCER_PLAYER'라고 하는 DB테이블을 생성하였고, 컬럼에는 기본키, 이름, 나이, 성별 컬럼항목을 추가하였다.

 

데이터삽입

INSERT INTO SOCCER_PLAYER(NAME, AGE, SEX) VALUES('박지성',36, '남자')
INSERT INTO SOCCER_PLAYER(NAME, AGE, SEX) VALUES('이영표',38, '남자')
INSERT INTO SOCCER_PLAYER(NAME, AGE, SEX) VALUES('손흥민',28, '남자')
INSERT INTO SOCCER_PLAYER(NAME, AGE, SEX) VALUES('기성용',32, '남자')

INSERT 구문을 이용하여, 데이터를 삽입하였다. 여기서 ID(기본키)는 1씩 자동증가 설정되어있으므로 넣어주지 않아도 된다. 

 

DB 결과셋

2. 커서에 DB테이블 담기 및 반복작업 수행

-- 한개 ROW정보를 담을 변수 선언
DECLARE @P_NAME		VARCHAR(100)
DECLARE @P_AGE		INT
DECLARE @P_SEX		VARCHAR(10)

-- 1. 커서 사용 선언
DECLARE SOCCER_CURSOR CURSOR FOR
SELECT
	 NAME
	,AGE
	,SEX
FROM SOCCER_PLAYER

-- 2. 커서 반복문 돌리기
OPEN SOCCER_CURSOR      --커서 사용을 위해 오픈
FETCH NEXT FROM SOCCER_CURSOR INTO @P_NAME,@P_AGE, @P_SEX

--커서를이용해 한개 행씩 읽음 
WHILE @@FETCH_STATUS = 0
BEGIN

	--반복 작업 수행
	PRINT('이름: ' + @P_NAME)
	
	FETCH NEXT FROM SOCCER_CURSOR INTO @P_NAME,@P_AGE, @P_SEX
END

--커서 닫고 초기화
CLOSE SOCCER_CURSOR
DEALLOCATE SOCCER_CURSOR

커서는 한개의 Row(행)단위로 데이터를 읽는다고 했으므로, 한개 Row정보를 담을 변수를 선언한다.

커서(CURSOR) 사용 선언을 하고 SELECT한 테이블을 커서에 담아준다. (DECLARE 커서이름 CURSOR FOR)

 

커서(CURSOR)를 통해 반복작업을 수행한다. 그리고 마지막으로 사용한 커서는 반드시 닫아주고 초기화 해주어야 한다.

이 댓글을 비밀 댓글로

[MS-SQL] CRUD 자동 스크립트 작성 내용 공유

Posted by 고 고특파원
2019. 7. 12. 18:58 프로그래밍/MS-SQL

해당 SQL스크립트는 MSSQL 저장프로시저를 자동으로 생성하는데 도움을 주는 프로시저이다.

프로그램을 개발하면서 가장 기본이되는 작업은 데이터를 읽고, 입력하고, 수정하고, 삭제하는 작업이다.

 

프로그램을 개발하다보면, 반복적으로 CRUD SQL문을 작성하는 경우가 많다.

시간을 어느정도 절약 할 수 있도록 공유된 프로시저를 이용하면 기본 SELECT, INSERT, UPDATE, DELETE 문을 만들어주기에 개발속도 및 개발 효율성을 증가 시킬 수 있다. (물론 초보자라면 직접 타이핑하는게 좋겠지만, 프로시저에 대한 완벽한 이해가 되어있으면 사용하는게 좋을듯 함.)

 

사용방법에 대해 간단히 소개한다.

 

물론 SqlServerManagementStudio에서 사용을 하는 것이며, 사용을 하기 위한 데이터베이스에 아래 프로시저를 생성한다.

 

-- EXEC [dbo].ZZZ.PROCEDURE_CREATOR '첫번째파라미터','두번째파라미터','세번째파라미터'

 

첫번째파라미터 => Database 테이블명

두번째파라미터 => 프로시저 생성자이름

세번째파라미터 => 프로시저에 대한 설명 공통문

 

 

아래 해당 프로시저를 보면 자신이 원하는 입맛대로 수정도 가능할듯 하니, 필요한사람은 복사해서 마음껏 가져다 쓰시고, 이 포스팅에 대한 공감하나 눌러주고 가시길..!

 

CRUD 자동 스크립트

-- ============================================= 
-- AUTHOR:   ProSungsiler
-- CREATE DATE: 2019-06-17
-- DESCRIPTION: 프로시저 스크립트 자동생성 
--				@P_TABLE_NAME	: DB 테이블 명
--				@P_CREATOR		: 프로시저 생성자 이름
--				@P_DESCRIPTION	: 주석 및 프로시저 설명
-- ============================================= 
-- EXEC [dbo].ZZZ_PROCEDURE_CREATOR 'PR_EMPLOYEE','ProSungsiler','기본코드'
CREATE PROCEDURE [dbo].[ZZZ_PROCEDURE_CREATOR]
	  
	 @P_TABLE_NAME	VARCHAR(50)		
	,@P_CREATOR		VARCHAR(50)		
	,@P_DESCRIPTION	VARCHAR(200)	
AS  
BEGIN  

	
	DECLARE @INDEX INT

	-- ===================컬럼정보======================== 
	DECLARE @P_COLUMN_NAME						VARCHAR(256)
	DECLARE @P_IS_NULLABLE						VARCHAR(256)
	DECLARE @P_DATA_TYPE						VARCHAR(256)
	DECLARE @P_CHARACTER_MAXIMUM_LENGTH			INT
	DECLARE @P_NUMERIC_PRECISION				INT
	DECLARE @P_NUMERIC_SCALE					INT
	DECLARE @P_PK_YN							VARCHAR(1)
	DECLARE @ORD_NO								INT
	-- ===================컬럼정보========================
	
	--SELECT 
	--	 *
	--FROM INFORMATION_SCHEMA.COLUMNS
	--WHERE TABLE_NAME = @P_TABLE_NAME
	--ORDER BY ORDINAL_POSITION ASC

	

	/* =====================================================
	-- C) 기본 프로시저 생성 
	-- =================================================== */
	PRINT('GO')
	PRINT('-- ============================================= ')
	PRINT('-- AUTHOR: '+ @P_CREATOR)
	PRINT('-- CREATE DATE: ' + CONVERT(VARCHAR,GETDATE(),23))
	PRINT('-- DESCRIPTION: ' + @P_DESCRIPTION + ' 생성')
	PRINT('-- ============================================= ')
	PRINT('-- EXEC [USP_'+ @P_TABLE_NAME +'_C]')
	PRINT('CREATE PROCEDURE [dbo].[USP_'+ @P_TABLE_NAME +'_C]' + CHAR(13))
	
	-- ==================================================================
	-- ===================1. 테이블 파라미터 정의======================== 

		DECLARE MYCUR CURSOR FOR
		SELECT 
			 COLUMN_NAME				-- 컬럼이름
			,IS_NULLABLE				-- NULL값 여부
			,DATA_TYPE					-- 데이터형식
			,CHARACTER_MAXIMUM_LENGTH	-- 문자열 길이 
			,NUMERIC_PRECISION			-- 숫자 정수부분
			,NUMERIC_SCALE				-- 숫자 소수부분
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @P_TABLE_NAME
		ORDER BY ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
				IF(UPPER(@P_DATA_TYPE) = 'VARCHAR' OR UPPER(@P_DATA_TYPE) = 'CHAR')
				BEGIN
					-- 일반문자열
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					
				END
				ELSE IF
					 ( UPPER(@P_DATA_TYPE) = 'NUMERIC' 
					OR UPPER(@P_DATA_TYPE) = 'DECIMAL'
					 )
				BEGIN
					-- 숫자
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
				END
				ELSE
				BEGIN
					-- 그외
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
				END
			
			SET @INDEX = @INDEX + 1

			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR
			
	-- ===================1. 테이블 파라미터 정의======================== 
	-- ==================================================================

	PRINT(CHAR(13)+'AS')
	PRINT('BEGIN')
	PRINT(CHAR(13))
	
	-- ==================================================================
	-- ===================2. 테이블 컬럼 정의======================== 

	PRINT('		INSERT INTO ' + @P_TABLE_NAME)
	PRINT('		(')
	
	DECLARE MYCUR CURSOR FOR
	SELECT 
		 COLUMN_NAME				-- 컬럼이름
		,IS_NULLABLE				-- NULL값 여부
		,DATA_TYPE					-- 데이터형식
		,CHARACTER_MAXIMUM_LENGTH	-- 문자열 길이 
		,NUMERIC_PRECISION			-- 숫자 정수부분
		,NUMERIC_SCALE				-- 숫자 소수부분
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @P_TABLE_NAME
	ORDER BY ORDINAL_POSITION ASC

	SET @INDEX = 0

	OPEN MYCUR

	FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE
	WHILE (@@FETCH_STATUS=0)
	BEGIN      
		
		IF(@INDEX = 0)
		BEGIN
			PRINT('			 '+ @P_COLUMN_NAME)
		END
		ELSE
		BEGIN
			PRINT('			,'+ @P_COLUMN_NAME)
		END
		
		SET @INDEX = @INDEX + 1

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE

	END

	CLOSE MYCUR

	DEALLOCATE MYCUR

	PRINT('		)')
	PRINT('		VALUES')
	PRINT('		(')
	
	DECLARE MYCUR CURSOR FOR
	SELECT 
		 COLUMN_NAME				-- 컬럼이름
		,IS_NULLABLE				-- NULL값 여부
		,DATA_TYPE					-- 데이터형식
		,CHARACTER_MAXIMUM_LENGTH	-- 문자열 길이 
		,NUMERIC_PRECISION			-- 숫자 정수부분
		,NUMERIC_SCALE				-- 숫자 소수부분
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @P_TABLE_NAME
	ORDER BY ORDINAL_POSITION ASC

	SET @INDEX = 0

	OPEN MYCUR

	FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE
	WHILE (@@FETCH_STATUS=0)
	BEGIN      
		
		IF(@INDEX = 0)
		BEGIN
			PRINT('			 @P_'+ @P_COLUMN_NAME)
		END
		ELSE
		BEGIN
			PRINT('			,@P_'+ @P_COLUMN_NAME)
		END
		
		SET @INDEX = @INDEX + 1

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE

	END

	CLOSE MYCUR

	DEALLOCATE MYCUR


	PRINT('		)')
	PRINT(CHAR(13))
	
	-- ===================2. 테이블 컬럼 정의======================== 
	-- ==================================================================
	PRINT('END')







	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT('GO')
	/* =====================================================
	-- R) 기본 프로시저 조회 
	-- =================================================== */
	PRINT('-- ============================================= ')
	PRINT('-- AUTHOR: '+ @P_CREATOR)
	PRINT('-- CREATE DATE: ' + CONVERT(VARCHAR,GETDATE(),23))
	PRINT('-- DESCRIPTION: ' + @P_DESCRIPTION + '조회')
	PRINT('-- ============================================= ')
	PRINT('-- EXEC [USP_'+ @P_TABLE_NAME +'_R]')
	PRINT('CREATE PROCEDURE [dbo].[USP_'+ @P_TABLE_NAME +'_R]' + CHAR(13))

	PRINT(CHAR(13)+'AS')
	PRINT('BEGIN')
	PRINT(CHAR(13))

	PRINT('		SELECT')
	
	-- ==================================================================
	-- ===================1. 테이블 컬럼 정의======================== 

	DECLARE MYCUR CURSOR FOR
	SELECT 
		 COLUMN_NAME				-- 컬럼이름
		,IS_NULLABLE				-- NULL값 여부
		,DATA_TYPE					-- 데이터형식
		,CHARACTER_MAXIMUM_LENGTH	-- 문자열 길이 
		,NUMERIC_PRECISION			-- 숫자 정수부분
		,NUMERIC_SCALE				-- 숫자 소수부분
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @P_TABLE_NAME
	ORDER BY ORDINAL_POSITION ASC

	SET @INDEX = 0

	OPEN MYCUR

	FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE
	WHILE (@@FETCH_STATUS=0)
	BEGIN      
		
		IF(@INDEX = 0)
		BEGIN
			PRINT('			 '+ @P_COLUMN_NAME)
		END
		ELSE
		BEGIN
			PRINT('			,'+ @P_COLUMN_NAME)
		END
		
		SET @INDEX = @INDEX + 1

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE

	END

	CLOSE MYCUR

	DEALLOCATE MYCUR

	-- ===================1. 테이블 컬럼 정의======================== 
	-- ==================================================================
	

	PRINT('		FROM ' + @P_TABLE_NAME) 


	PRINT(CHAR(13))
	PRINT('END')


	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT('GO')
	/* =====================================================
	-- U) 기본 프로시저 수정
	-- =================================================== */
	PRINT('-- ============================================= ')
	PRINT('-- AUTHOR: '+ @P_CREATOR)
	PRINT('-- CREATE DATE: ' + CONVERT(VARCHAR,GETDATE(),23))
	PRINT('-- DESCRIPTION: ' + @P_DESCRIPTION + '수정')
	PRINT('-- ============================================= ')
	PRINT('-- EXEC [USP_'+ @P_TABLE_NAME +'_U]')
	PRINT('CREATE PROCEDURE [dbo].[USP_'+ @P_TABLE_NAME +'_U]' + CHAR(13))

	-- ==================================================================
	-- ===================1. 테이블 파라미터 정의======================== 

		DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
				IF(UPPER(@P_DATA_TYPE) = 'VARCHAR' OR UPPER(@P_DATA_TYPE) = 'CHAR')
				BEGIN
					-- 일반문자열
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					
				END
				ELSE IF
					 ( UPPER(@P_DATA_TYPE) = 'NUMERIC' 
					OR UPPER(@P_DATA_TYPE) = 'DECIMAL'
					 )
				BEGIN
					-- 숫자
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
				END
				ELSE
				BEGIN
					-- 그외
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
				END
			
			SET @INDEX = @INDEX + 1

			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR
			
	-- ===================1. 테이블 파라미터 정의======================== 
	-- ==================================================================

	PRINT(CHAR(13)+'AS')
	PRINT('BEGIN')
	PRINT(CHAR(13))

	PRINT('		UPDATE ' + @P_TABLE_NAME)
	PRINT('		SET')
	-- ==================================================================
	-- ===================2. 테이블 업데이트 내역 정의===================
	 
	 DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
			
			IF(@P_PK_YN = 'N')
			BEGIN
				IF(@INDEX = 0)
				BEGIN
					PRINT('			 '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				ELSE
				BEGIN
					PRINT('			,'+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				
				SET @INDEX = @INDEX + 1	
			END
			
			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR

	-- ===================2. 테이블 업데이트 내역 정의===================
	-- ================================================================== 

	
	-- ==================================================================
	-- ===================3. 테이블 업데이트 기본키 내역 정의===================
	
	DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
			
			IF(@P_PK_YN = 'Y')
			BEGIN
				IF(@INDEX = 0)
				BEGIN
					PRINT('		WHERE '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				ELSE
				BEGIN
					PRINT('		  AND '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				
				SET @INDEX = @INDEX + 1
					
			END
			
			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR

	-- ===================3. 테이블 업데이트 기본키 내역 정의===================
	-- =========================================================================

	PRINT(CHAR(13))
	PRINT('END')







	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT('GO')
	/* =====================================================
	-- D) 기본 프로시저 수정
	-- =================================================== */
	PRINT('-- ============================================= ')
	PRINT('-- AUTHOR: '+ @P_CREATOR)
	PRINT('-- CREATE DATE: ' + CONVERT(VARCHAR,GETDATE(),23))
	PRINT('-- DESCRIPTION: ' + @P_DESCRIPTION + '삭제')
	PRINT('-- ============================================= ')
	PRINT('-- EXEC [USP_'+ @P_TABLE_NAME +'_D]')
	PRINT('CREATE PROCEDURE [dbo].[USP_'+ @P_TABLE_NAME +'_D]' + CHAR(13))

	-- =========================================================================
	-- ===================1. 파라미터 내역 정의===================
	
	DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
			
			IF(@P_PK_YN = 'Y')
			BEGIN
				
				IF(UPPER(@P_DATA_TYPE) = 'VARCHAR' OR UPPER(@P_DATA_TYPE) = 'CHAR')
				BEGIN
					-- 일반문자열
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					
				END
				ELSE IF
					 ( UPPER(@P_DATA_TYPE) = 'NUMERIC' 
					OR UPPER(@P_DATA_TYPE) = 'DECIMAL'
					 )
				BEGIN
					-- 숫자
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
				END
				ELSE
				BEGIN
					-- 그외
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
				END
			
				SET @INDEX = @INDEX + 1

			END

			
			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR

	-- ===================1. 파라미터 내역 정의===================
	-- =========================================================================


	PRINT(CHAR(13)+'AS')
	PRINT('BEGIN')
	PRINT(CHAR(13))
	
	PRINT('		DELETE FROM ' + @P_TABLE_NAME)
	-- ==================================================================
	-- ===================2. 테이블 삭제 기본키 내역 정의===================
	
	DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
			
			IF(@P_PK_YN = 'Y')
			BEGIN
				IF(@INDEX = 0)
				BEGIN
					PRINT('		WHERE '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				ELSE
				BEGIN
					PRINT('		  AND '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				
				SET @INDEX = @INDEX + 1
					
			END
			
			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR

	-- ===================2. 테이블 삭제 기본키 내역 정의===================
	-- =========================================================================


	PRINT(CHAR(13))
	PRINT('END')


END

 

 

이 댓글을 비밀 댓글로

윈도우 서버 MSSQL 외부접속 설정하기

Posted by 고 고특파원
2019. 7. 1. 22:20 프로그래밍/MS-SQL

1. 개발환경

해당 포스팅은 다음과 같은 기본 전제를 가진다.

 

OS : WINDOWS SERVER 2016 

 

서버의 인터넷 연결 환경

 

인터넷회선 - 공유기(IPTIME) - 서버 PC

 

MSSQL 서버 구축환경

기업용 회선은 공인IP가 고정IP인 반면, 일반 가정용 인터넷 회선의 경우 공인IP가 유동 IP로 할당되어진다고 한다.

(내가 알기론 그렇다고 하는데, 만약 아니거나 잘못된 점이 있다면 지적해주시길 바랍니다.)

 

이 포스팅은 위와 같이 일반 가정집 인터넷 회선을 통해서 MS-SQL 서버를 구축하는 내용이니 잘 참고 하시길 바란다.

 

유동 IP를 고정IP 처럼 사용하기 위해서는 iptime 공유기의 DDNS기능을 활용하면 된다. (아이피타임 공유기에서 기능을 제공하여야함)

 

 

1. 특수기능 -> DDNS 설정에서 DDNS를 등록하여 주도록 한다.

DDNS 설정

 

2. 외부에서 접속할 수 있도록 DDNS URL의 포트를 열어준다.

포트포워딩 설정

 

위의 예시에서는 SQL SERVER로 사용할 서버 PC의 내부 고정 IP는 192.168.0.1 이고, 내부 포트는 SQL Server의 겨우 1433포트가 기본으로 되어있다. 바깥에서 들어오는 외부포트는 보통 다르게 해주는게 보안상 더욱 좋다고 한다. (예제에서는 그냥 동일하게 하였음) , 위 방법과 같이 일반 HTTP통신을 하기 위해서 기본포트인 80포트를 열어주면 된다.

 

3. SQL SERVER에서 IIS기능을 설치하고, IIS 웹사이트를 구성한다.

 

 

4. 80번 포트 열림 확인

다음과 같은 화면이 열린다면 80번 포트가 정상적으로 열렸다는걸 확인 할 수 있다.

 

5. Sql Server Configuration Manager 설정 및 방화벽 설정

SQL Server Configuration Manager에서 포트를 설정해 주고, 방화벽 인바운드 규칙을 설정해 주면, 외부에서 SqlServer에 접속 할 수 있다.

이 댓글을 비밀 댓글로

[MSSQL] AdventureWorks Sample Database 사용법

Posted by 고 고특파원
2016. 1. 30. 12:48 프로그래밍/MS-SQL

이번 포스팅에서는 SQL Server Management Studio에서 Sample DataBase를 사용하는 방법에 대해 알아볼게요~!


Sample Database를 사용하기 전에 SQL Server Management Studio에 대해 알아보도록 할게요.

이것은 흔히 사람들이 말하는 MS-SQL을 쉽게 관리할 수 있도록 만들어진 DBMS에요!


사람들이 흔하게 알고 있는 DBMS의 종류는 MS-SQL말고도 오라클, MYSQL등 이 있어요~ 이 것들 중에서도 MS-SQL은 Microsoft사에서 만든 DBMS이구요 보통 C#이라는 언어와 호환성이 강하기 때문에 C#언어와 주로 사용되고 있어요~


이밖에도 C#에서 오라클, MYSQL등 을 사용 할 수 있어요 ADO.NET Provider를 설치해서 참조를 추가하여 사용할수 있어요!!


Microsoft에서는 사용자에게 MS-SQL Expresss 버전을 무료로 제공하고 있구요 Sample Database를 제공하면서 사용자가 다양한 체험을 할 수 있도록 하고 있습니다~


그렇다면 Sample Database를 어떻게 사용 할 수 있는지에 대한 설명은 밑의 그림과 함께 설명하도록 할게요!!

 

 

 

1. 구글에서 'download adventure works 2008' 검색하세요!

 

위 그림에서 보듯이 첫번째페이지로 들어가세요~

 

 

 

2. 제가 사용하는 DBMS는 SQL Server Management Studio 2008 이기 때문에 위의 빨간 동그라미친 부분을 들어갔어요~

 

 

 

3. 위의 동그라미 친 부분을 다운로드 하세요~!

 

 

 

4. .zip 파일을 압축을 풀면 위의 그림처럼 3개의 파일이 나오는데요 확장자가 .mdf, .ldf 인 파일 2개만 선택해서 SQL Server 경로에 복사하여 붙여넣기 하세요

 

저같은 경우의 경로는

 

C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA << 요기

 

Ex)

C:\Program Files\Microsoft SQL Server \ (사용자마다 다름) \MSSQL\DATA 이렇게 될거에요.

 

 

 

5. 위에서 설명했던 경로에 복사하여 붙여넣기한 모습이에요~~

 

요기까지가 기본적인 준비 단계였구 이제는 확인을 해볼 차례에요!

 

 

6.자신의 SQL Server Management Studio를 실행하고 Local로 접속을 하세요~

데이터베이스 우클릭 후 연결을 클릭~

 

 

7. 데이터 베이스 파일 찾기 추가 에서 제가 복사 해주었던 파일인 AdventureWorks2008_Data.mdf 파일을 선택 후 확인해주세요~

 

 

 

확인 후 의 모습이에요~

 

 

확인을 하게 되면 위의 그림처럼 AdventureWorks2008라는 데이터베이스가 생기게 되요~!

그렇다면 이제 AdventureWorks2008 Database를 사용 하고 체험할 수 있게 된거죠~

 

 

AdventureWorks2008 Database의 Table중의 하나를 조회한 모습 이에요

 

이제 이 Sample Database를 이용하여 마음껏 쿼리 연습 및 다양한 것들을 체험할 수 있게 되었어요~~! 끄읏!

 

 

 

 

Tags
이 댓글을 비밀 댓글로