SQL Server實戰六：T-SQL、遊標、儲存過程的操作

疯狂学习GIS發表於2024-05-10

SQLServer儲存過程

本文介紹基於Microsoft SQL Server軟體，實現資料庫T-SQL語言程式設計，以及遊標的定義、使用與儲存過程的建立、資訊查詢的方法。

1 計算1-100間所有可被3整除的數的個數與總和
2 從學生表S中選取SNO、SN、SEX，若為“男”輸出M，為“女”輸出F
3 面向複雜應用的T-SQL程式設計方法——查詢所有同學選課資訊：姓名、課程名、成績
4 面向複雜應用的T-SQL程式設計方法——為教師增加工資
5 使用遊標——定義遊標Cursor_Famale
6 使用遊標——建立儲存過程Pro_C查詢資訊

系列文章中示例資料來源於《SQL Server實驗指導（2005版）》一書。依據本系列文章的思想與對操作步驟、程式碼的詳細解釋，大家用自己手頭的資料，可以將相關操作與分析過程加以完整重現。

1 計算1-100間所有可被3整除的數的個數與總和

（1）啟動Microsoft SQL Server 2008 R2軟體；

（2）在“物件資源管理器”窗格中，在“資料庫”處右鍵，在彈出的選單中選擇“附加”選項；

（3）選擇需要加以附加的jxsk資料庫物理檔案，選擇定位資料夾“G:\sql\chutianjia sql”並選擇對應資料庫jxsk的物理檔案並選擇“確定”按鈕，再次選擇“確定”即可；

（4）單擊螢幕上方 “工具欄”選單中的“新建查詢”按鈕，開啟“查詢編輯器”視窗，並在“查詢編輯器”視窗中輸入以下T-SQL語句：

DECLARE @SUM SMALLINT, @I SMALLINT,@NUMS SMALLINT
SET @SUM=0
SET @I=1
SET @NUMS=0
WHILE (@I<=100)
BEGIN
IF (@I% 3=0)
BEGIN 
SET @SUM=@SUM+@I
SET @NUMS=@NUMS+1
END
SET @I=@I+1
END
PRINT'總和是'+STR(@SUM)
PRINT'個數是'+STR(@NUMS)

（5）單擊“工具欄”中的“執行(x)”按鈕，即可執行上述T-SQL語句，如下圖；

2 從學生表S中選取SNO、SN、SEX，若為“男”輸出M，為“女”輸出F

（1）單擊螢幕上方 “工具欄”選單中的“新建查詢”按鈕，開啟“查詢編輯器”視窗，並在“查詢編輯器”視窗中輸入以下T-SQL語句：

USE jxsk
GO
SELECT SNO AS 學號, SN AS 姓名,
性別=
CASE SEX
WHEN '男' THEN 'M'
WHEN '女' THEN 'F'
END
FROM S
GO

（2）單擊 “工具欄”中的“執行(x)”按鈕，即可執行上述T-SQL語句，如下圖；

（3）首次執行後發現結果中“性別”一欄均為“NULL”，與預期將達到的結果不一致。透過檢查發現自己的T-SQL語句中出現錯誤，更改後效果如下圖；

3 面向複雜應用的T-SQL程式設計方法——查詢所有同學選課資訊：姓名、課程名、成績

（1）單擊螢幕上方 “工具欄”選單中的“新建查詢”按鈕，開啟“查詢編輯器”視窗，並在“查詢編輯器”視窗中輸入以下T-SQL語句：

USE jxsk
GO
SELECT SN AS 姓名, CN AS 課程名,
成績=
CASE
WHEN SCORE IS NULL THEN '未考'
WHEN SCORE<60 THEN '不及格'
WHEN SCORE>=60 AND SCORE<70 THEN '及格'
WHEN SCORE>=70 AND SCORE<80 THEN '中'
WHEN SCORE>=80 AND SCORE<90 THEN '良好'
WHEN SCORE>=90 THEN '優'
END 
FROM SC,S,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO
ORDER BY S.SNO,C.CNO,SCORE DESC
GO

