(MS-SQL 2000에서 사용할 수 있는 URL 쿼리 부분에 대한 강의입니다. MS-SQL 첫번째 강좌를 보고 세팅한 후 각각의 내용을 따라서 해보시면 쉽게 이해할 수 있을 것입니다. ) 2. URL 쿼리 사용
안녕하세요. 지난 강좌를 따라오는데 별 어려움은 없었는지요?
자! 오늘은 지난번에 만들어 놓은 가상디렉토리를 가지고 SQL 질의를 사용해 보려구 합니다.
맛보기로는 마지막에 조금 해 보았죠..
지금 이부분이 만약 자신에게 필요하다고 느끼신다면, 아니 XML에 대한 또다른
접근 방식을 생각해 볼 기회를 가지고 싶다면 일단은 따라해 보시기 바랍니다.
당연한 얘기지만 이부분을 따라하기 위해서는 1. XML/IIS 지원 부분을 먼저 수행하셔야 하네요.
그리고 제가 IP로 적어놓은 부분은 여러분의 IIS 서버명이나 IP등으로 바꾸면 그대로 동작합니다.
그렇게 해서 테스트해 보세요..
1. SQL문 실행
먼저 지난번의 질의어를 다시 한번 볼까요??
http://localhost/mini?sql=SELECT+*+FROM+Employees+FOR+XML+AUTO&root=ROOT
이렇게 사용했었습니다. 분석을 해보죠..
http://localhost/ 이부분은 그냥 이해 되시죠. 바로 본인 PC의 IIS 서버입니다.
그다음에 mini 가 사용되었습니다. 이것은 바로 첫 번째 강좌에서 만들었던
가상디렉토리 이름입니다.
그리고 다음을 생각해 보죠.. 우리가 HTML의 <form>태그에서 전송방식을 Get 방식을
취하게 되면 어떻게 될까요? 즉, 다음처럼 사용한다면??
<form action="http://www.word.pe.kr/test.xml" method="get">
<input type="text" name="title" value="XML">
<input type="text" name="content" value="URLQuery">
<input type="submit" name="전송">
</form>
많이 보던 형식이니 어렵지 않지요??
여기서 기본값 상태에서 "전송" 버튼을 누르면 URL창에 어떻게 표시가 되죠..
http://www.word.pe.kr/test.xml?title=XML&content="URLQuery"
이렇게 되겠죠.. 휴!! 뭘 설명하려는 데 이렇게 어렵게 했냐면요??
바로 mini 다음에 쓰인 ?나 &에 대한 겁니다.
즉, 제 말은 MSSQL에서 새롭게 만든 것이 아니라
기존의 HTML에서 사용하던 것을 썼다는 것을 설명하는 것이죠.. 이해가 되나요? ^^;;
그래서 다시 위의 질의어를 보면요.. 2개가 보입니다. 하나는
sql= ~~~ 그리고 나머지 하나는
root=~~~ 그쵸!!!
sql 부분은 척 보시면 알겠지만 질의어를 날려주는 부분입니다. 근데 좀 이상하네요..
원래대로 라면
SELECT * FROM Employees FOR XML AUTO
이어야 하는데 사이에 +기호가 들어갔습니다. 이것은 URL에서 공백을 인정하지 않기 때문이죠.
만약 다음처럼 사용하신다면 공백이 %20으로 모두 변환되어 처리될 것입니다. 인코딩이라고 하죠.
다음을 클릭해서 주소 입력 부분을 확인해 보시기 바랍니다.
http://localhost/mini?sql=SELECT * FROM Employees FOR XML AUTO&root=ROOT
확인 하셨나요? 즉, 그대로 공백으로 사용하던 아님 +를 공백대신 사용하든 상관이 없지요..
자 그런데 여기서 또 못보던 놈이 나왔군요?? FOR XML AUTO
이것은 MSSQL에 알려주는 것입니다. XML로 결과값을 반환해라 정도로 해석하면 되겠네요.
AUTO라는 것은 자동으로 알아서 변환하라는 의미입니다. FOR XML에는 세가지 모드가 있습니다.
RAW, AUTO, EXPLICIT가 그것인데요.. 일단 이름 정도만 알아두자구요..
그리고 root=ROOT란 부분이 있습니다.
MSSQL에서 XML 결과로 리턴을 하더라도 만약 여러개의 엘리먼트가 나타난다면
그것을 하나로 묶어주는 루트 엘리먼트가 필요하겠죠.
안그러면 IE에서 볼 수가 없잖아요... 그쵸. 이건 기초강좌에서도 설명이 나왔던 부분이네요.
루트엘리먼트!!!
그래서 여기에 root=ROOT라고 써준 겁니다. 여기에 다른 걸 써도 됩니다.
http://localhost/mini?sql=SELECT+*+FROM+Employees+FOR+XML+AUTO&root=computer
이렇게 사용하면 루트엘리먼트가 <coumputer>가 되겠죠..
휴~~ 이제 하나의 질의어를 설명하였습니다. 하지만 모든 형태는 여기에서부터 시작되네요..
항상 그렇듯이 기초부터 튼튼히~~
자. 이제부터는 SQL 연습이라고 해도 과언이 아닙니다. 일단 한번 따라해 보도록 하죠.
먼저 SQL 구문으로 표시를 하고 다음에 URL형태로 나타낼 것입니다.
여러분은 쿼리 분석기에서 한번 실행해 보시구요.
그다음에 URL도 실행해 보세요.
- Northwind 디비에서 Customers 테이블에 있는 고객중 상위 2명의 아이디와 이름을 가져와라.
SELECT top 2 CustomerID, ContactName FROM Customers
SELECT top 2 CustomerID, ContactName FROM Customers FOR XML AUTO
위와 같은 질의어를 쿼리 분석기에서 실행하면 첫 번째는 테이블 형태로 결과가 나오고요.
두 번째는 XML 형태로 나타납니다.
그렇죠!! 자 그럼 이번엔 IE에서 확인해 보도록 하죠..
http://localhost/mini?sql=SELECT+top+2+CustomerID,+ContactName+FROM+Customers+FOR+XML+AUTO
잉~~ 에러가 났습니다. 무엇이 문제인지 아시는 분???
에러 메시지가 뭐죠?
XML 문서에서는 상위 요소 하나만 허용됩니다. 1 줄, 60 위치 대략 이렇네요..
즉,, 루트 엘리먼트가 없다는 것입니다. 소스를 이렇게 바꿔주면 문제가 없겠죠..
http://localhost/mini?sql=SELECT+top+2+CustomerID,+ContactName+FROM+Customers+FOR+XML+AUTO&root=ROOT
참 결과를 보면 내가 가져온 아이디와 이름이 모두 속성으로 표현되어 있습니다.
그리고 엘리먼트 이름은 테이블 명으로 되구요.
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Customers CustomerID="ALFKI" ContactName="Maria Anders" />
<Customers CustomerID="ANATR" ContactName="Ana Trujillo" />
</ROOT>
AUTO로 설정하면 기본적으로 이런 형태로 가져옵니다. 테이블은 엘리먼트, 필드는 속성.....
그럼 잠깐 결과를 놓고 지난번의 기초강좌를 연상해 보실래요??
먼저 <? ~~ ?>는 제가 PI라고 했던 부분입니다. 이렇게 첫부분에 사용되면 XML 선언이라고 했죠..
즉, XML 버전 1.0을 사용하고 인코딩으로 utf-8을 사용한다..
utf-8은 유니코드를 8비트 문자로 변경하는 것이라네요..
그 다음에 보면 우리가 지정한 루트엘리먼트로 <ROOT>태그가 있구요.
그리고 테이블명이 엘리먼트로 나오고 각 속성들이 나타납니다.
<Customers> 엘리먼트는 빈 엘리먼트로 되어 있네요..
만약 이 형태를 바꾸려면 AUTO대신 RAW를 지정해도 되고요.
EXPLICT로 자신이 지정한 형태로 결과를 받아 볼 수도 있습니다.
예제를 하나 더 해보죠..
- Northwind 디비에서 Customers 와 Orders 테이블에서 고객 ID 순으로 정렬해서
고객 아이디, 주문아이디, 주문날짜를 가져와라..
먼저 SQL 문을 보도록 하죠... SELECT Customers.CustomerID, OrderID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID, OrderID
위 문장은 쿼리 분석기에서 잘 실행됩니다.
그럼. 위 문장을 XML로 IE에서 가져오도록 하려면 다음처럼 사용해야 합니다.
http://localhost/mini?sql=SELECT+Customers.CustomerID,OrderID,OrderDate+FROM+Customers,+Orders+WHERE+Customers.
CustomerID=Orders.CustomerID+Order+by+Customers.CustomerID,OrderID+FOR+XML+AUTO&root=ROOT
잘 됩니다. 하지만 과연 어떤 사람이 이렇게 긴 URL을 입력하려구 할까요??
저부텀도 당연히 안하겠습니다.
그래서 저장 프로시저나 템플릿이라는 개념이 등장합니다.
템플릿에 대해서는 다음 강좌에 설명할 계획이구요.
오늘은 먼저 저장 프로시저(Stored Procedure)를 알아보도록 하죠..
2. 저장 프로시저(Stored Procedure) 실행
저장 프로시저는 전혀 새로운 개념이 아닙니다. 기존의 디비에 있던 것이죠.
잠깐 MSSQL에서 SQL문이 수행되는 과정을 설명해 보도록 할까요?
만약 새로운 SQL문이 들어왔다. MSSQL은 어떻게 처리를 할까요??
Parsing
이름 확인
보안 점검
최적화 (Optimize)
Compile
이러한 과정을 거친답니다. 즉, 먼저 Parsing (구문분석)을 수행하고요.
이름확인, 각각의 사용된 이름이 정확한가 확인합니다.
그리고 나서 해당 사용자가 해당 구문을 사용할 권리가 있는지 확인하고 최적화후
컴파일한다는 것이지요.
그러나 만약 저장 프로시저를 사용한다면?
만들 때는
Parsing
이름 확인
보안 점검
첫실행때
최적화 (Optimize)
Compile
이렇게 됩니다. 그렇다면 두 번째 실행부터는 메모리에서 검색해서 바로 실행할 수 있습니다.
즉, 일반적인 SQL을 사용할 경우에도 저장 프로시저를 사용한다면
트래픽이나 성능향상을 가져올 수 있다는 것이죠..
넵.. 잠시 제가 디비 이야기를 했습니다. 실제로 전 디비를 잘은 몰라요..
그냥 어디서 주워들은 것 적어 놓았네요..
그런데.. 또하나의 저장 프로시저의 장점이 바로 편리하다는 것입니다.
일일이 SQL 구문을 모두 입력하지 않아도 되니까요.
그걸 여기에서 활용해 보자는 것이지요.. 함 시작해 볼까요??
- NorthWind 디비에서 Categories 테이블의 카테고리이름을 가져와라..
일단 SQL문은 간단합니다. 다음처럼 작성하면 되겠네요.
SELECT CategoryName
FROM Categories
자 그러나 우리는 먼저 이걸 저장 프로시저로 만들어야 할 것이구요.
그리고 결과를 XML로 가져와야겠네요. 다음처럼 하세요.
CREATE PROC CategoryInfo
AS
SELECT CategoryName
FROM Categories
FOR XML AUTO
이렇게 먼저 CategoryInfo라는 저장 프로시저를 만들었습니다.
그럼 우리가 일반적으로 저장 프로시저를 수행할 때 어떻게 했냐면요?
EXEC 저장프로시저명
EXEC CategoryInfo
이런 형태로 사용했잖아요.. 그래서 URL 쿼리도 다음처럼만 사용하시면 됩니다.
http://localhost/mini?sql=EXEC+CategoryInfo&root=ROOT
어때요?? 이해가 되나요?.. 혹!! 이해가 잘 안되면 Q&A 게시판이나 저한테 멜 주세요.. ^_^;;
다음 질의 하나 더 해볼까요?
- NorthWind 디비에서 Categories 테이블의 주어진 카테고리 ID의 카테고리 이름을 검색해라.
이 경우에는 주어진 카테고리 ID를 입력을 받아야 합니다.
이것은 매개변수를 활용해서 처리할 수 있죠..
저장 프로시저는 다음과 같이 만들어 주면 됩니다.
CREATE PROC CategoryInfoWithInputParam
@CategoryID int = 1
AS
SELECT CategoryName
FROM Categories
WHERE Categories.CategoryID = @CategoryID
FOR XML AUTO
혹, 저장 프로시저를 잘 모르시는 분은 어려울 수도 있겠네요.
웹상에서 MSSQL 강좌는 무지 많거든요. 유명하신 분들 많으니깐 금방 배울 수 있을 거예요.
여기서는 간단히 설명드리겠습니다.
@CategoryID int = 1
이부분이 대충 봐서 매개변수 선언인 것 같죠.
만약 매개변수 값을 지정하지 않으면 기본값으로 1이 지정됩니다.
그리고 AS 키워드 다음에 수행할 SQL 문장을 적어 놓았습니다.
자 이걸 URL 쿼리로 함 날려 볼까여?? 같은 방식으로 하면 됩니다.
http://localhost/mini?sql=EXEC+CategoryInfoWithInputParam&root=ROOT
어때요 잘 되죠.. 위에서는매개변수값을 넣지 않았네요..
기본적으로 CategoryID=1인 것이 출력되었겠죠..
만약 CategoryID가 2인 값을 가져오려면 다음 둘중의 아무것이나 사용하셔도 됩니다.
http://localhost/mini?sql=EXEC+CategoryInfoWithInputParam+2&root=ROOT
http://localhost/mini?sql=EXEC+CategoryInfoWithInputParam+@CategoryID=2&root=ROOT
자.. 이런 형태로 저장 프로시저를 사용할 수 있다는 것입니다.
위 세 개의 문장이 어떻게 다른지 한번식 살펴보세요.. 휴~~
참!! 그런데 때론 root=ROOT 부분을 URL에 넣지 않도록 만들고 싶을 때도 있겠네요..??
그럴 때는 저장 프로시저안에 SELECT '<ROOT>'와 같이 루트 엘리먼트를 만들어 주면 됩니다.
그렇게 하기 위한 저장프로시저를 한번 만들어 볼까요?...
CREATE PROC CategoryInfoWithoutRoot
@CategoryID int = 1
AS
SELECT '<ROOT>'
SELECT CategoryName
FROM Categories
WHERE Categories.CategoryID = @CategoryID
FOR XML AUTO
SELECT '</ROOT>'
진하게 표시한 부분이 새로 추가된 부분입니다.
이럴 경우에는 URL에 루트 엘리먼트를 지정하지 않아도 된다는 거죠. 즉, 다음처럼 사용하면 됩니다.
http://localhost/mini?sql=EXEC+CategoryInfoWithoutRoot+3
이상으로 저장 프로시저에 대한 설명을 마치려고 합니다.
저장 프로시저는 기본적으로 DB에서 지원하던 것을 가져다가 사용한 것입니다.
하지만 앞으로 우리가 해볼 템플릿이나 스키마는 XML 측면에서 접근한 것이니
좀더 관심을 가지고 볼 만하겠죠..
3. XSL 적용
다음으로는 XSL을 적용하는 것을 잠깐만 보겠습니다.
계속 그저 텍스트로만 IE에서 뿌려주니깐..
어떻게 XSL을 적용할 수 있을까? 하고 궁금해 하실 것 같아서요.
지금 설명하는 것은 URL 쿼리에서 사용할 수 있는 방법입니다.
먼저 위에서 배운 질의중의 하나를 해 보죠..
http://localhost/mini?sql=SELECT+FirstName,LastName+FROM+Employees+FOR+XML+AUTO&root=root
바로 Employees 테이블에서 이름을 가져오는 예제가 되겠네요..
결과는 우리가 지금까지 수행한 것과 같습니다.
그러나 다음은 웹에서 보는 일반적인 형태로 나타나게 되겠지요..
http://localhost/mini?sql=SELECT+FirstName,LastName+FROM+Employees+FOR+XML+AUTO&root=root&xsl=employee.xsl
자! 위 둘의 차이점은 하나입니다.
바로 xsl=employee.xsl 이거죠..
즉, xsl로 employee.xsl을 사용한다는 것입니다.
그렇다면 employee.xsl의 내용은 아래를 눌러보세요..
이걸 우리가 전에 간단히 배운 xsl문서처럼 해석해 보려구 해보세요..
그리 어렵지는 않을 겁니다. 꼭~~ 확인해 보세요..
employee.xsl 소스보기
XSL에 대해서는 일단 여러분들이 함 찾아보세요.. 기회가 되면 강좌를 할까합니다.
그러니 여기서는 여러분이 소스를 한번씩 분석해 보세요.. 할수 있을거예요.
휴~~ SQL 부분의 강좌가 생각보담 어렵네요.. 어쨌거나..
그럼.. 2002/08/21 From 미니
|
☞ 내 이글루에 이 글과 관련된 글 쓰기 (트랙백 보내기) [도움말]