저장프로시저 MSSQL, 통과쿼리

이전에 까페에 적었던글 쉽게 찾아 보려고 블로그에 복사해서 옮겼습니다.

https://cafe.naver.com/xpath/38001

 

안녕하세요 앤디즘입니다. 통과쿼리를 사용하다가 복습용 노트를 작성합니다.

아래 링크의 피자쟁이님의 저같은 초보님들을 위한 mssql 통과쿼리(저장프로시저)활용 과 로그인님의 통과쿼리 만든는 방법 의 두 글을 보고 통과쿼리의 기본을 익혔다고 생각합니다. 통과쿼리가 처음이신분은 아래의 링크를 먼저 보시고 경험해보시길 권합니다.

​

링크:

완전초보 좌충 우돌 #21-1 FE에서 MSSQL 저장 프로시저 실행(1) VBA 실행코드

완전초보 좌충 우돌 #21-2 FE에서 MSSQL 저장 프로시저 실행(2) 계기와 목차

완전초보 좌충 우돌 #21-3 FE에서 MSSQL 저장 프로시저 실행(3) 실행하기

피자쟁이님의 저같은 초보님들을 위한 mssql 통과쿼리(저장프로시저)활용 ​

로그인님의 통과쿼리 만든는 방법

​

노트를 하고자 하는 것은 목적은 두가지입니다.

​

피자쟁이님글에 대한 복습

저장 프로시저를 사용했을때 장점에 대한 정리

​

엑세스를 무턱대고 시작하다가

인터넷과 연결되는 신세상을 만나게되고 (FE와 BE를 나누어 네트워킹 작업이 가능해지는 단계)

연결된 서버에서 계산을 시키는 또 다른 세상을 만나게되는 단계 이야기 입니다.

​

저장 프로시져를 통한 통과쿼리를 사용하는 이유는 속도측면에서 많은 연산 시간절약을 제공해줍니다.

테이블 구성이나 연산 구성을 먼저 잘해야하는건 기본이겠지만

저 같이 그때 그때 필요한것들을 배우며 적용하는 사람의 경우에는 오랜 시간동안 하면서 배워 가기 때문에

실상 내가 만든 테이블구성이 적절한지 효과적인지 잘 모르는 경우가 태반입니다.

​

가장 효과를 봤던 부분이 테이블 전체의 계산을 조건에 맞춰서 결과를 내 놓을때 입니다.

예를 들어서 누계라던가, 월별 합계라던가, 결국 통계에 관련된 계산을 하거나 거기에 조건을 입히는 경우입니다.

이 부분은 엑세스를 사용하면서 항상 시간이 걸렸고, 어떨때는 프리징이 생겨서 프로그램을 강제 종료하기도 했었습니다. 이렇게 프리징이 발생하는 구간은 저장 프로시저를 사용해서 해결을 보시면 어떨까 싶습니다.

​

프리징 구간은 개개인이 다를것이고 있는 분도, 없는 분도 있을 듯 합니다. 프리징이 있고, 매개변수에 의한 계산이 이뤄져야 하는 케이스에 대한 이야기입니다.

​

(막상 적으려고 예제 파일을 만들고 했는데...내용이 참 방대하네요. (몇시간동안 예제파일도 만들고 했는데...흑흑, 계속 읽어보니 큰 내용은 겹치는 부분이 많아서, 제가 딱 하고 싶은 말만 적어 놓겠습니다. 내용 진행상 겹치는 부분이 있습니다. 이해해주세요~)

​

MSSQL을 여시고

완전 초보 좌충우돌 #56 (#21-4) FE에서 MSSQL 저장 프로시저 실행하기(4) 통과쿼리

완전 초보 좌충우돌 #56 (#21-4) FE에서 MSSQL 저장 프로시저 실행하기(4) 통과쿼리

 

1) 저장 프로시저를 엽니다. (어떻게 여시는지 딱 아시겠죠?)

​

열면 이런애들이 나옵니다.

​

2) 녹색 글씨들은 지워줍니다(주석입니다.)

3) 빨간 글씨들은 지워주고, 파란 밑줄 부분을 눈여겨 봐줍니다.

4) 이제 저장 프로시져의 뼈대만 남았습니다.

빨간 화살표가 있는 1번 화살표에 내가 사용할 서버 이름을 적어줍니다.

​

5) 핑크색 박스 부분을 채워 넣습니다.

​

1번줄은 USE 서버이름을 적습니다.

Q. 서버이름을 어디서 보나요?

A. SSMS프로그램에 아래 검은 박스 쳐진부분입니다. 대부분 첫줄에 USE쓰고 한칸 띄면 자동으로 뜹니다.

​

11번 줄에는 프로시져 이름을 넣는 곳입니다. 어떤 이름을 넣으셔도 동작하는데 지장없습니다. 혹시나 테이블명이나 쿼리명과 연관성때문에 걱정하시는 분들이 계실지도 몰라서 설명드립니다.

하단의 SELECT 이후의 박스친 부분은 원하는 쿼리 내용을 복사해서 붙여 넣으시면 됩니다만...

간혹 함수자체가 다른 경우가 있습니다.

​

이럴때는 구글을 여시고 검색어 뒤에 MSSQL 이나 SQL을 이어서 검색해보시면 됩니다.

​

한가지 예로 날짜에서 월을 뽑으려 하는데...

format(날짜,'mm')을 했지만 결과가 안나왔습니다.

년도인 yyyy는 되는데 월은 depart(MM,[날짜]) 이런식으로 해결했습니다. 아래 그림에도 보이실 겁니다.

일단 여기까지 하셨으면 9번줄에 CREATE 라는 단어를 기억해두시고...

​

자 이제 실행을 시켜봅시다.

​

별일이 없으면 메시지가 완료됐다고 나옵니다.

​

저장 프로시저 트리뷰를 닫으시고 + 표시가 되게 클릭한번 하시고

위의 refresh버튼을 한 3번 정도 눌러주세요. 간혹 한번에 리프레시가 안될때가 있습니다.

그 뒤에 트리뷰 + 버튼을 여시면 내가 지정했던 이름으로 프로시저가 저장된것을 볼 수 있습니다.

(이름뒤에 1을 따로붙여서 예제파일로 사용중입니다. 이름뒤에 1이 붙었다고 놀라지 마세용)

결과를 보시면 아까 9 번줄에 있던 CREATE 가 ALTER로 바뀐걸 보실수 있습니다.

​

별건 아니지만 알아두셔야 할것이

1) 프로시저 수정을 하려고 열고 싶다면, 좌측 개채탐색기에서 우클릭>수정으로 열어야 합니다.

2) 이 프로시저를 수정하고 싶다면, 코딩을 수정하시고, 위에서 보셨던 실행 버튼을 누르시면 수정이됩니다.

(네임택 우클릭 저장 버튼을 찾아서 누르면..다른 반응이 나옵니다.)

​

그리고 매개변수는 피자쟁이님 글에 자세히 나옵니다.

제 프로시저에서는 이렇게 쓰였습니다.

ALTER 밑에 10번줄 보시면 @년도 int

그리고 21번줄 HAVING 뒤쪽 부분에 =@년도

​

그리고 위의 그림과 같이 인서트나 업데이트에도 사용할수 있습니다.

​

​

좋은 글을 피자쟁이님이 써주셔..제가 작게작게 몰라서 해멧던 부분들을 정리했습니다.

​

그럼 오늘도 좋은 하루되세요.

읽어주셔서 감사합니다.