프로그래밍: 24개의 글

Dropzone js를 사용한 파일업로드 [기본]

Posted by 고 고특파원
2020. 4. 2. 19:40 프로그래밍/javascript
dropzone 파일업로드

파일 업로드 기능을 구현하기 찾아보다 발견한 Dropzone에 대해 정리를 한다.

 

샘플 코드는 아래 깃 헙에 업로드하였다.

 

https://github.com/workshko/javascriptSutdy/tree/master/dropzone

 

workshko/javascriptSutdy

자바스크립트 스터디 프로젝트. Contribute to workshko/javascriptSutdy development by creating an account on GitHub.

github.com

index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>dropzone 샘플</title>

    <link rel="stylesheet" href="css/dropzone.min.css" />
</head>
<body>
    <h2>dropzone 테스트</h2>

    <div class="dropzone-area">

        <form name="fname">
            <div class="dropzone" id="fileDropzone"></div>
            <button id="btn-upload-file">서버전송</button>
        </form>

    </div>

    <script src="js/dropzone.min.js"></script>
    <script src="https://code.jquery.com/jquery-git.min.js"></script>

    <script>

        //fileDropzone dropzone 설정할 태그의 id로 지정
        Dropzone.options.fileDropzone = {

        url: '업로드할url',          //업로드할 url (ex)컨트롤러)
        init: function () {
            /* 최초 dropzone 설정시 init을 통해 호출 */
            var submitButton = document.querySelector("#btn-upload-file");
            var myDropzone = this; //closure

            submitButton.addEventListener("click", function () {
                
                console.log("업로드");
                //tell Dropzone to process all queued files
                myDropzone.processQueue(); 

            });
            
            //기존에 업로드된 서버파일이 있는 경우,
            // $.ajax({
            //     url: 'upload.php',
            //     type: 'post',
            //     data: {request: 2},
            //     dataType: 'json',
            //     success: function(response){

            //         $.each(response, function(key,value) {
            //             var mockFile = { name: value.name, size: value.size };

            //             myDropzone.emit("addedfile", mockFile);
            //             myDropzone.emit("thumbnail", mockFile, value.path);
            //             myDropzone.emit("complete", mockFile);

            //         });

            //     }
            // });

        },
        autoProcessQueue: false,    // 자동업로드 여부 (true일 경우, 바로 업로드 되어지며, false일 경우, 서버에는 올라가지 않은 상태임 processQueue() 호출시 올라간다.)
        clickable: true,            // 클릭가능여부
        thumbnailHeight: 90,        // Upload icon size
        thumbnailWidth: 90,         // Upload icon size
        maxFiles: 5,                // 업로드 파일수
        maxFilesize: 10,            // 최대업로드용량 : 10MB
        parallelUploads: 99,        // 동시파일업로드 수(이걸 지정한 수 만큼 여러파일을 한번에 컨트롤러에 넘긴다.)
        addRemoveLinks: true,       // 삭제버튼 표시 여부
        dictRemoveFile: '삭제',     // 삭제버튼 표시 텍스트
        uploadMultiple: true,       // 다중업로드 기능
        
        };

    </script>

</body>

</html>




 

여기서 작성 시 중요한 점은, 

<div class="dropzone" id="fileDropzone"></div>
 
파일이 업로드 되는 구역의 div tag의 id를 'fileDropzone' 으로 지정하였을 경우, 

스크립트단에서 Dropzone.options.fileDropzone 으로 지정하여 dropzone을 세팅하여야 한다.

 

그 외의 옵션에 대해서는 주석을 통해 기록해 두었으니 참고하도록 하고, 더 많은 옵션에 대해 알아보고 싶다면 아래 링크에서 확인해보도록 한다.

 

https://www.dropzonejs.com/

 

Dropzone.js

dropzone.js DropzoneJS is an open source library that provides drag’n’drop file uploads with image previews. It’s lightweight, doesn’t depend on any other library (like jQuery) and is highly customizable. Scroll down!

www.dropzonejs.com

다음 편에서는. Net MVC5에서 Dropzone js를 적용하여 파일 업로드를 실제로 구현해보는 예제를 해볼 예정이다.