（2）單擊 “工具欄”中的“執行(x)”按鈕，即可執行上述T-SQL語句，如下圖；

（3）首次執行後發現系統報錯。透過檢查發現自己的T-SQL語句中出現錯誤，即丟失了CASE，更改後效果如下圖；

4 面向複雜應用的T-SQL程式設計方法——為教師增加工資

（1）在“物件資源管理器”中選擇“資料庫”→“jxsk”→“表”→“dbo.T”，右擊並在彈出的視窗中選擇“編輯前200行”；檢視各教師的工資情況；

（2）單擊螢幕上方 “工具欄”選單中的“新建查詢”按鈕，開啟“查詢編輯器”視窗，並在“查詢編輯器”視窗中輸入以下T-SQL語句：

USE jxsk
UPDATE T SET SAL=SAL+
CASE
WHEN T.TNO IN (
SELECT TC.TNO FROM T,TC
WHERE T.TNO=TC.TNO AND (SAL+COMM)>=4000
GROUP BY TC.TNO HAVING COUNT(*)>=2) THEN 300
WHEN T.TNO IN (
SELECT TC.TNO FROM T,TC
WHERE T.TNO=TC.TNO AND (SAL+COMM)>=3000 AND (SAL+COMM)<4000
GROUP BY TC.TNO HAVING COUNT(*)>=2) THEN 200
WHEN T.TNO IN (
SELECT TC.TNO FROM T,TC
WHERE T.TNO=TC.TNO AND (T.SAL+T.COMM<3000)
GROUP BY TC.TNO HAVING COUNT(*)>=2) THEN 100
WHEN T.TNO IN (
SELECT TC.TNO FROM T,TC
WHERE T.TNO=TC.TNO
GROUP BY TC.TNO HAVING COUNT(*)=1) THEN 50
ELSE 0
END
GO

（3）單擊 “工具欄”中的“執行(x)”按鈕，即可執行上述T-SQL語句,如下圖；

（4）首次執行後發現系統報錯。透過系統內部提示，考慮到該錯誤應為上節實驗課所設立的觸發器對該資料庫表修改加以限制，使得語句無法執行；

（5）在“物件資源管理器”中選擇“資料庫”→“jxsk”→“表”→“dbo.T”→“觸發器”，右鍵選中已存在的觸發器，在彈出的對話方塊中選擇“禁用”或“刪除”；考慮到今後實驗可能仍然會使用到這一觸發器，我選擇了“禁用”按鈕，如下圖；

（6）更改完畢後單擊 “工具欄”中的“執行(x)”按鈕，即可執行上述T-SQL語句,如下圖；

（7）在“物件資源管理器”中選擇“資料庫”→“jxsk”→“表”→“dbo.T”，右擊並在彈出的視窗中選擇“編輯前200行”；檢視各教師的工資情況已發生變化，如下圖；

5 使用遊標——定義遊標Cursor_Famale

（1）單擊螢幕上方 “工具欄”選單中的“新建查詢”按鈕，開啟“查詢編輯器”視窗，並在“查詢編輯器”視窗中輸入以下T-SQL語句：

DECLARE @SNO CHAR(6),@SNAME CHAR(8),@SEX CHAR(2),
@AGE TINYINT,@DEPT CHAR(10)
DECLARE CURSOR_FAMALE CURSOR
FOR SELECT SNO,SN,SEX,AGE,DEPT FROM S
OPEN CURSOR_FAMALE
FETCH NEXT FROM CURSOR_FAMALE
INTO @SNO,@SNAME,@SEX,@AGE,@DEPT
WHILE @@FETCH_STATUS=0
BEGIN
IF @SNAME='牛莉'
BEGIN
PRINT '找到牛莉的資訊如下'
PRINT @SNO+''+@SNAME+''+@SEX+''+
CONVERT (CHAR(2),@AGE)+''+@DEPT
BREAK
END
FETCH NEXT FROM CURSOR_FAMALE
INTO @SNO,@SNAME,@SEX,@AGE,@DEPT
END
IF @@FETCH_STATUS !=0
PRINT '很抱歉，沒有找到牛莉的資訊！'
CLOSE CURSOR_FAMALE
DEALLOCATE CURSOR_FAMALE

