Hey, Scripting Guy!데이터베이스 스크립트의 귀환
Microsoft Scripting Guys
이 기사의 코드 다운로드: HeyScriptingGuy2008_04.exe (151KB)
Scripting Guy의 약점을 굳이 한 가지 들자면(물론 여러분도 우리가 약점이 없다는 건 잘 아시겠지만) 항상 어떤 일을 하거나 하지 않은 데 따른 결과에 너무 신경을 쓴다는 정도일 겁니다. 덕분에 늘 자신이 원하는 일보다는 다른 사람들이 원하는 일이 무엇일까를 먼저 생각하게 됩니다. 그리고 우리 자신에게 어떤 결과로 돌아올지는 생각하지 않습니다.
데이터베이스 스크립팅에 대한 기사를 예로 들어 보겠습니다. 솔직히 데이터베이스 스크립팅에 대한 기사는 쓰기가 까다롭습니다. 데이터베이스 스크립팅 자체가 어려워서가 아닙니다. 사실 상당히 쉽습니다. 문제는 Scripting Guy는 데이터베이스 관련 작업을 할 기회가 별로 없기 때문에 데이터베이스에 대한 내용을 쓸 때마다 한 번씩 더 생각해야 한다는 데 있습니다. 이미 오래전에 알았겠지만 Scripting Guy를 뽑을 때 '생각하는 능력' 같은 건 신경도 쓰지 않습니다.
사실 우리 중 애당초 깊이 생각하는 사람이 있었다면 아마 지금 Scripting Guy는 한 명도 없겠죠.
Scripting Guy는 데이터베이스 스크립팅에 대한 기사가 이렇게 쓰기 힘든데도 왜 계속 쓰고 있을까요? 대답은 간단합니다. 우리가 이런 기사를 중단하면 세상이 어떤 혼란에 빠질지 걱정되기 때문입니다. 셜록 홈즈라는 주인공에 싫증을 느낀 아서 코난 도일 경은 "마지막 사건(The Final Problem)"이라는 작품에서 절벽에서 떨어뜨려 그를 죽이기로 결심합니다. 그런데 전 세계에서 항의가 빗발치기 시작했습니다. 일부 보도에 따르면 뉴스를 접한 런던 시민들이 실제로 모자와 소매에 애도의 표시를 달았다고 합니다. "마지막 사건"이 1893년 The Strand Magazine에 실리자 20,000명이 구독을 중단했다고 전해집니다.
혹독한 대가를 치렀군요.
Scripting Guy가 데이터베이스 스크립팅에 대한 기사를 죽이면 전 세계에 이와 같은 반향을 불러오지 않을까 우려하는 것입니다. 우리 때문에 사람들이 두통과 절망에 시달리고 독자 20,000명이 TechNet Magazine 구독을 중단하게 되는 일은 절대로 원하지 않으니까요. 따라서 Scripting Guy는 데이터베이스 스크립팅에 대한 기사를 계속 쓸 것을 공표하는 바입니다. 언제 쓸 거냐고요? 지금 바로 쓴다면 어떻겠습니까?
데이터베이스에 레코드 추가
이번 달 기사에서는 몇 가지 유용한 데이터베이스 작업 비법을 소개하겠습니다. 그렇습니다. 2007년 9월 칼럼(technetmagazine.com/issues/2007/09/HeyScriptingGuy)에서 소개한 비법에 추가로 몇 가지 더 소개하는 것입니다. Scripting Guy라도 똑같은 기사를 두 번이나 우려먹고 싶지는 않으니까요.
그렇게 해서 이번 달은 얼렁뚱땅 넘어갈 수 있을 텐데도 말입니다.
먼저 데이터베이스에 레코드를 추가하는 간단한 방법을 한 가지 소개하겠습니다. C:\Scripts\Inventory.mdb라는 데이터베이스에 Computers라는 테이블이 있고, Computers 테이블에는 다음 필드가 포함되어 있다고 가정해 봅니다.
- ComputerName
- SerialNumber
- UserName
- Department
자, 이제 이 데이터베이스에 새 컴퓨터를 추가하려면 어떻게 해야 할까요? 그림 1의 코드를 살펴보겠습니다.
Figure 1 Computers 테이블에 레코드 추가
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Scripts\Inventory.mdb"
objRecordSet.Open "INSERT INTO Computers (ComputerName, SerialNumber, UserName, Department)" & _
"VALUES ('atl-ws-07', '11234', 'Ken Myer', 'Finance')", _
objConnection, adOpenStatic, adLockOptimistic
이 스크립트의 첫 번째 줄에 대해서는 자세히 설명하지 않겠습니다. 관련 배경 지식이 필요한 독자는 스크립트 센터(microsoft.com/technet/scriptcenter)의 리소스를 참조하시기 바랍니다. 여기서는 adOpenStatic과 adLockOptimistic이라는 상수를 사용해 레코드 집합의 커서 형식과 레코드 잠금을 제어한다는 정도만 이야기하겠습니다. 대단하게 들리지만 사실 간단한 내용입니다. ADODB.Connection 및 ADODB.Recordset 개체의 인스턴스를 만든 후에는 다음 명령을 사용하여 데이터베이스를 엽니다.
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Scripts\Inventory.mdb"
이것은 Microsoft® Access® 2003 데이터베이스를 여는 명령입니다. Access 2007 데이터베이스를 열려면 다음 명령을 사용해야 합니다.
objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = C:\Scripts\Inventory.accdb"
주제에서 다소 벗어난 이야기지만, 다음과 같은 유사한 명령으로 SQL Server® 데이터베이스를 열 수도 있습니다.
objConnection.Open _
"Provider=SQLOLEDB;" & _
"Data Source=atl-sql-01;" & _
"Trusted_Connection=Yes;" & _ "InitialCatalog=Inventory;" & _
"User ID=fabrikam\kenmyer;Password=34DE6t4G!;"
지금까지 살펴본 내용은 앞으로 설명할 모든 데이터베이스 스크립트의 기반이 되는 기본적인 코드입니다. 실제로 여기서 강조하고 싶은 코드 부분은 다음입니다.
objRecordSet.Open _
"INSERT INTO Computers " & _
"(ComputerName, SerialNumber, " & _
"UserName, Department) " & _
"VALUES ('atl-ws-07', 'A14B1234', " & _
"'Ken Myer', 'Finance')", _
objConnection, adOpenStatic, _
adLockOptimistic
이 코드에서는 INSERT INTO 쿼리를 사용하여 Computers 테이블에 새 레코드를 삽입합니다. 테이블 이름 다음에 값이 들어가는 모든 필드의 이름을 쉼표로 구분하고 전체를 괄호로 묶어 삽입한 것을 알 수 있습니다.
여기서 "값이 들어가는 모든 필드"가 대체 무슨 뜻인지 궁금해 하는 독자도 있을 것입니다. 좋은 질문입니다. 새로 추가하는 컴퓨터에 아직 Department가 할당되지 않았다고 가정해 봅시다. 데이터베이스에서 Department 필드에 Null 값이 허용된다면 Department에 값을 할당하지 않은 채로 그냥 두어도 됩니다. 즉, 다음과 같이 쿼리를 작성할 수 있습니다.
objRecordSet.Open _
"INSERT INTO Computers " & _
"(ComputerName, SerialNumber, " & _
"UserName) " & _
"VALUES ('atl-ws-07', 'A14B1234', " & _
"'Ken Myer')", _
objConnection, adOpenStatic, _
adLockOptimistic
값 할당과 관련하여 필드 이름 뒤에 어떤 항목이 오는지 살펴보십시오. VALUES 키워드와 각 필드에 할당할 값(역시 괄호로 묶임)이 옵니다. 필드 이름을 나열할 때 순서는 관계가 없습니다. 예를 들어 ComputerName이 데이터베이스에서 첫 번째 필드가 아닐 수도 있지만 맨 먼저 나열할 수 있습니다. 필드 이름은 이렇게 순서에 관계없이 나열할 수 있지만 값의 경우는 이와 다릅니다. 값은 필드와 동일한 순서로 나열해야 합니다. 첫 번째로 나열하는 필드가 ComputerName이면 첫 번째 값은 컴퓨터의 이름이 되어야 합니다. 그렇지 않으면 ComputerName 필드에 사용자 이름이나 일련 번호가 할당되는 등의 문제가 발생합니다.
보다시피 값을 할당하는 방법은 그다지 복잡하지 않습니다. 값의 형식을 데이터 형식에 맞추는 데만 유의하면 됩니다. 즉, 날짜 값과 문자열 값은 작은따옴표로 묶어야 하고 숫자 값과 부울 값은 작은따옴표로 묶어서는 안 됩니다.
아! 그리고 O'Brien이라는 이름처럼 값 자체에 작은따옴표가 들어 있는 경우에는 다음과 같이 작은따옴표를 하나 더 추가해 "이스케이프" 문자로 사용해야 합니다.
'O''Brien'
뭐, 이상하지만 사실이 그런 것을 어쩌겠습니까.
데이터베이스에서 레코드 삭제
SQL 쿼리를 하나만 실행하면 데이터베이스에 레코드가 추가된다니, 멋지지 않습니까? 이보다 간단한 방법은 없을 것 같습니다. 적어도 우리가 아는 한은 말이죠.
그런데 이보다 더 멋진 것은 쿼리 하나로 데이터베이스에서 여러 레코드를 삭제할 수 있다는 사실입니다.
회사에서 인사(Human Resources) 부서를 없애기로 결정했다고 가정해 봅시다. 농담이 아니라 솔직히 Microsoft HR 부서가 존재한다는 자체만으로도 신경이 쓰이니, 없으면 좋겠지만 말입니다. 어쨌든 Inventory 데이터베이스에서 Human Resources 컴퓨터를 모두 삭제하려면 어떻게 해야 할까요? 그 방법은 그림 2에서 확인할 수 있습니다.
Figure 2 여러 레코드 삭제
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Scripts\Inventory.mdb"
objRecordSet.Open "DELETE * FROM Computers Where Department='Human Resources'", _
objConnection, adOpenStatic, adLockOptimistic
보다시피 이 스크립트의 시작 부분은 새 레코드를 추가하는 스크립트와 동일합니다. 다시 말해 상수를 몇 가지 정의하여 개체를 만들고 Inventory.mdb 데이터베이스에 연결하는 것입니다. 그런 다음 DELETE 쿼리를 사용해 Computers 테이블에서 모든 레코드(*) 또는 Department 필드가 Human Resources인 레코드를 모두 삭제하면 됩니다.
objRecordSet.Open _
"DELETE * FROM Computers " & _
"Where Department='Human Resources'", _
objConnection, adOpenStatic, _
adLockOptimistic
절벽에서 뛰어내리는 일만큼 쉽지 않습니까?
셜록 홈즈를 비꼬려는 의도는 전혀 없습니다.
데이터베이스의 레코드 업데이트
Human Resources 부서의 흔적을 모두 지워버리는 것만큼 신나는 일도 없을 겁니다. (Microsoft HR 부서에 전하는 말: 다른 회사 직원들과 그 HR 부서에 대한 이야기입니다. 당연히 우리 회사는 아니죠. 우리 회사 HR 부서가 없어진다면 정말 슬픈 일일 겁니다.) 어쨌든 Inventory 데이터베이스에서 HR 컴퓨터를 모두 삭제하는 것은 최선의 방법이라 할 수 없습니다. 왜냐고요? HR 부서 직원이 사용하던 하드웨어를 모두 가지고 종적을 감추지 않는 한(흠, 실제로 그럴 것도 같은데) 해당 컴퓨터는 여전히 회사 소유이고, 따라서 데이터베이스에 포함되어 있어야 합니다. 이러한 사실을 고려하여 다음과 같이 해보겠습니다. 데이터베이스에서 해당 레코드를 삭제하는 대신 각 레코드를 업데이트하는 것입니다. 각 컴퓨터의 부서 이름을 Human Resources에서 None으로 바꾸기만 하면 됩니다. 다시 말해 그림 3의 스크립트를 실행하는 것이지요.
Figure 3 레코드 업데이트
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Scripts\Inventory.mdb"
objRecordSet.Open "UPDATE Computers Set Department = 'None' " & _
"Where Department = 'Human Resources'", _
objConnection, adOpenStatic, adLockOptimistic
이 스크립트도 SQL 쿼리를 제외하면 앞서 살펴본 스크립트와 다를 바가 없습니다. 여기서는 UPDATE 쿼리를 사용하여 Computers 테이블의 레코드를 업데이트합니다. UPDATE 키워드와 함께 레코드를 업데이트하는 방법과 업데이트할 레코드를 쿼리에 지정하는 매개 변수 2개를 추가해야 합니다.
- Set Department = 'None'. 이 매개 변수는 Department 필드의 값을 새로 지정합니다. 사실 Set Department = 'None', UserName = 'None'과 같은 구문을 사용하면 한 번에 여러 필드의 값을 설정할 수 있습니다. 이 경우 Department 필드와 UserName 필드가 모두 None으로 설정됩니다.
- Where Department = 'Human Resources'. 이 매개 변수는 업데이트할 레코드를 지정하는 표준 Where 절입니다. 예제의 경우 Department 필드가 Human Resources인 모든 레코드가 해당합니다. 이 매개 변수를 사용하는 이유는 데이터베이스의 모든 레코드를 수정하는 것이 아니라 이전에 Human Resources 부서에 속해 있던 레코드(컴퓨터)만 수정해야 하기 때문입니다.
Update 쿼리를 다른 용도로 유용하게 활용할 수도 있습니다. 회사에서 모든 직원의 급여를 10% 인상하기로 결정했다고 가정해 봅니다. (흠, 적어도 한 가지 측면에서 Scripting Guy와 아서 코난 도일 경은 공통점이 있군요. 바로, 글 속에서 허구의 세계를 창조한다는 점이죠.) 다음 스크립트는 Employees라는 테이블을 열고 각 직원의 Salary 필드를 수정합니다. 그러면 Salary 필드가 어떻게 설정되는 걸까요? 이 쿼리는 해당 필드를 직원의 현재 급여에 1.1을 곱한 값으로 설정합니다. 즉, 각 직원의 급여가 10% 인상되는 것이죠. (아, 현실에서도 급여 인상이 이렇게만 쉽다면 얼마나 좋겠습니까!) 쿼리는 다음과 같습니다.
objRecordSet.Open _
"Update Employees " & _
"Set Salary = (Salary * 1.1)", _
objConnection, adOpenStatic, _
adLockOptimistic
이게 뭐가 그렇게 멋지다는 말이냐고요? 보다시피 Update 쿼리에서 계산을 실행할 수 있습니다. 모든 계약직 직원의 계약 기간을 60일 연장한다고 생각해 보십시오. 이 경우 다음과 같은 쿼리를 유용하게 사용할 수 있을 겁니다.
objRecordSet.Open _
"Update TempEmployees " & _
"Set ContractExpirationDate = " & _
"(ContractExpirationDate + 60)", _
objConnection, adOpenStatic, _
adLockOptimistic
유용한 데이터 검색 방법
유용한 쿼리에 대해서 이야기가 나온 김에, 마지막으로 데이터베이스에서 데이터를 검색하는 몇 가지 유용한 방법을 살펴보는 것으로 칼럼을 마치도록 하겠습니다. 간단하지만 매우 유용한 스크립트 하나를 예로 들어 보겠습니다. Inventory 데이터베이스에 Price라는 필드가 있다고 가정합니다. 이름에서 알 수 있듯이 이 필드는 컴퓨터의 가격을 나타냅니다. 회사에서 가장 고가의 컴퓨터 5대를 알고 싶다면 그림 4의 샘플 스크립트를 실행하면 됩니다.
Figure 4 레코드 집합 정렬
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Scripts\Test.mdb"
objRecordSet.Open "SELECT Top 5 * FROM Computers Order By Price", _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("ComputerName")
objRecordset.MoveNext
Loop
objRecordSet.Close
objConnection.Close
그림에서 볼 수 있듯이 이 SQL 쿼리는 두 가지 작업을 실행합니다. 즉, Price를 기준으로 레코드 집합을 정렬(Order By Price)하고, SELECT Top 5로 최상위 컴퓨터 5대(가장 고가의 컴퓨터 5대)만 결과로 반환합니다. 가장 고가의 컴퓨터 10대의 목록을 표시하려면 다음 쿼리를 사용하면 됩니다.
objRecordSet.Open _
"SELECT Top 10 * FROM Computers " & _
"Order By Price", _
objConnection, adOpenStatic, _
adLockOptimistic
참고로, 레코드 집합에서 반복 실행하고 각 컴퓨터의 이름을 표시하는 코드도 추가했습니다. 이전 스크립트의 경우에는 데이터를 반환하고 표시하지 않기 때문에 이러한 코드가 필요 없었습니다.
또는 다음 쿼리로 상위 10%에 해당하는 결과를 반환할 수도 있습니다.
objRecordSet.Open _
"SELECT Top 10 PERCENT * " & _
"FROM Computers Order By Price", _
objConnection, adOpenStatic, _
adLockOptimistic
그렇다면 가장 저가의 컴퓨터를 알아보려면 어떻게 해야 할까요? 이 경우에도 문제 없습니다. 같은 방법을 사용하되, 가장 저가에서 가장 고가의 순서(내림차순)로 레코드 집합을 정렬하기만 하면 됩니다. 다시 말하면 같은 쿼리에 레코드 집합을 내림차순으로 정렬하도록 지정하는 DESC를 사용하는 것입니다.
objRecordSet.Open _
"SELECT Top 5 * FROM Computers " & _
"Order By Price DESC", _
objConnection, adOpenStatic, _
adLockOptimistic
이제 아시겠죠? 왜 유용하다고 했는지를 말입니다.
흥미로운 스크립트를 두 가지만 더 소개하겠습니다. 데이터베이스에 원래 컴퓨터 예산으로 책정된 금액을 추적하는 Budgeted라는 필드가 있다고 가정해 보겠습니다. 그리고 실제 비용과 예산 금액을 비교하려는 경우 다음 쿼리를 실행하면 예산 금액보다 비용이 많이 소요된 컴퓨터의 목록이 반환됩니다.
objRecordSet.Open _
"SELECT * FROM Computers " & _
"Where (Budgeted < Price)", _
objConnection, adOpenStatic, _
adLockOptimistic
반면, 다음 쿼리는 모든 컴퓨터의 평균 가격을 계산(SELECT AVG(Price) FROM Computers)한 다음 평균 가격보다 저렴한 모든 컴퓨터의 목록을 반환합니다.
objRecordSet.Open _
"SELECT * FROM Computers " & _
"WHERE Price < " & _
"(SELECT AVG(Price) FROM Computers)", _
objConnection, adOpenStatic, _
adLockOptimistic
예, 맞습니다. Where 절에 SELECT 쿼리를 사용한 것입니다. 이 스크립트가 어떻게 작동하냐고요? 이는 칼럼 주제를 벗어난 또 다른 내용이므로 다음을 기약해야 할 것 같습니다. 단, 보다시피 SQL 쿼리를 사용하여 모든 유용한 정보를 검색할 수 있는 길을 열어 준다는 정도만 말하겠습니다.
이야기의 교훈
이번 달 칼럼을 시작하면서 밝혔듯이 Scripting Guy에게 데이터베이스 스크립팅에 대한 기사는 상당히 부담스럽습니다. 그러나 독자 여러분에게 유용한 쿼리를 소개할 수 있었다는 점에서 기사를 작성한 것에 대해 뿌듯하게 생각합니다. 그뿐만 아니라 이번이 아니더라도 언젠가는 다루어야 할 내용이니까요. 아서 코난 도일 경도 결국 팬들의 성화에 못이겨 셜록 홈즈를 되살려내지 않았습니까. 아서 코난 도일 경은 셜록 홈즈가 죽기 전의 이야기라는 설정하에 새로운 셜록 홈즈 소설을 조심스럽게 시작했습니다. 그러나 결국 독자의 압력에 손을 들고 셜록 홈즈가 일부러 죽은 척한 것이라는 다소 억지스러운 설정과 함께 그를 살려내기에 이르렀습니다. 그런데 이에 대해 모두가 만족했고 더 이상 불만은 없었죠.
재미있게도 마지막 셜록 홈즈 이야기가 발표되었을 때 30,000명의 신규 구독자가 The Strand Magazine을 구독하기 시작했습니다. 이에 영국과 미국의 출판사들은 아서 코난 도일 경의 소설을 출판하면서 그를 전 세계에서 가장 몸값이 비싼 작가의 반열에 올렸습니다.
흠, 아서 코난 도일 경은 독자에게 사랑받던 주인공을 다시 살려낸 대가로 세계 최고의 대우를 받는 작가 반열에 올랐는데, 죽음을 가장했다는 억지스러운 이야기를 동원하지 않고도 독자의 사랑을 받는 주제를 다시 살려낸 Scripting Guy는 TechNet Magazine 관계자에게 인정이라도 받게 될지 모르겠군요.
Dr. Scripto의 Scripting Perplexer
매달 퍼즐 풀이 실력뿐만 아니라 스크립팅 기술까지 시험해 볼 수 있는 문제입니다.
2008년 4월: 문자 고르기
이번 달 문제는 파란색 빈칸에 A에서 O 사이의 문자를 삽입해 VBScript 함수 이름을 완성하는 퍼즐입니다. 각 문자(A ~ O)는 한 번만 사용해야 합니다. 각 문자가 사전순으로 나타나지는 않습니다. 삽입한 문자는 함수 이름의 첫 글자나 마지막 글자가 될 수도 있고 중간에 오는 어떤 위치의 글자도 될 수 있습니다. 예를 들어 다음 줄에는 문자 D를 삽입하여 IsDate라는 함수 이름을 완성할 수 있습니다.
(더 크게 보려면 이미지를 클릭하십시오.)
이제 다음 표에 A부터 O 사이의 문자를 삽입하여 VBScript 함수 이름을 완성해 보십시오.
**** (더 크게 보려면 이미지를 클릭하십시오.)
ANSWER:
Dr. Scripto의 Scripting Perplexer
답: 문자 고르기, 2008년 4월
(더 크게 보려면 이미지를 클릭하십시오.)
Microsoft Scripting Guys는 Microsoft에 고용되어 일하고 있는 Microsoft의 직원들입니다. 이들은 좋아하는 야구 경기와 기타 여러 활동을 하는 시간을 제외하고는 항상 TechNet 스크립트 센터를 운영합니다. 자세한 내용은 www.scriptingguys.com에서 확인하십시오.
© 2008 Microsoft Corporation 및 CMP Media, LLC. All rights reserved. 이 문서의 전부 또는 일부를 무단으로 복제하는 행위는 금지됩니다..