'프로그래밍 > javascript' 카테고리의 다른 글

Dropzone js를 사용한 파일업로드 [기본]  (0) 2020.04.02
ASP.NET 에서 Ajax 사용하기.  (0) 2018.04.30
이 댓글을 비밀 댓글로

ASP.Net 오류코드와 함께 JsonResult 결과 받아오기

Posted by 고 고특파원
2020. 3. 25. 18:20 프로그래밍/ASP.NET
asp.net MVC error with Json

에러코드 500과 함께 JsonResult를 넘겨줬는데 Json 객체를 받아오지 못하는 경우가 발생하였다.

 

[컨트롤러단]

 

컨트롤러에서 500 에러코드와 함께 JsonResult 형식의 결과를 넘겨준다.

 

[뷰단]

로컬에서 Test 한경우

IIS10.0
에러코드 500에 정상적으로 메시지를 넘겨받음

 

JsonResult로 넘겨주었기 때문에 Response Header의  Content-Type : application/json; 인것을 확인할 수 있다.

그리고 넘겨받은 XMLHttpRequest 객체 안의 response 안에는 서버에서 받은 JsonResult(오류메시지)를 확인할 수 있다.

 

 

실제운영서버에서 Test 한경우

IIS8.5
에러코드 500에 정상적으로 메시지를 넘겨받지 못함

운영서버에서도 마찬가지로 JsonResult로 넘겨주었는데 Response Header의  Content-Type : text/html 이다.

넘겨받은 XMLHttpRequest 객체 안의 response 안에는 html문서형식의 값이 저장되었는 것을 볼 수 있다. 

 

로컬환경에서는 IIS 버전이 10.0 이어서 별탈없이 View에서 JsonResult를 넘겨받았다. 

그러나 실제 운영서버 환경의 경우 IIS 버전이 8.5라서 그런지..

에러코드만 넘어올뿐 response 객체에는 html문서로 넘어오는것을 발견하였다.

 

분명 JsonResult로 넘겼는데 말이다.. 여러 삽질 후 검색 끝에 아래 링크에서 해결방법을 찾았다.

 

https://gist.github.com/beccasaurus/929007/a8f820b153a1cfdee3d06a9c0a1d7ebfced8bb77

 

Without this, returning a 400 gives you just "Bad Request" ... with this, you can return a custom response

Without this, returning a 400 gives you just "Bad Request" ... with this, you can return a custom response - README.markdown

gist.github.com

 

Web.config 에서 오류가 발생하더라도 Response가 존재하면 같이 넘겨주도록 하는 옵션이 있는것 같다.

 

<system.webServer>

    <httpErrors existingResponse="PassThrough"></httpErrors>

</system.webServer>

 

 

 

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

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

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

C# 셀레니움으로 구글 로그인 하기

Posted by 고 고특파원
2020. 3. 2. 15:53 프로그래밍/C#

이전에는  C# 셀레니움을 이용하여 구글 로그인이 가능했었다.

그러나 현재 구글에 로그인을 하려고 하다보면, 자동화툴로 접근하여 로그인을 허용하지 않고 있다.

 

깃허브에서도 올라온 구글 로그인이 안되는 문제에 대한 의견들.

 

https://gist.github.com/ikegami-yukino/51b247080976cb41fe93

 

Automatically Google login by selenium

Automatically Google login by selenium. GitHub Gist: instantly share code, notes, and snippets.

gist.github.com

 

자동화툴로 진정 구글 로그인을 할수는 없을까?

 

글들을 보던중 흥미로운 내용의 유튜브 영상이 공유되었다.

 

https://www.youtube.com/watch?v=HkgDRRWrZKg&feature=youtu.be

해당 영상을 보면, stack overflow사이트를 통해 구글인증을 한 후 유튜브 사이트로 가게되면 구글에 정상적으로 로그인 되는것을 발견!

 

var driverService = ChromeDriverService.CreateDefaultService();
driverService.HideCommandPromptWindow = true; //크롬 콘솔창 숨기기 

var options = new ChromeOptions();

