T-SQL指令快速入門(七)—WHILE的使用(S )

Size: px

Start display at page:

Download "T-SQL指令快速入門(七)—WHILE的使用(S )"

株屠
7 years ago
Views:

T-SQL 指令快速入門 ( 七 ) WHILE 的使用作者 : 楊先民審稿 : 陳俊宇文章編號 :S030401403 出刊日期 :2003/04/21 資料庫類型 :SQL Server 2000 本文相關資訊 : 探討針對單句 T-SQL 可以直接改變交易設定的鎖定提示 (Lock Hint), 以及與死結 (Dead Lock) 相關的議題本文難易等級 : 易難前言

1 T-SQL 指令快速入門 ( 七 ) WHILE 的使用作者 : 楊先民審稿 : 陳俊宇文章編號 :S 出刊日期 :2003/04/21 資料庫類型 :SQL Server 2000 本文相關資訊 : 探討針對單句 T-SQL 可以直接改變交易設定的鎖定提示 (Lock Hint), 以及與死結 (Dead Lock) 相關的議題本文難易等級 : 易難前言前幾期都在介紹 T-SQL 指令的 SELECT 部分, 接下來在這一期將要介紹 WHILE 指令的使用, 其實 WHILE 指令算是沒有那麼常用, 但如果你需要它的時候, 又恨不得自己馬上就能會的指令, 本期就來介紹 WHILE 指令的使用吧 WHILE 的使用時機當你的 T-SQL 指令, 需要用到 WHILE 時, 意即會使用到迴圈, 也就是程式執行一次可能會不夠, 但要執行多少次呢? 也說不上來, 也許是執行 100 次, 也許是當條件達成時 ( 這個條件得你自己去設定 ) 程式才會停止, 總而言之, 如果一段指令需要反覆執行數次時, 你就可以使用 WHILE 指令了先來看一下 WHILE 指令的基本語法, 如程式一 : 發行人 : 劉致宏總編 : 張智凱主編 : 楊先民社務顧問 : 邱世萍技術編輯 : 羅慧真編輯顧問 : 許薰尹羅慧真張書源胡百敬許嘉仁申建忠楊先民陳俊宇高光弘鄭淑芬何致億美術設計 : 魏吉芝陳昀行銷企劃 : 曹雅瑩陳秀慧發行服務 : 謝佩珊劉清滿發行所 : 毅達行銷顧問股份有限公司客戶服務 :service@dbworld.com.tw 本電子刊物之所載標誌名稱分屬各該公司所有, 非經授權請勿轉載使用, 版權所有如經查證依法律追訴 1

WHILE ( 條件陳述句 ) 程式撰寫程式一 :WHILE 指令的基本語法示說明當然, 程式一寫的是最簡單的寫法, 是為了避免使用者混亂, 所以就寫很簡單的寫法, 例如寫一個最簡單的例子, 計算由 1 加到 100 的結果, 這一想就覺得應該要用 WHILE 迴圈來完成, 試試看該怎麼撰寫, 如程式二 :

2 WHILE ( 條件陳述句 ) 程式撰寫程式一 :WHILE 指令的基本語法示說明當然, 程式一寫的是最簡單的寫法, 是為了避免使用者混亂, 所以就寫很簡單的寫法, 例如寫一個最簡單的例子, 計算由 1 加到 100 的結果, 這一想就覺得應該要用 WHILE 迴圈來完成, 試試看該怎麼撰寫, 如程式二 : int int = 0 WHILE(@VAR1<=100) =@VAR1 + 1 程式二 : 由 1 加到 100 的寫法 2