（2）單擊 “工具欄”中的“執行(x)”按鈕，即可執行上述T-SQL語句，如下圖；

（3）由於我的資料庫表S中並沒有學生“牛莉”的資訊，因此在執行上述語言後系統提示“很抱歉，沒有找到牛莉的資訊！”；

（4）對資料庫表S中資訊加以修改，增添學生“牛莉”的資訊後，單擊 “工具欄”中的“執行(x)”按鈕，即可執行上述T-SQL語句，如下圖；

6 使用遊標——建立儲存過程Pro_C查詢資訊

（1）單擊螢幕上方 “工具欄”選單中的“新建查詢”按鈕，開啟“查詢編輯器”視窗，並在“查詢編輯器”視窗中輸入以下T-SQL語句：

CREATE PROCEDURE PRO_C @C_CURSOR CURSOR VARYING OUTPUT
AS
SET @C_CURSOR = CURSOR
FOR
SELECT SNAME,SCORE FROM STUDENT,SC,COURSE
WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND COURSE.CNAME='資料庫'
OPEN @C_CURSOR

（2）單擊 “工具欄”中的“執行(x)”按鈕，即可執行上述T-SQL語句，如下圖；

（3）首次執行後發現系統報錯。透過系統內部提示，考慮到該錯誤應為@符號後的空格導致，修改後單擊 “工具欄”中的“執行(x)”按鈕，即可執行上述T-SQL語句，如下圖；

（4）單擊螢幕上方 “工具欄”選單中的“新建查詢”按鈕，開啟“查詢編輯器”視窗，並在“查詢編輯器”視窗中輸入以下T-SQL語句：

DECLARE @MYCURSOR CURSOR
DECLARE @NAME VARCHAR(30)
DECLARE @IN_NAME CHAR(8)
DECLARE @SCORE INT
SELECT @IN_NAME = '王一山'
EXECUTE PRO_C @C_CURSOR = @MYCURSOR OUTPUT
FETCH NEXT FROM @MYCURSOR INTO @NAME,@SCORE
WHILE (@@FETCH_STATUS=0)
BEGIN
IF @NAME=@IN_NAME
BEGIN
PRINT @NAME+'選修了資料庫課程，成績是：'+CONVERT(CHAR(2), @SCORE)
BREAK
END
FETCH NEXT FROM @MYCURSOR INTO @NAME, @SCORE
END
IF (@@FETCH_STATUS!=0)
PRINT @IN_NAME+'沒有選修資料庫課程。'
CLOSE @MYCURSOR
DEALLOCATE @MYCURSOR

（5）單擊 “工具欄”中的“執行(x)”按鈕，即可執行上述T-SQL語句，如下圖；

（6）首次執行後發現系統報錯。在語句處搜尋、更改但仍有錯誤，且錯誤甚至越來越多，如下圖；

（7）此時利用系統錯誤提示，考慮到可能是上述儲存過程Pro_C構建出現錯誤；返回並對這一步驟加以檢查，發現其T-SQL語言中資料庫表與我個人資料庫表名稱、列名有不一致的地方，對其加以修改並單擊 “工具欄”中的“執行(x)”按鈕，即可執行上述T-SQL語句，如下圖；其中，修改之後的T-SQL語言為：

CREATE PROCEDURE PRO_C @C_CURSOR CURSOR VARYING OUTPUT
AS
SET @C_CURSOR = CURSOR
FOR
SELECT SN,SCORE FROM S,SC,C
WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND C.CN='資料庫'
OPEN @C_CURSOR