using (IWebDriver driver = new ChromeDriver(driverService, options))
{
	string id = "구글ID";
	string pw = "구글PW";                
 
	 driver.Url = "https://stackoverflow.com/users/login?ssrc=head";
    var googleBtn = driver.FindElement(By.ClassName("s-btn__google"));
    googleBtn.Click();
    Thread.Sleep(2000);

    var emailTag = driver.FindElement(By.Name("identifier"));
    emailTag.SendKeys(id);

    var nextBtn = driver.FindElement(By.Id("identifierNext"));
    nextBtn.Click();

    Thread.Sleep(5000);

    var passwordTag = driver.FindElement(By.Name("password"));
    passwordTag.SendKeys(pw);

    //암호 입력 버튼
    var passNextBtn = driver.FindElement(By.Id("passwordNext"));
    passNextBtn.Click();

    Thread.Sleep(5000);
    
    driver.Url = "https://www.google.co.kr/;
}



 

위와 같이 스택오버플로우 사이트에서 구글인증 후, 구글사이트로 리다이렉트 하면 정상적으로 구글에 로그인이 가능하다!

이 방법이 또 막힐지 모르겠지만, 현재까지는 이상없이 로그인이 가능하다.

 

단, 프로그램을 하면서 기계적이고 반복적으로 로그인 할 경우, 캡챠가 생성될 수 있으니 너무 빈번한 로그인을 피하는게 좋다.

 

 

혹시나 로그인이 안될경우, 계정의 보안을 낮추어 해보도록 한다!

이 댓글을 비밀 댓글로
  1. 저같은경우는 goto 문을 통해ㅐ try를 계속 실행하게했습니다

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)를 통해 반복작업을 수행한다. 그리고 마지막으로 사용한 커서는 반드시 닫아주고 초기화 해주어야 한다.

이 댓글을 비밀 댓글로

C#으로 크롤링 하기 - 다음사이트 자동로그인 기능

Posted by 고 고특파원
2019. 7. 24. 23:52 프로그래밍/C#

오늘은 C#을 이용하여 다음포털사이트에 자동으로 로그인하는 기능을 만들어 보도록 하겠다.

 

잘만 활용하면 크롤링이나 스크랩핑을 통해서 웹브라우저를 조작하여 대량의 데이터를 수집할 수 있다.

 

과정에 대해 간단히 설명하고, 작업과정을 보여주도록 하겠다.

 

1. 비쥬얼스튜디오 윈도우폼 프로젝트 생성

 

2. Nuget Package를 이용하여 셀레니움(Selenium) 설치

 

3. 셀레니움을 이용하여 크롬 브라우저 동작

 

 

1. 비쥬얼 스튜디오 Windows Forms Application 생성

프로젝트 생성

daumAutoLogin이라는 프로젝트를 생성하였다.

 

Windows Forms 간단한 디자인

폼안에 간단히 로그인 버튼과 크롬 브라우저가 작동하는 로그를 남길 수 있는 TextBox를 디자인 하였다.

 

2.  Nuget Package를 이용하여 셀레니움(Selenium) 설치

프로젝트 우클릭 후 NuGet Package For Solutioni... 을 클릭하고 셀레니움(Selenium)을 설치해준다.

 

총 3개의 WebDriver를 설치함으로써, 크롬브라우저를 조작할 수 있게 된다.

 

3. 셀레니움(Selenium)을 이용한 다음사이트 자동 로그인

var driverService = ChromeDriverService.CreateDefaultService();
driverService.HideCommandPromptWindow = true;       //크롬 콘솔창 숨기기

var options = new ChromeOptions();
//options.AddArgument("--window-position=-32000,-32000"); 
//options.AddArgument("headless");                    //윈도우창 위치값을 화면밖으로 조정

