一個SQL游標(biāo)的應(yīng)用實例
2014-11-14 10:34:27戴晴宜
科技資訊 2014年12期
戴晴宜
摘 要：關(guān)系數(shù)據(jù)庫中的操作會對整個行集起作用，應(yīng)用程序，特別是交互式聯(lián)機應(yīng)用程序，并不總能將整個結(jié)果集作為一個單元來有效地處理。這些應(yīng)用程序需要一種機制以便每次處理一行或一部分行。游標(biāo)就是提供這種機制的對結(jié)果集的一種擴展。本文通過一個實例，介紹了SQL存儲過程中游標(biāo)的使用。
關(guān)鍵詞：SQL游標(biāo) 游標(biāo)嵌套 試用權(quán)限
中圖分類號：TP311 文獻標(biāo)識碼：A 文章編號：1672-3791（2014）04（c）-0026-02
二次文獻共建共享網(wǎng)絡(luò)服務(wù)平臺（以下簡稱平臺）是《全國報刊索引》編輯部一個網(wǎng)絡(luò)服務(wù)平臺，它利用現(xiàn)代化信息技術(shù)和服務(wù)手段，聯(lián)合海內(nèi)外廣大圖情界同仁，在特色資源、近代文獻等珍貴文獻搶救和發(fā)掘方面合作共建，為廣大讀者用戶提供良好的信息參考和決策咨詢服務(wù)，也為海內(nèi)外研究國學(xué)的人士獲取信息提供更多的便利。目前，《全國報刊索引》編輯部已擁有全文數(shù)據(jù)庫、索引數(shù)據(jù)庫、專題數(shù)據(jù)庫和特色資源數(shù)據(jù)庫四種類型數(shù)據(jù)庫。[1]
在平臺的運維工作中，經(jīng)常會收到服務(wù)組提出的技術(shù)支持請求。有一次，為了配合數(shù)據(jù)庫產(chǎn)品的推廣活動，服務(wù)組希望技術(shù)部協(xié)助為一批試用用戶賬號開放產(chǎn)品試用權(quán)限。
平臺的文獻數(shù)據(jù)庫產(chǎn)品結(jié)構(gòu)如圖1所示，分為三層：產(chǎn)品大類（如全文庫、索引庫等）、產(chǎn)品類（如晚清期刊全文數(shù)據(jù)庫（1833～1911）、民國時期期刊全文數(shù)據(jù)庫（1911～1949）等）、產(chǎn)品（如遐邇貫珍、點石齋畫報等）。這次收到的要求是，按不同需要分配產(chǎn)品權(quán)限：如單個產(chǎn)品的試用、單個產(chǎn)品類的試用。
按照慣例，類似這種臨時性的數(shù)據(jù)處理請求，以SQL存儲過程的方式來完成數(shù)據(jù)處理的相關(guān)任務(wù)，再根據(jù)實際需要，評估是否需要將該功能加到平臺應(yīng)用中。
1 單個產(chǎn)品的試用權(quán)限
這里要做的是為每一個試用賬號在產(chǎn)品試用表中添加一條記錄，由于需要對試用賬號這個查詢結(jié)果集進行逐行處理，因此，需要使用一個游標(biāo)來實現(xiàn)，具體如下：
……
DECLARE @trid INT
DECLARE @cp INT
DECLARE usr CURSOR FOR
SELECT
trial_id
FROM trialusr
OPEN usr
FETCH NEXT FROM usr INTO @trid
WHILE @@fetch_status = 0
BEGIN
SET @cp = X /* X為任意數(shù)字 */
BEGIN TRANSACTION；
INSERT INTO trialcp（trial_id，cp，start，end）
VALUES（@trid，@cp，GETDATE（），（GETDATE（）+7））
COMMIT；
FETCH NEXT FROM usr INTO @trid
END
CLOSE usr
DEALLOCATE usr
……
2 單個產(chǎn)品類的試用權(quán)限
在這個需求中，有2個結(jié)果集，一個是試用賬號，另一個是產(chǎn)品類中的產(chǎn)品id，并且兩者都需要進行逐行處理，以實現(xiàn)一對一的關(guān)系，因此考慮使用2個游標(biāo)，并且嵌套使用，具體如下：
……
DECLARE @trid INT
DECLARE @cpl varchar（50）
DECLARE @cp INT
SET @cpl=捪摯饕？1950～）？
DECLARE usr CURSOR FOR
SELECT
trial_id
FROM trialusr
OPEN usr
FETCH NEXT FROM usr INTO @trid
WHILE @@fetch_status = 0
BEGIN
BEGIN
DECLARE cur_cp CURSOR FOR
SELECT cpid
FROM cplist
WHERE cpl = @cpl
OPEN cur_cp
FETCH NEXT FROM cur_cp INTO @cp
WHILE @@fetch_status = 0
BEGIN
BEGIN TRANSACTION；
INSERT INTO trialcp（trial_id，cp，start，end）
VALUES（@trid，@cp，GETDATE（），（GETDATE（）+7））
COMMIT；
FETCH NEXT FROM cur_cp INTO @cp
END
CLOSE cur_cp
DEALLOCATE cur_cp
FETCH NEXT FROM usr INTO @trid
END
CLOSE usr
DEALLOCATE usr
……
以上，是根據(jù)服務(wù)組提出的技術(shù)支持需求，形成的解決方案，在測試數(shù)據(jù)庫中測試及調(diào)試后，證實該方案可行，并準(zhǔn)確實現(xiàn)了服務(wù)組的要求。
3 結(jié)語
關(guān)系數(shù)據(jù)庫中的操作會對整個行集起作用。由SELECT語句返回的行集包括滿足該語句的WHERE子句中條件的所有行。這種由語句返回的完整行集稱為結(jié)果集。應(yīng)用程序，特別是交互式聯(lián)機應(yīng)用程序，并不總能將整個結(jié)果集作為一個單元來有效地處理。這些應(yīng)用程序需要一種機制以便每次處理一行或一部分行。游標(biāo)就是提供這種機制的對結(jié)果集的一種擴展。[2]
本次技術(shù)支持任務(wù)中，嘗試了通過游標(biāo)的使用，來實現(xiàn)批量數(shù)據(jù)的逐行處理。在數(shù)據(jù)處理的工作中，經(jīng)常會遇到類似對數(shù)據(jù)集進行逐行處理的任務(wù)，希望本實例，可以為SQL的初學(xué)者提供參考。
參考文獻
[1] 《全國報刊索引》二次文獻共建共享網(wǎng)絡(luò)服務(wù)平臺數(shù)據(jù)庫資源介紹[EB/OL].http：//www.cnbksy.com/shlib_tsdc/product/list.do.
[2] MSDN—Transact-SQL 參考：游標(biāo)（數(shù)據(jù)庫引擎）[EB/OL].http：//msdn.microsoft.com/zh-cn/library/ms191179（v=sql.105）.aspx.endprint
摘 要：關(guān)系數(shù)據(jù)庫中的操作會對整個行集起作用，應(yīng)用程序，特別是交互式聯(lián)機應(yīng)用程序，并不總能將整個結(jié)果集作為一個單元來有效地處理。這些應(yīng)用程序需要一種機制以便每次處理一行或一部分行。游標(biāo)就是提供這種機制的對結(jié)果集的一種擴展。本文通過一個實例，介紹了SQL存儲過程中游標(biāo)的使用。
關(guān)鍵詞：SQL游標(biāo) 游標(biāo)嵌套 試用權(quán)限
中圖分類號：TP311 文獻標(biāo)識碼：A 文章編號：1672-3791（2014）04（c）-0026-02
二次文獻共建共享網(wǎng)絡(luò)服務(wù)平臺（以下簡稱平臺）是《全國報刊索引》編輯部一個網(wǎng)絡(luò)服務(wù)平臺，它利用現(xiàn)代化信息技術(shù)和服務(wù)手段，聯(lián)合海內(nèi)外廣大圖情界同仁，在特色資源、近代文獻等珍貴文獻搶救和發(fā)掘方面合作共建，為廣大讀者用戶提供良好的信息參考和決策咨詢服務(wù)，也為海內(nèi)外研究國學(xué)的人士獲取信息提供更多的便利。目前，《全國報刊索引》編輯部已擁有全文數(shù)據(jù)庫、索引數(shù)據(jù)庫、專題數(shù)據(jù)庫和特色資源數(shù)據(jù)庫四種類型數(shù)據(jù)庫。[1]
在平臺的運維工作中，經(jīng)常會收到服務(wù)組提出的技術(shù)支持請求。有一次，為了配合數(shù)據(jù)庫產(chǎn)品的推廣活動，服務(wù)組希望技術(shù)部協(xié)助為一批試用用戶賬號開放產(chǎn)品試用權(quán)限。
平臺的文獻數(shù)據(jù)庫產(chǎn)品結(jié)構(gòu)如圖1所示，分為三層：產(chǎn)品大類（如全文庫、索引庫等）、產(chǎn)品類（如晚清期刊全文數(shù)據(jù)庫（1833～1911）、民國時期期刊全文數(shù)據(jù)庫（1911～1949）等）、產(chǎn)品（如遐邇貫珍、點石齋畫報等）。這次收到的要求是，按不同需要分配產(chǎn)品權(quán)限：如單個產(chǎn)品的試用、單個產(chǎn)品類的試用。
按照慣例，類似這種臨時性的數(shù)據(jù)處理請求，以SQL存儲過程的方式來完成數(shù)據(jù)處理的相關(guān)任務(wù)，再根據(jù)實際需要，評估是否需要將該功能加到平臺應(yīng)用中。
1 單個產(chǎn)品的試用權(quán)限
這里要做的是為每一個試用賬號在產(chǎn)品試用表中添加一條記錄，由于需要對試用賬號這個查詢結(jié)果集進行逐行處理，因此，需要使用一個游標(biāo)來實現(xiàn)，具體如下：
……
DECLARE @trid INT
DECLARE @cp INT
DECLARE usr CURSOR FOR
SELECT
trial_id
FROM trialusr
OPEN usr
FETCH NEXT FROM usr INTO @trid
WHILE @@fetch_status = 0
BEGIN
SET @cp = X /* X為任意數(shù)字 */
BEGIN TRANSACTION；
INSERT INTO trialcp（trial_id，cp，start，end）
VALUES（@trid，@cp，GETDATE（），（GETDATE（）+7））
COMMIT；
FETCH NEXT FROM usr INTO @trid
END
CLOSE usr
DEALLOCATE usr
……
2 單個產(chǎn)品類的試用權(quán)限
在這個需求中，有2個結(jié)果集，一個是試用賬號，另一個是產(chǎn)品類中的產(chǎn)品id，并且兩者都需要進行逐行處理，以實現(xiàn)一對一的關(guān)系，因此考慮使用2個游標(biāo)，并且嵌套使用，具體如下：
……
DECLARE @trid INT
DECLARE @cpl varchar（50）
DECLARE @cp INT
SET @cpl=捪摯饕？1950～）？
DECLARE usr CURSOR FOR
SELECT
trial_id
FROM trialusr
OPEN usr
FETCH NEXT FROM usr INTO @trid
WHILE @@fetch_status = 0
BEGIN
BEGIN
DECLARE cur_cp CURSOR FOR
SELECT cpid
FROM cplist
WHERE cpl = @cpl
OPEN cur_cp
FETCH NEXT FROM cur_cp INTO @cp
WHILE @@fetch_status = 0
BEGIN
BEGIN TRANSACTION；
INSERT INTO trialcp（trial_id，cp，start，end）
VALUES（@trid，@cp，GETDATE（），（GETDATE（）+7））
COMMIT；
FETCH NEXT FROM cur_cp INTO @cp
END
CLOSE cur_cp
DEALLOCATE cur_cp
FETCH NEXT FROM usr INTO @trid
END
CLOSE usr
DEALLOCATE usr
……
以上，是根據(jù)服務(wù)組提出的技術(shù)支持需求，形成的解決方案，在測試數(shù)據(jù)庫中測試及調(diào)試后，證實該方案可行，并準(zhǔn)確實現(xiàn)了服務(wù)組的要求。
3 結(jié)語
關(guān)系數(shù)據(jù)庫中的操作會對整個行集起作用。由SELECT語句返回的行集包括滿足該語句的WHERE子句中條件的所有行。這種由語句返回的完整行集稱為結(jié)果集。應(yīng)用程序，特別是交互式聯(lián)機應(yīng)用程序，并不總能將整個結(jié)果集作為一個單元來有效地處理。這些應(yīng)用程序需要一種機制以便每次處理一行或一部分行。游標(biāo)就是提供這種機制的對結(jié)果集的一種擴展。[2]
本次技術(shù)支持任務(wù)中，嘗試了通過游標(biāo)的使用，來實現(xiàn)批量數(shù)據(jù)的逐行處理。在數(shù)據(jù)處理的工作中，經(jīng)常會遇到類似對數(shù)據(jù)集進行逐行處理的任務(wù)，希望本實例，可以為SQL的初學(xué)者提供參考。
參考文獻
[1] 《全國報刊索引》二次文獻共建共享網(wǎng)絡(luò)服務(wù)平臺數(shù)據(jù)庫資源介紹[EB/OL].http：//www.cnbksy.com/shlib_tsdc/product/list.do.
[2] MSDN—Transact-SQL 參考：游標(biāo)（數(shù)據(jù)庫引擎）[EB/OL].http：//msdn.microsoft.com/zh-cn/library/ms191179（v=sql.105）.aspx.endprint
摘 要：關(guān)系數(shù)據(jù)庫中的操作會對整個行集起作用，應(yīng)用程序，特別是交互式聯(lián)機應(yīng)用程序，并不總能將整個結(jié)果集作為一個單元來有效地處理。這些應(yīng)用程序需要一種機制以便每次處理一行或一部分行。游標(biāo)就是提供這種機制的對結(jié)果集的一種擴展。本文通過一個實例，介紹了SQL存儲過程中游標(biāo)的使用。
關(guān)鍵詞：SQL游標(biāo) 游標(biāo)嵌套 試用權(quán)限
中圖分類號：TP311 文獻標(biāo)識碼：A 文章編號：1672-3791（2014）04（c）-0026-02
二次文獻共建共享網(wǎng)絡(luò)服務(wù)平臺（以下簡稱平臺）是《全國報刊索引》編輯部一個網(wǎng)絡(luò)服務(wù)平臺，它利用現(xiàn)代化信息技術(shù)和服務(wù)手段，聯(lián)合海內(nèi)外廣大圖情界同仁，在特色資源、近代文獻等珍貴文獻搶救和發(fā)掘方面合作共建，為廣大讀者用戶提供良好的信息參考和決策咨詢服務(wù)，也為海內(nèi)外研究國學(xué)的人士獲取信息提供更多的便利。目前，《全國報刊索引》編輯部已擁有全文數(shù)據(jù)庫、索引數(shù)據(jù)庫、專題數(shù)據(jù)庫和特色資源數(shù)據(jù)庫四種類型數(shù)據(jù)庫。[1]
在平臺的運維工作中，經(jīng)常會收到服務(wù)組提出的技術(shù)支持請求。有一次，為了配合數(shù)據(jù)庫產(chǎn)品的推廣活動，服務(wù)組希望技術(shù)部協(xié)助為一批試用用戶賬號開放產(chǎn)品試用權(quán)限。
平臺的文獻數(shù)據(jù)庫產(chǎn)品結(jié)構(gòu)如圖1所示，分為三層：產(chǎn)品大類（如全文庫、索引庫等）、產(chǎn)品類（如晚清期刊全文數(shù)據(jù)庫（1833～1911）、民國時期期刊全文數(shù)據(jù)庫（1911～1949）等）、產(chǎn)品（如遐邇貫珍、點石齋畫報等）。這次收到的要求是，按不同需要分配產(chǎn)品權(quán)限：如單個產(chǎn)品的試用、單個產(chǎn)品類的試用。
按照慣例，類似這種臨時性的數(shù)據(jù)處理請求，以SQL存儲過程的方式來完成數(shù)據(jù)處理的相關(guān)任務(wù)，再根據(jù)實際需要，評估是否需要將該功能加到平臺應(yīng)用中。
1 單個產(chǎn)品的試用權(quán)限
這里要做的是為每一個試用賬號在產(chǎn)品試用表中添加一條記錄，由于需要對試用賬號這個查詢結(jié)果集進行逐行處理，因此，需要使用一個游標(biāo)來實現(xiàn)，具體如下：
……
DECLARE @trid INT
DECLARE @cp INT
DECLARE usr CURSOR FOR
SELECT
trial_id
FROM trialusr
OPEN usr
FETCH NEXT FROM usr INTO @trid
WHILE @@fetch_status = 0
BEGIN
SET @cp = X /* X為任意數(shù)字 */
BEGIN TRANSACTION；
INSERT INTO trialcp（trial_id，cp，start，end）
VALUES（@trid，@cp，GETDATE（），（GETDATE（）+7））
COMMIT；
FETCH NEXT FROM usr INTO @trid
END
CLOSE usr
DEALLOCATE usr
……
2 單個產(chǎn)品類的試用權(quán)限
在這個需求中，有2個結(jié)果集，一個是試用賬號，另一個是產(chǎn)品類中的產(chǎn)品id，并且兩者都需要進行逐行處理，以實現(xiàn)一對一的關(guān)系，因此考慮使用2個游標(biāo)，并且嵌套使用，具體如下：
……
DECLARE @trid INT
DECLARE @cpl varchar（50）
DECLARE @cp INT
SET @cpl=捪摯饕？1950～）？
DECLARE usr CURSOR FOR
SELECT
trial_id
FROM trialusr
OPEN usr
FETCH NEXT FROM usr INTO @trid
WHILE @@fetch_status = 0
BEGIN
BEGIN
DECLARE cur_cp CURSOR FOR
SELECT cpid
FROM cplist
WHERE cpl = @cpl
OPEN cur_cp
FETCH NEXT FROM cur_cp INTO @cp
WHILE @@fetch_status = 0
BEGIN
BEGIN TRANSACTION；
INSERT INTO trialcp（trial_id，cp，start，end）
VALUES（@trid，@cp，GETDATE（），（GETDATE（）+7））
COMMIT；
FETCH NEXT FROM cur_cp INTO @cp
END
CLOSE cur_cp
DEALLOCATE cur_cp
FETCH NEXT FROM usr INTO @trid
END
CLOSE usr
DEALLOCATE usr
……
以上，是根據(jù)服務(wù)組提出的技術(shù)支持需求，形成的解決方案，在測試數(shù)據(jù)庫中測試及調(diào)試后，證實該方案可行，并準(zhǔn)確實現(xiàn)了服務(wù)組的要求。
3 結(jié)語
關(guān)系數(shù)據(jù)庫中的操作會對整個行集起作用。由SELECT語句返回的行集包括滿足該語句的WHERE子句中條件的所有行。這種由語句返回的完整行集稱為結(jié)果集。應(yīng)用程序，特別是交互式聯(lián)機應(yīng)用程序，并不總能將整個結(jié)果集作為一個單元來有效地處理。這些應(yīng)用程序需要一種機制以便每次處理一行或一部分行。游標(biāo)就是提供這種機制的對結(jié)果集的一種擴展。[2]
本次技術(shù)支持任務(wù)中，嘗試了通過游標(biāo)的使用，來實現(xiàn)批量數(shù)據(jù)的逐行處理。在數(shù)據(jù)處理的工作中，經(jīng)常會遇到類似對數(shù)據(jù)集進行逐行處理的任務(wù)，希望本實例，可以為SQL的初學(xué)者提供參考。
參考文獻
[1] 《全國報刊索引》二次文獻共建共享網(wǎng)絡(luò)服務(wù)平臺數(shù)據(jù)庫資源介紹[EB/OL].http：//www.cnbksy.com/shlib_tsdc/product/list.do.
[2] MSDN—Transact-SQL 參考：游標(biāo)（數(shù)據(jù)庫引擎）[EB/OL].http：//msdn.microsoft.com/zh-cn/library/ms191179（v=sql.105）.aspx.endprint