3 程式二的例子, 可以發現, 資料型別為整數, 然後在 WHILE 後面所接的條件句是的值小於 100 時, 則進入迴圈做事情,WHILE 迴圈利用這個程式區塊包起來, 很容易知道該執行到什麼地方可能會結束的初始值被填入 0, 所以這個迴圈是進去定了現在只看迴圈何時會跳離, 當程式執行到之後, 如果還沒有跳離 WHILE 迴圈, 是否小於 100, 小於 100 為止所以如果在整個這個變數都一直沒有改變, 那就會形成無窮迴圈, 永遠也沒有辦法跳出來了, 所以在寫程式時要避免這個問題也因此, 程式二在之前寫了段 +1, 的變數加 1, 這樣就不會有無窮迴圈的情況了當然, 程式二的簡單句子, 最後所得到的結果是 5050, 也就是 1 加 2 加到 100 所得到的結果, 如果您連這段 WHILE 迴圈也看不懂時, 建議要先學習一下程式設計理論相關的書籍, 因為這可以最基本的寫法啊! WHILE 的跳離時機當然, 除了將條件句寫在 WHILE 指令後面之外, 你也可以在之中控制迴圈何時會結束, 你可以利用 BREAK 強制跳出迴圈, 或是利用 CONTINUE 繼續迴圈的執行程式三即是從 Help 中所抓出來的程式 : USE pubs GO WHILE (SELECT AVG(price) FROM titles) < $30 UPDATE titles SET price = price * 2 SELECT MAX(price) FROM titles IF (SELECT MAX(price) FROM titles) > $50 ELSE BREAK CONTINUE PRINT 'Too much for the market to bear' 程式三 : 用 BREAK 與 CONTINUE 的程式程式三中, 可以發現, 迴圈何時會進去執行? 答案是當 SELECT AVG(price) FROM titles) < $30 的時候即會進入迴圈, 亦即有可能迴圈一次也不會進入 ( 因為一開始就大於等於 $30 也說不定 ), 那迴圈何時會跳離呢? 時機有兩點, 一為 IF(SELECT MAX(price) FROM titles)>$50 時就會跳離迴圈, 另外一個則是 SELECT AVG(price) FROM titles) >= $30 時就會跳離迴圈, 總共有兩個跳出點, 所以這類的程式如果你沒有很仔細看的話, 會不知程式如何跳離 WHILE 的使用時機了解 WHILE 指令寫法之後, 有人可能會問 : 什麼時候會用 WHILE 指令? 其實這問題很困難, 因為寫程式無法列出全部可能使用的情境, 如果可以這樣做的話, 那大家都會寫程式了所以在這裡, 僅提供兩種可能會使用 WHILE 的時機供各位參考, 其實寫程式萬變不離其宗, 差別只在語法該如何使用罷了假設有一個字串是 ' 北門街, 復興北路, 一女中,', 你希望將他們變成 3

4 北門街復興北路一女中這樣子的話 ( 對轉換文字到 SQL Server 很有幫助啊 ), 這時就必需要用到 WHILE 指令, 相關程式如程式四 varchar(50) int varchar(20) = ' 北門街, 復興北路, 一女中,' = '' while (@varlen<=len(@var1)) begin if substring(@var1,@varlen,1) = ',' else end begin end begin end = '' + substring(@var1,@varlen,1) + 1 程式四 : 利用 WHILE 指令來分字串程式四中, 先定義一個字串, 用來儲存想要轉換的字串, 而我們要做的動作就是由左邊字串, 以一次移動一格的方式往右前進, 一次取出一個字, 取到什麼時候呢? 取到整個字串都被取完為止這個變數, 就是每次會加 1, 直到到達整個字串的長度為止如果取出的字串不是逗號, 這個變數中, 如果取出的值是逗號, 值清空所以程式四的程式就完成了首先 WHILE 指令後面接 (@varlen<=len(@var1)), 的長度 ( 用 len 這個函數取出該字串的長度 ), 一開始一定是可以進入迴圈的, 進了迴圈再利用 if substring(@var1,@varlen,1) = ',' 判斷是否某一個位元是逗點, 這裡用 substring 變數中, 位置的 1 個位元, 這個值是每次都會變動 ( 下方有 + 1), 所以感覺起來很像每次往右移動一格, 變數為止而若是等於逗點, 則將資料印出, 並且利用 = '' 清空如果不等於逗點的話, 則利用 + substring(@var1,@varlen,1) 4