using (var driver = new ChromeDriver(driverService, options))
{

	// Go to the home page
    driver.Navigate().GoToUrl("http://www.naver.com");

    this.txtLog.Text = this.txtLog.Text + string.Format("Url 이동 : {0}", driver.Url) + Environment.NewLine;

    driver.Navigate().GoToUrl("https://logins.daum.net/accounts/signinform.do?url=https%3A%2F%2Fwww.daum.net%2F");

    this.txtLog.Text = this.txtLog.Text + string.Format("Url 이동 : {0}", driver.Url) + Environment.NewLine;

    
    var idField = driver.FindElementById("id");         //id를 통해 tag 셀렉트
    var pwField = driver.FindElementById("inputPwd");
    var loginButton = driver.FindElementById("loginBtn");

    this.txtLog.Text = this.txtLog.Text + "로그인 시도!" + Environment.NewLine;

    idField.SendKeys("로그인아이디");
    pwField.SendKeys("패스워드");

    loginButton.Click();

    this.txtLog.Text = this.txtLog.Text + string.Format("Url 이동 : {0}", driver.Url) + Environment.NewLine;
                
    string nowUrl = driver.Url;

    this.txtLog.Text = this.txtLog.Text + "로그인 성공!" + Environment.NewLine;

}

소스의 전체적인 부분을 순서대로 보면, ChromeDriver를 통해서 크롬브라우저를 실행하고, naver로 url을 이동한다.

 

다음 포털 로그인 사이트로 이동후, 포털의 아이디, 암호 엘리먼트를 찾아 자동으로 로그인 시켜주게 된다.

 

 

하나씩 살펴보도록 하겠다.

 

크롬 콘솔창 숨기기, 크롬 브라우저 숨기기 옵션

var driverService = ChromeDriverService.CreateDefaultService();
driverService.HideCommandPromptWindow = true;       //크롬 콘솔창 숨기기

var options = new ChromeOptions();
//options.AddArgument("--window-position=-32000,-32000"); 
//options.AddArgument("headless");                   	 //윈도우창 위치값을 화면밖으로 조정

이 부분은 소스를 실행하다보면 크롬 콘솔창이 보여지는데 이것을 안보이도록 설정하는 소스이다.

다음 ChromeOptions를 설정하는 것은 크롬 브라우저를 숨기기 위한 소스이다.

 

 

URL 이동, Html Tag 엘리먼트 찾기

driver.Navigate().GoToUrl("https://logins.daum.net/accounts/signinform.do?url=https%3A%2F%2Fwww.daum.net%2F");

this.txtLog.Text = this.txtLog.Text + string.Format("Url 이동 : {0}", driver.Url) + Environment.NewLine;

// Get the page elements
var idField = driver.FindElementById("id");         //id를 통해 tag 셀렉트
var pwField = driver.FindElementById("inputPwd");
var loginButton = driver.FindElementById("loginBtn");

this.txtLog.Text = this.txtLog.Text + "로그인 시도!" + Environment.NewLine;

idField.SendKeys("로그인아이디");
pwField.SendKeys("패스워드");

loginButton.Click();

GoToUrl() 메서드를 통해서 다음 포털 로그인 사이트로 이동한다.

FindElementById() 메서드를 통해서 Html 태그 엘리먼트를 찾고 값을 입력할 수 있다.

 

SendKeys() : 엘리먼트에 값을 입력

Click() : 로그인 버튼 수행

 

크롬브라우저 웹도구를 통해서 다음 포털 사이트의 엘리먼트 Id를 확인할 수 있으므로, 우리는 자동으로 로그인 작업을 수행할 수 있게 된다.

 

소스 실행 화면

 

자동로그인 시작

 

소스를 실행하고 자동로그인 시작 버튼을 하면 웹브라우저가 열린다.

 

 

네이버 사이트로 이동 하고난 후, 다음 포털 로그인 사이트로 이동

아이디와 패스워드를 자동으로 입력 후 다음 포털사이트에 정상적으로 로그인이 되는것을 확인할 수 있다.

 

크롤링은 보통 파이썬이라는 언어로 많이들 하고 있는데, C# 셀레니움을 이용해도 크롤링을 할 수 있을것 같다는 생각이 들었다.

 

C#은 직관적이고 윈도우 응용프로그램을 만들기가 쉽기 때문에, C#과 셀레니움 라이브러리를 이용하여 크롤링에 도전해보도록 하자!

 

독자도 크롤링 프로그램을 제작에 도전하고 또 한번 글을 쓰도록 하겠다.

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

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

▼▼▼▼▼▼▼▼▼▼▼
이 댓글을 비밀 댓글로
    • 2019.10.30 14:00
    비밀댓글입니다

[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

 

 

이 댓글을 비밀 댓글로