모두 sql 쿼리 분석기 를 열어주세요. sp 의 구문은 다음과 같습니다.
| CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ] |
뭐가 이리 복잡하냐구요??? 쪼금만 기다리세요.. 예제를 한번 보시면 아~~~ 그거구나... 하실 거예요.
백견이 불여일타 !!! 그럼 예제 들어갑니다. 쿼리 분석기에서 다음의 소스를 코딩하시고 실행을 해보세요. 참고로 실행하는 방법은 실행을 할 소스부분을 선택을 하시고, F5 또는 Alt-X 또는 Ctrl-E 를 누르시면 됩니다.
|
sp_print_name (요건 작성하지 마세요) create proc sp_print_name |
보시는 대로 sp_print_name 이라는 이름으로 sp를 만들었습니다. 하나씩 설명을 해 볼까요??
| create proc sp_print_name --> sp_print_name 이라는 이름으로 sp를 만들어라 @strName varchar(20) --> @strName 은 변수이름입니다. M$ SQL 에서는 변수에다가는 앞에 @을 붙여줘서 테이블 컬럼과의 구분을 하게 되죠. 참고로 이 변수는 parameter입니다. As --> sp의 시작을 알립니다. > Begin --> M$ SQL에서는 블럭 처리를 begin ~ end 로 처리합니다. 참고로 C 언어의 경우는 { ~ }, ASP 는 블럭이 없죠? print @strName --> 결과를 화면에 찍어라 ASP로 생각을 하시면 Response.Write strName 정도가 되겠죠? Set @strName = 'Hello World!!!' --> 변수에 어떤 값을 넣어줄 때는 Set 또는 Select를 사용합니다. print @strName End |
그리고, 쿼리 분석기(즉, Query Analyzer 에서 다음처럼 실행해 보세요)
| sp_print_name '깜찍이 ' |
라고 실행을 시켜보세요.. 그럼 어떻게 나옵니까??
| 깜찍이 Hello World !!! |
그렇습니다. 위와 같이 나옵니다. 어떻습니까? 간단하죠? 아니라구요?? 그럼 이해를 쉽게 하기 위해서 위의 소스를 ASP로 변환해서 보겠습니다.
sp_Hello.asp |
어떻습니까? 결과는 똑같이 나오죠? 제가 이렇게 굳이 ASP와 결부를 시키는 것은 이글을 읽으시는 분들이 ASP에 익숙하신 분들이라 판단이 들어서 입니다. 이 정도라면 첫번째 예제로 충분하리라고 생각됩니다.
아니라구요? 너무 쉽다구요?? 이런... ㅠ.ㅠ
그렇습니다. 제가 여러분의 실력을 너무 낮게 본게 아닌가 생각됩니다.. 죄송합니다. 전 지금 반성하는 기분으로 두손 다 들고 치고 있습니다.
간단한 sp를 작성해 봤는데요.. 이제 두번째 sp를 만들어보죠.. 어떤 언어를 하던지 단계가 있잖아요.
첨에는 hello world, 글구 두번째는 ~~~음 ~~~ 머더라?? 기억이 안나는 관계로 전 그냥 구구단을 하겠습니다. ^^
제가 C 언어 할때 아마 처음 쯤으로 기억이 되거든요.. 소스는 다음과 같습니다. ( 참고로 다시한번 말씀드립니다. sp는 SQL Server에서 쿼리 분석기에서 실행하시고, asp는 말씀안드려도 아시죠??)
| Create proc sp_gugudan as Begin Declare @intI int, @intJ int Set @intI = 1 Set @intJ = 1 While ( @intI < 10 ) Begin While ( @intJ < 10 ) Begin print convert( char(2), @intI ) + '* ' + cast( @intJ as char(2)) + ' = ' + convert( char(2), @intI * @intJ ) Set @intJ = @intJ + 1 End Set @intI = @intI + 1 Set @intJ = 1 End End |
자, 다 코딩하셨습니까?? 그럼 다음에 해야 할 일은?? 물론 실행을 해서 확인을 해보셔야죠...
| Exec sp_gugudan |
자 , 결과가 어떻습니까? 그 옛날 손바닥 맞아가면서 외운 구구단이 주우욱 나오지 않습니까?? 신기하지 않습니까??
아니라구요?? 쩝... 전 첨에 했을 때 신기했는데... 자 그럼 소스를 설명 해 보지요..
| create proc sp_gugudan as Begin Declare @intI int, --> 변수를 선언하는 부분입니다. 변수는 다음과 같이 declare를 이용해서 선언합니다. @intJ int 1회에 했던 내용은 parameter로 받는 부분이어서 decare를 사용하지 않았구요.. Set @intI = 1 --> 변수를 초기화합니다. 제가 처음에 언어를 배울 때 초기화 안해서 많이 고생했었어요.. Set @intJ = 1 버릇을 단단히 들이면 좋더라고요..참고로 C#을 하시려면 이것은 꼭 필요합니다. While ( @intI < 10 ) --> while Loop 입니다. 괄호안의 값이 참일 동안 계속 진행된다는 것.. 알고 계시죠? Begin While ( @intJ < 10 ) Begin print convert( char(2), @intI ) + '* ' + cast( @intJ as char(2)) + ' = ' + convert( char(2), @intI * @intJ ) --> convert 와 cast 는 동일한 기능을 하는 함수입니다. 그런데 이것을 같이 쓴 이유는?? 맞습니다. 여러분이 원하는걸로 골라서 쓰세요.. 골라쓰는 재미가 있습니다. Set @intJ = @intJ + 1 --> 한번 계산했으니까 1을 증가시켜야 구구단이 되겠죠? End Set @intI = @intI + 1 --> 한번 계산했으니까 1을 증가시켜야 구구단이 되겠죠? Set @intJ = 1 --> 다음 단에서는 1부터 구구단을 해야 하니까 다시 1로 맞춰주는 것이죠.. End End |
간단한 설명인데 이정도면 되겠지요? 그럼 이걸 저번회처럼 ASP로 변환해볼까요?
어떻습니까? 똑같은 결과가 나오죠?
이렇게 해서 기초적인 sp를 다루었습니다. 그럼 이제 본격적으로 sp를 들어가겠습니다. sp를 왜 사용하겠습니까?
굳이 이것처럼 글자만 찍으려고 sp를 사용하려 한다면 저같아도 안합니다. 역시 sp를 하는 이유는 DB안에서 복잡한 DB작업을 할 수 있기 때문이죠. 물론 transaction 처리까지 말이죠. 우선 사용할 테이블부터 만듭니다.
| Create Table Address ( idx int identity( 1, 1) primary key Not Null, -- identity( x, y ) 라는 함수는 x 부터 y씩 증가하면서 자동으로 값을 넣어준다는 것입니다. name varchar(20) Not Null, tel varchar(15) Not Null, email varchar(50) Null, regid varchar(15) Not Null, sex tinyInt Not Null -- 남자일 경우는 0, 여자일 경우는 1 ) |
그리고 다음과 같이 sp를 만들어 보세요.
| Create Proc sp_Insert_Address @strName varchar(20), @strTel varchar(15), @strEmail varchar(50), @strRegID varchar(15), @intIdx int output As Begin Declare @intSex tinyInt Begin Tran If ( subString( @strRegID, 8, 1 ) = '1' ) Set @intSex = 0 else Set @intSex = 1 Insert Into Address ( name, tel, email, regid, sex ) values ( @strName, @strTel, @strEmail, @strRegID, @intSex ) Set @intIdx = @@identity If ( @@error <> 0 ) RollBack Tran else Commit Tran End |
위의 sp는 입력된 값을 DB에 반영시키는 sp 입니다. 그럼 또 설명해 볼까요??
| Create Proc sp_Insert_Address @strName varchar(20), @strTel varchar(15), @strEmail varchar(50), @strRegID varchar(15), --> 여기까지는 아시겠죠? @intIdx int output --> 요놈은 처음보네요.. 하지만 걱정하지 마세요... 제가 있잖습니까? 요놈은 output이라는 놈으로 다 실행하고 난 다음에 이 값을 리턴해주죠. 여러개도 쓸 수 있걸랑요. 전 사실 요놈이 좋아서 이걸 자주 쓴답니다. 만일 100개의 게시판의 게시물 숫자를 구하려면... ASP에서 레코드셋을 백번 열어서 확인해야 하죠?? 하지만 여기에서는 output을 사용해서 한번에 다 받을 수 있죠... As Begin Declare @intSex tinyInt --> 전에 했던 변수선언입니다. Begin Tran --> 엇? 첨 보는 놈이죠?? 여러분 Transaction 이라고 들어보셨죠? 예제로 많이 쓰는 것이 은행에서 돈 뽑을 때를 예로 많이 들죠.. 만일 제가 돈을 뽑는데 처리가 거의 끝났습니다. 돈이 나와야 할 순간... 앗?? 이런 정전이 되어버렸네... 이런 띠바... ㅠ.ㅠ 이럴 경우 어떻게 합니까? DB에서는 돈이 나간걸로 처리되어있는데... 걱정마세요.. 이럴 때 있는 것이 transaction이랍니다. transaction은 모든 과정이 제대로 끝나지 않고 에러가 발생할 경우는 처음 상태로 모든 것일 되돌려 놓는답니다. ( RollBack ) 그렇지 않고 제대로 끝난다면 모든 것을 처리하죠. ( Commit ) If ( subString( @strRegID, 8, 1 ) = '1' ) --> substring( a, b, c ) : a 의 문자의 b 부터 c까지 뽑아와라.. 예를 들면 subString( 'abcdef', 2, 2) == > 'bc' 이렇게 된답니다. Set @intSex = 0 else Set @intSex = 1 Insert Into Address ( name, tel, email, regid, sex ) values ( @strName, @strTel, @strEmail, @strRegID, @intSex ) Set @intIdx = @@identity --> 아까 identity를 한 것 기억나시죠?? 그것은 아무 값을 안 넣어도 숫자가 자동으로 증가되어서 들어간다고 말씀드렸죠? 그런데 그 값을 어떻게 압니까? 그건 @@identity 라는 전역번수에 들어갑니다. 물론 휘발성입니다. 계속 바뀐다는 말이죠. 참고로 @ 1개면 지역변수, @ 2개면 전역변수입니다. If ( @@error <> 0 ) RollBack Tran else Commit Tran --> 아까 말씀드린 RollBack과 Commit 입니다. @ 2개면 뭔지 기억나시죠? 맞습니다. 전역변수입니다. 이 @@error에는 어떤 값이 저장되느냐... 에러 번호가 저장됩니다. 에러번호가 0이라면 정상적으로 처리된 것이고, 그것이 아니라면 어떤 좋지않은 일이 발생한 것이겠죠? End |
| Exec sp_Insert_Address '이름', '전화번호', '이메일', '주민등록번호', 0 |
위의 값을 넣어보시고 실행해 보세요. 주민등록번호는 자신의 번호를 넣어서 실행하세요. 안그러면 에러납니다.
이제 이것을 ASP 페이지에서 호출하는 방법을 말씀드려야겠죠?? 앗 벌써 시간이 이렇게?? 이제 저도 일을 해야하겠네요.. 오늘 벌써 오후인데 일을 해 놓은게 없네요... ㅠ.ㅠ
일부러 소스에 주석처리를 다 하지 않았습니다. 그러면 여러분들이 소스를 한번 더 보시고 도움이 되실 듯해서요..
자, 그럼... 이제까지 한 것들을 잠시 되돌아 볼까요?? 처음에는 sp를 작성해서 그 유명한 "Hello World !!!" 프로그램을 작성해봤구요.. 그리고 다음에는 ASP로 같은 프로그램을 작성해 보았구요.. 그리고 또 멀 했더라...
아,,, 구구단을 했었죠?? 그리고 주소록을 만들기 위해서 주소록 테이블을 만들었고, 데이터를 넣을 수 있는 sp를 작성해 보았습니다.
오늘은 무엇을 할 것인가... 오늘은 그중에 제일 중요한 것입니다. 사실 이 부분이 없다면 이 강좌는 안하는 것이나 마찬가지이지요.. 바로 ASP에 sp를 붙이는 방법에 관한 것입니다.
다시 한번 강조합니다. sp를 사용하는 이유는 간단히 다음과 같습니다.
* SP는 DB에서 컴파일됩니다. 이것은 execution plan을 만드므로 당연히 속도가 빨라진다는 말입니다.
* 테이블이 sp에 의해서 수정될 수 있도록 했으므로 데이터를 좀 더 안전하게 핸들링할 수 있습니다.
* ASP 코딩이 좀 더 간결해진다.
* Parameter 를 여러개 받을 수 있고, 여러개를 리턴할 수 있다.
* 가장 중요한 것은 일반 SQL 문장보다는 훨씬 빠르다는 것입니다.
그럼 간단한 sp를 ASP에서 호출하는 예제부터 시작하겠습니다
| Create Procedure sp_Example As Begin Insert into Address ( name, tel, email, regid, sex ) values ( '홍길동', '011-000-0000', 'admin@test.com, '111111-*******', 0) End |
위의 sp가 하는 일은 무엇입니까?
맞습니다. 바로 주소록 테이블에 한개의 레코드를 넣는 일을 합니다. 물론 파라미터는 아무것도 없죠.. 그럼 이걸 ASP에서 호출하는 예제를 보시겠습니다. 우선 C:\program files\common files\systems\ado\adovbs.inc 파일을 예제와 동일한 폴더에 복사를 하세요. 아시겠지만 상수를 사용할 수 있도록 하는 것입니다.
|
ADO 상수들을 사용하기 위해서 Adovbs.inc 를 편입하는 방법보다는 타입 라이브러리를 직접 걸어주는 것이 더 바람직한 방법입니다. 아래의 강좌를 참고하시기 바랍니다. |
태오 커멘트
sp_Example.asp
|
자 이렇게 하시고 한번 실행해 보시죠.. 브라우저에는 아무런 변화가 없고 하얀 화면만 나오죠? "이게 뭐야 !!! 너 사기치는거지???" 라고 말씀하시려고 했죠?? 하하하...
당연히 아무런 반응이 없죠. ASP 소스에는 브라우저에 무엇을 쓰라는 명령이 아무것도 없으니까요.. 그럼 쿼리 분석기에 가셔서 Select * from Address 를 한번 실행해 보세요..
앗!!! 그렇습니다. 한개의 레코드가 들어와 있습니다. 아까 말씀드린 것과 같이 ASP 소스 깔끔해지고 얼마나 좋습니까... sp를 ASP에 넣는 방식은 이렇습니다.
<<< 소스 설명 >>>
|
|
일부러 소스에 주석처리를 다 하지 않았습니다. 그러면 여러분들이 소스를 한번 더 보시고 도움이 되실 듯해서요.. 소스를 그대로 코딩하고 확인하는 것 보다는 부디 흐름과 방법을 이해하시려 노력해 보시기 바랍니다. 방법을 알면 어려울 것이 없으니까요...
sp를 만들어서 그걸 ASP에다가 붙이는걸 했었죠?하지만 그건 단순히 sp에 입력되어있는 레코드를 붙이는 것이구요..
오늘 할 것은 form에서 받아와서 그 값을 DB에 반영시키는 것을 할 것입니다.
눈 크게 뜨시구요..
자 그럼 시작합니다.
| ######################################################### Create Proc sp_Insert_Address @strName varchar(20), @strTel varchar(15), @strEmail varchar(50), @strRegID varchar(15), @intIdx int output As Begin Declare @intSex tinyInt Begin Tran If ( subString( @strRegID, 8, 1 ) = '1' ) Set @intSex = 0 else Set @intSex = 1 Insert Into Address ( name, tel, email, regid, sex ) values ( @strName, @strTel, @strEmail, @strRegID, @intSex ) Set @intIdx = @@identity If ( @@error <> 0 ) RollBack Tran else Commit Tran End ######################################################### |
자.. 위의 sp는 무엇일까요?
맞습니다.
2부에서 작성해 두었던 sp 입니다.
이것을 갖고 놀겠습니다.
우선 htm파일을 하나 만듭니다. 주소를 입력하는 페이지 이지요..
<< address.write.htm >>
| ######################################################### |
위의 소스는 설명을 생략해도 무방하겠죠?
그리고 다음의 ASP 소스를 만듭니다.
<< address.write_ok.htm >>
| ######################################################### ######################################################### |
안되시는 분은 C:\program files\common files\systems\ado\adovbs.inc 파일을 같은 폴더에 복사하시고 다시 해보세요.
그럼 설명을 한번 해 볼까요?
지난 강좌에 설명된 부분은 설명에서 제외를 하겠습니다.
모르는 부분이 나오는 분은 빨리 지난강좌를 열어보세요..
| with objCmd .ActiveConnection = objCon .CommandText = "sp_Insert_Address" .CommandType = adCmdStoredProc ' 바로 이 부분이 새로 추가된 부분입니다. ' 말하자면 sp로 parameter를 전달해주는 역할이라고 할 수 있지요. ' 첨보는 놈이 있습니다. 무엇이지요? 맞습니다. Parameters라는 놈입니다. ' s 가 붙었으니 collection이라는 것은 맞는듯하고.. ' 무엇을 하는 collection일까요? ' command 객체가 sp에 강한 이유가 바로 요놈 때문입니다. ' parameters 객체에다가 쿼리문을 미리 넣어두고 sp를 돌리는 것이라고 간단하게 설명할 수 있습니다. ' 쿼리문자체도 컴파일이 될 수 있다는 것을 아시는지요?? ' 그렇습니다. 컴파일이 되고 sp를 돌린다는 의미입니다. ' CreateParameter의 문법은 다음과 같습니다. ' 첫번째는 변수이름 ( sp의 변수는 @이 하나 붙는다고 했지요? ) ' 두번째는 변수타입 ( adovbs를 열어서 adVarWChar를 찾아보세요. 그 근처에 있는 것들이 여기에 들어가는 변수타입입니다.) ' 세번째는 이 변수가 input( DB에 넘겨주는 변수)인지 output( DB에서 넘겨받는 변수)인지를 나타내는 키워드입니다. ' 네번째는 변수의 길이이구요.( 참고로 integer는 0 입니다.) .Parameters.Append .CreateParameter("@strName", adVarWChar, adParamInput, 20) .Parameters.Append .CreateParameter("@strTel", adVarWChar, adParamInput,15) .Parameters.Append .CreateParameter("@strEmail", adVarWChar, adParamInput,50) .Parameters.Append .CreateParameter("@strRegID", adVarWChar, adParamInput,15) .Parameters.Append .CreateParameter("@intResult", adInteger, adParamOutPut,0) ' 이부분은 간단하게 보시면 됩니다. ' input 일 경우에 그 값을 넣어주는 것입니다. .Parameters("@strName") = strName .Parameters("@strTel") = strTel .Parameters("@strEmail") = strEmail .Parameters("@strRegID") = strRegID ' output 일 경우는 넣어주지 않아도 상관이 없습니다. ' 하지만 전 초기화 한다는 의미에서 0으로 넣어줍니다. .Parameters("@intResult") = 0 .Execute , , adExecuteNoRecords ' 이 부분도 첨 보는 문장이죠? ' 바로 output으로 지정해 둔 것을 받는 부분입니다. Dim intResult intResult = .Parameters("@intResult") End with Response.write intRecord if ( intResult <> 0 ) then Response.Write "데이터 저장중 에러가 발생했습니다. 다시 한번 시도해 주세요. 확인" else Response.Write "데이터 저장 성공 !!! 확인" end if |
'asp' 카테고리의 다른 글
| 진수 변환 시키기 (0) | 2007.05.02 |
|---|---|
| ASP로 다국어 지원 사이트 만들기 (0) | 2007.05.02 |
| Command 객체를 이용한 트랜잭션 처리 (1) | 2007.05.02 |
| 정적 sql 사용 예제 (3) | 2007.05.02 |
| Transaction controll (1) | 2007.05.02 |