5 最後所得的結果就是將這些字串的逗點去掉, 然後將值取出 CURSOR 的使用最後, 來看一下利用 WHILE 來完成 SQL Server 的禁斷招術吧! SQL Server 有一種古老的程式撰寫方式, 稱為 CURSOR( 游標 ), 主要是在伺服器端產生資料列, 經過一番處理之後再傳到前端的工作稱之此招術已經失傳了許久, 原因無它, 因為大多數的前端開發工具都用它的變種,Client CURSOR 來處理資料各位有撰寫過 VB 的 ADO 程式都有個印象, 利用 Recordset 物件取回資料庫中的資料列, 然後利用 MoveNext() 下一筆下一筆的將資料取出, 事實上這個就是 CURSOR, 只不過它是叫做 Client CURSOR, 將資料列取回到前端再由前端所做的動作我們現在所撰寫的是 Server CURSOR, 也就是資料其實是在 SQL Server 端做處理, 其優點可以一列一列做處理資料的動作, 達到一般無法容易撰寫的程式, 我們先來看看程式五 : USE pubs DECLARE tnames_cursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES OPEN tnames_cursor sysname FETCH NEXT FROM tnames_cursor WHILE (@@FETCH_STATUS <> -1) FETCH NEXT FROM tnames_cursor CLOSE tnames_cursor DEALLOCATE tnames_cursor 程式五 : 利用 CURSOR 取回資料列資料程式五中, 你必需宣告一個變數資料型別是 CURSOR, 的符號, 只是需要再用 FOR 後面接一個 SELECT 的句子這時,SQL Server 會將 SELECT 的查詢結果放置在記憶體中, 等待我們的使用然後, 利用 OPEN 指令開始 CURSOR, 再宣告一個變數, 資料型別是 sysname, 代表要接收 CURSOR 所傳回來的值, 由於值還不確定, 所以並不需要定義資料型別是整數或是字串, 只需定義為 sysname 即可再來, 利用 FETCH NEXT FROM 的指令, 將 CURSOR 變數中, 此時你已經取得了第一筆記錄接著, 利用 WHILE 指令, 後面接 (@@FETCH_STATUS <> -1), 代表如果 CUROSR 不是最後一筆的話, 則進入迴圈 ( 因為若是最後一筆, 的值會等於 -1), 我們要一筆一筆將 CURSOR 的值取出然後, 將資料一個一個用 print 印出來, 再利用 FETCH NEXT FROM 指令取得下一筆資料如果已經沒有值了, 即跳出迴圈, 光這一段, 你以前用過 VB 的程式設計師應該不陌生, 你可能是這樣寫的 while not rs.eof() rs.movenext loop 只是我們將這段, 搬到 SQL Server 上撰寫罷了 5

CUROSR 呼叫完了, 接下來別忘了將 CURSOR 關閉, 利用 CLOSE tnames_cursor 然後再將 CURSOR 從記憶體釋放掉, 利用 DEALLOCATE tnames_cursor 整支程式即大功告成最後, 整個查詢結果就像圖一, 利用 WHILE 指令將資料一列列取回圖一 : 利用

WHILE 算是基本的功能之一最新 Pocket PC 程式設計自學手冊第一本針對 Pocket PC 專案開發者所撰寫的 MFC 程式設計書籍, 內容涵蓋許多開發 Pocket PC 應用程式所需具備的重要觀念, 書中範例強調簡單實務性高容易上手, 只要是對 Pocket PC

6 CUROSR 呼叫完了, 接下來別忘了將 CURSOR 關閉, 利用 CLOSE tnames_cursor 然後再將 CURSOR 從記憶體釋放掉, 利用 DEALLOCATE tnames_cursor 整支程式即大功告成最後, 整個查詢結果就像圖一, 利用 WHILE 指令將資料一列列取回圖一 : 利用 CURSOR 將程式取回的結果當然, 使用 CURSOR, 可以幫助我們做非常多的事, 至少利用 UPDATE SELECT 等指令無法容易達到一列一列做資料取回, 並且修改的動作, 而利用 CURSOR 就可以方便我們對一列一列資料的處理所以大家要練習 WHILE 指令, 因為對寫 SQL 程式來說, 使用 WHILE 算是基本的功能之一最新 Pocket PC 程式設計自學手冊第一本針對 Pocket PC 專案開發者所撰寫的 MFC 程式設計書籍, 內容涵蓋許多開發 Pocket PC 應用程式所需具備的重要觀念, 書中範例強調簡單實務性高容易上手, 只要是對 Pocket PC 商業應用系統開發有興趣的專業人士, 都可從本書精闢的介紹中了解箇中技術作者 : 恆逸資訊楊先民出版 : 學貫行銷股份有限公司 ISBN: 定價 :NT.$ 550 頁數 :464 頁楊先民曾開發多項專案, 參與微軟多項大型研討會, 目前為恆逸資訊專任講師, 著有實戰 Pocket PC 程式設計 ( 學貫行銷出版 ) 一書 6

ASP.NET密技集錦(C#)-I (N )

ASP.NET密技集錦(C#)-I (N ) ASP.NET 密技集錦 (C#)-I 2002 年 11 月號作者 : 許嘉仁審稿 : 許薰尹文章編號 :N021101002 Server Control 使用 Client 端 Script 難易度 : 易相關資源與開發工具 : 1. Visual Studio.NET 2. ASP.NET 3. C#.NET 難 Server Control 的好用, 大家都很清楚了, 但是結合 Client